Best way to combine 3 tables data together I have found is the following script.$result = mysql_query("SELECT *
FROM visit
JOIN login ON (login.id = visit.visitby)
JOIN residents ON (residents.id = visit.resid)");
Before you do it the hard way try the easy way…just add a WHERE CLAUSE
SELECT login.fname , residents.fname , visit . *
FROM login, visit, residents
LIMIT 0 , 30
This SQL joins two tables together where the campaign id is the same.
If the results back are many, then I may want to check another table to see if something is true.
We can add another join statement “INNER JOIN table3 ON table3.campaignid = table1.campaignid” as long as it has the campaignid in it also. Then we just add the statement “AND table3.impressions_month != 0”
We could join many tables like this…….
ORIGINAL VERSION (Two tables)
$result2 = mysql_query("SELECT * FROM table1
INNER JOIN ( SELECT MAX(table1.id) AS id FROM table1
INNER JOIN table2 ON table2.campaignid = table1.campaignid
WHERE table1.date='$thisday'
AND table1.client ='$boardname'
AND table2.enabled != 'disabled'
GROUP BY table1.campaign )
AS m ON m.id = table1.id
order by table1.campaign ASC
");
UPDATED VERSION (three tables)
$result2 = mysql_query("SELECT * FROM table1
INNER JOIN ( SELECT MAX(table1.id) AS id FROM table1
INNER JOIN table2 ON table2.campaignid = table1.campaignid
INNER JOIN table3 ON table3.campaignid = table1.campaignid
WHERE table1.date='$thisday'
AND table1.client ='$boardname'
AND table2.enabled != 'disabled'
AND table3.impressions_month != 0
GROUP BY table1.campaignid )
AS m ON m.id = table1.id
order by table1.campaign ASC
");
Another more full example
// t1="messages";
// t2="workers";
// t3="jobs";
$sql = "SELECT * FROM messages
INNER JOIN ( SELECT
messages.id AS id,
workers.fname AS fname,
workers.sname AS sname,
jobs.jobname AS jobname,
jobs.refnumber AS jobref
FROM messages
INNER JOIN workers ON workers.id = messages.workerid
INNER JOIN jobs ON jobs.refnumber = messages.jobref)
AS m ON m.id = messages.id
";
// $sql = "SELECT * FROM messages JOIN workers ON messages.workerid = workers.id";
if(!$result = $mysqli->query($sql)){
die('There was an error running the query [' . $mysqli->error . ']');
}
while($row = $result->fetch_assoc()){
$message[] = $row['message'];
$fname[] = $row['fname'];
$sname[] = $row['sname'];
$time[] = $row['time'];
$jobname[] = $row['jobname'];
$jobref[] = $row['jobref'];
}
If you have a fname in both tables you are trying to get results from, you can do this….
$resulth = mysql_query("SELECT *, login.fname AS myfname
FROM visit
JOIN login ON (login.id = visit.visitby)
JOIN residents ON (residents.id = visit.resid) order by visit.id desc limit 10");
//then echo out the 'AS'
echo $row['myfname']
