Select query with Forein key Relation
in [Databases]
|
From: Nasreen Laghari on 23 Apr 2008 05:11 Hi, I have a table which contains 2 foreign key relation columns. I'm trying to get all columns from main table as well as all column from those 2 foreign key relation tables. The query i'm using is : select * from gig where gig.gigid = $gigDetail LEFT JOIN genre ON gig.genreId=genre.genreId LEFT JOIN venue ON gig.venueID = venue.vid ORDER BY gig.gigid"; is this query OK? I know how to get value from gig table colums but how could i get value of columns from venue table? Regards Nasreen Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
From: Evert Lammerts on 23 Apr 2008 06:48 SELECT * FROM gig LEFT JOIN genre ON gig.genreId = genre.genreId LEFT JOIN venue ON gig.venueID = venue.vid WHERE gig.gigid = $gigdetail I'd replace the dash with [table].[columnames]. Also, you're using four different naming conventions in your columns - gigid, genreId, venueID and vid. If I were you I'd go for one of them and apply this to all. Evert Nasreen Laghari wrote: > Hi, > > I have a table which contains 2 foreign key relation columns. I'm trying to get all columns from main table as well as all column from those 2 foreign key relation tables. > > The query i'm using is : > > select * from gig where gig.gigid = $gigDetail LEFT JOIN genre ON gig.genreId=genre.genreId LEFT JOIN venue ON gig.venueID = venue.vid ORDER BY gig.gigid"; > > is this query OK? > > I know how to get value from gig table colums but how could i get value of columns from venue table? > > Regards > > Nasreen > > > > > > > Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. > > > ____________________________________________________________________________________ > Be a better friend, newshound, and > know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ >
From: Evert Lammerts on 23 Apr 2008 07:59 It'd be consistent to use uniform naming conventions for your columns. E.g., an ID column is called 'id' in every table, and if you use two words in a column name, to separate them either by an underscore or by a capital letter for the second word. In PHP a dot is an append operator - to reference a member of a certain namespace you use '->'. In this case you don't need it though. To get a column 'vname' from the table 'venue' you use: while ($row = mysql_fetch_assoc($result)) { $sub = $row["venue.vname"]; } Remember that in this case, $sub will always only hold the value of the last result - or of the only result if there is only one result row. Nasreen Laghari wrote: > Hi Evert, > > What to you mean by this "If I were you I'd go for one of them and > apply this to all." > also to get the value of columns do i need to do following in php > > /while ($row = mysql_fetch_array($result)) > {/ > / $sub= $row[venue].[vname];/ > /}/ > > Regards > > > > ----- Original Message ---- > From: Evert Lammerts <evert.lammerts(a)gmail.com> > To: Nasreen Laghari <nasreen_laghari(a)yahoo.com> > Cc: php-db(a)lists.php.net > Sent: Wednesday, April 23, 2008 11:48:39 AM > Subject: Re: [PHP-DB] Select query with Forein key Relation > > SELECT * > FROM gig > LEFT JOIN genre ON gig.genreId = genre.genreId > LEFT JOIN venue ON gig.venueID = venue.vid > WHERE gig.gigid = $gigdetail > > I'd replace the dash with [table].[columnames]. Also, you're using four > different naming conventions in your columns - gigid, genreId, venueID > and vid. If I were you I'd go for one of them and apply this to all. > > Evert > > Nasreen Laghari wrote: > > Hi, > > > > I have a table which contains 2 foreign key relation columns. I'm > trying to get all columns from main table as well as all column from > those 2 foreign key relation tables. > > > > The query i'm using is : > > > > select * from gig where gig.gigid = $gigDetail LEFT JOIN genre ON > gig.genreId=genre.genreId LEFT JOIN venue ON gig.venueID = venue.vid > ORDER BY gig.gigid"; > > > > is this query OK? > > > > I know how to get value from gig table colums but how could i get > value of columns from venue table? > > > > Regards > > > > Nasreen > > > > > > > > > > > > > > Be a better friend, newshound, and know-it-all with Yahoo! Mobile. > Try it now. > > > > > > > ____________________________________________________________________________________ > > Be a better friend, newshound, and > > know-it-all with Yahoo! Mobile. Try it now. > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ > > > > > > ------------------------------------------------------------------------ > Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try > it now. > <http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ%20>
|
Pages: 1 Prev: Designing An API with PHP/MySql Next: php and mssql on seperate servers |