1

I keep getting this error every time I try to update a record in MySQL, I tested a smaller version of my form out yesterday in another post here

and I know the code to update the record in MySQL works but when I try to use it in the actual form I need to use I keep getting this error:

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 ''tickets' SET 'work_performed' = 'Test', 'item_qty1' = '1', ' at line 1

here is my code that processes the update:

    <?php
// database connection //

include 'db_connect.php';

//This gets all the other information from the form

// start of form inputs //

$work_performed=$_POST['work_performed'];
$item_qty1=$_POST['item_qty1'];
$item_qty2=($_POST['item_qty2']);
$item_qty3=$_POST['item_qty3'];
$item_qty4=($_POST['item_qty4']);
$item_qty5=$_POST['item_qty5'];
$manuf_1=$_POST['manuf_1'];
$manuf_2=$_POST['manuf_2'];
$manuf_3=$_POST['manuf_3'];
$manuf_4=$_POST['manuf_4'];
$manuf_5=$_POST['manuf_5'];
$part_number1=$_POST['part_number1'];
$part_number2=$_POST['part_number2'];
$part_number3=$_POST['part_number3'];
$part_number4=$_POST['part_number4'];
$part_number5=$_POST['part_number5'];
$part_description1=$_POST['part_description1'];
$part_description2=$_POST['part_description2'];
$part_description3=$_POST['part_description3'];
$part_description4=$_POST['part_description4'];
$part_description5=$_POST['part_description5'];
$part_price1=$_POST['part_price1'];
$part_price2=$_POST['part_price2'];
$part_price3=$_POST['part_price3'];
$part_price4=$_POST['part_price4'];
$part_price5=$_POST['part_price5'];
$price_extension1=$_POST['price_extension1'];
$price_extension2=$_POST['price_extension2'];
$price_extension3=$_POST['price_extension3'];
$price_extension4=$_POST['price_extension4'];
$price_extension5=$_POST['price_extension5'];
$material_total=$_POST['material_total'];
$sales_tax=$_POST['sales_tax'];
$shipping_cost=$_POST['shipping_cost'];
$work_date1=$_POST['work_date1'];
$work_date2=$_POST['work_date2'];
$work_date3=$_POST['work_date3'];
$work_date4=$_POST['work_date4'];
$work_date5=$_POST['work_date5'];
$tech_name1=$_POST['tech_name1'];
$tech_name2=$_POST['tech_name2'];
$tech_name3=$_POST['tech_name3'];
$tech_name4=$_POST['tech_name4'];
$tech_name5=$_POST['tech_name5'];
$cost_code1=$_POST['cost_code1'];
$cost_code2=$_POST['cost_code2'];
$cost_code3=$_POST['cost_code3'];
$cost_code4=$_POST['cost_code4'];
$cost_code5=$_POST['cost_code5'];
$pay_rate1=$_POST['pay_rate1'];
$pay_rate2=$_POST['pay_rate2'];
$pay_rate3=$_POST['pay_rate3'];
$pay_rate4=$_POST['pay_rate4'];
$pay_rate5=$_POST['pay_rate5'];
$total_hours1=$_POST['total_hours1'];
$total_hours2=$_POST['total_hours2'];
$total_hours3=$_POST['total_hours3'];
$total_hours4=$_POST['total_hours4'];
$total_hours5=$_POST['total_hours5'];
$hours_subtotal1=$_POST['hours_subtotal1'];
$hours_subtotal2=$_POST['hours_subtotal2'];
$hours_subtotal3=$_POST['hours_subtotal3'];
$hours_subtotal4=$_POST['hours_subtotal4'];
$hours_subtotal5=$_POST['hours_subtotal5'];
$total_hours=$_POST['total_hours'];
$material_total=$_POST['material_total'];
$labor_cost=$_POST['labor_cost'];
$grand_total=$_POST['grand_total'];
$id=$_POST['id'];

