Using UNION in prepared statements in PHP/MySQL

Question

I'm currently having trouble with this section of my code. draw_table() is a function from another .php file where it uses foreach to iterate over each row and echo them, it works normally when executing without the UNION.

If I simply use $prepared3 = $pdo->prepare('SELECT S,QTY FROM SP WHERE P = :part'); instead w/out UNION then it will print out a table as usual and works normally, but I need to print out extra information of the same entity from another table as well so I attempted UNION but then when selected and submitting no table is printed at all.

There are 3 tables: S, P, SP. Table S's primary key is S, likewise P's is P, and SP's are S and P.

<p>
<form action="#" method="post">
    <b>3. Select Part</b>
    <select name="Part">
        <option></option>
        <option value="P1">P1</option>
        <option value="P2">P2</option>
        <option value="P3">P3</option>
        <option value="P4">P4</option>
        <option value="P5">P5</option>
        <option value="P6">P6</option>
        
        <input type="submit" name="submit1" value="Show suppliers"/>
    </select>
</form>
</p>

<?php
    if(isset($_POST['submit1']))
    {
        $selectedpart = $_POST['Part'];
        echo "Part: " . $selectedpart;
        
        $prepared3 = $pdo->prepare
            ('
            SELECT S,QTY FROM SP WHERE P = :part
            UNION
            SELECT PNAME,COLOR,WEIGHT FROM P WHERE P = :part
            ');
        $prepared3->execute(array(':part' => $selectedpart));
        $p = $prepared3->fetchAll(PDO::FETCH_ASSOC);
        draw_table($p);
    }
?>

All the subqueries in a `UNION` have to return the same number of columns. You're returning 2 columns in the first subquery, 3 columns in the second. — Barmar, Apr 07 '22 at 22:55
@BillKarwin That's not the problem. PDO automatically switches to emulated statements when you reuse the placeholder. — Barmar, Apr 07 '22 at 22:57
@Barmar I see, I tried to add an additional column to the first SELECT statement to reflect that and the table printed; but the columns from the second SELECT statement is now printed under the headers of the first. — Long Huang, Apr 07 '22 at 23:04
Okay, I got it to work with "SELECT SP.S,SP.QTY,P.PNAME,P.WEIGHT,P.COLOR FROM SP,P WHERE SP.P = :part AND P.P = :part". Thank you. — Long Huang, Apr 07 '22 at 23:09

Using UNION in prepared statements in PHP/MySQL

0 Answers0