Help with php/mysql search box

  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

Alright, I'm real new to this whole mysql and everything different than html. I was wondering if someone could tell me what I'm missing or what I'm doing wrong. I'm trying to code a search box that goes through my phpmyadmin database and displays the results. Heres the code for the search box:

Code: [ Select ]
<form method="post" action="http://www.sportznow.com/results.php" target="_blank">
<div align="center">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td bordercolor="#000000">
     <p align="center">
                <input type="text" name="search" size="12">
                 &nbsp;<br>
                <font size="2" face="Verdana, Arial, Helvetica, sans-serif">Player
                Search:</font>
                <input type="submit" value="Go!!" name="Go"></p>
</td>
</tr>
</table>
</div>
</form>
  1. <form method="post" action="http://www.sportznow.com/results.php" target="_blank">
  2. <div align="center">
  3. <table border="0" cellpadding="0" cellspacing="0">
  4. <tr>
  5. <td bordercolor="#000000">
  6.      <p align="center">
  7.                 <input type="text" name="search" size="12">
  8.                  &nbsp;<br>
  9.                 <font size="2" face="Verdana, Arial, Helvetica, sans-serif">Player
  10.                 Search:</font>
  11.                 <input type="submit" value="Go!!" name="Go"></p>
  12. </td>
  13. </tr>
  14. </table>
  15. </div>
  16. </form>


Here is the code for the page it directs to. It connects to the database fine and no failed query messages come up. However, it just says that the database found no matches even though I'm searching for stuff in the database. Here is the code:
Code: [ Select ]
<? $hostname = "********";
$username = "********";
$password = "********";
$dbName = "**********";

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");
?>
    <?
//error message (not found message)begins
$XX = "No Matches Found";
//query details table begins
$query = mysql_query("SELECT * FROM details WHERE $metode LIKE '%$search%' LIMIT 0, 50");
while ($row = @mysql_fetch_array($query))
{
$variable1=$row["Player"];
$variable2=$row["Avg"];
$variable3=$row["HR"];
$variable4=$row["RBI"];
//table layout for results

print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("<td>$variable4</td>");
print ("</tr>");
}
//below this is the function for no record!!
if (!$variable1)
{
print ("$XX");
}
//end
?>
  1. <? $hostname = "********";
  2. $username = "********";
  3. $password = "********";
  4. $dbName = "**********";
  5. MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
  6. @mysql_select_db( "$dbName") or die( "Unable to select database");
  7. ?>
  8.     <?
  9. //error message (not found message)begins
  10. $XX = "No Matches Found";
  11. //query details table begins
  12. $query = mysql_query("SELECT * FROM details WHERE $metode LIKE '%$search%' LIMIT 0, 50");
  13. while ($row = @mysql_fetch_array($query))
  14. {
  15. $variable1=$row["Player"];
  16. $variable2=$row["Avg"];
  17. $variable3=$row["HR"];
  18. $variable4=$row["RBI"];
  19. //table layout for results
  20. print ("<tr>");
  21. print ("<td>$variable1</td>");
  22. print ("<td>$variable2</td>");
  23. print ("<td>$variable3</td>");
  24. print ("<td>$variable4</td>");
  25. print ("</tr>");
  26. }
  27. //below this is the function for no record!!
  28. if (!$variable1)
  29. {
  30. print ("$XX");
  31. }
  32. //end
  33. ?>


I got code from various tutorials around the internet. Anyone help is greatly appreciated!
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • b_heyer
  • Web Master
  • Web Master
  • User avatar
  • Posts: 4581
  • Loc: Maryland

Post 3+ Months Ago

What does or does not happen when you use this code?
  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

Well, it seems to work fine because it connects fine and the query works, but I get "no matches found" even though I am searching for something I know is in the database. Any thoughts about why?
  • b_heyer
  • Web Master
  • Web Master
  • User avatar
  • Posts: 4581
  • Loc: Maryland

Post 3+ Months Ago

Since you said you are using phpmyadmin, try querying from in there:
Code: [ Select ]
"SELECT * FROM details WHERE $metode LIKE '%somesearch%' LIMIT 0, 50"
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

What's $metode?

What I would do....

Change this...

Code: [ Select ]
$query = mysql_query("SELECT * FROM details WHERE $metode LIKE '%$search%' LIMIT 0, 50");

into this...

Code: [ Select ]
$sql = "SELECT * FROM details WHERE $metode LIKE '%$search%' LIMIT 0, 50";
$query = mysql_query($sql);
  1. $sql = "SELECT * FROM details WHERE $metode LIKE '%$search%' LIMIT 0, 50";
  2. $query = mysql_query($sql);


That way at least, if you're not getting the results you want, you can enter a simple...

Code: [ Select ]
echo $sql;.'<br>';

...in there so you've got a real query to paste into phpMyAdmin with all the variables complete.

