help in sending php array to mysql table

[psycho007]

I was wondering if somebody could help me. this is driving me crazy.

I am trying to send an array from a mysql_fetch_assoc result from table total_orders to table orders_complete. I have the following code:

[php]
<?php include("config.php");$sql = "SELECT item, cost FROM orders_total";$result = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { $newValue = $row['item'] . $row['cost'].", "; $sql = "UPDATE orders_complete SET ord_descr='$newValue ' WHERE date='xx/xx/xx' and time='xx:xx:xx'"; if(!mysql_query($sql)) { user_error("Failed to update row: $newValue", E_USER_NOTICE); }}?>
[/php]

The above code sucessfully updates the row with time=xx:xx:Xx and date=xx/xx/xx but...
When i search the orders_complete table for the new updated row. It only seems to enter into the table the last key of the array and not the FULL array
i.e.
total_orders table echos

12" pizza £12
half lb burger £3.40
side portion of chips

would enter into the database ONLY side portion of chips

if i put a exit(); inbetween the 2 end }} shown at the end of the script
when I do a seach for the row it only shows as entered

12" pizza £12

and nothing else.

I was wondering if somebody could help me in what im doing wrong. it looks like that the while loop when cycled is overwriting each entry hence I can only see 1 value of the array rather than the total array seporated with a ,

[Dormilich]

the problem is the UPDATE statement. as long as date and time are the same (as seems to be) your previous entries will be overwritten (that's what the UPDATE command does). move the mysql_query() out of the while loop.
[PHP]while($row = mysql_fetch_assoc($result))
{
$newValue .= $row['item'] . $row['cost'] . ", ";
}
// remove last comma
$sql = "UPDATE orders_complete SET ord_descr='$newValue ' WHERE date='xx/xx/xx' and time='xx:xx:xx'";[/PHP]

[psycho007]

Hi thanks for such a quick responce.

I have removed the comma and placed the sql update statement outside the while loop. Didnt seem to make a difference. The update query entered the last array value. also if you do a [php]print_r($variable);[/php] this also shows the last item in the array. been trying to get my head around this for 2 days now :o(

[Dormilich]

could you post a var_dump() of your sql (UPDATE) query?

check if you have ".=" in the while loop, otherwise $newValue will always be the last array element.

[psycho007]

sure

this is a var_dump of the sql query out of the while loop

string(109) "UPDATE orders_complete SET ord_descr='Hlf lb Chicken burger3.50, ' WHERE date='06/11/08' and time='13:33:53'"

below shows a var_dump of the sql statement within the while loop

string(109) "UPDATE orders_complete SET ord_descr='9 Inch Seafarer Pizza8.00, ' WHERE date='06/11/08' and time='13:33:53'" string(111) "UPDATE orders_complete SET ord_descr='9 Inch Beef Eater Pizza8.00, ' WHERE date='06/11/08' and time='13:33:53'" string(113) "UPDATE orders_complete SET ord_descr='9 Inch Plus Special Pizza8.00, ' WHERE date='06/11/08' and time='13:33:53'" string(124) "UPDATE orders_complete SET ord_descr='Hlf lb Garlic Mushroom Cheese burger3.50, ' WHERE date='06/11/08' and time='13:33:53'" string(109) "UPDATE orders_complete SET ord_descr='Hlf lb Chicken burger3.50, ' WHERE date='06/11/08' and time='13:33:53'"

[Dormilich]

move the sql statement out of the loop. use the loop only to build the SET string like reply #2.

[psycho007]

ok

moved the sql statement out of the link and changed the $newValue within the loop to:
[php]
$newValue = $row['item'] .= $row['cost'];
[/php]

still only inserts the last entry in the array :o(

[psycho007]

oh hang on a min, i think u did it :o)

[Dormilich]

[PHP]$newValue .= $row['item'] . $row['cost'] . ", ";[/PHP]

[psycho007]

very strange. it does insert but i get an error message on line 7 when I add the . after the $newValue
[php]
$newValue .= $row['item'] .= $row['cost'];
[/php]

error message receive

PHP Notice: Undefined variable: newValue in C:\Inetpub\wwwroot\takeaway\testinsertitems.php on line 7

although it does insert it into the table. how can I not get this error message to appear?

[Dormilich]

see post #7, it doesn't make sense to call the .= operator (add following to current value) twice.

add [PHP]$newValue = '';[/PHP] before the loop

PS double (or better triple) check the code you write, you have quite a lot of typos that trouble you

[psycho007]

sorry im new to php programming and im not too sure what you mean. If i remove the . after the $newValue it only inserts the last value in the array, if i keek the . after the $newValue it inserts the entire array but displays the error message i posted in the previous post

[Dormilich]

probably the best would be if you post the script (or the relevant part) it's hard to guess the errors from one line of code.

[psycho007]

wow its worked, array fully entered into the table with no errors. Your a star :o)

sorry im new to php ;o)

[Dormilich]

no, I'm a god (though xml god) - nah, joking.
to be honest, it was more of a sql understanding and "find the typo" problem

congratulations that you finally made it

[psycho007]

i added a ',' at the end of the $newValue so that each entry into the table field would be seporated with a , as shown as :

[php]
$newValue .= $row['item'] .= $row['cost'].=',';
[/php]

My next stage would be then to retreive the string as a whole and use the explode function to seporate each entry via a comma.

would it be something like

[php]
explode(',', $row['item']);
[/php]

cut a long story short I have been asked to create a web based internal ordering system for a local Pizza delivery company. the total_orders table is a temporary table which holds exactly what the customer would like. then using the code you have helped me with, it will then be inserted into the orders_complete table for later reference, so for example if the casher needs to re print the customer receipt or the kitchen check they can do.

[Dormilich]

you make me curious. I like to have a look at the complete code, there seems room for improvement (or comments). you could also PM the code to me.

[psycho007]

I bet your right, as im new i bet i could probily cut half the code down to achully what i am using. ive been working on it for nearly 2 months now, and have learnt a lot, well not enough to overcome your help with was greatfully received :o) I dont mind, i think you would probily laugh at the amount of if and else if statements im using,LOL.

I would like to add you to my Buddy's list of thats ok with you? how would you want me to pass the full code to you?

[Dormilich]

I would like to add you to my Buddy's list of thats ok with you? how would you want me to pass the full code to you?

one - that ok with me
two - do as you desire, you can contact me via my member details (click on the name...)

i think you would probily laugh at the amount of if and else if statements im using,LOL.

I bet you'd laugh about my code, I had written after learning php for 2 months, too (so would I now)

[psycho007]

ha, you should see my database.sql text for creating the tables LOL

theres about 15 tables or so.

[psycho007]

so, whats your thoughts on retreiving the array and seporating each line using the comma. do you think the explode function is the best way forward for this?

[Dormilich]

can't say that without the full code. there is always (nearly) more than one way to solve a problem.

[psycho007]

ok i will get the code to you tomorrow if thats ok? getting a little late now :o(

thank you again for helping me out so rapidly. You should be in a programming job :o)

[Dormilich]

ok i will get the code to you tomorrow if thats ok? getting a little late now :o(

that leaves me now some time for watching some more slayers episodes

thank you again for helping me out so rapidly. You should be in a programming job :o)

sometimes i wish it would

one last question for today: is the pizza good?

[psycho007]

LOL yes, definatly haha its the best pizza in town :o)

hehe

if you dont mind, tomorrow I will post you the total.php

basically im having problems in removing a single row from the order_total table