Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Regular Coder
    Join Date
    Feb 2010
    Posts
    104
    Thanks
    5
    Thanked 3 Times in 3 Posts

    php & mysql weird issues!

    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!

    Code:
    else{$sortSQL = '';}
    $newSQL = 'SELECT ' . $fields . ' FROM ' . $table . $conditionalSQL . $sortSQL;	
    	
    $connection = connect();
    	
    $result = mysql_query($newSQL);
    the $newSQL variable is as follows:
    Code:
    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!? :
    Code:
    SELECT * FROM event ORDER BY eventTitle ASC

    Thanks!
    Last edited by semiSkim; 04-17-2011 at 11:26 PM.

  • #2
    New Coder
    Join Date
    Apr 2011
    Location
    UK
    Posts
    29
    Thanks
    0
    Thanked 7 Times in 7 Posts
    That's strange. Dose doing this work:

    PHP Code:
    $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.

    PHP Code:
    var_dump($newSQL); 
    Looking at

    PHP Code:
    $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.

  • #3
    Regular Coder
    Join Date
    Feb 2010
    Posts
    104
    Thanks
    5
    Thanked 3 Times in 3 Posts
    Your first suggestion works, and the varibale outputs:

    Code:
    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!:
    Code:
    string(88) "SELECT * FROM event WHERE eventDirector LIKE '%danny%' ORDER BY eventTitle ASC"

  • #4
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    is

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

    Code:
    $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.

  • #5
    New Coder
    Join Date
    Apr 2011
    Location
    UK
    Posts
    29
    Thanks
    0
    Thanked 7 Times in 7 Posts
    What about:

    PHP Code:
    $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?

  • #6
    Regular Coder
    Join Date
    Feb 2010
    Posts
    104
    Thanks
    5
    Thanked 3 Times in 3 Posts
    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!

  • #7
    Regular Coder
    Join Date
    Feb 2010
    Posts
    104
    Thanks
    5
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by AnalogSF View Post
    What about:

    PHP Code:
    $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!

    Code:
    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
    Code:
    string(88) "SELECT * FROM event WHERE eventDirector LIKE '%danny%' ORDER BY eventTitle ASC"

  • #8
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    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.
    Last edited by bullant; 04-18-2011 at 12:11 AM.

  • #9
    New Coder
    Join Date
    Apr 2011
    Location
    UK
    Posts
    29
    Thanks
    0
    Thanked 7 Times in 7 Posts
    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%'

  • #10
    Regular Coder
    Join Date
    Feb 2010
    Posts
    104
    Thanks
    5
    Thanked 3 Times in 3 Posts
    this is very weird....

    This is the result of an echo directly before the query:
    [code]
    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:
    Code:
    SELECT * FROM event WHERE eventDirector=1 ORDER BY eventTitle ASC
    EDIT: there is definitely a space!

  • #11
    New Coder
    Join Date
    Apr 2011
    Location
    UK
    Posts
    29
    Thanks
    0
    Thanked 7 Times in 7 Posts
    That is weird.

    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.

  • Users who have thanked AnalogSF for this post:

    semiSkim (04-18-2011)

  • #12
    Regular Coder
    Join Date
    Feb 2010
    Posts
    104
    Thanks
    5
    Thanked 3 Times in 3 Posts
    One more thing to note:

    if I put this line before the query then it works fine:
    Code:
    $newSQL = "SELECT * FROM event WHERE eventDirector LIKE '%danny%' ORDER BY eventTitle ASC";
    Anyways here is a larger chunk of the code!
    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());

  • #13
    New Coder
    Join Date
    Apr 2011
    Location
    UK
    Posts
    29
    Thanks
    0
    Thanked 7 Times in 7 Posts
    So doing:
    PHP 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();

    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.

  • Users who have thanked AnalogSF for this post:

    semiSkim (04-18-2011)

  • #14
    Regular Coder
    Join Date
    Feb 2010
    Posts
    104
    Thanks
    5
    Thanked 3 Times in 3 Posts
    yep!

    I just made a change as you posted to update this:
    Code:
    $conditionalSQL =  ' WHERE ' . $SQL;
    to this:
    Code:
    $conditionalSQL =  " WHERE eventDirector='danny'";
    and it then works! Something weird is happening with this statement.

    Value of $SQL just before its assigned to $conditionalSQL:
    Code:
    string(38) "eventDirector LIKE '%danny%'"
    how $SQL is attained:
    Code:
    $SQL = urldecode($xml->tableSettings[0]->SQL);
    Last edited by semiSkim; 04-18-2011 at 12:54 AM.

  • #15
    New Coder
    Join Date
    Apr 2011
    Location
    UK
    Posts
    29
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Must be something to do with the value of $SQL then.

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


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •