Data From 2 Database Tables Into 1 Html Table

[Setavai]

Hello, as i am learning from this book, at the same time im trying to complete my website, that being said, obviously there are some errors in the code,

 

the thing im trying to make is a table that lists as followed:

Contact details        |      requested
------------------------------------------
name: Setavai          | request 1 - request sub-title
phone: 12345678        | request 4 - request sub-title
email: email@email.com | request 2 - request sub-title
------------------------------------------
name: name2            | request 2 - request sub-title
phone: phone2          | request 5 - request sub-title
email: email2@...      | request 1 - request sub-title

etc.

my tables are designed as followed:

table 1 :
id | request   | sub-title
--------------------------
1  | request1  | subtitle1
2  | request2  | subtitle2

table 2 :
id | name      | phone     | email     | request
------------------------------------------------
1  | Setavai   | 12345678  | email@..  | 1
2  | Setavai   | 12345678  | email@..  | 4
3  | Setavai   | 12345678  | email@..  | 2
4  | name2     | phone2    | email2@.. | 2
etc

where table2.request = table1.id

 

At the moment i managed to get the contact details section to work with a while loop, but i cant figure out how i can get the requested section to list the "request - sub-title" on the other side of the contact details

 

incase your wondering about my code (currently its wrong as im still trying to figure it out with some trial-and-error) heres the section that creates that table

$q = "SELECT name, phone, email, requested FROM submitted ORDER BY $order_by LIMIT $start, $display";		
$r = @mysqli_query ($dbc, $q); // Run the query.
// Table:
echo '<table align="center" cellspacing="0" cellpadding="5" width="75%">
<tr class="add">
	<td align="left"><b><a href="orders.php?sort=cnt">Contact</a></b></td>
	<td align="left"><b><a href="orders.php?sort=req">requested</a></b></td>
	</tr>
';

// Fetch and print all the records....
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
echo '<td align="left">Name:' . $row['name'] . '<br />Phone:'.$row['phone'].'<br/>email:'.$row['email'].'</td>';
		echo '<td align="left">request1 - sub-title1 <br /> request2 - sub-title2<br />';
		echo '</td>
		</td></tr>
	';
}

echo '</table>'; 

thats what i have so far,

i still have to select the corresponding request + sub-title from the 2nd table in a query, 

but i cant seem to figure out how to go around doing this,

i have tested a few things by creating the following query:

SELECT t1.requested, t2.request, t2.sub-title
FROM submitted AS t1
INNER JOIN requests AS t2 ON t1.requested = t2.id
WHERE t1.name =  "Setavai"

which resulted in an array with the following content

1 | request1 | subtitle1
4 | request4 | subtitle4
2 | request2 | subtitle2

which basically gives the rows that match the requested id's

but i dont know how to add this to my code

 

any help would be appreciated

[margaux]

You only need 1 query 

$q = "SELECT name, phone, email, r.request, sub_title FROM submitted AS s INNER JOIN requests AS r ON s.request = r.id";

 

 

If I understand correctly what you are trying to do, I recommend a third table as at the moment you're storing the same submitted details many times over. Submitteds can have many requests and requests can belong to more than one submitted so you need a link table (can't remember the correct term), submitted-requests which would have 3 fields - an id, a submitted_id and a request_id. submitted_id and request_id would need to be set up as indices.

 

Your select statement would have another join. Have a go at doing this yourself and come back if you have any questions.

 

[Setavai]

You only need 1 query 

$q = "SELECT name, phone, email, r.request, sub_title FROM submitted AS s INNER JOIN requests AS r ON s.request = r.id";

 

 

If I understand correctly what you are trying to do, I recommend a third table as at the moment you're storing the same submitted details many times over. Submitteds can have many requests and requests can belong to more than one submitted so you need a link table (can't remember the correct term), submitted-requests which would have 3 fields - an id, a submitted_id and a request_id. submitted_id and request_id would need to be set up as indices.

 

Your select statement would have another join. Have a go at doing this yourself and come back if you have any questions.

 

i think i understand what you mean, so basically have 1 table contain the contact details, a new table to contain only the contact id and the id's of their requests, and the third table to contain the actual requests,

 

though i'd prefer continueing with what i have at the moment, server-load wise theres not really a problem since there wont be that many users, at most 2 a week, and im making an admin panel that would delete the records once they where processed, so there wont be that much in the database,

 

so after trying out your query it seems to be working better then what i had,

although as of now it simply places 1 request in each table-square, giving me 4x the same contact details in the table, with a different request behind each.

 

im trying to get 1x the contact details in the left square, and 4x the request + its sub-title in the right square,

so ye, the main problem is figuring out how to get them to be placed in 1 square, 

i dont think a foreach loop placed inside a while statement would work, so how would i go about doing this?

 

(in the original post's code, changed the query and changed line 87 to have " '. $row[request] .' - '. $row[sub_title] .' " instead of the normal text i placed there to clarify how im trying to go about doing this, obviously this would only parse it once.)

[margaux]

I can understand why you want to keep to 2 tables but you'll be continually having to find workarounds to compensate for not good data design. Good data design is more scaleable and you will learn more in the process. Here's an example of a query across 3 tables:

SELECT name, s.id AS contact, phone, email, request, sub_title FROM submitted AS s 
INNER JOIN submitted_requests AS sr ON s.id = sr.submitted_id 
INNER JOIN requests AS r ON sr.request_id = r.id ORDER BY name

 

 

To output in a table you can do something like 

// Fetch and print all the records....
$contact = 0;
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
    if ($row['contact'] != $contact) {
        echo '<tr><td align="left">Name: ' . $row['name'] . '<br />Phone: '.$row['phone'].'<br/>email: '.$row['email'].'</td>';
        echo '<td align="left">' .$row['request'] . ' - ' . $row['sub_title'];
        $contact = $row['contact'];
    }
    else {
        echo '<br/>' .$row['request'] . ' - ' . $row['sub_title'];
        $contact = $row['contact'];
    }
} 
echo '</table>'; 

 

You'll need to tidy up the output but you get the general idea.

[Setavai]

Ah, i see how i can get this done now, and ye, i realize changing my database design would make future additions to my website alot easier aswell, so i might aswell change it now and only have to do a small part of my code, instead of having to find workarounds for everything later on, which will ofcourse make it alot more time consuming,

 

ill have a look if i can get it to work using your output idea, and will then work on editing it to work with 3 tables instead of 2, and might edit this post later on with the results, incase someone else has a similar question to mine

[Larry]

Thanks, Margaux, for helping out!