PHP-MySQL: Arranging rows from seperate tables together/Expression to determine row origin
Posted
by Koroviev
on Stack Overflow
See other posts from Stack Overflow
or by Koroviev
Published on 2010-03-10T14:06:08Z
Indexed on
2010/03/11
19:29 UTC
Read the original article
Hit count: 245
I'm new to PHP and have a two part question.
I need to take rows from two separate tables, and arrange them in descending order by their date. The rows do not correspond in order or number and have no relationship with each other.
---EDIT---
They each contain updates on a site, one table holds text, links, dates, titles etc. from a blog. The other has titles, links, specifications, etc. from images. I want to arrange some basic information (title, date, small description) in an updates section on the main page of the site, and for it to be in order of date.
Merging them into one table and modifying it to suit both types isn't what I'd like to do here, the blog table is Wordpress' standard wp_posts and I don't feel comfortable adding columns to make it suit the image table too. I'm afraid it could clash with upgrading later on and it seems like a clumsy solution (but that doesn't mean I'll object if people here advise me it's the best solution).
------EDIT 2------
Here are the DESCRIBES of each table:
mysql> describe images;
+---------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| project | varchar(255) | NO | | NULL | |
| title | varchar(255) | NO | | NULL | |
| time | timestamp | NO | | CURRENT_TIMESTAMP | |
| img_url | varchar(255) | NO | | NULL | |
| alt_txt | varchar(255) | YES | | NULL | |
| text | text | YES | | NULL | |
| text_id | int(11) | YES | | NULL | |
+---------+--------------+------+-----+-------------------+----------------+
mysql> DESCRIBE wp_posts;
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| post_author | bigint(20) unsigned | NO | | 0 | |
| post_date | datetime | NO | | 0000-00-00 00:00:00 | |
| post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content | longtext | NO | | NULL | |
| post_title | text | NO | | NULL | |
| post_excerpt | text | NO | | NULL | |
| post_status | varchar(20) | NO | | publish | |
| comment_status | varchar(20) | NO | | open | |
| ping_status | varchar(20) | NO | | open | |
| post_password | varchar(20) | NO | | | |
| post_name | varchar(200) | NO | MUL | | |
| to_ping | text | NO | | NULL | |
| pinged | text | NO | | NULL | |
| post_modified | datetime | NO | | 0000-00-00 00:00:00 | |
| post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content_filtered | text | NO | | NULL | |
| post_parent | bigint(20) unsigned | NO | MUL | 0 | |
| guid | varchar(255) | NO | | | |
| menu_order | int(11) | NO | | 0 | |
| post_type | varchar(20) | NO | MUL | post | |
| post_mime_type | varchar(100) | NO | | | |
| comment_count | bigint(20) | NO | | 0 | |
+-----------------------+---------------------+------+-----+---------------------+----------------+
---END EDIT---
I can do this easily with a single table like this (I include it here in case I'm using an over-elaborate method without knowing it):
$content = mysql_query("SELECT post_title, post_text, post_date FROM posts ORDER BY post_date DESC");
while($row = mysql_fetch_array($content))
{
echo $row['post_date'], $row['post_title'], $row['post_text'];
}
But how is it possible to call both tables into the same array to arrange them correctly? By correctly, I mean that they will intermix their echoed results based on their date. Maybe I'm looking at this from the wrong perspective, and calling them to a single array isn't the answer?
Additionally, I need a way to form a conditional expression based on which table they came from, so that rows from table 1 get echoed differently than rows from table 2? I want results from table 1 to be echoed differently (with different strings concatenated around them, I mean) for the purpose of styling them differently than those from table two. And vice versa. I know an if...else statement would work here, but I have no idea how can I write the expression that would determine which table the row is from.
All and any help is appreciated, thanks.
© Stack Overflow or respective owner