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_ass oc 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($sq l) or die(mysql_error ()); while($row = mysql_fetch_ass oc($result)) { $newValue = $row['item'] . $row['cost'].", "; $sql = "UPDATE orders_complete SET ord_descr='$new Value ' WHERE date='xx/xx/xx' and time='xx:xx:xx' "; if(!mysql_query ($sql)) { user_error("Fai led 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_ass oc($result))
{
$newValue .= $row['item'] . $row['cost'] . ", ";
}
// remove last comma
$sql = "UPDATE orders_complete SET ord_descr='$new Value ' 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($variab le);[/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\wwwr oot\takeaway\te stinsertitems.p hp 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