Multy SELECT from different tables with not the same column

[rwhite35]

Not sure how your tables look, but it seems like (edited @9:50a) you want a join clause. If table 'post' and table 'nwo' both have a column labeled 'datetime', and you want to pull records where the datetimes match up, try

Code: Select all

 SELECT a.datetime, b.datetime FROM post AS a JOIN nwo AS b ON a.datetime=b.datetime ORDER BY a.datetime DESC LIMIT 4;

This would have pulled the datetime data from both tables, returning a set of rows where the datetimes matched.

I don't have a command controller available right this moment so I can't test the select statement to confirm. I would suggest trying this select statement in you MySQL control panel or on the command line. The error reporting will be more robust.

[rwhite35]

You can select all (*) columns as you originally had it, I specified the datetime column so it was clear as to what was happening. The following would return all columns where the datetime data matched up.

Code: Select all

SELECT a.*, b.* FROM post AS a JOIN nwo AS b ON a.datetime=b.datetime ORDER BY a.datetime DESC;

[rwhite35]

I really think its in your query logic. The while statements looks fine. In this example, I wouldn't run the conditional statement if($row['title']){echo $row['title']}... but that's not the problem. For simplicity, try the while loop like

Code: Select all

while ($row = mysql_fetch_assoc($rs)) {
    echo $row["title"];
    echo $row["titles"];
}

But I think your result is coming back different than you expect. The way it is currently written, there doesn't seem to be a relationship between post or nwo. If the two don't share some common data, then you are better off making two separate queries and processing the result outside your query statement.

post.title and nwo.titles don't have to have the same column labels, but one of the fields should be data that matches. example: post.title="My Book" and nwo.titles="My Book". Now one record can be returned with data from both tables.

Have you tried your select statement in phpMyAdmin or mysql controller? Does your query produce what you want?

[rwhite35]

The syntax for the alias is correct. Its how your using the whole query mechanism. Typically you'll query two or more tables in order to return records with relative data from each table. Hence the concept of a relational database.

Two tables:
tbl1.user_id, tbl1.user_name
tbl2.login_user, tbl2.login_date

Table 1 contains user data while Table 2 contains log in dates for each user (by inserting the user_id).

Code: Select all

SELECT a.*, b.* FROM tbl1 AS a JOIN tbl2 AS b ON a.user_id=b.login_user LIMIT 1;

This would return the first row where there was a match.

In Controller:
| user_id | user_name | login_user | login_date |
| 21 | Joe Doe | 21 | 2013-01-31 14:20:30 |

You would see this result inside PHP with

Code: Select all

while ($row=mysql_fetch_assoc($rs)){
   print_r($row);
}

Array (
  [user_id]=>21,
  [user_name]=>Joe Doe,
  [login_user] => 21
  [login_date] => 2013-01-31 14:20:30
)

In the above fictional tables, user_id and login_user are the same, so MySQL returns a result that can be output or assigned. But the key was JOIN and common data that would put the two tables together in to one row.