[Resolved] Querying multiple tables

ph3n0m

Just wondering what is the best way to go about this.

Single database, four seperate tables (events, tasks, notes, contact).

Common fields for each table are datestamp and status

What I would like to do is search all four tables, merge the results together and sort by the datestamp

So that, for example I would get returned (according to datestamp)

most recent
next most recent
next most recent
next most recent
next most recent

Should I be looking at Join or Unions?

Lars_Berg

union

ph3n0m

*sorry I mean to say - is there anything extra I can add to this code that would indicate which table any of the returns came from?

i.e. first row returned came from contacts, second row from event, thrid row from event, etc

include("db.inc.php");

$sql = "
(SELECT datestamp, status from contacts where userid='$id')
UNION
(SELECT datestamp, status from event where userid='$id')
UNION
(SELECT datestamp, status from notes where userid='$id')
UNION
(SELECT datestamp, status from tasks where userid='$id')
ORDER BY datestamp DESC LIMIT 10
"; 
$sql_result = mysql_query($sql) or die(mysql_error());
$num = mysql_numrows($sql_result);
if ($num == "0"){
echo "";} 
else {
while ($row = mysql_fetch_array($sql_result)) {
$status = $row["status"];
$datestamp = $row["datestamp"];

echo "$status: $datestamp<br>
";
}
}

Lars_Berg

SELECT datestamp, status,'contacts'

SELECT datestamp, status,'Events'

ph3n0m

I have changed the code to

<?
include("db.inc.php");

$sql = "
(SELECT datestamp, status, 'contacts' from contacts where userid='123')
UNION
(SELECT datestamp, status, 'event' from event where userid='123')
UNION
(SELECT datestamp, status, 'notes' from notes where userid='123')
UNION
(SELECT datestamp, status, 'tasks' from tasks where userid='123')
ORDER BY datestamp DESC LIMIT 10
"; 
$sql_result = mysql_query($sql) or die(mysql_error());
$num = mysql_numrows($sql_result);
if ($num == "0"){
echo "";} 
else {
while ($row = mysql_fetch_array($sql_result)) {
$status = $row["status"];
$datestamp = $row["datestamp"];

echo "$status: $datestamp<br>
";
}
}
?>

However my output for each line is still not indicating what table returned what result - what have I overlooked?

ph3n0m

resolved it 🙂

<?

if (!$id){
}
else {
include("db.inc.php");

$sql = "
(SELECT datestamp, status, 'contact' as n from contacts t where userid='$id')
UNION
(SELECT datestamp, status, 'event' as n from event t where userid='$id')
UNION
(SELECT datestamp, status, 'notes' as n from notes t where userid='$id')
UNION
(SELECT datestamp, status, 'tasks' as n from tasks t where userid='$id')
ORDER BY datestamp DESC LIMIT 10
"; 
$sql_result = mysql_query($sql) or die(mysql_error());
$num = mysql_numrows($sql_result);
if ($num == "0"){
echo "";} 
else {
while ($row = mysql_fetch_array($sql_result)) {
$coloumn = $row["n"];
$status = $row["status"];
$datestamp = $row["datestamp"];

echo "$coloumn, $status: $datestamp<br>
";
}
}
}
?>