Generate XML files with PHP and MySQL

[Gfmultimediadesigns]

Hello,
I'm attempting to generate an xml file through php which pulls from two seperate tables in a single database. I will be the first to admit that I am still learning and it is probably a very simple solution but for some reason i can't seem to figure it out. So any help would be very kind. Here is the code for my recent attempt.

// connection to the database
$dbhandle = mysql_connect(DB_SERVER, DB_USER, DB_PASS)
or die("Unable to connect to MySQL");

// select a database to work with
$selected = mysql_select_db(DB_NAME, $dbhandle)
or die("Could not select examples");

$output = "<?xml version=\"1.0\" ?>\n";
$output .= "<batch>";

$output .="<order>";

// iterate over each table and return the fields for each table


$result_fld = mysql_query( "SELECT orders_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country FROM zen_orders WHERE orders_id >'1'" );
$result_fld2 = mysql_query( "SELECT orders_id, products_model, products_quantity FROM zen_orders_products WHERE orders_id >'1'" );


while( $row1 = mysql_fetch_row($result_fld) ) {
$output .="<ShipTo>";
$output .="<Address>";
$output .="<Po>\"$row1[0]\"</Po>";
$output .= "<Line1>\"$row1[1]\"</Line1>";
$output .= "<Street1>\"$row1[3]\"</Street1>";
$output .= "<Street2>\"$row1[4]\"</Street2>";
$output .= "<City>\"$row1[5]\"</City>";
$output .= "<State>\"$row1[7]\"</State>";
$output .= "<Zip>\"$row1[6]\"</Zip>";
$output .= "<Country>\"$row1[8]\"</Country>";
$output .= "<Country>\"$row1[0]\"</Country>";
$output .= "<Country>\"$test[0]\"</Country>";

$output .="</Address>";
$output .="</ShipTo>";

while($test = mysql_fetch_row($result_fld2)){
if($test[0] == $row1[0] ) {
$output .="<Line>";
$output .= "<model>\"$test[1]\"</model>";
$output .= "<q>\"$test[2]\"</q>";
$output .="</Line>";
}
}

$output .="</order>";

$output .= "</batch>";

// tell the browser what kind of file is come in
header("Content-type: text/xml");
// print out XML that describes the schema
echo $output;

// close the connection
mysql_close($dbhandle);
?>

Thanks again for your help

[Motoma]

What is wrong with this? Errors? Bad formatting? Improper structure?

[Gfmultimediadesigns]

my bad, the problem i am running into is i need it to pull the products from the second query and each product is a seperate row with the order # as the key value but when the loop structure runs it goes by a row to row basis so its not pulling all the rows ie if a customer orders more then one product it will only show the first product. I can't seem to figure out what i need to do to get it to pull multiple rows based on the order #

[code green]

The industry standard for what you are trying to achieve is done by having two tables (at least). An `orders` table with the customer details and total order value and an `order_id`. The second table `orderlines` has multiple entries for each `order_id` matching the number of lines ordered. Without this structure you are barking up the wrong tree.

[Gfmultimediadesigns]

Thats pretty much how the database is setup. the first query pulls from the customer info table while the second is pulling from the order_products table. its just i can't seem to figure out how to have it pull multiple rows from the second table based on the order_id. the code works but it goes row by row within the loop so it only pulls one of the however many products they order. the solution is probably something simple but for some reason i can not grasp the answer.

[mihai123]

you need to change your sql query

SELECT orders_id.zen_orders, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, orders_id.zen_orders_products, products_model, products_quantity FROM zen_orders, zen_orders_products WHERE orders_id.zen_orders=orders_id.zen_orders_products

I'm not sure that this query will work but for sure if you'll use a join method there that will be just fine.
Google it: mysql join left syntax examples

[Gfmultimediadesigns]

Awesome, I went ahead and implemented the suggested code and it returns this table
Orders_id Delivery_name Etc. Orders_id Products_model
2 Test2 etc 2 Model3
3 Test3 etc 3 Model5
4 Test4 etc 4 Model2
5 Test5 etc 5 Model2
5 Test5 etc 5 Model4

Now my final question ( I think ) is I need it to loop through and spit out the delivery info and the products ordered info in an xml format. From what I understand when using a loop it will run through each row, but if someone orders more then one product it creates a new row for each product model ordered. So is it possible to join rows based on the order id? Or is there a specific loop structure I should use? I’ve tried while() as well as the foreach() I’ve also tried a nested while() but that keep sending back errors. So at this point I’m dumbfounded. Thanks again for any assistance!

[code green]

mihai123 has given you a query that generates one big recordset with all orders and orderlines. I prefer to use your original idea of one query to capture the orders and a second to capture the orderlines. It is a little bit tidier to handle and allows the early identification of errors. But back to handling the array you have.

[PHP]//Not necessary but good practice
$lines = array();
$custorder = array();

foreach($record as $order){
$id = $order['order_id'];
if(!array_key_exists($id,$lines){ #is it a new order number?
$lines[$id]= 0; #New id flag

//Collect the basic order details
$custOrder[$id]['name'] = $order['delivery_name'];
//etc
}
else{ #Collect the order lines
$lines[$id] ++; #increment the no of lines ordered
$custOrder[$id]['model'.$lines[$id]] = $order['products_model'];
$custOrder[$id]['quantity'.$lines[$id]] = $order['products_quantity'];
//etc
}
}[/PHP]This is off the top of my head so excuse the syntax and there are certainly other methods. But you should end up with a 2d array of the format

Array(id=>first_order_id( 
   Array(name=>delivery_name,.. etc, model1=>orderline,quantity1=>quantity,
   model2=>orderline,quantity2=>quantity etc)
   id=>second_order_id Array(second order details) .. 3rd() etc)