search mysql database for records

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

]{ronic
Forum Commoner
Posts: 27
Joined: Thu Mar 04, 2004 10:42 pm

search mysql database for records

Post by ]{ronic »

Hey,

Can anyone see why the following script is not displaying the correct results:

Code: Select all

<html>
<head>
<title>Search</title>
</head>
<body>

<?php
$db_server = "#####";
$user = "#####";
$pass = "#####";
$db_private = "#####";
//They are searching...do that for them (we know this from the hidden "go" field in the table)
if ($_POST&#1111;go] == "True") &#123;
//Open database server connection
$db_connect = mysql_connect($db_server, $user, $pass, $db);
if (! $db_connect)&#123;
Echo "Couldn't connect";
exit;
&#125;

//Open database
if (! mysql_select_db($db_private, $db_connect))&#123;
echo "Error selecting database to use " . mysql_error();
exit;
&#125;

//Build the SQL statement based on POST options
//The basic SQL will select all records
$db_query = "SELECT";
$db_query .= "description";
$db_query .= "FROM";
$db_query .= "C_Memory";

//depending on which of the following are filled in (none, some, all)
//the resultset returned will be filtered down
if (strlen(trim($_POST&#1111;description])) > 0) &#123;$db_query .= "AND description LIKE '$_POST&#1111;description]%' ";&#125;
echo $db_query;
exit;

//Run our query
$result1 = mysql_query($db_query, $db_connect);
if (! $result1)&#123;
echo mysql_error();
exit;
&#125;

//loop through all the results and display them
echo "<table cellpadding="2" border="1"><tr><td colspan=5>Search Results</td>\r\n";
while($row = mysql_fetch_array($result1)) &#123;
echo "<tr><td>$row&#1111;description]></td></tr>\r\n";
&#125; // while

echo "</table>\r\n\r\n";
echo "</body></html>";
//exit out
exit;
&#125;
?>
<table width="500">
<tr>
<td width="500">Search Products </td>
</tr>
</table>
<table>
<form name="mysearch" action="search.php" method="post">
<tr>
<td>Product Description</td><td><input type="text" size="20" value="" name="Product Description"></td>
</tr>
<tr>
<td colspan="2" align="center">
 <input type="hidden" name="go" value="True">
 <input type="submit" value="Search">
</td>
</tr>
</table>
</form>
</body>
</html>
When the script is run .. it returns instead of any results:

Code: Select all

SELECTdescriptionFROMC_MemoryWHERE 1
Any know why this is happening .. or maybe knows of another script that I could use to search the mysql database and return the results

Thanks

]{ronic
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

Code: Select all

if ($_POST[go] == "True") { 
should be:
if ($_POST['go'] == "True") {
and change this:

Code: Select all

$db_query = "SELECT";
$db_query .= "description";
$db_query .= "FROM";
$db_query .= "C_Memory";
to this:

Code: Select all

$db_query = "SELECT description FROM C_Memory";
finally, change:

Code: Select all

if (strlen(trim($_POST[description])) > 0) {$db_query .= "AND description LIKE '$_POST[description]%' ";}
echo $db_query;
exit;
to this:

Code: Select all

$desc = $_POST['description'];
if (strlen($desc) > 0) {
$db_query .= " WHERE description LIKE '$desc%'";
}
Remove the lines "echo $db_query" and "exit" - they are why nothing is happening!

Where on earth did you find this script? It's quite badly written :) (sorry if you wrote it!)
]{ronic
Forum Commoner
Posts: 27
Joined: Thu Mar 04, 2004 10:42 pm

Post by ]{ronic »

Hey,
I didn't write the script
Thanks for your help .. I found the script in a post on another forum ..

With the changes you suggested it is working alot better .. Altho it is displaying all the records in the "description" fields not just the ones containg the word used in the search ..

Say I type in samsung .. I only want it to return "description" fields that have the word samsung within that field .. Any idea on how to acheive this?

Even if you have a better script in mind to acheive this ..

I have no idea on search scripts .. and havnt found much around the net on how to create them.

Thanks

]{ronic
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

Sure, just change this:

<input type="text" size="20" value="" name="Product Description">

to:

<input type="text" size="20" value="" name="description">

Now it should work when you submit the form, i.e. it'll only bring back records matching what you entered.
]{ronic
Forum Commoner
Posts: 27
Joined: Thu Mar 04, 2004 10:42 pm

Post by ]{ronic »

Hey

Ausome, that works great .. Altho if I use "samsung" or "winbond Seitec" which are the fist word/s in the description field that works fine .. but if I search "256MB" which is a few words into the field it brings back no results... or search "PC2700" which is the next word after "samsung" .. Any idea on how to make it show results for the other words ie "PC2700"?

Also can u search across multuple tables or just one?


Thanks heaps .. your a legend


]{ronic
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

The trick to the search is this part:

$db_query .= " WHERE description LIKE '$desc%'";

Think of the % sign as the equivalent of a * in a directory listing. i.e.

*.gif would list all gif files, right?

So if you want to match a string anywhere in the field, you could do:

$db_query .= " WHERE description LIKE '%$desc%'";

Ideally you should be using a fulltext search on this, but that depends on your version of MySQL - so try the above for now.
]{ronic
Forum Commoner
Posts: 27
Joined: Thu Mar 04, 2004 10:42 pm

Post by ]{ronic »

Hey,

That works great .. Thanks

Can u search multiple tables ie:

$db_query = "SELECT description, price FROM C_Memory, C_Audio, C_VGA";

As you would by adding the fields?

I guess not I tried it .. is it posible to do so some how?


]{ronic
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

Yes, just different syntax:

SELECT table1.*, table2.* FROM table1,table2 WHERE table1.field LIKE '%whatever%' OR table2.field LIKE '%whatever%'
]{ronic
Forum Commoner
Posts: 27
Joined: Thu Mar 04, 2004 10:42 pm

Post by ]{ronic »

Hmmz so u mean:

Code: Select all

$db_query = "SELECT C_Memory.*, C_Audio.*, C_VGA.*FROM C_Memory,C_Audio,C_VGA WHERE C_Memory.description LIKE '%whatever%' OR C_Audio.description LIKE '%whatever%' OR C_VGA.description LIKE '%whatever%'";
Altho i wanted to add the price field as well which i had as:

Code: Select all

$db_query = "SELECT description, price FROM C_Memory";
If i have like 20 tables .. is this a messy way to do it?

]{ronic
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

Yes that is pretty much how you would do it although watch your spacing (you have the C_VGA.*FROM with no space before FROM which would cause an SQL error).

Doing this across 20 tables isn't ideal, but is still perfectly valid SQL. The only other way would be to create a temporary table by dragging in all of the fields from all of the tables you need and then searching that - but I wouldn't recommend it really.
]{ronic
Forum Commoner
Posts: 27
Joined: Thu Mar 04, 2004 10:42 pm

Post by ]{ronic »

I have:

Code: Select all

//Build the SQL statement based on POST options
//The basic SQL will select all records
db_query = "SELECT C_Memory.*, C_Audio.*, C_VGA.*FROM C_Memory,C_Audio,C_VGA WHERE C_Memory.description LIKE '%whatever%' OR C_Audio.description LIKE '%whatever%' OR C_VGA.description LIKE '%whatever%'";

But it gives me an error on that line .. Also does the below code stay the same:

Code: Select all

//depending on which of the following are filled in (none, some, all)
//the resultset returned will be filtered down
$desc = $_POST&#1111;'description']; 
if (strlen($desc) > 0) &#123; 
$db_query .= " WHERE description LIKE '%$desc%'"; 
&#125;
Also where do i put the other fields so i can display those with the results .. I had:

Code: Select all

$db_query = "SELECT description, price FROM C_Memory";



Thanks

]{ronic
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

The error is because of the thing I pointed out to you above (no space before FROM) and because you're appending that WHERE description block to the end of it - which is now invalid SQL.

You MUST get in the habit of echoing out the SQL query itself and also the results of mysql_error() - they will actually tell you what is wrong rather than just a random line number.

Your other fields are correct, put them there - but don't forget to extract them from the results, specifically:

<?=$row['description']?> - change that to the value to display
]{ronic
Forum Commoner
Posts: 27
Joined: Thu Mar 04, 2004 10:42 pm

Post by ]{ronic »

Hey,

The thing is I get the error on line 30 when the script is executed .. Line 30 is:

Code: Select all

db_query = "SELECT C_Memory.*, C_VGA.* FROM C_Memory,C_VGA WHERE C_Memory.description LIKE '%whatever%' OR C_VGA.description LIKE '%whatever%'";
I had already taken the space out .. Is it becouse both tables are using the "description" field ?
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

No, that's absolutely perfectly valid SQL - there is nothing wrong with it (other than in the missing $ from db_query, but I guess you just didn't select that part when pasting?)

Echo out $db_query right before you pass it to mysql_query and post it here, maybe it is being modified elsewhere in the code.
]{ronic
Forum Commoner
Posts: 27
Joined: Thu Mar 04, 2004 10:42 pm

Post by ]{ronic »

If i search "samsung" the result i get is:

Code: Select all

You have an error in your SQL syntax near 'WHERE description LIKE '%samsung%'' at line 1
Post Reply