PHP - Multiple SQL queries to a HTML table using search results

Question

I have found similar topics, but many of the results use mysqli_multi_query which is something that I wish to avoid due to future implementations of user-generated queries.

Currently I am achieving my desired result using the following PHP, but I have a feeling there is a more effective method which I am missing. I am also afraid of concurrent queries and the possibility of such an involved process occupying connections. Though the userload at any one time should not be > 20.

Here is the code, any critique or insight appreciated! (I am still learning PHP as I go, therefore my code is likely shit in that regard!):

Table schema (which needs work as well - developing best structure as I go essentially):

CREATE TABLE `EAM`(
    `EAM_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `EAM_IPADDR` VARCHAR(15) NOT NULL, 
    `EAM_PORT` INT(5) NOT NULL,
    `EAM_STATE` VARCHAR(6) NOT NULL);

CREATE TABLE `ACTIVE`(
    `EAM_ID` INT NOT NULL,
    `ACTIVE_STATUS` VARCHAR(25) NOT NULL,
    `ACTIVE_TIME` TIME DEFAULT NULL,
    FOREIGN KEY (EAM_ID) REFERENCES EAM(EAM_ID));

CREATE TABLE `MAP_IMG` (
  `MAP_IMG_BLDG` varchar(25) NOT NULL,
  `MAP_IMG_ROOM` varchar(25) NOT NULL,
  `MAP_IMG_X` int(8) DEFAULT NULL,
  `MAP_IMG_Y` int(8) DEFAULT NULL,
  `MAP_IMG_ROOM_STATUS` varchar(10) NOT NULL DEFAULT 'NOTCLEAR');

CREATE TABLE `LOCATION`(
    `LOCATION_ROOM` VARCHAR(25) NOT NULL PRIMARY KEY,
    `LOCATION_BLDG` VARCHAR(25) NOT NULL,
    `EAM_ID` INT NOT NULL,
    `LOCATION_COMMENT` VARCHAR(250) DEFAULT NULL,
    FOREIGN KEY (EAM_ID) REFERENCES EAM(EAM_ID));

$dbQuery = "SELECT EAM_ID FROM LOCATION WHERE LOCATION_BLDG = 'LQ1'"; //TODO: Eventually make this 'LQ1' a variable for page selected.
$dbQueryResult = mysqli_query($dbConnection, $dbQuery) OR DIE("Bad Query: $dbQuery");

    echo "<table class='table table-striped'><thead>"; 
    echo "<tr><th>BUILDING</th>";
    echo "<th>ROOM NUMBER</th>";
    echo "<th>ROOM STATUS</th>";
    echo "<th>LAST UPDATE</th>";
    echo "<th>EAM IP ADDRESS</th>";
    echo "<th>EAM PORT</th></tr></thead>";

  while($row = mysqli_fetch_assoc($dbQueryResult)) {

      $eamID = $row['EAM_ID']; //Assign EAM_ID from initial query to $eamID for use throughout subsequent queries.


      $dbq2 = "SELECT LOCATION_BLDG, LOCATION_ROOM FROM LOCATION WHERE EAM_ID = $eamID";
      $dbqr2 = mysqli_query($dbConnection, $dbq2) OR DIE("Bad Query: $dbq2");

      while($r2 = mysqli_fetch_assoc($dbqr2)) {

        echo "<tr><td>{$r2['LOCATION_BLDG']}</td>";
        echo "<td>{$r2['LOCATION_ROOM']}</td>";

      }

      $dbq2 = "SELECT ACTIVE_STATUS, ACTIVE_TIME FROM ACTIVE WHERE EAM_ID = $eamID";
      $dbqr2 = mysqli_query($dbConnection, $dbq2) OR DIE("Bad Query: $dbq2");

       while($r2 = mysqli_fetch_assoc($dbqr2)) {

         echo "<td>{$r2['ACTIVE_STATUS']}</td>";
         echo "<td>{$r2['ACTIVE_TIME']}</td>";

       }

      $dbq2 = "SELECT EAM_IPADDR, EAM_PORT FROM EAM WHERE EAM_ID = $eamID";
      $dbqr2 = mysqli_query($dbConnection, $dbq2) OR DIE("Bad Query: $dbq2");

       while($r2 = mysqli_fetch_assoc($dbqr2)) {

        echo "<td>{$r2['EAM_IPADDR']}</td>";
        echo "<td>{$r2['EAM_PORT']}</td></tr>";

       }
  }

Why not use one query that joins the tables? Edit your question and add the schemas of the three tables. — Sloan Thrasher
– Sloan Thrasher, Commented May 10, 2018 at 14:18
To complement @SloanThrasher, have a look at LEFT JOIN, INNER JOIN and related "table joiners" — Julian Koster
– Julian Koster, Commented May 10, 2018 at 14:19
Also, when you use a variable to replace the location id, use a parameterized query and bind() to avoid SQL Injection. — Sloan Thrasher
– Sloan Thrasher, Commented May 10, 2018 at 14:26

Sloan Thrasher · Accepted Answer · 2018-05-10 14:25:09Z

1

Instead of doing the queries in a loop to get related data, use one query at the top to return the data.

SELECT 
    a.`EAM_ID`,
    a.`LOCATION_BLDG`,
    a.`LOCATION_ROOM`,
    b.`ACTIVE_STATUS`,
    b.`ACTIVE_TIME`
FROM `LOCATION` a
LEFT JOIN `ACTIVE` b
ON a.`EAM_ID` = b.`EAM_ID`
LEFT JOIN `EAM` c
ON a.`EAM_ID` = c.`EAM_ID`
WHERE a.`LOCATION_BLDG` = 'LQ1'

answered May 10, 2018 at 14:25

Sloan Thrasher

5,0383 gold badges26 silver badges44 bronze badges

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

7 Comments

ethan Over a year ago

It appears each query inside the loop is expected to return data, otherwise the user's HTML table would be broken, so INNER JOINs are possible here.

billybutthead Over a year ago

Awesome. Thank you for the reply and solution. That is far better than numerous queries in loop. Also to your comment above, once I use variable in this query, I will be sure to use bind() as well!

Sloan Thrasher Over a year ago

If you only want rows that have matching rows in the other two tables, remove the LEFT on the JOINS. If it works, be sure to accept the answer.

billybutthead Over a year ago

@ethan would it be possible to use mysqli_store_result here and then access data from this through fetch_assoc or fetch_row? Maybe store them as objects to access them?

Sloan Thrasher Over a year ago

You can fetch all of the rows from the query into an associated array, then loop through them to output the rows in your HTML table. take a look at mysqli_fetch_all

|

Collectives™ on Stack Overflow

PHP - Multiple SQL queries to a HTML table using search results

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related