SQL Syntax Error

[ryanfilard]

This is my code:

$req = $_GET['s'];
$as = "SELECT users.username, users.fname, users.lname, users.tags FROM users.users WHERE ";
$terms = explode(" ", $req);

foreach($terms as $smart){
$a++;

if ($a == 1)
$as .= "users.tags = '$req' ";

else

$as .= "OR users.tags LIKE '$req' ";

}


mysql_select_db($database_Users, $Users);
$query_Recordset1 = "SELECT username, fname, lname, tags FROM users";
$Recordset1 = mysql_query($smart, $Users) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($smart);
$totalRows_Recordset1 = mysql_num_rows($smart);

 

This is What I Get:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nah' at line 1

 

URL:

 

http://www.celbre.com/search.php?s=nah&button=Search

 

[requinix]

mysql_query($smart, $Users)

$smart isn't the variable you want to be using... You could even say it's !$smart.

[ryanfilard]

Yeah, I could not think of one to use. I will use $queryoutcome

[Maq]

In fact, you shouldn't be using it in any of these lines:

$Recordset1 = mysql_query($smart, $Users) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($smart);
$totalRows_Recordset1 = mysql_num_rows($smart);

[Psycho]

Your query is obviously incorrect. When you have these types of errors, echo the query to the page so you can see what was created. I really can't see why you would get that error, there are problems with your code that generates the $as value for a query - but I don't see where you ever run that query! But, I have to assume you are running it somewhere, so here are the problems I see

 

First, you are defining the table as

FROM users.users WHERE

That is a field - not a table.

 

Second, the if/else within the loop do not have curly braces {} and the execution lines do not immediately follow the if/else. Besides there is a much better way to do that. What I don't understand is what yu are trying to accomplish in that loop. Based upon what you have you want to see if the user tag EXACTLY matches the first value or if the user tag is LIKE the remaining values. Don't you want that to be a LIKE for all of the values? Plus, your logic is wrong - you should be using the $smart variable inside that loop. The code you have will create a WHERE condition multiple times using the complete value sent in the $_GET['s'] variable. Additionally, I don't think you can have spaces in a GET value sent in the query string - they will be converted to %20. You would need to use urldecode() on the value first.

 

Here is an example

//Connect to the database before processing the input

$terms = explode(' ', urldecode($_GET['s']));
//Convert array values for MySQL WHERE conditions
foreach($terms as $key => $value)
{
    if($value =='')
    {
        unset($terms[$key]);
    }
    else
    {
        $value = mysql_real_escape_string($value);
        $terms[$key] => "users.tags LIKE '%{$value}'";
}

//Create the array
$as = "SELECT users.username, users.fname, users.lname, users.tags
       FROM users.users
       WHERE " . implode(" OR ", $terms);

[Psycho]

Yeah, I could not think of one to use. I will use $queryoutcome

 

You aren't understanding what he said. Regardless of whether your variable names make sense, you are using the wrong variables that you create. $smart was the value created within the foreach() loop to create the $as variable that IS a query. But, then you used $smart when trying to execute the query. $smart would only contain the last value in the array. But, had you used proper naming for your variables you would have seen that error.