...

View Full Version : php & mysql weird issues!



semiSkim
04-17-2011, 10:16 PM
The below code returns rows when run through the console, but always returns 0 rows when run through PHP! Whats even more odd is that if the contents of the variable are inserted directly into the mysql_query statement then the code executes fine!



else{$sortSQL = '';}
$newSQL = 'SELECT ' . $fields . ' FROM ' . $table . $conditionalSQL . $sortSQL;

$connection = connect();

$result = mysql_query($newSQL);


the $newSQL variable is as follows:


SELECT * FROM event WHERE eventDirector LIKE '%danny%' ORDER BY eventTitle ASC


EDIT:

when the SQL is set to the following it works, so for some reason the removal of the SQL like resolves the issue!? :


SELECT * FROM event ORDER BY eventTitle ASC



Thanks!

AnalogSF
04-17-2011, 10:34 PM
That's strange. Dose doing this work:


$result = mysql_query("SELECT * FROM event WHERE eventDirector LIKE '%danny%' ORDER BY eventTitle ASC");

If so try using var_dump to dump the $newSQL variable to make sure it is what you think it is.


var_dump($newSQL);

Looking at


$newSQL = 'SELECT ' . $fields . ' FROM ' . $table . $conditionalSQL . $sortSQL;

There are no spaces between $table, $conditionalSQL & $sortSQL. Do the variables contain them? otherwise the SQL it's creating might not be valid.

semiSkim
04-17-2011, 10:39 PM
Your first suggestion works, and the varibale outputs:



SELECT * FROM event WHERE eventDirector LIKE '%danny%' ORDER BY eventTitle ASC


The spaces have already been applied before the SQL is joined, and you can see its fine above! I have copied and pasted the code into phpmyadmin and it works fine!


EDIT:

var dump just for the hell of it!:


string(88) "SELECT * FROM event WHERE eventDirector LIKE '%danny%' ORDER BY eventTitle ASC"

bullant
04-17-2011, 10:45 PM
is



SELECT * FROM event WHERE eventDirector LIKE '%danny%' ORDER BY eventTitle ASC
a result of the following echo?


$newSQL = 'SELECT ' . $fields . ' FROM ' . $table . $conditionalSQL . $sortSQL;

echo $newSQL; die();if it is then either

1) you are not connected to the db

2) the table name does not exist

3) the column names you are selecting do not exist

4) no data in eventDirector matches %danny%

otherwise insert the echo to see what the actual query being run is.

AnalogSF
04-17-2011, 10:46 PM
What about:


$result = mysql_query($newSQL) or die(mysql_error());

Dose it show any errors? and dose echo mysql_num_rows($result); give 0 as the result too?

semiSkim
04-17-2011, 10:52 PM
yes it is the result of an echo.

1) am connected to the DB, it works without the WHERE clause

2) the table name does exist

3) the column name does exist

4) the data is there, and the query fails to return anything with even a character such as "a" (for which there should be many, many results!)



I'm confused :/

The connection is defiantly to the correct database, double checked that!

semiSkim
04-17-2011, 10:53 PM
What about:


$result = mysql_query($newSQL) or die(mysql_error());

Dose it show any errors? and dose echo mysql_num_rows($result); give 0 as the result too?

ah we seem to be making progress thanks!



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 ''%danny%' ORDER BY eventTitle ASC' at line 1




string(88) "SELECT * FROM event WHERE eventDirector LIKE '%danny%' ORDER BY eventTitle ASC"

bullant
04-17-2011, 11:09 PM
run the echo again because you now have a syntax error message which tells you your query is not correct and so the original output from the echo no longer applies. I suspect you have changed something without realising it.

AnalogSF
04-17-2011, 11:12 PM
Well at least you know the query is failing. I can't see what's wrong with it though. That is the exact value of $newSQL just before it is used in the query?

Other than what bullant has said I'm not sure what could be wrong. Is there definitely a space between LIKE and '%danny%'

semiSkim
04-17-2011, 11:19 PM
this is very weird....

This is the result of an echo directly before the query:


SELECT * FROM event WHERE eventDirector LIKE '%danny'%' ORDER BY eventTitle ASC
[code]

if I copy the SQL directly into the query it works fine!!!

I have also tried the below SQL in a variable and that also fails with a syntax error:
[code]
SELECT * FROM event WHERE eventDirector='danny' ORDER BY eventTitle ASC


Interestingly this doesn't have a syntax error:


SELECT * FROM event WHERE eventDirector=1 ORDER BY eventTitle ASC


EDIT: there is definitely a space!

AnalogSF
04-17-2011, 11:27 PM
That is weird. :confused:

Can you post the code that assigns $conditionalSQL its value. Maybe there is something very strange with that since changing it to eventDirector=1 works.

semiSkim
04-17-2011, 11:29 PM
One more thing to note:

if I put this line before the query then it works fine:


$newSQL = "SELECT * FROM event WHERE eventDirector LIKE '%danny%' ORDER BY eventTitle ASC";


Anyways here is a larger chunk of the code!


if(strlen($SQL) != 0){$conditionalSQL = ' WHERE ' . $SQL;}else{$conditionalSQL = '';}
if(strlen($currentSort) != 0){$sortSQL = ' ORDER BY ' . $c_dataSource[$currentSort] . ' ' . strtoupper($c_setSort[$currentSort]);}
else{$sortSQL = '';}
$newSQL = 'SELECT ' . $fields . ' FROM ' . $table . $conditionalSQL . $sortSQL;

$connection = connect();

$result = mysql_query($newSQL) or die(mysql_error());

AnalogSF
04-17-2011, 11:35 PM
So doing:


if(strlen($SQL) != 0){$conditionalSQL = ' WHERE ' . $SQL;}else{$conditionalSQL = '';}
if(strlen($currentSort) != 0){$sortSQL = ' ORDER BY ' . $c_dataSource[$currentSort] . ' ' . strtoupper($c_setSort[$currentSort]);}
else{$sortSQL = '';}
$newSQL = 'SELECT ' . $fields . ' FROM ' . $table . $conditionalSQL . $sortSQL;

$connection = connect();

var_dump($newSQL);
$result = mysql_query($newSQL) or die(mysql_error());


definitely outputs just the SQL posted and nothing else?

What about the code that assigns $SQL.

semiSkim
04-17-2011, 11:51 PM
yep!

I just made a change as you posted to update this:


$conditionalSQL = ' WHERE ' . $SQL;


to this:


$conditionalSQL = " WHERE eventDirector='danny'";


and it then works! Something weird is happening with this statement.

Value of $SQL just before its assigned to $conditionalSQL:


string(38) "eventDirector LIKE '%danny%'"


how $SQL is attained:


$SQL = urldecode($xml->tableSettings[0]->SQL);

AnalogSF
04-17-2011, 11:57 PM
Must be something to do with the value of $SQL then.

Could you post the value of $xml->tableSettings[0]->SQL without urldecoding it.

semiSkim
04-17-2011, 11:59 PM
object(SimpleXMLElement)#2 (1) { [0]=> string(54) "eventDirector%20LIKE%20%26apos%3B%25danny%25%26apos%3B" }

AnalogSF
04-18-2011, 12:05 AM
It urldecodes to:


eventDirector LIKE '%danny%'

Your browser is changing ' to ' but mysql_query isn't that is the problem.

Update:

Forgot to post fix.

Doing:


str_replace(''', "'", urldecode($xml->tableSettings[0]->SQL));


Should fix it.

semiSkim
04-18-2011, 12:09 AM
so it does!

Thanks very much for your help! :)

Now to sort out the decoding......!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum