php multiple mysql commands in array

Question

Is it possible build an array of SQL commands to query the DB? What I have is three tables, each have columns with amounts due. Would like to select anything that is due and display on screen so it can be invoiced (preferably in a table) and each row with it's respective customers dues.

I can select everything that is due using UNION ALL between the three tables, however I cant figure out how to list them by ID in the table row.

Below is what I have so far. At this pace I'll have to run each query separately and list them in three separate lists. Suggestions?

      <table>
        <tr>
            <th> ID</th>
            <th> Cost 1</th>
            <th> Cost 2</th>
            <th> Cost 3</th>
        </tr>

      <?php 
    $list1 = "SELECT ID, Cost FROM Table1 WHERE Invoiced IS NULL;";
    //$list2 = "SELECT ID, Price2 FROM Table2 WHERE Expiration BETWEEN '$curDate' AND '$maxDate';";
    //$list3 = "SELECT ID, Price3 FROM Table3 WHERE Expiration BETWEEN '$curDate' AND '$maxDate'";

    $result = mysql_query($list1, $link) or die(mysql_error());

    $num_rows = mysql_num_rows($result);
    $num_fields = mysql_num_fields($result);

    for ($i=0; $i<$num_rows; $i++) {
        for ($j=0; $j<$num_fields; $j++) {
            $invoice[$i][mysql_fieldname($result,$j)] = mysql_result($result,$i,mysql_field_name($result,$j));
            }
        }

    //eventually the order it should be listed on screen
    for($i=0; $i<count($invoice); $i++) {
        echo "<tr><td>".$invoice[$i]["ID"]."</td>
        <td>".$invoice[$i]["Cost"]."</td>
        <td>".$invoice[$i]["Price2"]."</td>
        <td>".$invoice[$i]["Price3"]."</td></tr>";
    }

      ?>
      </table>

Edit after comment:

Query being passed and returning syntax error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'all LEFT JOIN table1 ON all.ID = table1.ID LEFT JOIN t' at line 7:

    $query = "
    SELECT all.ID, table1.Cost1, table2.Price2, tabl3.Price3
    FROM 
        (SELECT ID, Cost1 FROM table1 WHERE Invoiced IS NULL
            UNION
        SELECT ID, Price2 FROM table2 WHERE Expiration BETWEEN '$curDate' AND '$maxDate'
            UNION
        SELECT ID, Price3 FROM table3 WHERE Expiration BETWEEN '$curDate' AND '$maxDate') AS all
    LEFT JOIN table1 ON all.ID = table1.ID
    LEFT JOIN table2 ON all.ID = table2.ID
    LEFT JOIN table3 ON all.ID = table3.ID
    ";

so many suggestions come to mind. 1: Don't use mysql_* commands as they are deprecated. 2: Why are these items which look almost exactly the same in different tables. 3: Your use of rows and fields isn't needed, and is just an extremely large overhead. — Petrogad
– Petrogad, Commented Oct 24, 2012 at 0:55
@Frederico I renamed the tables and their columns to not confuse here, they all serve different functions and should not be in the same table. — gSaenz
– gSaenz, Commented Oct 24, 2012 at 2:43
@gSaenz Make yours look like mine. 2 things different about it: 1) The source of your syntax error is the fact that you used all as the derived table alias where I used allID. ALL is a MySQL reserved keyword and must be quoted with backtickts. 2) You don't need the costs or WHERE clauses in the UNION query, only the IDs. If you include the costs too, you'll get duplicates. – Michael Berkowski just now edit — Michael Berkowski
– Michael Berkowski, Commented Oct 24, 2012 at 10:52
@MichaelBerkowski Perfect. Question, I was throwing the WHERE clauses to 1) make my search faster, isn't the way it is now (without WHERE) going to select everything? Also, does this method account for duplicates? See, each table is a different product and each customer can have more than one of each product. 2) Thus wanting to get only the ones that are due soon. For example: ID 1 has two products from table2, one expires next month and the other expires next year. This is why I was using BETWEEN (and Invoiced IS NULL) to get only the one that are due next month (and not billed yet) — gSaenz
– gSaenz, Commented Oct 24, 2012 at 14:25
@gSaenz Oops, my mistake - while I meant for you to omit the WHERE from the UNION subquery, it still needs to be on the main query. I didn't intend to leave it out when I hastily typed that - added it below. — Michael Berkowski
– Michael Berkowski, Commented Oct 24, 2012 at 14:27

Michael Berkowski · Accepted Answer · 2012-10-26 03:23:00Z

2

From the table header you have created above which places the three Cost columns in a single row by ID, you seem to imply that you want to to JOIN the three tables together on their ID. I am using a LEFT JOIN here, to be sure that all rows from Table1 are present, even if there is no corresponding row in either of the other two tables.

SELECT
  Table1.ID,
  Table1.Cost as Cost1,
  Table2.Price2 AS Cost2,
  Table3.Price3 AS Cost3
FROM
  Table1 
  LEFT JOIN Table2 ON Table1.ID = Table2.ID
  LEFT JOIN Table3 ON Table1.ID = Table3.ID
WHERE 
  Table1.Invoiced IS NULL
  AND Table2.Expiration BETWEEN '$curDate' AND '$maxDate'
  AND Table3.Expiration BETWEEN '$curDate' AND '$maxDate'

Update after comments:

In the case that Table2 may have an ID not held in Table1 or Table3, for example (where Table1.ID can't be considered authoritative), you can get the total set of DISTINCT ID from all 3 tables via a UNION and use that to join against:

SELECT
  allID.ID,
  Table1.Cost1,
  Table2.Price2 AS Cost2,
  Table2.Price3 AS Cost3
FROM
  /* Subquery gets a distinct set of IDs from all tables via UNION 
     so the outer query has a complete list to join against the other tables */
  (
    SELECT ID FROM Table1
    UNION SELECT ID FROM Table2
    UNION SELECT ID FROM Table3
  ) allID
  LEFT JOIN Table1 ON allID.ID = Table1.ID
  LEFT JOIN Table2 ON allID.ID = Table2.ID
  LEFT JOIN Table3 ON allID.ID = Table3.ID
/* Sorry, forgot the WHERE clause here */
WHERE
  Table1.Invoiced IS NULL
  AND Table2.Expiration BETWEEN '$curDate' AND '$maxDate'
  AND Table3.Expiration BETWEEN '$curDate' AND '$maxDate'

Note that the existence of three tables with nearly identical column structures in a one-to-one relationship probably implies a design problem. You might consider combining these into a single table.

A further note about the PHP:

In PHP, we almost never use an incremental for loop for iteration as you would in C/C++. Instead, we typically make use of a foreach or when fetching rows from a query, a while loop.

// Fetch in a while loop
$invoice = array();
// $result is your query resource as you already have it...
while ($row = mysql_fetch_assoc($result)) {
  // Accumulate rows into $invoice array
  $invoice[] = $row;
}
// Then loop over the array:
foreach ($invoice as $inv) {
  echo "<tr>
    <td>{$inv['ID']}</td>
    <td>{$inv['Cost1']}</td>
    <td>{$inv['Cost2']}</td>
    <td>{$inv['Cost3']}</td>
  </tr>"; 
}

Final update:

Yes, the WHERE clause will restrict for all conditions met. If you need to limit them individually, you must do so in subqueries which are then joined together, using the same UNION subquery to get the distinct set of ID

SELECT
  allID.ID,
  T1.Cost1,
  T2.Price2 AS Cost2,
  T3.Price3 AS Cost3
FROM
  (
    SELECT ID FROM Table1
    UNION SELECT ID FROM Table2
    UNION SELECT ID FROM Table3
  ) allID
  LEFT JOIN (SELECT ID, Cost AS Cost1 FROM Table1 WHERE Invoiced IS NULL) T1 ON allID.ID = T1.ID
  LEFT JOIN (SELECT ID, Price2 AS Cost2 FROM Table2 WHERE Expiration BETWEEN '$curDate' AND '$maxDate') T2 ON allID.ID = T2.ID
  LEFT JOIN (SELECT ID, Price3 AS Cost3 FROM Table3 WHERE Expiration BETWEEN '$curDate' AND '$maxDate') T3 ON allID.ID = T3.ID

edited Oct 26, 2012 at 3:23

answered Oct 24, 2012 at 0:54

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

gSaenz Over a year ago

I renamed the tables and their columns to not confuse here, they all serve different functions and should not be in the same table. Regarding the LEFT JOIN, I thought about that also. However there may be items in table2 to invoice while that customer (from table2) may not have anything to invoice on table1 or 3. These three tables have no relationship to each other. I will change to while loop, thank you for the tip.

Michael Berkowski Over a year ago

@gSaenz Have a look at the new query added above, which uses a subquery that builds a distinct list of all possible ID, no matter which tables they do or do not exist in.

gSaenz Over a year ago

logic seems to be there, however it returns a syntax error. Have updated my query above. Thank you

Michael Berkowski Over a year ago

@gSaenz Make yours look like mine. 2 things different about it: 1) The source of your syntax error is the fact that you used all as the derived table alias where I used allID. ALL is a MySQL reserved keyword and must be quoted with backtickts. 2) You don't need the costs or WHERE clauses in the UNION query, only the IDs. If you include the costs too, you'll get duplicates.

Collectives™ on Stack Overflow

php multiple mysql commands in array

1 Answer 1

Update after comments:

A further note about the PHP:

Final update:

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update after comments:

A further note about the PHP:

Final update:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related