...

View Full Version : Errors going from 3.23.52 to 4.1.20



ro1960
08-18-2006, 01:29 PM
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.

guelphdad
08-18-2006, 03:24 PM
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.

ro1960
08-18-2006, 04:06 PM
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:



<?
$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>";
}
?>

Fumigator
08-18-2006, 04:26 PM
Well here's what I found in the online manual:

Upgrading MySQL (http://dev.mysql.com/doc/refman/4.1/en/upgrade.html)

Upgrading from 3.23 to 4.0 (http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html)

Upgrading from 4.0 to 4.1 (http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html)

Beagle
08-18-2006, 04:44 PM
I don't think it's from register globals, but it might be from magic_quotes being on.

guelphdad
08-18-2006, 05:12 PM
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.

Kid Charming
08-18-2006, 05:15 PM
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:



SELECT col FROM table WHERE id = $id


becomes



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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum