Darn 'mysql_fetch_array(): supplied argument is not a valid MySQL' error.

[Russia]

I have this search script, that finds a username in a table and displays it.

 

                             <form name="search" method="post" action="<?=$PHP_SELF?>">
Search: <input type="text" name="username" />
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>                            
                                            
                                            
<? 
//This is only displayed if they have submitted the form 
$searching = $_POST['searching'];
$find = $_POST['username'];
if ($searching =="yes") 
{ 
echo "<h2>Results</h2><p>"; 

//If they did not enter a search term we give them an error 
if ($find == "") 
{ 
echo "<p>You forgot to enter a search term"; 
exit; 
} 

// Otherwise we connect to our Database 
// We preform a bit of filtering

$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

//Now we search for our search term, in the field the user specified 
$data = mysql_query("SELECT username FROM users WHERE upper($find) LIKE'%$find%'"); 

//And we display the results 
while($result = mysql_fetch_array( $data )) 
{ 
echo $result['username']; 
echo "<br>"; 
echo "<br>"; 
} 

//This counts the number or results - and if there wasn't any it gives them a little message explaining that 
$anymatches= mysql_num_rows ($data); 
if ($anymatches == 0) 
{ 
echo "Sorry, but we can not find an entry to match your query<br><br>"; 
} 

//And we remind them what they searched for 
echo "<b>Searched For:</b> " .$find; 
} 
?>     

                       

For some reason it does not work and gives this message:

 

[b]Warning[/b]:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in [b]C:\Program Files\EasyPHP5.2.10\www\yanille\users.php[/b] on line [b]153[/b]

Error: Unknown column 'MI' in 'where clause'

 

 

The reason it says MI is because I searched mi in the database which is part of the username in mike123

 

What would be the problem?

 

I think I did make the query correct. Didn't I?

 

Thanks for the help ahead of time.

[Zurev]

Not sure why you're using mysql UPPER() when you are making the string uppercase before that with PHP's strtoupper().

 

So you can get rid of upper() in your query, though the real issue I would assume is not having a space after LIKE.

 

$data = mysql_query("SELECT username FROM users WHERE $find LIKE '%$find%'"); 

[Russia]

The same error:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\EasyPHP5.2.10\www\yanille\users.php on line 152

Plus another one.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Program Files\EasyPHP5.2.10\www\yanille\users.php on line 160
Sorry, but we can not find an entry to match your query

Thanks for the help you are providing.

 

 

Error: Unknown column 'MI' in 'where clause'

[Zurev]

Wait, oh wow my total bad. You're letting the user choose what column they want to select out of the database? Obviously that's going to fail.

 

$data = mysql_query("SELECT username FROM users WHERE `username` LIKE '%$find%'"); 

 

username, or whatever field you're looking to compare it to.

[Russia]

Okay thanks, right now it only displays the username as an echo, im trying to make it echo a few more things like the id of the user and the level they are.

 

I tried adding

 

while($result = mysql_fetch_array( $data )) 
{ 
echo $result['username']; 

//NEW ADDED FIELDS
echo $result['level'];
echo $result['user_id'];

echo "<br>"; 
echo "<br>"; 
}

 

Those columns do exist.

 

Basically from the search I want to display a few things.

[Zurev]

So the query issue is completely resolved? And the username(s) show from the echo?

 

And the level/user_id do not? What does it say if anything?

[Russia]

It just doesnt show it

Like its not even there

 

but it shows the username I searched for.

 

Lets say there are like mike, mike12, and michael

 

If I search for 'mi'

 

This is how it appears

 

mike



mike12



michael

 

 

I also need it to show under the names it found, some more information.

 

 

 

[PFMaBiSmAd]

SELECT username

 

You are only SELECTing the username in the query, so that is the only value available. If you want to select other columns, you would need to write your query to select those other columns or to select all your columns, which ever is more appropriate.

[Russia]

How exactly would I make it select multiple columns, but only search in the 'username' column?

 

Thus allowing me to post more information about the user found.

[Zurev]

You can select * from which selects all column information, or select username, user_id, level

 

The latter is more efficient if you don't need ALL information about the user.

[Russia]

Thanks so much mate, I appreciate it.

[Russia]

Here is the final code anyone is free to use

 

<form name="search" method="post" action="<?=$PHP_SELF?>">
Search: <input type="text" name="username" />
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>							


<? 
//This is only displayed if they have submitted the form 
$searching = $_POST['searching'];
$find = $_POST['username'];
if ($searching =="yes") 
{ 
echo "<h2>Results</h2><p>"; 

//If they did not enter a search term we give them an error 
if ($find == "") 
{ 
echo "<p>You forgot to enter a search term"; 
exit; 
} 

// Otherwise we connect to our Database 
// We preform a bit of filtering

$find = strtolower($find); 
$find = strip_tags($find); 
$find = trim ($find); 

//Now we search for our search term, in the field the user specified 
  $data = mysql_query("SELECT * FROM users WHERE `username` LIKE '%$find%'"); 
//$data = mysql_query("SELECT username FROM `users` WHERE $find LIKE %$find%"); 
//And we display the results 
while($result = mysql_fetch_array( $data )) 
{ 
echo $result['username']; 
  echo "<br>"; 
echo $result['level'];
  echo "<br>"; 
echo $result['user_id'];
echo "<hr>"; 
} 

//This counts the number or results - and if there wasn't any it gives them a little message explaining that 
// $anymatches= mysql_num_rows($data); 
if (mysql_num_rows($data) == 0) 
{ 
echo "Sorry, but we can not find an entry to match your query<br><br>"; 
} 

//And we remind them what they searched for 
echo "<b>Searched For:</b> " .$find; 
} 
?>