Board index   FAQ   Search  
Register  Login
Board index php forum :: php coding PHP coding => General

Multy SELECT from different tables with not the same column

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

Multy SELECT from different tables with not the same column

Postby ashaman » Thu Jan 31, 2013 4:15 am

I ama new with php :help:
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?
ashaman
New php-forum User
New php-forum User
 
Posts: 4
Joined: Thu Jan 31, 2013 4:03 am

Re: Multy SELECT from different tables with not the same col

Postby rwhite35 » Thu Jan 31, 2013 5:58 am

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
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu Sep 27, 2012 10:55 am

Re: Multy SELECT from different tables with not the same col

Postby rwhite35 » Thu Jan 31, 2013 7:05 am

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
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu Sep 27, 2012 10:55 am

Re: Multy SELECT from different tables with not the same col

Postby ashaman » Thu Jan 31, 2013 8:05 am

rwhite35 wrote: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
ashaman
New php-forum User
New php-forum User
 
Posts: 4
Joined: Thu Jan 31, 2013 4:03 am

Re: Multy SELECT from different tables with not the same col

Postby rwhite35 » Thu Jan 31, 2013 9:03 am

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
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu Sep 27, 2012 10:55 am

Re: Multy SELECT from different tables with not the same col

Postby ashaman » Thu Jan 31, 2013 9:19 am

rwhite35 wrote: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.
ashaman
New php-forum User
New php-forum User
 
Posts: 4
Joined: Thu Jan 31, 2013 4:03 am

Re: Multy SELECT from different tables with not the same col

Postby rwhite35 » Thu Jan 31, 2013 10:24 am

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.
rwhite35
New php-forum User
New php-forum User
 
Posts: 13
Joined: Thu Sep 27, 2012 10:55 am


Return to PHP coding => General

Who is online

Users browsing this forum: No registered users and 4 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron