Update mysql fields that were inserted with a php loop

benhowdle89

Hi, i'm trying to build an ordering system for the employees at work to place orders with suppliers. I have managed to insert records into the db fine with a php for loop and jquery grabbing all of the relevant data from table rows, but i need to be able to let the employees edit previous orders if they need to, i imagine in the same way they inserted the records (jquery+php loops, etc)

This is the way i insert the records:

$('#submit').live('click',function(){ 				
						var postData = {};
						postData['data[order_id]'] = $('#order_id').text();
						$('#items tr').not(':first').each(function(index, value) {
							var keyPrefix = 'data[' + index + ']';
							postData[keyPrefix + '[supp_short_code]'] = $(this).closest('tr').find('.supp_short_code').text();
							postData[keyPrefix + '[project_ref]'] = $(this).closest('tr').find('.project_ref').text();
							postData[keyPrefix + '[om_part_no]'] = $(this).closest('tr').find('.om_part_no').text();
							postData[keyPrefix + '[description]'] = $(this).closest('tr').find('.description').text();
							postData[keyPrefix + '[quantity_input]'] = $(this).closest('tr').find('.quantity_input').val();
							postData[keyPrefix + '[cost_of_items]'] = $(this).closest('tr').find('.cost_of_items').text();
							postData[keyPrefix + '[cost_total_td]'] = $(this).closest('tr').find('.cost_total_td').text();
						});

                $.ajax
                    ({
                    type: "POST",
                    url: "updateorder.php",
					dataType: "json",
                    data: postData,
                    cache: false,
                    success: function()
                        {
							alert("Order Updated");
                        }
                    });
			});

And my updateorder.php:

if (isset($_POST['data']) && is_array($_POST['data'])) {
					foreach ($_POST['data'] as $row => $data) {
						$result = mysql_query("UPDATE orders SET project_ref='".$data['project_ref']."' where order_id = '".$data['order_id']."'") or die(mysql_error());
						$result1 = mysql_query("UPDATE orders SET supp_short_code='".$data['supp_short_code']."' where order_id = '".$data['order_id']."'") or die(mysql_error());
						$result2 = mysql_query("UPDATE orders SET om_part_no='".$data['om_part_no']."' where order_id = '".$data['order_id']."'") or die(mysql_error());
						$result3 = mysql_query("UPDATE orders SET description='".$data['description']."' where order_id = '".$data['order_id']."'") or die(mysql_error());
						$result4 = mysql_query("UPDATE orders SET quantity='".$data['quantity_input']."' where order_id = '".$data['order_id']."'") or die(mysql_error());
						$result5 = mysql_query("UPDATE orders SET cost_of_items='".$data['cost_of_items']."' where order_id = '".$data['order_id']."'") or die(mysql_error());
						$result6 = mysql_query("UPDATE orders SET cost_total='".$data['cost_total_td']."' where order_id = '".$data['order_id']."'") or die(mysql_error());
					}
				}

When i run this query it sets all of the fields to "1" (which is the order_id the person is editing)

A couple of questions, could anyone think of a better way to structure the mysql tables? and can someone suggest how to edit the order if its been inserted via for loops? I'm also aware that i'm not protecting against sql injection or anything yet, but i will i promise!

Thanks

Bjom

I'm also aware that i'm not protecting against sql injection or anything yet, but i will i promise!

No most likely you will not. It's either getting built in from the start or never. Better switch to mysqli with prepared statements now.

What's inside the $data array?
Have a look at it like so:

echo '<pre>';
print_r($data);
echo '</pre>';

the update statement can address more than one field at a time btw

SET fld1 = 'somevalue', fld2 = 'some other value', fld3 = 'whatever value'

benhowdle89

Hmm, i printed out $data and it just shows exactly what the post should have been but it still set all of the fields to "1"??

Bjom

needs more scrutinizing then
next step: go into the loop look at $data, next step after that: modify your code like so:

$sql = "UPDATE orders SET project_ref='".$data['project_ref']."' where order_id = '".$data['order_id']."'";
echo $sql;
$result = mysql_query($sql);

and look at the sql that you actually send..

benhowdle89

ok, i used this PHP instead:

if (isset($_POST['data']) && is_array($_POST['data'])) {
				  //$sql = "UPDATE orders SET ";
				  foreach ($_POST['data'] as $row => $data) {
					//$sql .= "quantity='".$data['quantity_input']."',";
					$sql = "UPDATE orders SET quantity='".$data['quantity_input']."' where order_id = ".$data['order_id']."";
					echo $sql;
					$result = mysql_query($sql); 
				  }
				}

And i see in Firebug the echo response was this:

UPDATE orders SET quantity='2' where order_id = 2UPDATE orders SET quantity='4' where order_id =

So its trying to execute two queries, where it should be one. In my jQuery i say not:first. I would thinking that would stop it reading the <th> row??

Bjom

Get rid of the . "" at the end of the sql statement for one thing.

If quantity is integer double or single, then get rid of the quotation marks around your values.

$sql = "UPDATE orders SET quantity=".$data['quantity_input']." where order_id = ".$data['order_id'];

Did you get an error when executing the sql? did it work? what did it insert?

The other thing would be to integrate those update statements into one. then have a look again.

About the firebug thing: I think it just blends in the two echos. shouldnt have anything to do with the actual execution of the sql