Combine Few MySQL Tables on one PHP page

[jason97673]

Is it possible to combine tables using PHP and MySQL.

 

Basically, their is table1 and table2 each with multiple fiellds. Each row in each table will have a different ID(unique - primary key, but the different tables will have the same set of IDs). Is it possible to extract data from these two tables using PHP and work with the data on one page.

 

For example what would be the proper syntax for this -

select * from table1 and table2 where ID = abc1

 

Then you have the PHP part where I need to put the query into an array so I can work with the data. However, keep in mind, some, if not all of the column names in the two tables will be unique.

 

When I have one table I usually use

 

$data = mysql_query("SELECT * FROM table1 where id='abc1'");
$info = mysql_fetch_array( $data );

Then precede to display the data by using

echo $info['column_name']

But if the tables I combined have the same column name, it wont work properly. I guess the proper way to say it or do it would be to create multiple queries on the same page because if you combine two tables that have the same column names, something would go wrong as you can't have the same column names in a database.

 

Hope I explained this clearly.

[MatthewJ]

You could select the columns from each table like "columnname.tablename AS alias" and set a different alias for each of the columns with identical names.

 

Then you would use $info['alias'] to get the data.

 

The first question about selecting from multiple tables should be

 

SELECT * FROM table1, table2 WHERE id='abc123'

 

All together it would be something like

 

SELECT column1.table1 AS field1, column2.table1 AS field2, column1.table2 AS field3 FROM table1, table2 WHERE id='abc123'

 

Hope that helps

[jason97673]

Thanks for the reply. I actually thought about using "aliases" after I made the post. However it is possible, I may have to combine 15 different tables with 10 columns in each table, and if I did that, that would be a very long query, and probably not proper coding. I just made the example shorter so it would be easy to understand.

 

Perhaps it might be better coding to make a seperate page for this one very long query and "include" it.

[jason97673]

Any more help? thanks

[jason97673]

Well I think I finally did what I have intended to do. However, the way I did it is probably the easy way which is probably bad coding.

 

For my ten different tables or however mny I have, I created a variable for each one such as

$firstDown = mysql_query("SELECT * FROM 1stdown where profileID='$profileID'");
$secondDown = mysql_query("SELECT * FROM 2nddown where profileID='$profileID'");

Then:

$row1 = mysql_fetch_array( $firstDown );
$row2 = mysql_fetch_array( $secondDown );

 

And then simply displayed it in table format using $row1['cmp'] and $row2['cmp'].

 

This looks to be the easy way and Id prob keep it that way unless I can find a way to do it all without many different queries.

But this looks to be bad coding so Ill still be looking for ways to upgrade this.