//below section is not ready //
//$employee_number=$_POST['employee_number'];
//$date_finished=$_POST['date_finished'];
//$tech_signature=$_POST['tech_signature'];
//$customer_signature=$_POST['customer_signature'];
//$print_name=$_POST['print_name'];


//Writes the information to the database

    mysql_query("UPDATE 'tickets' SET   'work_performed' = '$work_performed',
                                        'item_qty1' = '$item_qty1',
                                        'item_qty2' = '$item_qty2',
                                        'item_qty3' = '$item_qty3',
                                        'item_qty4' = '$item_qty4',
                                        'item_qty5' = '$item_qty5',
                                        'manuf_1' = '$manuf_1', 
                                        'manuf_2' = '$manuf_2',  
                                        'manuf_3' = '$manuf_3', 
                                        'manuf_4' = '$manuf_4',
                                        'manuf_5' = '$manuf_5',
                                        'part_number1' = '$part_number1',
                                        'part_number2' = '$part_number2',
                                        'part_number3' = '$part_number3',
                                        'part_number4' = '$part_number4',
                                        'part_number5' = '$part_number5',
                                        'part_description1' = '$part_description1', 
                                        'part_description2' = '$part_description2', 
                                        'part_description3' = '$part_description3', 
                                        'part_description4' = '$part_description4',
                                        'part_description5' = '$part_description5', 
                                        'part_price1' = '$part_price1', 
                                        'part_price2' = '$part_price2', 
                                        'part_price3' = '$part_price3', 
                                        'part_price4' = '$part_price4', 
                                        'part_price5' = '$part_price5',
                                        'price_extension1' = '$price_extension1', 
                                        'price_extension2' = '$price_extension2', 
                                        'price_extension3' = '$price_extension3', 
                                        'price_extension4' = '$price_extension4', 
                                        'price_extension5' = '$price_extension5', 
                                        'material_total' = '$material_total', 
                                        'sales_tax' = '$sales_tax', 
                                        'shipping_cost' = '$shipping_cost', 
                                        'work_date1' = '$work_date1',
                                        'work_date2' = '$work_date2',   
                                        'work_date3' = '$work_date3', 
                                        'work_date4' = '$work_date4', 
                                        'work_date5' = '$work_date5', 
                                        'tech_name1' = '$tech_name1', 
                                        'tech_name2' = '$tech_name2', 
                                        'tech_name3' = '$tech_name3', 
                                        'tech_name4' = '$tech_name4', 
                                        'tech_name5' = '$tech_name5', 
                                        'cost_code1' = '$cost_code1', 
                                        'cost_code2' = '$cost_code2', 
                                        'cost_code3' = '$cost_code3', 
                                        'cost_code4' = '$cost_code4', 
                                        'cost_code5' = '$cost_code5', 
                                        'pay_rate1' = '$pay_rate1', 
                                        'pay_rate2' = '$pay_rate2', 
                                        'pay_rate3' = '$pay_rate3', 
                                        'pay_rate4' = '$pay_rate4',
                                        'pay_rate5' = '$pay_rate5', 
                                        'total_hours1' = '$total_hours1', 
                                        'total_hours2' = '$total_hours2', 
                                        'total_hours3' = '$total_hours3', 
                                        'total_hours4' = '$total_hours4', 
                                        'total_hours5' = '$total_hours5', 
                                        'hours_subtotal1' = '$hours_subtotal1', 
                                        'hours_subtotal2' = '$hours_subtotal2', 
                                        'hours_subtotal3' = '$hours_subtotal3', 
                                        'hours_subtotal4' = '$hours_subtotal4', 
                                        'hours_subtotal5' = '$hours_subtotal5', 
                                        'total_hours' = '$total_hours', 
                                        'material_total' = '$material_total', 
                                        'labor_cost' = '$labor_cost', 
                                        'grand_total' = '$grand_total'  WHERE 'id' = '$id'"); 




mysql_affected_rows();

echo mysql_error();

?>
<html>
<body>
<center>
<br><br><br>
<form name="results" method="post" action="ticket_results.php" enctype="multipart/form-data" id="ticketresult">
<input type="submit" class="submit" id="ticketresult" style="width: 165px" value="Do Something">
</form>
</center>
</body>
</html>

UPDATE TO QUESTION,

so I experimented and have removed any duplicate values, such as item_qty2, item_qty3, item_qty4 and minimized my values down to this,

mysql_query("UPDATE `tickets` SET   `work_performed` = '$work_performed',
                                        `item_qty1` = '$item_qty1',
                                        `manuf_1` = '$manuf_1', 
                                        `part_number1` = '$part_number1',
                                        `part_description1` = '$part_description1', 
                                        `part_price1` = '$part_price1', 
                                        `price_extension1` = '$price_extension1', 
                                        `material_total` = '$material_total', 
                                        `sales_tax` = '$sales_tax', 
                                        `shipping_cost` = '$shipping_cost', 
                                        `work_date1` = '$work_date1',
                                        `tech_name1` = '$tech_name1', 
                                        `cost_code1` = '$cost_code1', 
                                        `pay_rate1` = '$pay_rate1', 
                                        `total_hours1` = '$total_hours1', 
                                        `hours_subtotal1` = '$hours_subtotal1', 
                                        `total_hours` = '$total_hours', 
                                        `material_total` = '$material_total', 
                                        `labor_cost` = '$labor_cost', 
                                        `grand_total` = '$grand_total'  WHERE `id` = '$id'");

And this works flawlessly every time, but as soon as I add a value such as item_qty2 or part_number2 etc I get the syntax error. This entire form worked 1 time but required me to input a value in every field but I don't always need a value in every field!

Community
  • 1
  • 1
Jason
  • 224
  • 3
  • 11

2 Answers2

4

The reason why your query won't work is because your are wrapping the column name and table name with single quotes. They are identifiers and not string literals so they shouldn't be wrap with single quote.

UPDATE tickets SET work_performed = .....

If it happens that the column names and/or tables names used are reserved keywords, they can be escape with backticks not with single quotes. ex,

UPDATE `tickets` SET `work_performed` = .....

In this case, the backticks aren't required since none of them are reserved keywords.

Other links:

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    Thanks JW for your help at least somebody stayed on topic! – Jason Jun 01 '13 at 03:05
  • JW for some reason it worked 1 time then stopped so I played with the code and posted up top the new experimental code that seems to work flawlessly but I had to remove 3/4's of my code. – Jason Jun 01 '13 at 04:59
  • @Jason you're updated question seems to be different one from the initial question. You should open up another one for that. Anyway, can you put the query in a variable and echo that variable? And post it here so we can help you debug on it. – John Woo Jun 01 '13 at 05:47
  • I think the issue is with how I how I'm updating the record, it's requiring me to input all of the values, if I don't it gives me a syntax error, I don't always need to fill in all the values so I need to figure a way to use all the values in the form but only update those with a value added to them. I will ask a new question thank you for the help JW it's much appreciated. – Jason Jun 01 '13 at 06:59
  • anyway, try this, in the `POST` part, `$work_performed=(isset($_POST['work_performed'])? $_POST['work_performed'] : 'NULL')` and in your query, `work_performed = COALESCE($work_performed, work_performed)` – John Woo Jun 01 '13 at 07:12
  • Ok I'll give that a try, I posted the new question as well – Jason Jun 01 '13 at 07:24
  • oh sorry it should be, `$work_performed = (isset($_POST['work_performed']) ? "'" . $_POST['work_performed'] . "'" : 'NULL')` – John Woo Jun 01 '13 at 07:24
  • and this is in the query part: `work_performed = COALESCE($work_performed, work_performed)` – John Woo Jun 01 '13 at 07:25
1

If any single one of your inputted values has an apostrophe, it will break your query because you aren't escaping it.

Additionally, column names should be enclosed in backticks `, not single quotes.

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592