how to i make a form update page to update database table records, but with multiple records at the same time?

Question

i have a table like this:

id     product_category     product_name     product_range     discount_amt
---------------------------------------------------------------------------
1      Post Card            4x6              5M to 9,999       0.007
2      Post Card            4x6              10M to 14,999     0.01
3      Post Card            4x6              15M to 19,999     0.013
4      Post Card            4x6              20M to 24,999     0.015
5      Post Card            4x6              Over 25M          0.019

i'm calling just the discount_amt column into page like this:

$pricediscountquery = mysql_query("SELECT * FROM pricing_discount") or die(mysql_error());
$i=0;
while($pricingdiscountrow = mysql_fetch_array( $pricediscountquery )) {
$pricingdiscountarray[$i++]=$pricingdiscountrow['discount_amt'];
}

i'm displaying form fields and discount amounts prefilled in the values of the form fields like this:

<p>5M to 9,999: <input type="text" name="pc_4x6_5m_to_9999" value="<?php echo $pricingdiscountarray[0]; ?>" /></p>
<p>10M to 14,999: <input type="text" name="pc_4x6_10m_to_14999" value="<?php echo $pricingdiscountarray[1]; ?>" /></p>
<p>15M to 19,999: <input type="text" name="pc_4x6_15m_to_19999" value="<?php echo $pricingdiscountarray[2]; ?>" /></p>
<p>20M to 24,999: <input type="text" name="pc_4x6_20m_to_24999" value="<?php echo $pricingdiscountarray[3]; ?>" /></p>
<p>Over 25M: <input type="text" name="pc_4x6_over_25m" value="<?php echo $pricingdiscountarray[4]; ?>" /></p>

i'm creating my post variables like this:

$pc_4x6_5m_to_9999 = mysql_real_escape_string(htmlspecialchars($_POST['pc_4x6_5m_to_9999']));
$pc_4x6_10m_to_14999 = mysql_real_escape_string(htmlspecialchars($_POST['pc_4x6_10m_to_14999']));
$pc_4x6_15m_to_19999 = mysql_real_escape_string(htmlspecialchars($_POST['pc_4x6_15m_to_19999']));
$pc_4x6_20m_to_24999 = mysql_real_escape_string(htmlspecialchars($_POST['pc_4x6_20m_to_24999']));
$pc_4x6_over_25m = mysql_real_escape_string(htmlspecialchars($_POST['pc_4x6_over_25m']));

i'm trying to update multiple rows/records at the same time, just in the discount_amt column like this:

mysql_query("INSERT INTO pricing_discount (id,discount_amt) VALUES (1,$pc_4x6_5m_to_9999),(2,$pc_4x6_10m_to_14999),(3,$pc_4x6_15m_to_19999),(4,$pc_4x6_20m_to_24999),(5,$pc_4x6_over_25m) ON DUPLICATE KEY UPDATE discount_amt=VALUES(discount_amt); ") or die(mysql_error());

the one above doesn't create error in code, but it creates error on web page: 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 '),(2,),(3,),(4,),(5,) ON DUPLICATE KEY UPDATE discount_amt=VALUES(discount_amt)' at line 1

also tried this:

mysql_query("INSERT INTO pricing_discount (id,discount_amt) VALUES (1,'$pc_4x6_5m_to_9999'),(2,'$pc_4x6_10m_to_14999'),(3,'$pc_4x6_15m_to_19999'),(4,'$pc_4x6_20m_to_24999'),(5,'$pc_4x6_over_25m') ON DUPLICATE KEY UPDATE discount_amt=VALUES(discount_amt); ") or die(mysql_error());

this gave an error in code so i didn't even save it and try to run it:

mysql_query("INSERT INTO pricing_discount (id,discount_amt) VALUES (1,"$pc_4x6_5m_to_9999"),(2,"$pc_4x6_10m_to_14999"),(3,"$pc_4x6_15m_to_19999"),(4,"$pc_4x6_20m_to_24999"),(5,"$pc_4x6_over_25m") ON DUPLICATE KEY UPDATE discount_amt=VALUES(discount_amt); ") or die(mysql_error());

