Grow your CSS skills. Land your dream job.

PHP multiple table

  • # July 14, 2010 at 6:57 pm

    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

    # July 15, 2010 at 7:09 am

    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 ‘]}’;
    // }

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".