根据第一个表的id查询多个表

Question

I need to fill a datatables with data from two different tables. Easy, i would think, just join or subquery. Unfortunately the second table is not based on ID, so i cannot filter on that one. Even if i could, i don't know how to put it in the datatables.

I've searched for days now, still no luck on this..

Table wp_mollie_forms_registrations has:

#id  # description #
#----#-------------#
#100 # Race #
#101 # Pull #
####################

Table wp_mollie_forms_registration_fields has:

#id  # field # value
#----#------#-------#
#100 # Naam # Theun #
#100 # E-mail # [e-mail] #
#100 # Leeftijd # 28 #
#100 # Soort voertuig # Auto #
#100 # Betaalmethode # ideal #
#101 # Naam # Theun    #
#101 # E-mail# [e-mail] #
#101 # Woonplaats # Groningen #
#101 # Merk en type # New Holland #
#101 # Gewichtsklasse # 2.8T #
#101 # Betaalmethode # ideal #
#####################

This is the code:

$query = "select * from A";
$items_result = mysqli_query($conn,$query) or die;

if ($items_result->num_rows > 0) {
echo "<table id='table_id' class='display'><thead><tr><th>ID</th> 
<th>description</th><th>Name</th><th>Age</th><th>Email</th></tr></thead> 
</tbody>";
while ($row = mysqli_fetch_assoc($items_result)){
echo "<tr><td>".$row["id"]."</td><td>".$row["description"]."</td> 
<td>".$Name."</td><td>".$row["Age"]."</td><td>".$row["Email"]."</td>
</tr>";
}

How would i do the following?: select * from table_A and use id to select name, age and email, put this info in my datatables and go to next line?

EDIT: It works, but not showing Naam (name) E-mail and Age (leeftijd) I now have:

$query = "SELECT wp_mollie_forms_registrations.id, wp_mollie_forms_registrations.description, tn.value AS 'Naam', te.value AS 'E-mail', ta.value AS 'Leeftijd' ".
"FROM wp_mollie_forms_registrations".
" INNER JOIN (SELECT registration_id, value FROM wp_mollie_forms_registration_fields WHERE field = 'Naam') tn ON wp_mollie_forms_registrations.id = tn.registration_id".
" INNER JOIN (SELECT registration_id, value FROM wp_mollie_forms_registration_fields WHERE field = 'E-mail') te ON wp_mollie_forms_registrations.id = te.registration_id".
" INNER JOIN (SELECT registration_id, value FROM wp_mollie_forms_registration_fields WHERE field = 'Leeftijd') ta ON wp_mollie_forms_registrations.id = ta.registration_id";
if(!mysqli_query($conn, $query)){ echo "Error: ".mysqli_error($conn); }
$items_result = mysqli_query($conn,$query) or die;

if ($items_result->num_rows > 0) {
echo "<table id='table_id' class='display'><thead><tr><th>ID</th><th>description</th><th>Name</th><th>Age</th><th>Email</th></tr></thead></tbody>";
while ($row = mysqli_fetch_assoc($items_result)){
    echo "<tr><td>".$row["id"]."</td><td>".$row["description"]."</td><td>".$row["tn.value"]."</td><td>".$row["ta.value"]."</td><td>".$row["te.value"]."</td>
</tr>";
}
    echo "</tbody></table>";
} else {
    echo "0 results";
}

Answer 1

Here is the code that should work based on your responses:

$query = "SELECT wp_mollie_forms_registrations.id as 'ID', wp_mollie_forms_registrations.description as 'Description', tn.value AS 'Naam', te.value AS 'Email', ta.value AS 'Leeftijd' ".
"FROM wp_mollie_forms_registrations".
" INNER JOIN (SELECT registration_id, value FROM wp_mollie_forms_registration_fields WHERE field = 'Naam') tn ON wp_mollie_forms_registrations.id = tn.registration_id".
" INNER JOIN (SELECT registration_id, value FROM wp_mollie_forms_registration_fields WHERE field = 'E-mail') te ON wp_mollie_forms_registrations.id = te.registration_id".
" INNER JOIN (SELECT registration_id, value FROM wp_mollie_forms_registration_fields WHERE field = 'Leeftijd') ta ON wp_mollie_forms_registrations.id = ta.registration_id";
if(!mysqli_query($conn, $query)){ echo "Error: ".mysqli_error($conn); }
$items_result = mysqli_query($conn,$query) or die;

if ($items_result->num_rows > 0) {
echo "<table id='table_id' class='display'><thead><tr><th>ID</th><th>description</th><th>Name</th><th>Age</th><th>Email</th></tr></thead></tbody>";
while ($row = mysqli_fetch_assoc($items_result)){
    echo "<tr><td>".$row["ID"]."</td><td>".$row["Description"]."</td><td>".$row["Naam"]."</td><td>".$row["Leeftijd"]."</td><td>".$row["Email"]."</td>
</tr>";
}
    echo "</tbody></table>";
} else {
    echo "0 results";
}

The reason you were not getting certain columns to show is because you had the wrong values in the 'echo' statement in PHP. For example, tn.value as Naam defines tn.value as 'Naam'. So changing the values in the PHP to reflect that should let the query work.

For a more detailed description of what the query is doing, here is an ultra simplified version. Imagine you have to tables, A and B, and each has a field 'id' and 'value'. Take a look at this query:

SELECT A.value, B.value
FROM A
INNER JOIN B
ON A.id = B.id

This will select the values from tables A and B and return them, but ONLY IF there exists a match between the ids in A and the ids in B. You can read more about joins Here.

The subquery part is relatively simple. It selects all 'registration_id's and 'value's from table B where the field is a specific type, such as 'Naam'.