Page 1 of 1

PHP/MySQL Mystery Error

Posted: Tue Jul 15, 2008 1:42 pm
by Sephirangel
Hey there,
Im new to these forums and this is my first post so ill get to the point!
The following code is giving me the following error:

"Could not carry out the query because: 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"

Code: Select all

for($n = 1; $n <= $noOfItems; $n++){
                $itemCode = $_SESSION["ItemCode".$n];
                $quant = $_SESSION["Quantity".$n];
                $price = $_SESSION["PricePerItem".$n];
                
                $salesNoQ = "INSERT INTO saleitems (Sales#, ItemCode, SaleQuantity, PricePerItem) VALUES ('".$salesNo."','".$itemCode."','".$quant."','".$price."');";
                
                if(mysql_query($salesNoQ)){
                    echo "Query has been carried out successfully!";
                }else{
                    die("Could not carry out the query because: ".mysql_error());
                }
                
            }
I am pretty stumped...as this error would imply that one of my quotation marks is out of place in the mysql statement yet i keep getting the second part of the last iff statement! The statement is carried out a multiple amount of times depending on the amount of items a customer buys, hence the need for it being dynamic.

Any suggestions? Ive been picking my brains at this for hours!
Thanks in advance!

Josh

Re: PHP/MySQL Mystery Error

Posted: Tue Jul 15, 2008 1:46 pm
by Dynamis
Sephirangel wrote:
$salesNoQ = "INSERT INTO saleitems (Sales#, ItemCode, SaleQuantity, PricePerItem) VALUES ('".$salesNo."','".$itemCode."','".$quant."','".$price."');";
What is w/ the # after sales?

Also, you do not need a semi-colon at the end of your statement. If the # is not needed your code should be

Code: Select all

$salesNoQ = "INSERT INTO saleitems (Sales, ItemCode, SaleQuantity, PricePerItem) VALUES ('".$salesNo."','".$itemCode."','".$quant."','".$price."')";
with # (not sure if this character is allowed though)

Code: Select all

$salesNoQ = "INSERT INTO saleitems (Sales#, ItemCode, SaleQuantity, PricePerItem) VALUES ('".$salesNo."','".$itemCode."','".$quant."','".$price."')";
Probably much better technique to name the variable sales_num instead of sales#

EDIT:
I just tried to make a table w/ a column named sales# and it would not work. So I'm assuming # is not valid in columns names.

Re: PHP/MySQL Mystery Error

Posted: Tue Jul 15, 2008 5:17 pm
by Sephirangel
I have re-named the field to Sales_Num and have found the query to be working now so many thanks!!! =)
I have another odd error though, on this same page, all of my queries seem to be executing twice. The record that the query enters is entered into the database twice. I have no 'open' conditional statements in the rest of the page so im not sure why its doing this.

Any ideas? Many thanks Dynamis for the advise!

Re: PHP/MySQL Mystery Error

Posted: Tue Jul 15, 2008 5:52 pm
by Dynamis
Considering the code to add to the database is inside your for loop, that would suggest that the for loop is going through an additional time. Perhaps print out the values of noOfItems as it iterates to make sure they are correct.

Re: PHP/MySQL Mystery Error

Posted: Tue Jul 15, 2008 9:03 pm
by jack_indigo
Sephirangel wrote: "Could not carry out the query because: 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"
It might help if you put a die($salesNoQ); after line #6 (going by your original post) and read what the SQL statement is. Then, cut and paste and feed into a MySQL command prompt to see what the error message was in perhaps more detail.

You'll want to start using backtick characters (`) on your `column` and `table names` because as time progresses and MySQL starts expanding its syntax, your favorite little table or column name may interfere with a MySQL command. I didn't do this for like six years and I had to start doing this after I migrated a project from MySQL 4 to 5 and had a nightmare of 60 pages to have to do this on, using the just-in-case approach to MySQL migration.

More advice:

* I'm a fan of adapted Hungarian syntax instead of variables like $itemCode. This helps me and other programmers later on to visualize what the main intent was for a variable. So I would be doing something like $sItemCode and $nQuant and $nPrice.

* I stopped using $_SESSION. It's harder to put into a web farm when you want to scale the app, and then you have the issue of session timeouts. Instead, I either store stuff in the database, accessed by a key I keep in a cookie, or I just use encrypted session and/or persistent cookies. If you haven't used the mcrypt_encrypt and mcrypt_decrypt API, consider it because it runs faster than you doing your own custom encryption with character mangling functions. You might need to add this mcrypt extension to your PHP, however.

* Put a space before and after the concat symbol (.). It makes it easier to read the logic.

* Use of mysql API is faster, but there's a few problems with it. Most of the active development these days is going into the PDO API, even though PDO is a little slower. However, that's all about to change with mysqlnd -- which will make PDO very fast. So I recommend avoiding mysql and mysqli API and going with PDO API.

* I give you an A+ for keeping the { on the same line as the declaration. I'm old school and that's how we did it back in the day. I see no reason to change. For me, it makes it easier to read the code that way.

* Don't know if that was a typo or what, but your closing } appears to be indented wrong, making it hard to visually realize it's mated to your for() statement.

* Consider 4 character width tabs, not 8.

* Instead of doing variable inserts into your SQL, consider using one of these approaches:

Strategy # 1. Don't use SQL directly. Use an ORM tool like Outlet ORM or Propel ORM. This helps you rip out code much faster.

Strategy # 2. Stick all your SQL statements in a central conf file. Store them as templated SQL with ^ or ? where the variables should go. Assign these constants to a variable when you need them, and then replace the ? or ^ symbols inside with a preg_replace statement (one iteration, not global replace) with your new variable. For a zippy fast conf file, just make it into a class variable with public variables storing the templated SQL statements.

Strategy # 3. Adapt Strategy # 2 but don't use preg_replace. Instead, use PDO's bindValue() API before running the SQL.

* Avoid spraying your SQL statements throughout the project, making it hard to fix a particular SQL statement without having to grep the entire project for similar SQL statements that might need the same or similar fix. One of the above 3 strategies will help you achieve that.

* I don't often see ProperCase type SQL column and table names. Most database designs I've seen used all lowercase table and column names, and then stuck the underscore (_) between words: sale_quantity, price_per_item. For me, when it's like 3am and I'm tired, it helps me separate variables from SQL statements when reading programming logic.

* You get an A+ for making your SQL statement syntax in UPPERCASE. This helps people see the logic a lot better and delineate it from the rest of the programming logic.

Re: PHP/MySQL Mystery Error

Posted: Wed Jul 16, 2008 6:03 am
by Sephirangel
Wow. Alot of advice and tips there jack_indigo! I will take these on board and check out PDO! Thankyou very much for the advice as I am fairly new to web development. Much appreciated!

Dynamis, the sql statement changes to accomodate the potentially infinite number of items, so the sql statement could execute an x number of times. What is happening is that it is entering the same record twice for 1 item. However, I believe that I have solved this error now so many thanks for your advice! =)

A very informative and warm welcome to the forums from both of you so thankyou!