SQL Script works in phpAdmin but not on web site echo

Question

The following SQL script works in SQL Editor in phpMyAdmin. However, in PHP I cannot get this script to display real content online. It is all blank. The problem is somewhere in my echo. I tried using $row['a.qty'] but that did not work.

$sql = "SELECT  a.qty + b.qty - c.qty as 'QTY', a.part_num as 'Part Num', a.part_desc as 'Description'
        FROM    Inv_Physical_Count a,
                Inv_Restock b,
                Inv_Orders c
        WHERE   a.part_id = b.part_id
        AND     a.part_id = c.part_id
        ORDER BY a.order_form_seq";

        $q = $pdo->prepare($sql);
        $q->execute(array());

        while ($row = $q->fetch(PDO::FETCH_ASSOC)) 
        {
            echo    '<tr>';
            echo    '<td>' . $row['qty'] . '</td>';
            echo    '<td>' . $row['part_num'] . '</td>';
            echo    '<td>' . $row['part_desc'] . '</td>';               
        }

Use the `as` values, i.e. `$row['QTY']` and `$row['Part Num']` because that is what you're returning, not the column names. You may have a little trouble with items having spaces in them and you should back tick, not quote. — Jay Blanchard, Jul 05 '17 at 20:40

score 3 · Accepted Answer · answered Jul 05 '17 at 20:45

First, back tick (not quote) your AS values, especially if they are to contain spaces:

$sql = "SELECT  a.qty + b.qty - c.qty as `QTY`, a.part_num as `Part Num`, a.part_desc as `Description`

You're telling the query these values should be the column names. Because of that use the AS values as your array identifiers:

while ($row = $q->fetch(PDO::FETCH_ASSOC)) 
{
    echo    '<tr>';
    echo    '<td>' . $row['QTY'] . '</td>';
    echo    '<td>' . $row['Part Num'] . '</td>';
    echo    '<td>' . $row['Description'] . '</td>';               
}

Jay - wow! Back-tick worked. I have never used back-tick. Thank you very much! — Bricked, Jul 05 '17 at 21:05

SQL Script works in phpAdmin but not on web site echo

1 Answers1