[Resolved] strange join

mhalloran

I have a select statement that I'm having problems with.

Here's deal:

I have two tables with these fields:

table_1
id, message, date

table_2
id, message, date

What I want to do is select the messages from both tables and order it by the joined dates. Is this possible?

LordShryku

This screams bad database design....
Exact same table. Any reason why you can't just combine them into one table?

mhalloran

Well, I'm totally oversimplifying. Table_1 has about 20 fields and I just need to interlace the messages from another table for a quick query. There are two other tables involved as well but joining based on the dates of two tables is where I'm stuck.

I could redesign the database structure but I was hoping for an easy way out.

LordShryku

OK, well in that case, is this truely a join situation? Usually a join is used to select certain data out of two tables with a common denominator. If you're just trying to select all messages out of both tables, that would probably be either two seperate queries, or a union instance.

mhalloran

a union? How would that work?

Thanks for the replies by the way....

LordShryku

Here's the manual page for it. Pretty straight forward.

mhalloran

yeah, I was just reading that, thanks....

That's not really what I'm looking for because I need the fields ordered by date. If there are two messages in table 1 and then 1 in table two and then 2 in table one again, the output should read:

table1 msg
table1 msg
table2 msg
table1 msg
table1 msg

make sense? What I think I'm going to have to do for this is create a temporary table that contains the dates, ids and table name. Afterward, I can order by the dates in the temporary table and then grab the contents from the table as specified in the temp table.

LordShryku

Well, another option would just be to sort them in php with a multidimensioal array. Just an idea: strtotime the dates to get them in a number format as the keys. Example:

$results = array();
$sql = "SELECT date,
               id
               message
          FROM table_1";
$res = mysql_query($sql);
if(mysql_num_rows($res) > 0) {
   while($row = mysql_fetch_array($res)) {
      $t = strtotime($date);
      $results[$t] = array("id"      => $row['id'],
                           "message" => $row['message']);
   }
}

unset($sql);
unset($res);

$sql = "SELECT date,
               message
          FROM table_2";
$res = mysql_query($sql);
if(mysql_num_rows($res) > 0) {
   while($row = mysql_fetch_array($res)) {
      $t = strtotime($date);
      $results[$t] = array("id"      => $row['id'],
                           "message" => $row['message']);
   }
}

sort($results);
reset($results);

Just an idea...

mhalloran

Thank you my lord - good thinking 🆒

The thing is that there are more fields that I need to retrieve and I'll have to add a paging mechanism to that but it just may work.

Good advice - I'll mark this baby as resolved.

LordShryku

No problem! Just post back if you get stuck 🙂