mysql_fetch_array problem

need to get the results of each row into another page

[James_V]

Hi,
Im having a problem with a script im trying to use to upload an xml order. The table is arranged like this:

orders_id products_model products_qty
10 1234 1
15 5678 2
15 90 1
10 3456 1

I need pull the model and qty for each item ordered and insert into another page formated to upload to a server in xml.

Here is what I have so far:
order.php file

# Get items and qty
$query = ("select products_model, products_quantity from orders_products where orders_id = '" . (int)$order_id . "'");
$result = mysql_query($query) or die ('<p>' . mysql_error());
while ($row = mysql_fetch_array($result)) {
$item_info = $row['products_model']; #<---Problem
$item_qty = $row['products_quantity']; #<---Problem
}
__________________________________
xml.php file

<item>
<itemNumber>'. $item_info .'</itemNumber>'; # first item for order 10
$content .= '<qty>'. $item_qty .'</qty>
</item>
<item>
<itemNumber>'. $item_info .'</itemNumber>'; # second item for order 10
$content .= '<qty>'. $item_qty .'</qty>
</item>

Problem being that im not sure how to get each row from the query into a variable to include it in the xml.php file. I know that the while statement will loop but how do I get each row and not just the first or last one. I can echo the results just fine just can't seem to pull each order row to insert into the xml.php
Does this make any sense?

Thanks

[ironik]

You could try storing it as an array:

while ($row = mysql_fetch_array($result)) {
$item_info[] = $row['products_model']; #<---Problem
$item_qty[] = $row['products_quantity']; #<---Problem
}

And then on the xml.php page use foreach to iterate through one array and supply a key for accessing the second.

$content = '';
foreach ($item_info as $key=>$val)
{
$content .= '<item>
<itemNumber>'. $val .'</itemNumber>'; # second item for order 10
$content .= '<qty>'. $item_qty[$key] .'</qty>
</item>
}

Or, if you don't need to explicitly store the mysql results in a seperate variable you could use the while() loop to generate your content:

$content = '';
while ($row = mysql_fetch_array($result)) {
$content .= '<item>
<itemNumber>'. $row['products_model'] .'</itemNumber>'; # second item for order 10
$content .= '<qty>'. $row['products_quantity'] .'</qty>
</item>
}

[James_V]

Ok, I see where your going but i cant see to get it without getting an invalid foreach error.
Here is exactly what I have right now:

Order.php

-edited out-

# Get items and qty
$query = ("select products_model, products_quantity from orders_products where orders_id = '" . (int)$order_id . "'");
$result = mysql_query($query) or die ('<p>' . mysql_error());
while ($row = mysql_fetch_array($result)) {
$order['item_info[]'] = $row['products_model'];
$order['item_qty[]'] = $row['products_quantity'];
}
$this->item = array('item_info' => $order['item_info[]'],
'item_qty' => $order['item_qty[]']);

-edited out-

xml.php

<?php
require('includes/application_top.php');

# Include the order class
include(DIR_WS_CLASSES . 'order.php');
$order = new order($oID);

# Function to download the XML file
function download($content, $filename, $filetype = 'text/plain')
{
$filetype = 'application/octet-stream';
header('Content-Type: ' . $filetype);
header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Content-Length: ' . strlen($content));
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
echo $content;
exit();
}

# Start the XML content
$content = '<?xml version="1.0" encoding="ISO-8859-1"?>';

