Fetch values from two different SQL queries

Question

I want to make a table with values from two different tables. I do that with the while loop.

The problem now is, that I only get the output from the $result query, but I want the output from the $personalien and the $result query.

Is there a good way to merge these two mysqli results or how can I combine them so that the output from both is on the same line?

Here is the code:

// $res_anlassID is the number 5

$conn = mysqli_connect("localhost", "root", "", "art");
if ($conn-> connect_error) {
    die("Connection failed" . $conn-> connect_error);
}
           
$sql = "SELECT schuetze.vorname, schuetze.nachname, schuetze.jahrgang, schuetze.ort 
        FROM schuetze 
        WHERE schuetze.id in (
            SELECT resultate.schuetzeID 
            FROM resultate 
            WHERE resultate.anlassID = '$res_anlassID')";
$personalien = $conn->query($sql);
 
$sql = "SELECT resultate.vmTotalReh, resultate.vmTotalGams, resultate.vmTotalKlapphas, resultate.vmTotalRollhas, resultate.vmTotal, resultate.vmTiefschussReh, resultate.vmTiefschussGams 
        FROM resultate 
        WHERE resultate.anlassID = '$res_anlassID'";
$result = $conn->query($sql);
        
while ($row = $result-> fetch_assoc()) {
    echo "</td><td>" . $row['vorname'] . "</td><td>" . $row['nachname'] . "</td><td>" . $row['jahrgang'] . "</td><td>" . $row['ort'] . "</td><td>" . $row['vmTotalReh'] . "</td><td>" . $row['vmTotalGams'] . "</td><td>" . $row['vmTotalKlapphas'] . "</td><td>" . $row['vmTotalRollhas'] . "</td><td>" . $row['vmTotal'] . "</td><td>" . $row['vmTiefschussReh'] . "</td><td>" . $row['vmTiefschussGams'] . "</td></tr>";
}
echo "</table>";
$conn-> close();

How do you want to combine them? Can you show sample results from the queries and how the table should look? — Barmar, Jun 04 '21 at 15:00
it seems like you should just be using a single query that joins the tables, not two separate queries. — Barmar, Jun 04 '21 at 15:01
you must do 2 loop if you can't combine them together thanks to SQL (but I don't see why you couldn't) — DonKnacki, Jun 04 '21 at 15:01
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Jun 04 '21 at 15:07

score 1 · Answer 1 · edited Jun 04 '21 at 15:09

Use a single query that joins the two tables, rather than using WHERE schuetz.id IN (...) will return columns from both tables.

$sql = "SELECT schuetze.vorname, schuetze.nachname, schuetze.jahrgang, schuetze.ort,
        resultate.vmTotalReh, resultate.vmTotalGams, resultate.vmTotalKlapphas, resultate.vmTotalRollhas, resultate.vmTotal, resultate.vmTiefschussReh, resultate.vmTiefschussGams
        FROM schuetze
        JOIN resultate ON schuetze.id = resultate.schuetzeID
        WHERE resultate.anlassID = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $res_anlassID);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    // display the row here
}

I've also changed the query to use a prepared statement and bind_param() rather than substituting the variable into the SQL string.

Thank you very much, that was exactly what I needed. – Jun 10 '21 at 07:32 — , Jun 10 '21 at 07:32

score 0 · Answer 2 · edited Jun 04 '21 at 17:51

//Merge both the queies and use Joins
$sql="SELECT
   schuetze.vorname,
   schuetze.nachname,
   schuetze.jahrgang,
   schuetze.ort,
   resultate.vmTotalReh,
   resultate.vmTotalGams,
   resultate.vmTotalKlapphas,
   resultate.vmTotalRollhas,
   resultate.vmTotal,
   resultate.vmTiefschussReh,
   resultate.vmTiefschussGams 
FROM
   schuetze schuetze 
   INNER JOIN
      resultate resultate 
      ON resultate.schuetzeID = schuetze.id 
WHERE
   resultate.anlassID = '$res_anlassID'";


$personalien_result = $conn->query($sql);
while ($row = $personalien_result-> fetch_assoc()) {
//Loop the elements
}

score -1 · Answer 3 · answered Jun 04 '21 at 15:05

-1

It looks like a JOINmay help you, as to what type of table join would depend on your table structure and what information you want displayed.

see: https://www.w3schools.com/sql/sql_join.asp

this helped me understand JOIN when I first needed them.

answered Jun 04 '21 at 15:05

dyer926

47
1
10

Fetch values from two different SQL queries

3 Answers3