MySQLi: Query with a variable to another table

Question

So, I have a database with a few tables in it. Most of these tables contain information about the same document and are connected by an ID (it's stupid since it could all be in the same table, but the company I'm helping out for can't change this right now). The content of the 2 tables that I'm currently working on can be seen below.

tblDocument: Contains ID + other info.

tblVerification: Contains ID, verificationNo + other info.

Now, what I'd like to do is to find the verificationNo in tblVerification from the ID in tblDocument, using PHP and MySQLi. I've found a solution that allows me to pass a variable into the query, but it doesn't feel like this is the most efficient solution and the code confuses me. Any additional information about how the query works or a new simpler solution is greatly appreciated.

<?php

$mysqli = new mysqli("ip", "name", "pw", "db");
$dbDoc = $mysqli->query("SELECT * FROM tblDocument ORDER BY ID");

while ($row = $dbDoc->fetch_assoc()) {
    $tempID = $row["ID"];

    $bind = 's';
    $queryThis = $mysqli->prepare("SELECT verificationNo FROM tblVerification WHERE ID = ?");
    $queryThis->bind_param($bind, $tempID);
    $queryThis->execute();
    $queryThis->bind_result($result);
    $queryThis->fetch();
    $queryThis->close();
    $tempVer = $result;

    echo $tempVer . " ";
}
?>

Actual question: Is this the most efficient way to achieve this result using only PHP and MySQLi and/or what can you do to simplify it?

Luca Romano · Accepted Answer · 2016-01-07 11:28:20Z

2

Have you try with join?

"SELECT tblDocument.*, tblVerification.verificationNo
FROM tblDocument INNER JOIN tblVerification
    ON tblDocument.ID=tblVerification.id
ORDER BY ID"

answered Jan 7, 2016 at 11:28

Luca Romano

1187 bronze badges

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

Comments

Ravi Hirani · Accepted Answer · 2016-01-07 11:27:13Z

1

You should use SQL JOIN in this case:-

Your query should be:-

"SELECT tblDocument.*, tblVerification.verificationNo
FROM tblDocument
LEFT JOIN tblVerification
ON tblDocument.ID = tblVerification.ID
ORDER BY tblDocument.ID;"

answered Jan 7, 2016 at 11:27

Ravi Hirani

6,5341 gold badge31 silver badges43 bronze badges

Comments

antoniodvr · Accepted Answer · 2016-01-07 11:31:33Z

1

You can simply join the two tables:

SELECT tv.id, tv.verificatioNo 
FROM tblVerification tv
INNER JOIN tblDocument td
ON tv.id = td.id
ORDER BY tv.id;

And if you want to filter for some particular id add the WHERE condition:

WHERE tv.id = ?

answered Jan 7, 2016 at 11:31

antoniodvr

1,2591 gold badge14 silver badges15 bronze badges

Collectives™ on Stack Overflow

MySQLi: Query with a variable to another table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related