Page 1 of 1

search query and results

Posted: Wed Jan 16, 2008 11:11 am
by seriousdamage
Hi, could someone look at the bebelow code and help me fixing the error message?
I have a page with one text field to search and a php script to show the results.
and I get the following error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/local/psa/home/vhosts/giustinianinicola.com/httpdocs/contacts/search.php on line 28

Code: Select all

<html>
<body>
<form name="search" method="POST" action="search.php"> 
<p>Search: <input name="search" type="text" id="search"> 
 
<p><input type="submit" name="submit" value="submit"> 
</form>
</body>
</html>

Code: Select all

<? 
$host = "localhost"; 
$user = "******"; 
$pass = "******"; 
$dbname = "contacts"; 
 
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>"); 
mysql_select_db($dbname); 
 
 
$result = mysql_query("SELECT * FROM contacts where search=" . $_POST['search'] ."'");
 
 
echo "<table border=0 width=1400>
<tr bgcolor=#87ceeb>
<td width=100><b>First Name</td><td width=100><b>Last Name</td>
<td width=100><b>Company Name</td><td width=100><b>Website</td>
<td width=100><b>Street + Number</td><td width=100><b>City</td>
<td width=100><b>Post Code</td><td width=100><b>Country</td>
<td width=100><b>E-Mail 1</td><td width=100><b>E-Mail 2</td>
<td width=100><b>Phone</td><td width=100><b>Mobile</td>
<td width=100><b>Christmas Card</td><td width=100><b>Birthday</b></td>
 
 
</tr>";
 
 
while($row = mysql_fetch_array($result)) {  
 
echo "<tr bgcolor=#4682b4>";
  echo "<td><font color=#FFFFFF>" . $row['first_name'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['last_name'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['company_name'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['website'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['street'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['city'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['zip'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['country'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['mail1'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['mail2'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['phone'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['mobile'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['card'] . "</td>";
  echo "<td><font color=#FFFFFF>" . $row['birthday'] . "</font></td>";
 
  echo "</tr>";
  }
echo "</table>";
?>
Many Thanks
Nic

Re: search query and results

Posted: Wed Jan 16, 2008 11:15 am
by Zoxive
Add

Code: Select all

or die (mysql_errno().": ".mysql_error()."<BR>");
After your mysql_query function, just like you have it for mysql_connect.

I do see your error, but doing that will help show you your current error, and help with future ones.

Re: search query and results

Posted: Wed Jan 16, 2008 11:28 am
by jimthunderbird
I think it has an error in your sql statement.

Code: Select all

 
$result = mysql_query("SELECT * FROM contacts where [b]search=" . $_POST['search'] ."'"[/b]);
 
it should be:

Code: Select all

 
$result = mysql_query("SELECT * FROM contacts where [b]search='". $_POST['search'] ."'"[/b]);
 
put '' around $_POST['search']

Also try mysql_error() to see what's wrong. If you have phpmyadmin, try running the sql in it and see what's wrong.

Re: search query and results

Posted: Wed Jan 16, 2008 11:49 am
by seriousdamage
Hi,
I think it was a combination of both, but now I get a new error.

1054: Unknown column 'search' in 'where clause'

Any idea?

Thanks
Nic

Re: search query and results

Posted: Wed Jan 16, 2008 11:53 am
by Zoxive
Exactly what it means.

In the table `contacts` there is no column `search`.

Re: search query and results

Posted: Wed Jan 16, 2008 11:54 am
by jimthunderbird
You may not have a field named "search" in your contact table...

Re: search query and results

Posted: Wed Jan 16, 2008 1:27 pm
by seriousdamage
then I am understanding this wrong,
how can I say that whatever I am asking to serch for, in the html form, it has to seach in the entire database, in all columns,
and then show the entire row?

Thanks
Nic

Re: search query and results

Posted: Thu Jan 17, 2008 4:08 am
by seriousdamage
Hi,
who could help me checking what is wrong with the below SQL statement?
I am trying to tell the script to search the all databese for the string contained in the search field of the form called search.html.
I just can't make it work.

Code: Select all

$result = mysql_query("SELECT * FROM contacts where ='" . $_POST['search'] ."'") or die (mysql_errno().": ".mysql_error()."<BR>");

Re: search query and results

Posted: Thu Jan 17, 2008 8:08 am
by Zoxive
seriousdamage wrote:I am trying to tell the script to search the all databese for the string contained in the search field of the form called search.html.
That's not possible in the terms you are saying.
Making a search script is a lot more complicated then you realize, especially when you want to to match everything. Which I don't even think is advisable.
Sql Needs to know what to match, its not just going to guess and look in every table and column.

Below is a very simple search, matching one column in one table, returning all the data in the row.

Code: Select all

$Term = 'Text you are searching for';
$Sql = "Select * From `table` where `column` LIKE '%$Term%'";

Re: search query and results

Posted: Thu Jan 17, 2008 8:28 am
by seriousdamage
But I still don't understand how can I allow a user on my website to search for info using a webform.

Re: search query and results

Posted: Thu Jan 17, 2008 9:37 am
by seriousdamage
Hi, problem solved,
I have written the code as follows, maybe this was the long way around, but for a biginner like me, it just does the job.

Code: Select all

$result = mysql_query("SELECT * FROM contacts where first_name LIKE '" . $_POST['search'] ."' OR last_name LIKE '" . $_POST['search'] ."' OR company_name LIKE '" . $_POST['search'] ."' OR website LIKE '" . $_POST['search'] ."' OR street LIKE '" . $_POST['search'] ."' OR city LIKE '" . $_POST['search'] ."' OR zip LIKE '" . $_POST['search'] ."' OR country LIKE '" . $_POST['search'] ."' OR mail1 LIKE '" . $_POST['search'] ."' OR mail2 LIKE '" . $_POST['search'] ."' OR phone LIKE '" . $_POST['search'] ."' OR mobile LIKE '" . $_POST['search'] ."' OR card LIKE '" . $_POST['search'] ."' OR birthday LIKE '" . $_POST['search'] ."'") or die (mysql_errno().": ".mysql_error()."<BR>");