this also gave error in code:

mysql_query("INSERT INTO pricing_discount (id,"discount_amt") VALUES (1,"$pc_4x6_5m_to_9999"),(2,"$pc_4x6_10m_to_14999"),(3,"$pc_4x6_15m_to_19999"),(4,"$pc_4x6_20m_to_24999"),(5,"$pc_4x6_over_25m") ON DUPLICATE KEY UPDATE discount_amt=VALUES(discount_amt); ") or die(mysql_error());

if you hard code values like this it works, but i'm doing variables:

mysql_query("INSERT INTO pricing_discount (id,discount_amt) VALUES (1,1),(2,3),(3,3),(4,12),(5,12) ON DUPLICATE KEY UPDATE discount_amt=VALUES(discount_amt); ") or die(mysql_error());

what's the correct way to do this?

if you're wondering why i didn't do UPDATE instead of INSERT when trying to update records, it's because i was following this stackoverflow:

Multiple Updates in MySQL

Now would be a really good time to switch to mysqli_ or (better still) PDO. — Strawberry, Oct 31 '13 at 13:41
echo your query so that you can see what's being passed to MySQL. You'll see that all your variables are empty. — Strawberry, Oct 31 '13 at 14:08

score 1 · Accepted Answer · answered Oct 31 '13 at 13:57

1

I am assuming, in your table discount_amt will be float or double datatype. then below sql query qill work. for int, float, decimal or double datatype no need to add value between single quote'.

$query = "
 INSERT INTO pricing_discount 
 (id
 ,discount_amt
 ) VALUES 
 (1,".$pc_4x6_5m_to_9999."),
 (2,".$pc_4x6_10m_to_14999."),
 (3,".$pc_4x6_15m_to_19999."),
 (4,".$pc_4x6_20m_to_24999."),
 (5,".$pc_4x6_over_25m.") 
 ON DUPLICATE KEY UPDATE discount_amt = VALUES(discount_amt);
 ";

mysql_query($query) or die(mysql_error());

answered Oct 31 '13 at 13:57

Rajiv Ranjan

1,869
1
11
20

this seems to work as well. maybe it's better since using floats, however, how to i redirect to another page after submitting form and the database saves the new values? i put this after the query, but it just makes the same page come up, which would be ok but when the page is refreshed, the values in the form fields are the previous values, not the updated values. header("Location: pricing-discount-details.php"); – leoarce Oct 31 '13 at 14:06
can you share page names or flow. Like listing page name, form page name and insert/update page name. Will try will solve the problem. – Rajiv Ranjan Oct 31 '13 at 14:12
i haven't created the view page yet. i'm doing the edit page right now. it's only going to be 2 pages for editing this table. there will be no page to create entries into the table. so when i create the view page, people can go to view first then edit second, but they will be able to go to edit directly if they want to. when done modifying fields in edit page and saving, person should go to view page. view page will be: pricing-discount-details.php. edit page is: edit-pricing-discount.php – leoarce Oct 31 '13 at 14:18
If `header("Location:pricing-discount-details.php");` not working then try javascript redirection `echo '';` after `mysql_query($query) or die(mysql_error());` line. – Rajiv Ranjan Oct 31 '13 at 14:25

score 0 · Answer 2 · answered Oct 31 '13 at 13:47

Just to recap, what's wrong with this...

 $query = "
 INSERT INTO pricing_discount 
 (id
 ,discount_amt
 ) VALUES 
 (1,'$pc_4x6_5m_to_9999'),
 (2,'$pc_4x6_10m_to_14999'),
 (3,'$pc_4x6_15m_to_19999'),
 (4,'$pc_4x6_20m_to_24999'),
 (5,'$pc_4x6_over_25m') 
 ON DUPLICATE KEY UPDATE discount_amt = VALUES(discount_amt);
 ";


  mysql_query($query) or die(mysql_error());

how to i make a form update page to update database table records, but with multiple records at the same time?

2 Answers2