CODEWITHSUNDEEP
phpmysqlloopsmysqli
ZbadhabitZ
ZbadhabitZ

Reputation: 2913

Looping MySQL Results With Additional Queries

I'm hoping someone may be able to help me conceptualize a solution to a PHP page I'm writing.

Effectively, I have one master table in a MySQL database called "Servers." In it are four fields; department, serverA, serverB, serverC. For context, the department entry is the name of a department, and serverA, serverB, and serverC correspond to hostnames of the three servers for each department.

What I am trying to do is grab the name(s) of the department, serverA, serverB, and serverC and use that data to populate multiple SQL queries. I have my data separated into multiple tables for cleanliness and relationship. Here's what I've got;

<?php
$conn = mysqli_connect("server", "root", "root") or die('Error connecting to MySQLserver.');
mysqli_select_db($conn, "Database") or die("Failed to connect to database");
$sql = "SELECT * FROM Servers";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
    $deptartment = $row['department']
    $serverA = $row['serverA'];
    $serverB = $row['serverB'];
    $serverC = $row['serverC'];
}
} else {
echo "Unable to connect to server.  Please check your settings.";
}
?>

What I'd then like to do is to do a sort of foreach, wherein foreach record of serverA, the following happens;

<?php
$conn = mysqli_connect("server", "root", "root") or die('Error connecting to MySQLserver.');
mysqli_select_db($conn, "Database") or die("Failed to connect to database");
$sql = "SELECT * FROM Data WHERE `serverName` = '$serverA' ORDER BY `timestamp` DESC LIMIT 1";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
    $serverName = $row['serverName'];
    $serverType = $row['serverType'];
    $serverAge = $row['serverAge'];
}
} else {
echo "Unable to connection to server.  Please check your settings.";
}
?>

I could then use the same theory to loop through the results of serverB and have each record for serverB fulfill another query to get other data.

I seem to be struggling with the right syntax for how to loop through each of the results and populate other queries with it.

Any possible guidance?

Thank you!

Sample Data:
department: Math
serverA: math-server-windows.school.edu
serverB: math-server-mac.school.edu
serverC: math-server-linux.school.edu

department: Psychology
serverA: psychology-server-windows.school.edu
serverB: psychology-server-mac.school.edu
serverC: psychology-server-linux.school.edu

department:  Arts
serverA: artsserver.school.edu
serverB: artsold.school.edu
serverC: artsbackup.school.edu

Upvotes: 1

Views: 105

Answers (1)

Dan
Dan

Reputation: 11084

You could use JOINs to make this much simpler.

Table Data has a field servername that corresponds to either serverA, serverB or serverC in the servers table. So you can run a query like so:

SELECT Data.*,
  A.serverA AS serverA, 
  B.serverB AS serverB, 
  C.serverC AS serverC 
FROM Data
JOIN servers as A on Data.servername = A.serverA
JOIN servers as B on Data.servername = B.serverB
JOIN servers as C on Data.servername = C.serverC
ORDER BY <whatever>

So now each row will have the server information for each department, so loop through the results like so:

while( $row = $result->fetch_assoc() ){
  echo "\nDept: ".$row['department'];
  echo "\nA: ".$row['serverA'];
  echo "\nB: ".$row['serverB'];
  echo "\nC: ".$row['serverB'];
}

Upvotes: 1

Related Questions