How do code a query over 3 tables?

[rocky48]

I am trying to write a query in PHP on a MYSQL database to retrieve data from more than one table.

 

My database is a database of greetings card verses and is made up of 3 tables.

VERSES

-ID=int>>PK

-Event=int

-Sub_Type=int

-Verse=varchar

 

EVENTS

-ID=int..PK

-Event_Type=varchar

 

EVENT_SUB

-ID=int>>PK

-Event_Sub_Type=varchar

 

A previous script uses $POST to identify which Event_Type is chosen and from the array it will show the ID number for the EVENT_SUB and therefore it will also mean that I can use the Event_Sub_Type in the output to the screen.

In have looked at JOINS, but can not see how you write a JOIN which uses 3 tables.

Once I get the logic for the SELECT QUERY right I should be able to change the PHP code to suit.

Can anyone help?

[Jessica]

You do it the same way as with two. Example:

SELECT ...

FROM table1

LEFT JOIN table2

ON table1.x = table2.x

LEFT JOIN table 3

ON table1.x = table3.x

[rocky48]

Thanks Jesi that was very helpful, but I still have a problem!

 

I have managed to get most of the information onto the table, but I need to display the ID in the verses table and for some reason I get the ID from the Event_Sub table, even if I qualify the table name (Verses.ID).  Can anyone spot where I am going wrong?

 

Here is the code:

$get_Event_sql  = "SELECT Verses.ID, Verses.Verse, Verses.Sub_Type, Verses.Event, Events.ID, Events.Event_Type, Event_Sub.ID, Event_Sub.Event_Sub_Type
FROM Verses
LEFT JOIN Events
ON Verses.Event = Events.ID
LEFT JOIN Event_Sub
ON Verses.Sub_Type = Event_Sub.ID
WHERE Verses.Event = '".$_POST["Event_Type"]."' 
ORDER BY Verses.ID ASC";


$get_Event_res = mysqli_query($mysqli, $get_Event_sql) or die(mysqli_error($mysqli));

//create the display string
$display_block = "
<p> The Event Type is <b> ['Event_Sub.Event_Type']</b> </p>
<table width=\"50%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"#87CEEB\" >
<tr>
<th>ID</th>
<th>VERSE</th>
<th>MOOD</th>
</tr>";

while ($Verse_info = mysqli_fetch_array($get_Event_res)) {
	$Event_id = $Verse_info['Verses.ID'];
	$Verse_text = nl2br(stripslashes($Verse_info['Verse']));
	$Mood_info = $Verse_info['Event_Sub_Type'];

	//add to display
 	$display_block .= "
	<tr>
	<td width=\"1%\" valign=\"top\">".$Event_id."<br/></td>
	<td width=\"35%\" valign=\"top\">".$Verse_text."<br/></td>
	<td width=\"35%\" valign=\"top\">" .$Mood_info."<br/></td>
	</tr>";
}

//free results
mysqli_free_result($get_Event_res);
mysqli_free_result($verify_Event_res);

//close connection to MySQL
mysqli_close($mysqli);

//close up the table
$display_block .= "</table>";
}

 

I hope someone can help as I have exhausted my knowledge.

[Jessica]

In the select statement you'll use Verses.ID but in the array returned it will just be ID. The problem is that you have two three columns named ID. Select each using an AS to rename them so they are different.

$get_Event_sql  = "SELECT Verses.ID AS versesID, Verses.Verse, Verses.Sub_Type, Verses.Event, Events.ID AS eventsID, Events.Event_Type, Event_Sub.ID AS event_subID, Event_Sub.Event_Sub_Type
FROM Verses
LEFT JOIN Events
ON Verses.Event = Events.ID
LEFT JOIN Event_Sub
ON Verses.Sub_Type = Event_Sub.ID
WHERE Verses.Event = '".$_POST["Event_Type"]."' 
ORDER BY Verses.ID ASC";

 

Then it will be $row['versesID'] for the verses table ID.

 

 

[rocky48]

Thanks again Jesi!

As my knowledge is limited or I'm just plain thick today, can you show me the syntax for the $row['versesID'].

 

The code below gives an error:

Fatal error: Function name must be a string in C:\websites\Test\MshowType.php on line 50

 

while ($Verse_info = mysqli_fetch_array($get_Event_res)) {
	$Event_id = $Verse_info($row['VersesID']);
	$Verse_text = nl2br(stripslashes($Verse_info['Verse']));
	$Mood_info = $Verse_info['Event_Sub_Type'];

 

If not perhaps I misunderstood your last sentance.

 

Thanks again!

[Jessica]

I assumed you called your array $row. The key of your existing array will be versesID.

$Event_id = $Verse_info['VersesID'];

[rocky48]

Sorry! I am thick.

 

Where do you set up the array called $row?

[Jessica]

You don't, forget about that.

[rocky48]

When I typed in

$Event_id = $Verse_info['VersesID'];

all I get is a blank field in the table??

[Jessica]

Post your new sql. Did you call it VersesID or versesID

[rocky48]

AHH! I always fall for that one!

 

It's my age!  No so young as I feel!

 

My brain is siezing up I think.