www.webdeveloper.com
Page 1 of 3 123 LastLast
Results 1 to 15 of 33

Thread: mysql table search

  1. #1
    Join Date
    May 2003
    Posts
    225

    Post mysql table search

    Can anyone lelp me make a (advanced) mysql table search?

    I would like users to use a drop-down list to select a column to search, a search field to enter text to search, and, if possable, a "exact" or "containing" drop-down, to select with. I will enter my pass , username, server, and table to search when i get it, if you could make them 4 variables near the top

    oh, two more things,
    if you can highlight the text thet the user searched for, and (2) supply the entire row to show the other ones.
    Last edited by diamonds; 06-14-2003 at 04:43 PM.

  2. #2
    Join Date
    Dec 2002
    Location
    Calgary, Canada
    Posts
    6,120
    Looking for free consulting ????
    Thats not nice..
    Cheers

    Khalid

    Message Posting Guidelines In These Forums, Please read these before posting any question.
    Web site: webapplikations.com
    Web Resources Page:Web Resources

  3. #3
    Join Date
    May 2003
    Posts
    225

    Lightbulb how about...

    how about it prints the entire table, and removes all rows that don't comply with the search?

  4. #4
    Join Date
    Dec 2002
    Location
    High on life
    Posts
    10,104
    Depending on what you are trying to do, you may want to look at this: http://www.mysql.com/doc/en/String_c...functions.html

    Also, your query might look something like this:

    $query = "SELECT * FROM tablename WHERE column_name LIKE '$search%'";

    The % means to matche any number of characters. So, if $search equaled 'test', it would also return 'testing', 'Testing' (note case insensativity) or 'tester'. Now, if you want to match 'my test', you would do this:

    $query = "SELECT * FROM tablename WHERE column_name LIKE '%$search%'";

    Personal website http://www.ryanbrill.com/
    Business website: http://www.infinitywebdesign.com/
    TypeSpace http://www.typespace.org/

    I reject your reality and substitute it with my own!

  5. #5
    Join Date
    May 2003
    Posts
    225

    Great...

    I get you 100%.
    But how can you make it into a complete script?
    surely you cannot go
    PHP Code:
    <?php echo "SELECT * FROM tablename WHERE column_name LIKE '$search%'"?>
    and expect it to work? Right?

  6. #6
    Join Date
    Dec 2002
    Location
    High on life
    Posts
    10,104

    Re: Great...

    Originally posted by diamonds
    But how can you make it into a complete script?
    As Khalid said, this isn't a site to look for free contract programming... If you have questions regarding the script, we'd be happy to help, but if you need an entire script, you may want to contact a programmer (myself included), or look around on the 'net for something that does what you need...

    Personal website http://www.ryanbrill.com/
    Business website: http://www.infinitywebdesign.com/
    TypeSpace http://www.typespace.org/

    I reject your reality and substitute it with my own!

  7. #7
    Join Date
    May 2003
    Posts
    225

    where

    like where? Do you know some scripts on the web or others I can contact?

  8. #8
    Join Date
    Dec 2002
    Location
    High on life
    Posts
    10,104
    Try searching google... http://www.google.com/search?hl=en&i...mysql+database The few I looked at are more or less expounded versions of what I posted above.. I only gave the query...

    As far as other PHP programmers, I'm the only one I know... lol

    Personal website http://www.ryanbrill.com/
    Business website: http://www.infinitywebdesign.com/
    TypeSpace http://www.typespace.org/

    I reject your reality and substitute it with my own!

  9. #9
    Join Date
    May 2003
    Posts
    225

    grrr

    I got close... but everything was coming up with errors...
    Oh well... the computer is as dumb as the programmer!
    I will see what I can come up with in the next hour...
    anyone have any immediate ideas?

  10. #10
    Join Date
    Dec 2002
    Location
    High on life
    Posts
    10,104
    Post the code you are having trouble with and an explination of the problem.

    Personal website http://www.ryanbrill.com/
    Business website: http://www.infinitywebdesign.com/
    TypeSpace http://www.typespace.org/

    I reject your reality and substitute it with my own!

  11. #11
    Join Date
    May 2003
    Posts
    225

    here

    This is the code I want the most and what iv'e posted is slightly modified so the username and password is a variable is at the top:



    PHP Code:
    <?php

    $username 
    ""
    $password ""
    $host ""

      
    // 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($host,$username,$password);

    //specify database ** EDIT REQUIRED HERE **
    mysql_select_db("database") 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>";
      
    ?>
    Now part of the way down is a specify database *EDIT REQUIRED HERE* comment. What do I do here?

    oh, I forgot this:

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

  12. #12
    Join Date
    Dec 2002
    Location
    High on life
    Posts
    10,104
    PHP Code:
    //specify database ** EDIT REQUIRED HERE **
    mysql_select_db("database") or die("Unable to select database"); //select which database we're using 
    you just need to change "database" to be the name of your database...

    Personal website http://www.ryanbrill.com/
    Business website: http://www.infinitywebdesign.com/
    TypeSpace http://www.typespace.org/

    I reject your reality and substitute it with my own!

  13. #13
    Join Date
    May 2003
    Posts
    225
    duh...

    PHP Code:
    <?php
    $user 
    ""
    $pass ""
    $host ""
    $database ""
    $table ""

      
    // 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($host,$user,$pass); //(host, username, password)

    //specify database ** EDIT REQUIRED HERE **
    mysql_select_db($database) 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>";
      
    ?>
    is there anything else thet needs editing? there is one more EDIT HERE comment...

  14. #14
    Join Date
    Dec 2002
    Location
    High on life
    Posts
    10,104
    Yep, in the next EDIT HERE area, you need to set the tablename, and the column name. Something like this:

    PHP Code:
    $query "SELECT * FROM yourtable WHERE yourcolumn LIKE \"%$trimmed%\" ORDER BY yourcolumn"

    Personal website http://www.ryanbrill.com/
    Business website: http://www.infinitywebdesign.com/
    TypeSpace http://www.typespace.org/

    I reject your reality and substitute it with my own!

  15. #15
    Join Date
    May 2003
    Posts
    225

    Talking thanks

    Like this?(I highlighted all new text)
    Code:
    
    <?php
    $user = ""
    $pass = ""
    $host = ""
    
    
    $database = ""
    
    
    $table = ""
    
    $col = trim($_GET['col'] ;); //trim whitespace from the stored 
    
      // 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($host,$user,$pass); //(host, username, password)
    
    //specify database ** EDIT REQUIRED HERE **
    mysql_select_db("database") or die("Unable to select database"); //select which database we're using
    
    // Build SQL Query  
    
    
     
    $query = "select * from $table where $col like \"%$trimmed%\" order by $col";
    
    
    
     $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 = 1 + $s ;
    
    // now you can display the results returned
      while ($row= mysql_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 >=$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 + 1 ;
      echo "<p>Showing results $b to $a of $numrows</p>";
      
    ?>
    Last edited by diamonds; 06-18-2003 at 07:40 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles