9 Replies - 1907 Views - Last Post: 27 November 2006 - 03:41 AM

#1 Kempy535  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-November 06

mysql search problem

Posted 23 November 2006 - 09:31 AM

Hi Im trying to make a search feature for a site in php that uses a mysql data base. I want to be able to search the whole data base, I have 11 fields, the search word entered in via a form. So far I have the following codes,
this is the code in the search.php page which is the return page from the form,
<?php

$searchf = $_POST["searchf"];

function testsearch()
{
$result = mysql_query("SELECT * FROM stocklist LIKE $searchf");
while($row = mysql_fetch_array($result))
  {
  echo $row['make']."  ".$row['model']."  ".$row['title'] ."  ".$row['price'];
  echo "<br />";
  } 
 }
  testsearch();
?>



This is the form code that on the ;
<form action="search.php" target="mainFrame" method="post">
Search: <input type="text" name="searchf" />
<input type="submit" />
</form>



The main problem I am having at the moment is i get this error message,
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\Rising sun\search.php on line 20

I cant figure out why it wont work its sending me mad.
Firstly will the code work? Is it just about changing the mysql_fetch_array for another command? I have other mysql querys running and they work fine. Any help or idea will be hugly appresated.
Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: mysql search problem

#2 snoj  Icon User is offline

  • Married Life
  • member icon

Reputation: 93
  • View blog
  • Posts: 3,583
  • Joined: 31-March 03

Re: mysql search problem

Posted 23 November 2006 - 09:43 AM

you have a SQL syntax error. It looks like you need ' marks around $searchf so mysql knows and treats it like a string.
Was This Post Helpful? 0
  • +
  • -

#3 Kempy535  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-November 06

Re: mysql search problem

Posted 23 November 2006 - 10:55 AM

I just tried that and I still get the same error code :angry:
Was This Post Helpful? 0
  • +
  • -

#4 Skinnyarms  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 18-November 06

Re: mysql search problem

Posted 23 November 2006 - 08:03 PM

Maybe I'm missing something, but I don't see a WHERE clause...

Your query should look something like

$result = mysql_query("SELECT   *
FROM	  stocklist
WHERE	(your_column_name_here) LIKE '$your_value_here'");



Like the above poster says, the LIKE clause is intended for strings, so you'll need single quotes around your php variable.

Also, I'm no php expert, but from what I can tell your $searchf contains all your post variables, so it seems to me like you'll want to loop through them, i don't know the syntax for php, so I'll kind of pseudocode it for you...

$myString = "SELECT   * FROM stocklist";

for($i = 1, $i < Length($searchf), i++) {
	$myString += "WHERE ($i LIKE '$evaluated_value(i)')";
}

$result = mysql_query(myString);
");



Hope that helps!
Was This Post Helpful? 0
  • +
  • -

#5 snoj  Icon User is offline

  • Married Life
  • member icon

Reputation: 93
  • View blog
  • Posts: 3,583
  • Joined: 31-March 03

Re: mysql search problem

Posted 23 November 2006 - 08:17 PM

That's still wrong as you'd have more than one WHERE. Though skinnyarms is right, you are missing WHERE.
Was This Post Helpful? 0
  • +
  • -

#6 Kempy535  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-November 06

Re: mysql search problem

Posted 24 November 2006 - 04:39 AM

I want it to search all the colloums at once not ust one?
So can i use the * instead of (your_column_name_here) in the code below?

$result = mysql_query("SELECT   *
FROM	  stocklist
WHERE	(your_column_name_here) LIKE '$your_value_here'");

Was This Post Helpful? 0
  • +
  • -

#7 Skinnyarms  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 18-November 06

Re: mysql search problem

Posted 24 November 2006 - 06:47 AM

View Posthotsnoj, on 23 Nov, 2006 - 08:17 PM, said:

That's still wrong as you'd have more than one WHERE. Though skinnyarms is right, you are missing WHERE.



ACK! You're right! Corrected:

$myString = "SELECT   * FROM stocklist WHERE 1 = 1";

for($i = 1, $i < Length($searchf), i++) {
	$myString += "AND ($i LIKE '$evaluated_value(i)')";
}

$result = mysql_query(myString);
");



and as for searching all at once, there's no way I know of to do that with out looping through the post variables.

Also worth noting that the LIKE clause only works for strings, but if your not doing any sort of wild card characters, you might as well use the equal sign. (WHERE myKey = 'myValue')
Was This Post Helpful? 0
  • +
  • -

#8 Kempy535  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-November 06

Re: mysql search problem

Posted 26 November 2006 - 03:40 PM

Hi guys i got it to work in the end. Thanks for you help. Heres how I did it.


<?php
$searchf = $_POST["searchf"];
$sql = "SELECT * FROM stocklist WHERE make = '$searchf' OR model = '$searchf' OR type = '$searchf'";

$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result))
  {
  echo $row['make']."&nbsp;&nbsp;".$row['model']."&nbsp;&nbsp;".$row['title'] ."&nbsp;&nbsp;".$row['price'];
  echo "<br />";
  } 
?>


Only problem is I cant get it to work with the wild cards % :(

This post has been edited by Kempy535: 26 November 2006 - 03:41 PM

Was This Post Helpful? 0
  • +
  • -

#9 snoj  Icon User is offline

  • Married Life
  • member icon

Reputation: 93
  • View blog
  • Posts: 3,583
  • Joined: 31-March 03

Re: mysql search problem

Posted 26 November 2006 - 05:02 PM

IIRC, the wildcard only works with LIKE.
Was This Post Helpful? 0
  • +
  • -

#10 Kempy535  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-November 06

Re: mysql search problem

Posted 27 November 2006 - 03:41 AM

Cheers it all working as it should now. Thanks for your help.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1