phpMyAdmin usually throws back more useful error messages :)
  • b_heyer
  • Web Master
  • Web Master
  • User avatar
  • Posts: 4581
  • Loc: Maryland

Post 3+ Months Ago

good point it's also a good idea to add in the error option ;)

Code: [ Select ]
$query = mysql_query($sql) OR DIE(mysql_error());
  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

Alright, I used some of your suggestions and put this in:

Code: [ Select ]
$query = mysql_query($sql) OR DIE(mysql_error());
"SELECT * FROM details WHERE $metode LIKE '%search%' LIMIT 0, 50";
  1. $query = mysql_query($sql) OR DIE(mysql_error());
  2. "SELECT * FROM details WHERE $metode LIKE '%search%' LIMIT 0, 50";


Now when I try to search it says: Query was Empty. Any ideas how to fix?
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

you need to have the $query bit after your SQL statement...

and the statement itself..

"SELECT * FROM details WHERE $metode LIKE '%search%' LIMIT 0, 50";

needs to be

$sql = "SELECT * FROM details WHERE $metode LIKE '%search%' LIMIT 0, 50";

:)
  • b_heyer
  • Web Master
  • Web Master
  • User avatar
  • Posts: 4581
  • Loc: Maryland

Post 3+ Months Ago

and then that has to go above the mysql_query() function.
  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

I can't seem to get this to work right at all. I'm getting the following error now:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE '%search%' LIMIT 0, 50' at line 1

I take it their is a problem somewhere in here:
Code: [ Select ]
$sql = "SELECT * FROM details WHERE $metode LIKE '%search%' LIMIT 0, 50";


Thanks for everyones help!!!!!!!!!!!!!!!!!!!!
  • gsv2com
  • Professor
  • Professor
  • User avatar
  • Posts: 776
  • Loc: Nippon

Post 3+ Months Ago

Where in your code are you calling data into $metode?
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

Okies, comment out...

Code: [ Select ]
$query = mysql_query($sql)

That line, and replace it with...

Code: [ Select ]
echo $sql;


Then copy the entire SQL statement the browser throws back at you and paste here exactly what it's attempting to execute :)
  • TheIndependent
  • Born
  • Born
  • TheIndependent
  • Posts: 4

Post 3+ Months Ago

I am working on using this same exact script as it turns out... For me right now I have the script working about the same as the previous posted above, but I have a bit more information..

It looks like the "%search%" is not getting pulled..

the query I get is

SELECT * FROM viddata WHERE title LIKE '%search%' LIMIT 0, 50

IF I use that in sql I get zippo,,, but if I replace the '%search%' in the query I then get the results I was looking for...

So what we need to figure out is why that variable isn’t getting pulled out like it should be.. Any help would be appreciated...
  • TheIndependent
  • Born
  • Born
  • TheIndependent
  • Posts: 4

Post 3+ Months Ago

$sql = "SELECT * FROM viddata WHERE $metode LIKE '%' LIMIT 0, 50";

will return everything in the table..
  • TheIndependent
  • Born
  • Born
  • TheIndependent
  • Posts: 4

Post 3+ Months Ago

shameless bump
  • TheIndependent
  • Born
  • Born
  • TheIndependent
  • Posts: 4

Post 3+ Months Ago

ahHAH

SELECT * FROM viddata WHERE title LIKE '%search%' LIMIT 0, 50 is wrong

SELECT * FROM viddata WHERE title LIKE '%$search%' LIMIT 0, 50 is soooo right.

It wasn't pulling out the variable..

Now all i need to figure out how to do is make it so that the drop down field selector isn't a one only selection but a multiple selection..
  • compupix
  • Born
  • Born
  • compupix
  • Posts: 2

Post 3+ Months Ago

I've been having the same problem. I found the tutorial at
http://support.thinkhost.com/index.php? ... ticleid=69

I've determined that my variables are not being received in the PHP page. All the rest of the PHP code seems to work fine... just the field name and field search values: $metode and $search are not being populated in the receiving page.

Here is the code that I am using in my HTML form page:
=========================
<form method="post"
action="http://drewfaber.com/db/string-search-user-input-results.php"
target="_blank">

<select name="metode" size="1">
<option value="Roll_Number">Roll Number</option>
<option value="First_Expo_Date">Date</option>
<option value="Description">Description</option>
</select>

<input type="text" name="search" size="20"><br>
Search database:
<input type="submit" value="Search!" name="Go">

</form>

=============================

$metode and $search do not receive metode and search.

(The query works fine when hard coded with a field and a search value.)

Thanks,
Drew
  • compupix
  • Born
  • Born
  • compupix
  • Posts: 2

Post 3+ Months Ago

I just did a

print_r( $GLOBALS );

and this was included in my results:

[_REQUEST] => Array ( [metode] => Roll_Number [search] => 1616

How do I reference the variables that contain the values "Roll_Number" and "1616"?

Post Information

  • Total Posts in this topic: 18 posts
  • Users browsing this forum: No registered users and 112 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.