$content .= '<order>';
$content .= '<shipTo>';
$content .= '<refNumber>'.$oID.'</refNumber>';
$content .= '<company>'. $order->delivery['delivery_company'] . '</company>';
$content .= '<firstName>'.$order->customer['fname'].'</firstName>';
$content .= '<lastName>'. $order->customer['lname'] . '</lastName>';
$content .= '<address1>'. $order->delivery['street_address'] .'</address1>';
$content .= '<address2>'. $order->delivery['delivery_suburb'] .'</address2>';
$content .= '<city>'. $order->delivery['city'] .'</city>';
$content .= '<state>'.$order->delivery['iso'].'</state>';
$content .= '<zip>'. $order->delivery['postcode'] .'</zip>';
$content .= '<country>'.$order->delivery['country'].'</country>';
$content .= '<email>'.$order->customer['email_address'].'</email>';
$content .= '<phone>'.$order->customer['telephone'].'</phone>';
$content .= '<message>Thank You</message>';
$content .= '<service>PRIORITY-MAIL</service>';
$content .= '</shipTo>';
$content .= '<billTo>';
$content .= '<account>1</account>';
$content .= '<username>guest</username>';
$content .= '<password>php</password>';
$content .= '</billTo>';
$content .= '<items>';
$content .= '<item>';
$content .= '<itemNumber>'. $order->item['item_info'] .'</itemNumber>'; #first item in order 10
$content .= '<qty>'. $order->item ['item_qty'] .'</qty>';#qty of first item
$content .= '</item>';
$content .= '<item>';
$content .= '<itemNumber>'. $order->item['item_info'] .'</itemNumber>'; #second item in order 10
$content .= '<qty>'. $order->item['item_qty'] .'</qty>'; #qty of second item
$content .= '</item>';
$content .= '<item>';
$content .= '<itemNumber>'. $order->item['item_info'] .'</itemNumber>'; #third item...and on and on
$content .= '<qty>'. $order->item['item_qty'] .'</qty>';
$content .= '</item>';
$content .= '<item>';
$content .= '<itemNumber>'. $order->item['item_info'] .'</itemNumber>';
$content .= '<qty>'. $order->item['item_qty'] .'</qty>';
$content .= '</item>';
$content .= '<item>';
$content .= '<itemNumber>'. $order->item['item_info'] .'</itemNumber>';
$content .= '<qty>'. $order->item['item_qty'] .'</qty>';
$content .= '</item>';
$content .= '<item>';
$content .= '<itemNumber>'. $order->item['item_info'] .'</itemNumber>';
$content .= '<qty>'. $order->item['item_qty'] .'</qty>';
$content .= '</item>';
$content .= '<item>';
$content .= '<itemNumber>'. $order->item['item_info'] .'</itemNumber>';
$content .= '<qty>'. $order->item['item_qty'] .'</qty>';
$content .= '</item>';
$content .= '<item>';
$content .= '<itemNumber>'. $order->item['item_info'] .'</itemNumber>';
$content .= '<qty>'. $order->item['item_qty'] .'</qty>';
$content .= '</item>';
$content .= '<item>';
$content .= '<itemNumber>'. $order->item['item_info'] .'</itemNumber>';
$content .= '<qty>'. $order->item['item_qty'] .'</qty>';
$content .= '</item>';
$content .= '<item>';
$content .= '<itemNumber>'. $order->item['item_info'] .'</itemNumber>';
$content .= '<qty>'. $order->item['item_qty'] .'</qty>';
$content .= '</item>';
$content .= '</items>';
$content .= '</order>';

# If the download flag is set and == 1 then download the file
if ($_GET['dl'] =='1') {
download($content, $oID.'.xml');
}
require(DIR_WS_INCLUDES . 'application_bottom.php');
?>

should these 2 lines in the order.php
$this->item = array('item_info' => $order['item_info[]'],
'item_qty' => $order['item_qty[]']);

Be

$this->item = array('item_info[]' => $order['item_info[]'],
'item_qty[]' => $order['item_qty[]']);

sorry for all this, just know enough to be a danger. lol

[James_V]

Ok, im closer but cant seem to get the array worked out.

order.php file

# Get items and qty
$query = ("select products_model, products_quantity from orders_products where orders_id = '" . (int)$order_id . "'");
$result = mysql_query($query) or die ('<p>' . mysql_error());
while ($row = mysql_fetch_array($result)) {
$order['item_info'] = $row['products_model'];
$order['item_qty'] = $row['products_quantity'];
}

$this->item = array('item_info' => $order['item_info'],
'item_qty' => $order['item_qty']);

xml.php file

$content .= '<items>';
foreach ($order->item as $key=>$val)
{
$content .= '<item>';
$content .= '<itemNumber>'. $val .'</itemNumber>';
$content .= '<qty>'. $item_qty[$key] .'</qty>';
$content .= '</item>';
}
$content .= '</items>';

But the output is:

<item>
<itemNumber>12569</itemNumber>
<qty />
</item>
<item>
<itemNumber>1</itemNumber>
<qty />
</item>

I know I'm missing something simple.

[ironik]

The problem your having is that you are overwriting the same array key in your while() loop.

You'll have to assign something unique to each array key. Try this code:

# Get items and qty
$query = ("select products_model, products_quantity from orders_products where orders_id = '" . (int)$order_id . "'");
$result = mysql_query($query) or die ('<p>' . mysql_error());
while ($row = mysql_fetch_array($result)) {
$order['item_info'] = $row['products_model'];
$order['item_qty'] = $row['products_quantity'];
$items[] = $order; // Assign an incremental key to $items
}

xml.php file

$content .= '<items>';
foreach ($items as $key=>$val)
{
$content .= '<item>';
$content .= '<itemNumber>'. $val['item_info'] .'</itemNumber>';
$content .= '<qty>'. $val['item_qty'] .'</qty>';
$content .= '</item>';
}
$content .= '</items>';

[James_V]

Ok, got it. Used the simple way from your first sugestion of not passing it from order.php to xml.php with the array. Here is what I ended up with.

xml.php

$content .= '<items>';
$query = ("select products_model, products_quantity from orders_products where orders_id = $oID");
$result = mysql_query($query) or die ('<p>' . mysql_error());
while ($row = mysql_fetch_array($result)) {
$content .= '<item>';
$content .= '<itemNumber>'. $row['products_model'] .'</itemNumber>';
$content .= '<qty>'. $row['products_quantity'] .'</qty>';
$content .= '</item>';

and thanks for that last sugestion, now I see where I was messing up with overwriting the key. Gonna hang on to that bit of info. If nothing else I learned alot on arrays the last 2 days, thanks so much for the help.

[coopster]

Understanding arrays is going to be key in server-side development, glad to see ironik was able to get you started here!

Welcome to WebmasterWorld, James_V.