Query in PHP doesn't work but run in PHP MyAdmin works

[michaelkirby]

Hi all,

 

I have a query in my php code that partly works but the AND statement is not working. When run in PHP MyAdmin this query worked fine. Any ideas?? See code below:

 

$query="select * from users where towncity= 'Maidenhead' and fname = $search_text or organisationname ='$search_text' or surname ='$search_text'";

[otuatail]

Are we supposed to answere this ?

[Alex]

You're missing a pair of quotes.

 

$query="select * from users where towncity= 'Maidenhead' and fname = '$search_text' or organisationname ='$search_text' or surname ='$search_text'";

[michaelkirby]

Thanks for pointing out that I missed the quotes but still the AND statement is not applied. I'm just not to sure how this works in PHP MyAdmin but not when I run the same query through my code???

 

Any ideas?

[ScotDiddle]

michaelkirby,

 

I wasn't sure of your "and / or" grouping, so I added my own...

 

The following code worked for me.

 

Scot L. Diddle, Richmond VA

 

<?php

Header("Cache-control: private, no-cache");
Header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
Header("Pragma: no-cache");


require('DB_Connect.php');

$customer_num  = '9898989898';
$search_text   = 'G2000';
$search_color  = 'SKY';
$search_vendor = 'Gildan';


$query="select * from cart where customer_num = '9898989898' and (sku = '$search_text' or color_code ='$search_color' or vendor_full ='$search_vendor');";

$result = mysql_query($query);

if (!$result) {
	echo "Failed Query in script : " . __FILE__ . " on line : " . __LINE__ - 3;
}
else {

	while ($row = mysql_fetch_array($result)) {

		$display = TRUE;

		if ($display) {
		    printArray($row, '$row', __FILE__, __LINE__);
		}

	}
}

function printArray($arrayName, $name = false, $file=NULL, $line=NULL) {

	if ($file == NULL) {
		$file = '*** Not Specified ***';
	}

	if ($line == NULL) {
		$line = '*** Not Specified ***';
	}

	if ($name)  {
		echo "Name :: $name" . " :: Reporting from line :: " . $line .  " :: in program :: " . $file . " ::<br /> \n";
	}
	if (!is_array($arrayName)) {

		$saveArray = $arrayName;

		$arrayName = 'Not An Array';

		$possibleString = TRUE;
	}
	if (empty($arrayName)) {
		$arrayName = 'Array Contains No Data';
	}

	echo "<font color=\"#205E75\"> \n";
	echo "<pre> \n";

		print_r($arrayName);

	echo "</pre> \n";

	echo "</font> \n";

	if ($possibleString == TRUE) {

		$string = $saveArray;

		if ($string == NULL) {

			$string = 'Null String';
		}

		echo "<font color=\"#205E75\"> \n";
		echo " <br /> <br /> \n";
		echo '$array as string: ' . $string  . "<br /> <br /> \n";
		echo " <br /> <br /> \n";
		echo "</font> \n";

	}
}


?>

[Ken2k7]

I have already explained this. Please refer here - http://www.phpfreaks.com/forums/index.php/topic,296307.msg1403392.html#msg1403392

 

It's not that your AND statement is NOT applied. It is, but just in the wrong order. Like I said in the other topic, AND has precedence over OR. So because of that, your SQL will first match this:

 

towncity= 'Maidenhead' and fname = '$search_text'

So it'll try to find where towncity is equal to Maidenhead and fname is equal to [/tt]$search_text[/tt]. If not matches are found there, it will then continue with OR.

 

So I'll break it down:

1. towncity= 'Maidenhead' and fname = '$search_text'

2. or organisationname ='$search_text'

3. or surname ='$search_text'

 

That's not the order you want. In fact, you want:

1. towncity= 'Maidenhead' and

2. fname = '$search_text' or organisationname ='$search_text' or surname ='$search_text'

 

So to make sure OR runs first, you need to enclosed them in parentheses.

 

It's like in math:

[tex]5+4\cdot6=29[/tex]

That's because multiplication takes precedence over addition.

 

[tex](5+4)\cdot6=54[/tex]

Parentheses takes precedence over multiplication.

 

Same logic here.

 

I hope this explains the issue clearly.

 

All the best,

Ken