Mystery SQL errors popping up


#1

I have a web application built in PHP accessing a MySQL database on the back end that has been running flawlessly for 4 years. I made one minor modification back in March, and it has been running flawlessly the entire 4 years.

All of a sudden, today it has developed amnesia! I am getting errors like these:

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 ‘AND approved =1 ORDER BY branchNumber ASC LIMIT 0, 2000’ at line 4

and

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

Does anyone know of any recent changes to either SQL or PHP on the servers that might be precipitating this? The specific databases in question are running on the rafiki:knabe server.

Suggestions?

Thanks so very much.

James Yarrow
Maccimizer


#2

What is the data type of field approved?


#3

The data type is int(1) with 0 being not approved, and 1 being approved. As stated before, this is an application that has been working flawlessly for 4 years, that suddenly on Friday decided that it was rife with errors. This screams version change, but I can’t get confirmation from anyone that this is what has happened.

Jim


#4

I can’t think of anything. Probably support of “`” or some other characters is deprecated. But I never heard of anything like that yet.

What does DH support say?


#5

The error is likely just before

AND approved =1

so what is in the SQL statement at that place?

Just a guess: it’s something like this in PHP: $sql = "SELECT … WHERE somefield=’$somevalue’ AND approved =1 ";
If $somevalue sometimes contains an unescaped single apostrophe, you will get an error.


#6

Other possibility that comes to mind is that you’re using a column name which was OK in earlier versions of MySQL, but is a reserved word in newer versions (such as 5.1, which we’re in the process of upgrading to). If that’s the case, you will need to stick backticks (these things) around the offending column name in your SQL.


#7

Support says the change to PHP5 caused the failures (this is, of course, without them even looking at a speck of my code or what is happening with it).

To put this into context a brief overview:

All pages use an include file to open the connection to the server and set the SQL server variables. This is what is in the include file:

<?php # FileName="Connection_php_mysql.htm" # Type="MYSQL" # HTTP="true" $hostname_conn_snoopy = "snoopy.some_server_somewhere.com"; $database_conn_snoopy = "snoopy"; $username_conn_snoopy = "username"; $password_conn_snoopy = "password"; $conn_snoopy = mysql_pconnect($hostname_conn_snoopy, $username_conn_snoopy, $password_conn_snoopy) or trigger_error(mysql_error(),E_USER_ERROR); ?>

This gets called on the top of my orders pages, orders1, 2 and 3 with this command:

<?php require_once('Connections/conn_snoopy.php'); ?>

On page 1 this works, and when a branch is selected from the pulldown list, and the name of the person is entered, when submit is clicked, the data IS posted to the SQL server (I looked in the database, and the new record is created with the new data).

The code for the submit button on page 1 is as such:

The code for the submit button on page 2 is as such:

A hint to me that something isn’t right in China on the orders2 page is the fact that data that should be showing up on the top of the page isn’t showing up. Here are the calls that are supposed to display the data:

[color=#FF4500]<?php echo $lastid;?>[/color]

<?php echo $today; ?> Branch Number: [color=#FF4500][b]<?php echo $row_orderDetail['branchNumber']; ?>[/b][/color] Branch Name: [color=#FF4500][b]<?php echo $row_branchName['branchName']; ?>[/b][/color] Ordered By: [b][color=#FF4500]<?php echo $row_orderDetail['name']; ?>[/color][/b]

The really wild thing about the first variable called, $lastid, is carried from page to page in the URL, and it shows up correctly in the URL.

The connection calls to the server are exactly the same on all three pages, however it seems that after posting the data from the initial page, PHP seems not to be communicating with the SQL server any more.

Has anyone had this problem or does anyone have a suggestion? This is absolutely maddening, since this php/sql solution has been working flawlessly for over 4 years and suddenly overnight it is broken and I’m having to rewrite it.

Jim


#8

[quote=“maccimizer, post:7, topic:52909”]
The connection calls to the server are exactly the same on all three pages, however it seems that after posting the data from the initial page, PHP seems not to be communicating with the SQL server any more. [/quote]

There doesn’t appear to be a problem with connecting to the server or how you display the data. You’re just not going to get data until you fix the syntax errors in the SQL statements.

These syntax errors are being generated by the PHP code that actually fetches the data from the server - not the code for connecting to the server or displaying the data.

If you’re saying the $lastid is empty and you expect it to have the value in the query string, then you are probably overlooking explicitly obtaining the value from the query string with something like:

$lastid = $_GET[‘lastid’];


#9

You found the problem. I was using the old call $HTTP_GET_VARS which has been deprecated. Changing it to the $_GET as you recommended made the problem go away.

Thank you SO VERY MUCH, and thanks to all in the community who spend their valuable time trying to assist me with this matter.

Jim Yarrow[hr]

This is what is in that place:

$branchNumber = $_GET[‘branchNumber’];

mysql_select_db($database_conn_snoopy, $conn_snoopy);
$query_rs_orders = “SELECT *
FROM orders
WHERE branchNumber=$branchNumber
AND approved = 1
ORDER BY branchNumber ASC”;

If I manually send what’s between the quotes to my SQL server via myPhpAdmin, I get this error:

#1054 - Unknown column ‘$branchNumber’ in ‘where clause’

$branchNumber is a string set in the previous screen that tells the server which branch we want order history from.

This is the code from the previous page which is supposed to be setting up the $branchNumber variable. It may be wrong, but I don’t have the expertise to figure out exactly how it is wrong. Your assistance is very much appreciated.

Show orders only from this Branch: <?php do { ?> <?php echo $row_branchNumbers['branchNumber']?> <?php } while ($row_branchNumbers = mysql_fetch_assoc($branchNumbers)); $rows = mysql_num_rows($branchNumbers); if($rows > 0) { mysql_data_seek($branchNumbers, 0); $row_branchNumbers = mysql_fetch_assoc($branchNumbers); } ?>

#10

try

mysql_select_db($database_conn_snoopy, $conn_snoopy);
$query_rs_orders = “SELECT *
FROM orders
WHERE branchNumber=”.$branchNumber.“
AND approved = 1
ORDER BY branchNumber ASC”;


#11

Same error is returned after replacing my code with the code suggested.

Jim


#12

try changing the
$branchNumber = $_GET[‘branchNumber’];
to
$branchNumber = $_POST[‘branchNumber’];


#13

For what it’s worth, you can generalize that to $_REQUEST[‘branchNumber’], which will use either $_GET or $_POST depending on which one has the value set.


#14

Changing it to $_REQUEST worked like a charm. That page is now working flawlessly. Can $_REQUEST be popped in anywhere in the place of $_GET or $_POST?

Thanks again to all who have helped me work out the little bugs since the server version upgrades.

Jim Yarrow


#15

Yes — as long as you don’t need to specifically look at GET or POST arguments (which may be the case at times!), $_REQUEST can be substituted for either.


#16

I suppose I should be less lazy and RTFM. Thank you for your kind and patient response. The information was valuable and solved the problem.

Jim