Web Analytics Made Easy -
StatCounter Search mysql problems - CodingForum

Announcement

Collapse
No announcement yet.

Search mysql problems

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Search mysql problems

    I'm still having problems trying to get a search script working the way I want it. Basically, I have a form where users enter the search type, enter the text to be searched for, and this in turn is passed to the php script, which should return the results, but also have hyperlinks at the top of selected fields, whereby if you click on them it will re-order the results (say alphabetical order of surname).

    At the moment, I am able to do a search with no problems, but cannot work out how to allow for wildcards in the search text (say, entering "A%" in the type "Surname" will return all results where the surname begins with A). Nor can I get the re-order links to work - if I click on them I get a "No results".

    I'm fairly new to php, so am really struggling at the moment. Heres the form (searchdb.htm) and the php script (test.php):

    Form.htm
    PHP Code:
    <html>
    <
    head>
      <
    title>Database search</title>
    </
    head>

    <
    body>

    <
    h1><font face="Arial, Helvetica, sans-serif" color="#333333">Database Search</font></h1>

    <
    form action="test.php" method="post">
            <
    font face="Arial, Helvetica, sans-serif" color="#333333" size="2">Choose
            Search Type
    :</font><br>
            <
    select name="searchtype">
              <
    option value="forename">Forename
              
    <option value="surname">Surname
              
    <option value="category">Category
              
    <option value="title">Title
              
    <option value="shows">Shows
            
    <select>
            <
    br>
            <
    font face="Arial, Helvetica, sans-serif" size="2" color="#333333">Enter
            Search Term
    :</font><br>
            <
    input name="searchterm" type=text>
            <
    br>
            <
    input type=submit value="Search">
          </
    form>

    </
    body>
    </
    html

    Test.php
    PHP Code:
    <?php
    $default_sort 
    'surname';
    $allowed_order = array ('sex''surname','forename');

    if(
    $_GET['order']) $order $_GET['order'];
    else 
    $order $default_sort;

    mysql_connect ('xxxx','xxxx','xxxx');
    mysql_select_db ('contacts');

    $column $_POST['searchtype'];
    $term $_POST['searchterm'];

    $query "SELECT * FROM admin_contacts WHERE $column LIKE '%$term%' ORDER BY $order";
    $result mysql_query ($query);

    $numrows mysql_num_rows($result);
    if (
    $numrows == 0) {
        echo 
    "No data to display!";
        exit;
    }

    $row mysql_fetch_assoc ($result);
    echo 
    "<TABLE border=1>\n";
    echo 
    "<TR>\n";
    foreach (
    $row as $heading=>$column) {
        echo 
    "<TD><b>";
        if (
    in_array ($heading$allowed_order)) {
            echo 
    "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
        } else {
            echo 
    $heading;
        }
        echo 
    "</b></TD>\n";
    }
    echo 
    "</TR>\n";

    mysql_data_seek ($result0);
    while (
    $row mysql_fetch_assoc ($result)) {
        echo 
    "<TR>\n";
        foreach (
    $row as $column) {
            echo 
    "<TD>$column</TD>\n";
        }
        echo 
    "</TR>\n";
    }
    echo 
    "</TABLE>\n";
    ?>
    Thanks in advance.

    Pha3dr0n

  • #2
    As far as getting your columns to be sortable, I think you're missing something in the <a href=... tag. You've got to include the column and term in that link, along with your order.

    The fact that you're using the $_POST variable, and that you'll be including it in the url may pose a problem.. I'm not sure. You may want to have it using the $_GET, and your form's method should perhaps be changed to GET as well.

    For the wildcard issue, I thought the % sign was a wildcard. It looks to me as though if 'a' was the search term, 'cat' could be returned in the resultset, since you're looking for %a%

    If your question is if the user enters 'a%', you want this to be treated as anything starting with an 'a', then you're going to have to make some changes to your select statement.

    I'm not quite sure why you're currently including the wildcards, if you want the user to specify them. Give that one some thought.

    Good luck,
    Sadiq.

    Comment


    • #3
      Even though it's not quite relevant to your case, but are you aware of MySql function MATCH().....AGAINST?? I think for such searches it gives a better result (as it's used in this forum too) than simply asking your client to learn the wildcards.

      Nontheless if you still wana use your wildcars, as sad69 mentioned you have to remove the wildcards you're using yourself in the query and only let the client enters it himself.

      Comment


      • #4
        I see what you mean about the wildcards - its sorted

        Still havent a clue about getting the results to sort into order though.

        Comment


        • #5
          I don't understand that sort thing, you mean for example if you have to records containing your search word, but the 2nd record has 3 samples of the search word and the 1st record only has one sample, then you want the 2nd record comes first? If it's so then MATCH() AGAINST() already do it for you.

          Comment

          Working...
          X