PHP Query Failing After Site Move

[prayingmantis]

I just moved hosting providers for my website and there is one page that is not working properly. The old provider had MySQL Server 5.0.51a and PHP 5 so I setup the new server with MySQL Server 5.0.51a-Community and PHP 5.

After the site move, I get the following error:

query failed: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 1

This is the corresponding query that is failing:

$db = new ReviewDB();

    $review_query = "select * from tb_review_dvd where review_dvd = $review_dvd";
    $rq_result = mysql_query( $review_query, $db->handle );
    if( !$rq_result ) die ( "query failed:" . mysql_error() );
    $review = mysql_fetch_array( $rq_result, MYSQL_BOTH );

I have the same issue on the site with this code (basically the same coding):

$review_query = "select * from tb_review_theatricle where review_theatricle = $review_theatricle";
    $rq_result = mysql_query( $review_query, $db->handle );
    if( !$rq_result ) die ( "query failed:" . mysql_error() );
    $review = mysql_fetch_array( $rq_result, MYSQL_BOTH );

Any ideas what would be causing the problems since the servers are setup mostly in the same manner?
Reply With Quote

[cwarn23]

Although this is not a common problem and most would disagree that this problem exists, there is a slight incompatibility (from my point of view) with some versions of MySQL scripts. This is because in some later versions of MySQL (like you have possibly switched) there is a thing with quotations being necessary. So try replacing your first block of code with the following:

$db = new ReviewDB();

    $review_query = "SELECT * FROM `tb_review_dvd` WHERE `review_dvd` = '".$review_dvd."'";
    $rq_result = mysql_query($review_query, $db->handle);
    if(!$rq_result) die ("query failed:" . mysql_error());
    $review = mysql_fetch_array($rq_result, MYSQL_BOTH );

So in the MySQL syntax, database names and column names are surrounded by an apostrophie ` (Top left corner of keyboard beside number 1 key) and when finding the values in a database, they are surrounded by a regular set of single quotation marks. There is more to it than what I have just said such as arrays but those are the essentials that I have mentioned.

[prayingmantis]

Wow! Thanks for the quick answer. I tried the code and I am still getting:

query failed: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 1

This one has me stumped, I have never seen this happen before.

[cwarn23]

Try replacing line 3 with the following and check if the same message occurs. If the same message occurs then it means that the error is between the word 'where' and the last quotation mark.

$review_query = "SELECT * FROM `tb_review_dvd` WHERE `review_dvd` = '$review_dvd'";

Also try the below code if all of that fails:

$db = new ReviewDB();

    $review_query = "SELECT * FROM `tb_review_dvd` WHERE `review_dvd` = '$review_dvd'";
    $rq_result = mysql_query($review_query);
    if(!$rq_result) die ("query failed:" . mysql_error());
    $review = mysql_fetch_array($rq_result, MYSQL_BOTH );

[prayingmantis]

They are both failing. I made sure that the servers were setup identically so I am really confused as to why it does not work on the new server. Thanks for your help.

[lordspace]

I would use this:

$quoted = mysql_real_escape_string($review_dvd);

$review_query = "SELECT * FROM `tb_review_dvd` WHERE `review_dvd` = '$quoted'";

Note:
if you have other SQL queries like that do consider yourself as a future victim of SQL injection attack(s).