PHP multiple table

[nanook]

I’m trying to display multiple tables using a JOIN with jquery DataTables, I have a basic query working but need help with the JOIN. Below is my code and my JOIN statement:

<?php
// { initialise variables
$amt=10000;
$start=0;
// }
// { connect to database
function dbRow($sql){
$q=mysql_query($sql);
$r=mysql_fetch_array($q);
return $r;
}
function dbAll($sql){
$q=mysql_query($sql);
while($r=mysql_fetch_array($q))$rs[]=$r;
return $rs;
}
mysql_connect(‘localhost’,’root’,’vai0316′);
mysql_select_db(‘barksand_barks’);
// }
// { count existing records
$r=dbRow(‘select count(OrderID) as c from orders’);
$total_records=$r;
// }
// { start displaying records
echo ‘{"iTotalRecords":’.$total_records.’,
"iTotalDisplayRecords":’.$total_records.’,
"aaData":[‘;
$rs=dbAll("select OrderID, OrderShipping , OrderTotal from orders order by OrderID, OrderID limit $start,$amt ");
$f=0;
foreach($rs as $r){
if($f++) echo ‘,’;
echo ‘["’,$r[‘OrderID’],’",
"’,$r,’",
"’,$r,’"]’;
}
echo ‘]}’;
// }

SELECT orders.OrderID, orders.OrderShipAddress1, orderdetails.DetailItemName
FROM orders, orderdetails
WHERE order.OrderID = orderdetails.DetailOrderID

[nanook]

I was able to fix the sql statement to get all the information I needed but still need help on getting orders to appear on one line. Its reading every line in the DB as is should, if I have an order 119 and they ordered 4 items its displaying a line for each item that ordered. I’m trying to get it to do this:

order Item description color
119 Leash dog lead red
collar dog collar blue
120 food dog food
121 leash dog lead green

Below is the current working code, can any on lend a hand?

Thanks,
TIm

<?php
// { initialise variables
$amt=10000;
$start=0;
// }
// { connect to database
function dbRow($sql){
$q=mysql_query($sql);
$r=mysql_fetch_array($q);
return $r;
}
function dbAll($sql){
$q=mysql_query($sql);
while($r=mysql_fetch_array($q))$rs[]=$r;
return $rs;
}
mysql_connect(‘localhost’,’root’,’vai0316′);
mysql_select_db(‘barksand_barks’);
// }
// { count existing records
$r=dbRow(‘select count(OrderID) as c from orders’);
$total_records=$r;
// }
// { start displaying records
echo ‘{"iTotalRecords":’.$total_records.’,
"iTotalDisplayRecords":’.$total_records.’,
"aaData":[‘;
$rs=dbAll("select * from orders, orderdetails where orderdetails.DetailOrderID = orders.OrderID ");
$f=0;
foreach($rs as $r){
if($f++) echo ‘,’;
echo ‘["’,$r[‘OrderID’],’",
"’,$r,’",
"’,$r,’",
"’,$r,’",
"’,$r,’"]’;

}
echo ‘]}’;
// }

[Author]

Posts