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.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Dec 2005
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Errors going from 3.23.52 to 4.1.20

    I am migrating a client's site to a new host and it's running different versions of PHP and MySQL.

    The old site is running MySQL 3.23.52 / PHP Version 4.4.2

    The new site is running MySQL 4.1.20 / PHP Version 4.3.2

    As a result I am encountering a few errors specially with the mySQL queries.

    I get error messages like this (not very helpful):
    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 '' at line 13

    Where can I find some help about adapting my queries to work with MySQL 4.1.20?

    Thanks.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by ro1960
    I get error messages like this (not very helpful):
    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 '' at line 13
    Well it is about as helpful as your post.

    what is the query you are using?
    are you running it in php or phpmyadmin or something or straight in the mysql client?

    if you give us that information we may be able to help you with a solution.

  • #3
    New Coder
    Join Date
    Dec 2005
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry for my lack of clarity. I thought maybe there is obvious syntax differences between the 2 MySQL versions.


    So the query is in a php file, the variables are passed from a search form. I ran the query directly in phpMyAdmin and it worked and returned the expected results.

    I think this must have to do with register_globals being off on this server. I probably need to use $_POST to retrieve the passed variable values used in the query. But I am not very familiar with that so I will post my code here for your review:


    PHP Code:
    <?
    $connection 
    mysql_connect($host,$user,$password)
           or die ( 
    mysql_error() );
    $db mysql_select_db($database,$connection)
           or die ( 
    mysql_error() );

    $item "$q"
    $escaped_q mysql_real_escape_string($item); 
         
    if (
    $q_category == "any")
    {
    $cond1 "";
    }
    else
    {
    $cond1 "AND mag_toc.category = '$q_category'";
    }
           
    if (
    $q_journalist == "any")
    {
    $cond2 "";
    }
    else
    {
    $cond2 "AND mag_toc.journalist = '$q_journalist'";
    }

    if (
    $simple)
    {
    $query "    SELECT mag_toc.id AS toc_id, id_issue, date_archive, title, category, journalist, magazine.id, magazine.date_issue
                FROM mag_toc, magazine
                WHERE 
                CURDATE() >= magazine.date_issue
                AND magazine.id = mag_toc.id_issue
                AND
                (
                (mag_toc.title LIKE '%$escaped_q%' OR mag_toc.description LIKE '%$escaped_q%' OR mag_toc.body LIKE '%$escaped_q%') 
                )
                ORDER BY $order"
    ;
    }
    else
    {
    $query "    SELECT mag_toc.id AS toc_id, id_issue, date_archive, title, category, journalist, magazine.id, magazine.date_issue
                FROM mag_toc, magazine
                WHERE 
                CURDATE() >= magazine.date_issue
                AND magazine.id = mag_toc.id_issue
                AND
                (
                (mag_toc.title LIKE '%$escaped_q%' OR mag_toc.description LIKE '%$escaped_q%' OR mag_toc.body LIKE '%$escaped_q%') 
                AND (mag_toc.id_issue >= '$q_id_issue1' AND mag_toc.id_issue <= '$q_id_issue2')
                 $cond1
                 $cond2
                )
                ORDER BY $order"
    ;
    }

    $result mysql_query($query)
           or die ( 
    mysql_error() );
    echo 
    "<p class=\"header-burgundy\" style=\"margin-top:0px; margin-bottom:20px\">Investment & Technology Magazine - Search Results:</p><table cellspacing=\"4\" width=\"100%\">";
    while (
    $row mysql_fetch_array($result))
    {
    extract($row);
    $f_date_issue=date("M. Y",strtotime($date_issue));
    echo
    "<tr bgcolor=\"#eeeeee\"><td valign=\"top\"><b>".$title."</b><br /><span class=\"tblue\"><i>".$f_date_issue."</i></span></td><td align=\"center\" valign=\"bottom\">
    <a href=\"javascript:openArticle('body/article_view.php?id="
    .$toc_id."','popup".$toc_id."');\">VIEW</a></td></tr>";
    }
    ?>
    Last edited by guelphdad; 08-18-2006 at 04:37 PM.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Well here's what I found in the online manual:

    Upgrading MySQL

    Upgrading from 3.23 to 4.0

    Upgrading from 4.0 to 4.1

  • #5
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    I don't think it's from register globals, but it might be from magic_quotes being on.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Well the problem isn't your versions it is your PHP. You can't just slap cond1 cond2 in your query like you have. It will get interpreted to whatever you set it to. and if that gets set to "" as you have above then that is what shows in the query and mysql does not know what to do with it.

    you are better off ending the query without either of those items in AND when they do have a value you then ADD them to the end of the query.

  • #7
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Echo your query variables to see exactly what's being sent to the server.

    If your previous server had register_globals on, and the new one doesn't, that will screw up your queries, because if you're counting on your variables automatically being in the global space, you're sending a lot of nothing. This query:

    Code:
    SELECT col FROM table WHERE id = $id
    becomes

    Code:
    SELECT col FROM table WHERE id =
    Which will kick back the exact error you're describing. When you echo your queries, though, you'll be able to see for sure if your variables are really there.


  •  

    Posting Permissions

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