Reputation: 224
Can't figure out why I keep getting a Mysql error
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!
Upvotes: 1
Views: 85
Answers (2)
Reputation: 263723
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.
Upvotes: 4
Reputation: 324640
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.
Upvotes: 1