Multy SELECT from different tables with not the same column

[ashaman]

I ama new with php
I spent more of whole day to find solution but it not working as i want it.
I have some tables with different column names. I want to did it with one SELECT and continuos IF.
I tried different ways, but aways got error with $row=mysql_fetch_assoc($rs);
It got work only with alias, but i want to show more than one results from each table, but this code shown only by one result from each table.

Code: Select all

$rs = run_q("SELECT a.*, b.*  FROM posts as a, nwo as b ORDER BY a.datetime , b.datetime DESC LIMIT 4");
$row=mysql_fetch_assoc($rs);
if($row['title'])
{
    echo $row['title'];
}
    if($row['titles'])
{
    echo $row['titles'];}

nwo and posts are my tables
All other methods which i tried as JOIN or UNION and some more didnt work for me.
Where is the problem? It is in the SELECT line or with the continues code?

[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;


[ashaman]

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 posts AS a JOIN nwo AS b ON a.datetime=b.datetime ORDER BY a.datetime DESC;


Hello, this return empty page, idk why..
My table1 is posts, table2 is nwo.

Code: Select all

Maybe the problem is here?
while($row = mysql_fetch_assoc($rs)){
if($row['title'])
{
    echo $row['title'];
}
    if($row['titles'])
{
    echo $row['titles'];}}

'title' is column in table1 and 'titles' is column in table2

This

Code: Select all

$rs = run_q("SELECT a.*, b.*  FROM posts as a, nwo as b ORDER BY a.datetime DESC, b.datetime DESC LIMIT 2 ");
while($row = mysql_fetch_assoc($rs)){
if($row['title'])
{
    echo $row['title'];
}
    if($row['titles'])
{
    echo $row['titles'];}}

return proper 2 results from table2(nwo), but return only one record (doubled) from table1(posts).
And i am very very...
And i really can`t understand why this work properly with table 2, but not proper with table 1

[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?

[ashaman]

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. That's why I added the JOIN clause, because post should have some relationship to nwo, like a shared "title" or "id".

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

Hello, i use phpMyAdmin.
I'm trying to select 5 tables, but now just i`m try on with two of them to see if i can make it to work as i want it.
The names of the columns must be different, coz i want to shown with css two records from each table..
But what is wrong with my code with alias? I am newbie and dont understand where is the problem.

[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.