search query and results

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

Post Reply
seriousdamage
Forum Commoner
Posts: 30
Joined: Sat Nov 27, 2004 10:18 am

search query and results

Post 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
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: search query and results

Post 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.
User avatar
jimthunderbird
Forum Contributor
Posts: 147
Joined: Tue Jul 04, 2006 3:59 am
Location: San Francisco, CA

Re: search query and results

Post 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.
seriousdamage
Forum Commoner
Posts: 30
Joined: Sat Nov 27, 2004 10:18 am

Re: search query and results

Post 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
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: search query and results

Post by Zoxive »

Exactly what it means.

In the table `contacts` there is no column `search`.
User avatar
jimthunderbird
Forum Contributor
Posts: 147
Joined: Tue Jul 04, 2006 3:59 am
Location: San Francisco, CA

Re: search query and results

Post by jimthunderbird »

You may not have a field named "search" in your contact table...
seriousdamage
Forum Commoner
Posts: 30
Joined: Sat Nov 27, 2004 10:18 am

Re: search query and results

Post 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
seriousdamage
Forum Commoner
Posts: 30
Joined: Sat Nov 27, 2004 10:18 am

Re: search query and results

Post 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>");
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: search query and results

Post 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%'";
seriousdamage
Forum Commoner
Posts: 30
Joined: Sat Nov 27, 2004 10:18 am

Re: search query and results

Post by seriousdamage »

But I still don't understand how can I allow a user on my website to search for info using a webform.
seriousdamage
Forum Commoner
Posts: 30
Joined: Sat Nov 27, 2004 10:18 am

Re: search query and results

Post 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>");
Post Reply