Complex Query Inconsistency

DJM

Could some kindly soul please tell me why the first query works - it selects only those records that the query specifies, but the second doesn't work - it selects all records irrespective of the string passed to it?

    

$Result=mysql_query("SELECT * FROM $TableName
    WHERE subscriber_email LIKE '%$match%' AND subscriber_confirmed = 'Y' OR subscriber_name LIKE '%$match%' AND subscriber_confirmed = 'Y'
     OR subscriber_email LIKE '%$match' AND subscriber_confirmed = 'Y' OR subscriber_name LIKE '%$match' AND subscriber_confirmed = 'Y'
      OR subscriber_email LIKE '$match%' AND subscriber_confirmed = 'Y' OR subscriber_name LIKE '$match%'  AND subscriber_confirmed = 'Y'
       ORDER BY subscriber_email,subscriber_name", $ConnectID);



 $Result=mysql_query("SELECT * FROM $TableName
    WHERE subscriber_email NOT LIKE '%$no_match%' AND subscriber_confirmed = 'Y' OR subscriber_name NOT LIKE '%$no_match%' AND subscriber_confirmed = 'Y'
     OR subscriber_email NOT LIKE '%$no_match' AND subscriber_confirmed = 'Y' OR subscriber_name NOT LIKE '%$no_match' AND subscriber_confirmed = 'Y'
      OR subscriber_email NOT LIKE '$no_match%' AND subscriber_confirmed = 'Y' OR subscriber_name NOT LIKE '$no_match%'  AND subscriber_confirmed = 'Y'
       ORDER BY subscriber_email,subscriber_name", $ConnectID);

Thank you

jeremuck

You could probably do it yourself with good debugging techniques.

How do you know the first or the second query even worked at all? There could be an error with the database, and that's why it doesn't do what you expect.

Separate the data from the code: store the query string in it's own variable and print them out to the screen. Then you can inspect it very carefully.

Go into phpmyadmin and try the SQL statement, or log in to the client via the shell and try it.

Try this, and see if you can figure it out.


function printDBerror($message = "", &$dbLink)
{
	if($dbLink != "")
	{
		echo "MySQL said: " . mysql_error($dbLink) . "<br>";
	}

die(print( $message));
}

function echeckDB($message = "", &$dbLink)
{
	if(mysql_error($dbLink) != "")
	{
		printDBerror($message, &$dbLink);
	}

}

$sqlOne = "SELECT * FROM $TableName

WHERE subscriber_email LIKE '%$match%' AND subscriber_confirmed = 'Y' OR subscriber_name LIKE '%$match%' AND subscriber_confirmed = 'Y'

 OR subscriber_email LIKE '%$match' AND subscriber_confirmed = 'Y' OR subscriber_name LIKE '%$match' AND subscriber_confirmed = 'Y'

  OR subscriber_email LIKE '$match%' AND subscriber_confirmed = 'Y' OR subscriber_name LIKE '$match%'  AND subscriber_confirmed = 'Y'

   ORDER BY subscriber_email,subscriber_name";

$Result=mysql_query($sqlOne, $ConnectID);
echeckDB("Query: " . $sqlOne . "<br>", &$ConnectID);



$sqlTwo = "SELECT * FROM $TableName

WHERE subscriber_email NOT LIKE '%$no_match%' AND subscriber_confirmed = 'Y' OR subscriber_name NOT LIKE '%$no_match%' AND subscriber_confirmed = 'Y'

 OR subscriber_email NOT LIKE '%$no_match' AND subscriber_confirmed = 'Y' OR subscriber_name NOT LIKE '%$no_match' AND subscriber_confirmed = 'Y'

  OR subscriber_email NOT LIKE '$no_match%' AND subscriber_confirmed = 'Y' OR subscriber_name NOT LIKE '$no_match%'  AND subscriber_confirmed = 'Y'

   ORDER BY subscriber_email,subscriber_name";


 $Result=mysql_query($sqlTwo, ConnectID);

echeckDB("Query: " . $sqlTwo . "<br>", &$ConnectID);

echo "First Query: " . $sqlOne . "<br>";
echo "Second Query: " . $sqlTwo . "<br>';

DJM

Thanks for the code, I'll keep it for future use.

I ran the code, and it didn't produce any errors. The result is below (I've edited it a bit to improve readability)

First Query:

SELECT * FROM jssm_subscriber_details WHERE

subscriber_email LIKE '%ea%' AND subscriber_confirmed = 'Y'
OR
subscriber_name LIKE '%ea%' AND subscriber_confirmed = 'Y'
OR
subscriber_email LIKE '%ea' AND subscriber_confirmed = 'Y'
OR
subscriber_name LIKE '%ea' AND subscriber_confirmed = 'Y'
OR
subscriber_email LIKE 'ea%' AND subscriber_confirmed = 'Y'
OR
subscriber_name LIKE 'ea%' AND subscriber_confirmed = 'Y'

ORDER BY subscriber_email,subscriber_name

Second Query:

SELECT * FROM jssm_subscriber_details WHERE

subscriber_email NOT LIKE '%andy%' AND subscriber_confirmed = 'Y'
OR
subscriber_name NOT LIKE '%andy%' AND subscriber_confirmed = 'Y'
OR
subscriber_email NOT LIKE '%andy' AND subscriber_confirmed = 'Y'
OR
subscriber_name NOT LIKE '%andy' AND subscriber_confirmed = 'Y'
OR
subscriber_email NOT LIKE 'andy%' AND subscriber_confirmed = 'Y'
OR
subscriber_name NOT LIKE 'andy%' AND subscriber_confirmed = 'Y'

ORDER BY subscriber_email,subscriber_name

The two queries are syntactically identical, but the first still works as expected, and the second still pulls out every record regardless of the search term.

I know that the first query works because I test it with just 6 records so that I can predict what it should pull out beforehand, and it is always 100% correct.

I'm just as baffled as ever, but thanks for your help anyway.

tsinka

Hi,

I think '%ea% alread includes ea% and %ea so you don't need the second and third ones.

First query:

SELECT * FROM jssm_subscriber_details WHERE

(subscriber_email LIKE '%ea%' AND subscriber_confirmed = 'Y' )
OR
(subscriber_name LIKE '%ea%' AND subscriber_confirmed = 'Y')
ORDER BY subscriber_email,subscriber_name

Second query:

SELECT * FROM jssm_subscriber_details WHERE

(subscriber_email NOT LIKE '%andy%' AND subscriber_confirmed = 'Y')
OR
(subscriber_name NOT LIKE '%andy%' AND subscriber_confirmed = 'Y')
ORDER BY subscriber_email,subscriber_name

What exactly do you want to select with the second query it might be neccessary to correct it.

DJM

Thanks tsinka, that's a much neater way of doing it. The %var and var% are definitely not needed, so it saves a bit of code.

The second query still doesn't work though. The idea is to exclude records containing the search string (e.g. all ".hotmail" addresses). I should add that the two queries are not run at the same time.

Wien

what do you get when you execute this :

SELECT * FROM jssm_subscriber_details WHERE (subscriber_email NOT LIKE '%andy%' OR subscriber_name NOT LIKE '%andy%') AND subscriber_confirmed = 'Y'
ORDER BY subscriber_email,subscriber_name

tsinka

If you want to make that really sure you can try:

SELECT * FROM jssm_subscriber_details WHERE subscriber_email NOT LIKE '%andy%' AND subscriber_name NOT LIKE '%andy%' AND subscriber_confirmed = 'Y'
ORDER BY subscriber_email,subscriber_name

This would select all records where both subscriber name and subscriber email do not contain andy.

If you just want to exclude all records where the email doesn't contain andy:

SELECT * FROM jssm_subscriber_details WHERE subscriber_email NOT LIKE '%andy%' AND subscriber_confirmed = 'Y'
ORDER BY subscriber_email,subscriber_name

Nearly the same for the subscriber name.

If you use OR then e.g. it would also select all records where the email contains andy but the name doesn't contain andy which you might not want to happen.

DJM

Thanks for all the suggestions. This is the only one that works the way I want it to:

WHERE
subscriber_email NOT LIKE '%$no_match%' AND subscriber_name NOT LIKE '%$no_match%' AND subscriber_confirmed = 'Y'

That is, if the search term occurs anywhere in either field, the record is not selected.