Problem with "like" in sql query

[TheEddy]

$sql = "SELECT * FROM `users` WHERE `$searchField` like '%$search%'";

 

I still have to put in the EXACT value, it can't be similar.

[Pikachu2000]

Post an example of the search term you used and the data in the database that you feel it should have returned.

[TheEddy]

Post an example of the search term you used and the data in the database that you feel it should have returned.

$searchField would have been "emailAddress" and $search should have been "webmaster" and should have pulled:

 

[email protected] and [email protected]

[Pikachu2000]

That looks like it would work as written. Have you echoed the query string to make sure it contains the values it should contain?

[TheEddy]

That looks like it would work as written. Have you echoed the query string to make sure it contains the values it should contain?

This is what I have for results:

 

$sql = "SELECT * FROM `users` WHERE `$searchField` LIKE '%$search%'";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($result);
$userName = $row['userName'];
$uid = $row['userID'];

  if (mysql_num_rows($result) == 1) {
  print ("
  <ul>
  ");
    while ($row = mysql_num_rows($result)){
      print ("
  <li><a href=\"memberprofiles.php?uid=$uid\" title\"$userName\">$userName</a></li>
  ");
exit;
    }
  print ("
  </ul>
  ");
  }
  else {
    print ("
There were no results based on your criteria.
");
}

[Pikachu2000]

This is your problem: if (mysql_num_rows($result) == 1) {. If more than one result is returned, it lists none of them and echos the "no results" message. If you expect exactly one record to match, you should not be using a WHERE . . . LIKE query. If you expect zero or more records to match, you shouldn't be restricting the result set to one record, and you should echo the results in a while() loop.

[marcus]

Pikachu is absolutely correct. The function mysql_num_rows will return the amount of entries selected from your query, it doesn't return a boolean.

[TheEddy]

  if (mysql_num_rows($result) != 0) {
  print ("
  <ul>
  ");
    while ($row = mysql_fetch_assoc($result)){
  print ("
  <li><a href=\"memberprofiles.php?uid=$uid\" title\"$userName\">$userName</a></li>
  ");
exit;
}
  print ("
  </ul>
  ");
  }
  else {
    print ("
There were no results based on your criteria.
");
}

 

I don't know how I made that mistake where I had that equal to 1.  But I don't know how to loop the results

[marcus]

mysql_fetch_assoc stores the values in an array. So you could either use the list function like:

 

while(list($uid,$userName) = mysql_fetch_assoc($result)){

 

Or when you print the HTML you would do something like

 

echo "<li><a href=\"membersprofiles.php?uid=".$row['uid']."\" title=\"".$row['userName']."\">".$row['userName']."</a></li>\n";

[TheEddy]

What does "\n" do?

[marcus]

It just creates a new line in your output, makes your HTML looks neater in the end.

[TheEddy]

mysql_fetch_assoc stores the values in an array. So you could either use the list function like:

 

while(list($uid,$userName) = mysql_fetch_assoc($result)){

 

Or when you print the HTML you would do something like

 

echo "<li><a href=\"membersprofiles.php?uid=".$row['uid']."\" title=\"".$row['userName']."\">".$row['userName']."</a></li>\n";

That doesn't work btw.

[marcus]

What are you getting for your output?

[TheEddy]

What are you getting for your output?

Nothing will appear unless I search for email and only one result when there should be two.  If I search for email, I don't get the message that there is nothing there but no results are shown.