Page 1 of 1
selecting the "as" once selected as
Posted: Tue Aug 10, 2010 7:13 am
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;
}
}
Re: selecting the "as" once selected as
Posted: Tue Aug 10, 2010 8:01 am
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.
Re: selecting the "as" once selected as
Posted: Tue Aug 10, 2010 8:36 am
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";
Re: selecting the "as" once selected as
Posted: Tue Aug 10, 2010 8:45 am
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?
Re: selecting the "as" once selected as
Posted: Tue Aug 10, 2010 9:17 am
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.
Re: selecting the "as" once selected as
Posted: Tue Aug 10, 2010 9:20 am
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.
