selecting the "as" once selected as

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Steji
Forum Newbie
Posts: 19
Joined: Fri Jun 18, 2010 6:06 am

selecting the "as" once selected as

Post by Steji »

I'm struggling to get the syntax right for what I'm trying to do and I was hoping somebody could point me in the right direction, I can't find anything like what I'm trying to do but I have seen it before.

I'm trying to select a4,a5 and a6 from 3 tables and store them in 3 separate arrays from one query. So far I can't even get a4 to work. It does work however if I knock off the ['a4'] from the ['a4']['weight'].

Code: Select all

$qry = "SELECT a4.*, a5.*, a6.* from a4 as a4, a5 as a5, a6 as a6";
$res = mysql_query($qry) or die(mysql_error());

$A4130 = array();
$A4170 = array();
$A4300 = array();

while($row = mysql_fetch_array($res)) {
		if($row['a4']['weight'] == "130gsm"){
			$A4130[] = $row;
		}
		else if($row['a4']['weight'] == "170gsm"){
			$A4170[] = $row;
		}
		else if($row['a4']['weight'] == "300gsm"){
			$A4300[] = $row;
		}
	}
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: selecting the "as" once selected as

Post by Eran »

It's called an alias for future reference. Also, there is no need to alias a table to the its own name.
The table alias is not a part of the query results, so you can't refer to it like you are trying to do. If you want to access distinct columns from each table, give those columns specific aliases. As a rule of thumb, it's usually better to specify the columns in a select query instead of using the * symbol. And last, you aren't specifying any join conditions between the tables, which means this query is a cross join which returns every possible combination between the rows in all three table - which is usually not wanted.
Steji
Forum Newbie
Posts: 19
Joined: Fri Jun 18, 2010 6:06 am

Re: selecting the "as" once selected as

Post by Steji »

pytrin wrote:It's called an alias for future reference. Also, there is no need to alias a table to the its own name.
The table alias is not a part of the query results, so you can't refer to it like you are trying to do. If you want to access distinct columns from each table, give those columns specific aliases. As a rule of thumb, it's usually better to specify the columns in a select query instead of using the * symbol. And last, you aren't specifying any join conditions between the tables, which means this query is a cross join which returns every possible combination between the rows in all three table - which is usually not wanted.

Thanks pytrin. Good to know! The only problem I have now is avoiding the cross join? I just want to select all the data from each table, I don't want every possible combination?

This is what I have now:

Code: Select all

$qry = "SELECT a4.id, a4.weight as a4weight, a4.quantity, a4.singleprice, a4.doubleprice, a5.id, a5.weight, a5.quantity, a5.singleprice, a5.doubleprice, a6.id, a6.weight, a6.quantity, a6.singleprice, a6.doubleprice FROM a4, a5, a6";
Steji
Forum Newbie
Posts: 19
Joined: Fri Jun 18, 2010 6:06 am

Re: selecting the "as" once selected as

Post by Steji »

Sorry, more specifically I want to be able to select all the data and distinguish between which table is has come from. Does that mean I can't use joins or union?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: selecting the "as" once selected as

Post by mikosiko »

Looking to your table's columns seems that they store basically the same information, therefore, why you need 4 tables to store the same ?

my suggestion for you is to first review your data model and check why you can't use just one table... after that your query should be straight forward.
Steji
Forum Newbie
Posts: 19
Joined: Fri Jun 18, 2010 6:06 am

Re: selecting the "as" once selected as

Post by Steji »

mikosiko wrote:Looking to your table's columns seems that they store basically the same information, therefore, why you need 4 tables to store the same ?

my suggestion for you is to first review your data model and check why you can't use just one table... after that your query should be straight forward.
Yes I think that is what I will have to do. These 3 tables will be merged into one table named leaflets, eventually I will run into this problem again however. :(
Post Reply