SitePoint Sponsor

User Tag List

Results 1 to 23 of 23

Thread: database search

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    database search

    ive created a database and with your help ive created a page that lets me add data to this database.

    however im stuck on creating a page to search this database.

    ive searched google and tryed various php scripts ive found but none seem to work.

    my database simply contains customers details such as name, address, phone number etc,

    can someone recomend a script that does this that works

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    which database?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    which database?
    er SQL ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    er, you mean microsoft sql server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    er, you mean microsoft sql server?
    no didnt think it was microsoft, its a linux server and in cpanel i use phpmyadmin and mysqldatabase wizard

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    ah

    your database system is called mysql (see mysql.com)

    go into phpmyadmin, go to the SQL tab, and run this query --
    Code:
    SHOW CREATE TABLE yourtablename
    meanwhile, we'll get this thread moved to the mysql forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    im getting the following error

    Parse error: syntax error, unexpected T_VARIABLE in /home/public_html/search7.php on line 22
    PHP Code:
    <html>
    <head>
    <title>designplace.org search script</title>
    <meta name="author" content="Steve R, http://www.designplace.org/">

    </head>
    <body>

    <form name="form" action="search.php" method="get">
      <input type="text" name="q" />
      <input type="submit" name="Submit" value="Search" />
    </form>

    <?php

      
    // Get the search variable from URL

      
    $var = @$_GET['q'] ;
      
    $trimmed trim($var//trim whitespace from the stored variable

    // rows to return
    $limit=10

    // check for an empty string and display a message.
    if ($trimmed == "") ;
      {
      echo 
    "<p>Please enter a search...</p>";
      exit;
      }

    // check for a search parameter
    if (!isset($var))
      {
      echo 
    "<p>We dont seem to have a search parameter!</p>";
      exit;
      }

    //connect to your database ** EDIT REQUIRED HERE **
    mysql_connect("localhost","david","dave"); //(host, username, password)

    //specify database ** EDIT REQUIRED HERE **
    mysql_select_db("customers") or die("Unable to select database"); //select which database we're using

    // Build SQL Query  
    $query "select * from the_table where 1st_field like \"%$trimmed%\"  
      order by 1st_field"
    // EDIT HERE and specify your table and field names for the SQL query

     
    $numresults=mysql_query($query);
     
    $numrows=mysql_num_rows($numresults);

    // If we have no results, offer a google search as an alternative

    if ($numrows == 0)
      {
      echo 
    "<h4>Results</h4>";
      echo 
    "<p>Sorry, your search: &quot;" $trimmed "&quot; returned zero results</p>";

    // google
     
    echo "<p><a href=\"http://www.google.com/search?q=" 
      
    $trimmed "\" target=\"_blank\" title=\"Look up 
      " 
    $trimmed " on Google\">Click here</a> to try the 
      search on google</p>"
    ;
      }

    // next determine if s has been passed to script, if not use 0
      
    if (empty($s)) {
      
    $s=0;
      }

    // get results
      
    $query .= " limit $s,$limit";
      
    $result mysql_query($query) or die("Couldn't execute query");

    // display what the person searched for
    echo "<p>You searched for: &quot;" $var "&quot;</p>";

    // begin to show results set
    echo "Results";
    $count $s ;

    // now you can display the results returned
      
    while ($rowmysql_fetch_array($result)) {
      
    $title $row["1st_field"];

      echo 
    "$count.)&nbsp;$title;
      
    $count++ ;
      }

    $currPage = (($s/$limit) + 1);

    //break before paging
      
    echo "<br />";

      
    // next we need to do the links to other results
      
    if ($s>=1) { // bypass PREV link if s is 0
      
    $prevs=($s-$limit);
      print 
    "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
      Prev 10</a>&nbsp&nbsp;"
    ;
      }

    // calculate number of pages needing links
      
    $pages=intval($numrows/$limit);

    // $pages now contains int of pages needed unless there is a remainder from division

      
    if ($numrows%$limit) {
      
    // has remainder so add one page
      
    $pages++;
      }

    // check to see if last page
      
    if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

      
    // not last page so give NEXT link
      
    $news=$s+$limit;

      echo 
    "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";
      }

    $a $s + ($limit) ;
      if (
    $a $numrows) { $a $numrows ; }
      
    $b $s ;
      echo 
    "<p>Showing results $b to $a of $numrows</p>";
      
    ?>

    </body>
    </html>
    Last edited by spikeZ; Mar 14, 2011 at 02:55. Reason: added code tags

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please go into phpmyadmin, go to the SQL tab, and run this query --
    Code:
    SHOW CREATE TABLE the_table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    Parse error is because you are missing a ; of the end of this line:
    PHP Code:
    $trimmed trim($var//trim whitespace from the stored variable 
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  10. #10
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    Parse error is because you are missing a ; of the end of this line:
    PHP Code:
    $trimmed trim($var//trim whitespace from the stored variable 
    ahh ty

    now i get a search box, however when i enter something in the search box and press submit i get a "Oops! This link appears to be broken." error.

  11. #11
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    thats because your form action is set to go to search.php and your page is called search7.php.
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  12. #12
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    thats because your form action is set to go to search.php and your page is called search7.php.

    doh silly me

  13. #13
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    im not getting any search results back.

    do i need to tell it the name of the table on the database ?

  14. #14
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    Assuming that you have edited the line:
    PHP Code:
    // Build SQL Query  
    $query "select * from the_table where 1st_field like \"%$trimmed%\"  
      order by 1st_field"
    // EDIT HERE and specify your table and field names for the SQL query 
    the_table and 1st_field need changing
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  15. #15
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    the_table and 1st_field need changing
    ok the table is called user_info and the first column/field is user_id so i have

    PHP Code:
    // Build SQL Query  
    $query "select * from user_info where user_id like \"%$trimmed%\"  
      order by user_id"
    // EDIT HERE and specify your table and field names for the SQL query 
    the rest of the fields/columns are user_first_name user_surname user_email user_mobile etc etc

    do i need to add all the fields/columns to this line, if so would it be

    PHP Code:
    // Build SQL Query  
    $query "select * from user_info where user_id like \"%$trimmed%\, user_first_name like \"%$trimmed%\, user_surname like \"%$trimmed%\, user_email like \"%$trimmed%\, user_mobile like \"%$trimmed%\"  
      order by user_id"
    // EDIT HERE and specify your table and field names for the SQL query 
    anyway im still not getting any search results

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by spikeZ View Post
    the_table and 1st_field need changing
    which is why i asked what i asked in post #8

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    OK, try the following code:
    PHP Code:
    // Build SQL Query  
    $query "
        select 
              user_first_name 
            , user_surname 
            , user_email 
            , user_mobile 
        from 
            user_info
        where 
            user_id 
        like 
            '%" 
    $trimmed ."%'  
        order by 
            user_id"
    // EDIT HERE and specify your table and field names for the SQL query

     
    $numresults=mysql_query($query) or die(mysql_error()); 
    see what happens
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  18. #18
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 i did do as you asked but it didnt seem to help me much at the time, i now see what you was getting at.

    spikeZ ive added the code above and i still dont get any search results

    my code now looks like this

    PHP Code:
    <html>
    <head>
    <title>designplace.org search script</title>
    <meta name="author" content="Steve R, http://www.designplace.org/">
    </head>
    <!--  http://www.designplace.org/ -->
    <body>

    <form name="form" action="search7.php" method="get">
      <input type="text" name="q" />
      <input type="submit" name="Submit" value="Search" />
    </form>

    <?php

      
    // Get the search variable from URL

      
    $var = @$_GET['q'] ;
      
    $trimmed trim($var) ; //trim whitespace from the stored variable

    // rows to return
    $limit=10

    // check for an empty string and display a message.
    if ($trimmed == "") ;
      {
      echo 
    "<p>Please enter a search...</p>";
      exit;
      }

    // check for a search parameter
    if (!isset($var))
      {
      echo 
    "<p>We dont seem to have a search parameter!</p>";
      exit;
      }

    //connect to your database ** EDIT REQUIRED HERE **
    mysql_connect("localhost","david","dave"); //(host, username, password)

    //specify database ** EDIT REQUIRED HERE **
    mysql_select_db("customers") or die("Unable to select database"); //select which database we're using

    // Build SQL Query  
    $query "
        select 
              user_first_name 
            , user_surname 
            , user_email 
            , user_mobile 
        from 
            user_info
        where 
            user_id 
        like 
            '%" 
    $trimmed ."%'  
        order by 
            user_id"
    // EDIT HERE and specify your table and field names for the SQL query

     
     
    $numresults=mysql_query($query) or die(mysql_error());
     
    $numrows=mysql_num_rows($numresults);

    // If we have no results, offer a google search as an alternative

    if ($numrows == 0)
      {
      echo 
    "<h4>Results</h4>";
      echo 
    "<p>Sorry, your search: &quot;" $trimmed "&quot; returned zero results</p>";

    // google
     
    echo "<p><a href=\"http://www.google.com/search?q=" 
      
    $trimmed "\" target=\"_blank\" title=\"Look up 
      " 
    $trimmed " on Google\">Click here</a> to try the 
      search on google</p>"
    ;
      }

    // next determine if s has been passed to script, if not use 0
      
    if (empty($s)) {
      
    $s=0;
      }

    // get results
      
    $query .= " limit $s,$limit";
      
    $result mysql_query($query) or die("Couldn't execute query");

    // display what the person searched for
    echo "<p>You searched for: &quot;" $var "&quot;</p>";

    // begin to show results set
    echo "Results";
    $count $s ;

    // now you can display the results returned
      
    while ($rowmysql_fetch_array($result)) {
      
    $title $row["1st_field"];

      echo 
    "$count.)&nbsp;$title;
      
    $count++ ;
      }

    $currPage = (($s/$limit) + 1);

    //break before paging
      
    echo "<br />";

      
    // next we need to do the links to other results
      
    if ($s>=1) { // bypass PREV link if s is 0
      
    $prevs=($s-$limit);
      print 
    "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
      Prev 10</a>&nbsp&nbsp;"
    ;
      }

    // calculate number of pages needing links
      
    $pages=intval($numrows/$limit);

    // $pages now contains int of pages needed unless there is a remainder from division

      
    if ($numrows%$limit) {
      
    // has remainder so add one page
      
    $pages++;
      }

    // check to see if last page
      
    if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

      
    // not last page so give NEXT link
      
    $news=$s+$limit;

      echo 
    "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";
      }

    $a $s + ($limit) ;
      if (
    $a $numrows) { $a $numrows ; }
      
    $b $s ;
      echo 
    "<p>Showing results $b to $a of $numrows</p>";
      
    ?>

    <!--  http://www.designplace.org/ -->

    </body>
    </html>

  19. #19
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    ok just want to check - is there a record or records in the databse that would match with a user_id?!
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  20. #20
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    ok just want to check - is there a record or records in the databse that would match with a user_id?!
    yes there is, i enter the customers name, surname, email, phone etc on a form and that gets put into the database. user_id is set to AUTO_INCREMENT

  21. #21
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    Change the query to:
    PHP Code:
    $query "
        select 
              user_first_name 
            , user_surname 
            , user_email 
            , user_mobile 
        from 
            user_info
        where 
            user_id 
        like 
            '%" 
    $trimmed ."%'  
        or
            user_first_name
        like 
            '%" 
    $trimmed ."%'  
        or
            user_surname
        like 
            '%" 
    $trimmed ."%'  
        order by 
            user_id"

    See what gets returned
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  22. #22
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    still nothing

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    try testing your query outside of php first, before attempting to parameterize it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •