Simple "WHERE" question...

[SpockBert]

Sorry this will probably seem a ridiculously simple thing to ask but putting "where" in the search returns nothing..too common a word.

Anyway, here's the question: Can you have two arguments in a mySQL WHERE statement?

Need to query a list of hotels by both Country and City and having trouble getting it to work.

mysql_query("SELECT * FROM hoteldetails WHERE country='$country' "); //works ok

mysql_query("SELECT * FROM hoteldetails WHERE city='$whatCity' "); //works ok

mysql_query("SELECT * FROM hoteldetails WHERE country='$country' AND city='$whatCity' "); //does'nt work

mysql_query("SELECT * FROM hoteldetails WHERE country='$country' && city='$whatCity' "); //also doesn't work 🙁

So I dont know whether its a syntax issue, using the wrong thing for "and" or I'm asking something that can't be done.

Simon

[toplay]

All your syntax posted look correct. Have you tried displaying the full SQL so you can see what values are being used in the search? Then are you sure your table have those values?

If those are coming from a form, then I hope you're not relying on register_globals being on and have used the $GET or $POST variables instead. Example:

$country = isSet($POST['country']) ? $POST['country'] : ''; // Set empty or to a default value

[SpockBert]

Can i ask which syntax was ok? 'AND' or the '&&' one?

The country comes from the query string and the city var comes from a function. The values for '$whatCity' and '$country' seem to be ok as they work fine when used seperately, its only when used both together in the MySQL WHERE statement that i get no hotels returned.

[leatherback]

Hi,

Both look fine. You could try to add brackets around them, although that should not be a propblem

But echo out the full query string, and post it, maybe something funny is happening. Or of course..

THe combination does not exist in the database. I am confident you checked that though...

[SpockBert]

Ah fixed it, seems I was wrong, country on its own didn't work!

I had the country var from the query string and the city being passed to a function that contained all the sql code but I had the actual "$country = $_GET['country'];" outside the function, just tried moving it inside the function and it now works.

Thanks for your help though guys, and ToPlay, that bit of code you posted:

$country = isSet($POST['country']) ? $POST['country'] : ''; // Set empty or to a default value

I'll probably use that in another little problem I got concerning the min() function returning blank values! so cheers for that.

Thanks again guys.

[toplay]

SpockBert wrote:

Can i ask which syntax was ok? 'AND' or the '&&' one?

The country comes from the query string and the city var comes from a function. The values for '$whatCity' and '$country' seem to be ok as they work fine when used seperately, its only when used both together in the MySQL WHERE statement that i get no hotels returned.

leatherback and I think the syntax looks alright. You still haven't displayed the full query for us. We need to see it and you need to double check that you have those values in your table.

Make sure you have MySQL error checking in place in case there is a syntax error in the query. Example:

// An example for retrieving zero or more rows
// $country and $whatCity should already be set to something and
// addslashes is used if necessary.

$sql = "SELECT * FROM hoteldetails WHERE country='$country' AND city='$whatCity'";

echo "Query is: $sql <br>";  // Display it for us!

$result = mysql_query($sql);
if (!$result) {
   // Handle error
   echo 'Query failed. SQL: ', $sql, '<br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
   exit;
}
// The while loop stops when there's no data left; it might not even go in loop
// and echo anything when there's no data returned on the first call to
// mysql_fetch_assoc()
while($row = mysql_fetch_assoc($result)) {  // Retrieve data until no more
   echo $row['country'], '<br />';
   echo $row['city'], '<br />';
}

[toplay]

SpockBert wrote:

Ah fixed it, seems I was wrong, country on its own didn't work!

I had the country var from the query string and the city being passed to a function that contained all the sql code but I had the actual "$country = $_GET['country'];" outside the function, just tried moving it inside the function and it now works.

Thanks for your help though guys, and ToPlay, that bit of code you posted:

$country = isSet($POST['country']) ? $POST['country'] : ''; // Set empty or to a default value

I'll probably use that in another little problem I got concerning the min() function returning blank values! so cheers for that.

Thanks again guys.

Oh, good. I'm glad you fixed it.

Happy coding 🙂