[PHP-DB] Select from multiple tables
Hi all, Say I have an object like array schoolName = string board = array string string I generally create two MySql tables schools: id PRIMARY KEY, SchoolName boards: id FOREGIN KEY refers Table A(id), board and then do two selects. The problem is that, the number of selects increase as the number of one-to-many relationships increase. Is there a better way to do this ? I have to extend an existing code so I cannot use any libraries like doctrine Kranthi. http://goo.gl/e6t3 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select from multiple tables
On Wed, Feb 29, 2012 at 3:01 PM, Kranthi Krishna kranthi...@gmail.com wrote: Hi all, Say I have an object like array schoolName = string board = array string string I generally create two MySql tables schools: id PRIMARY KEY, SchoolName boards: id FOREGIN KEY refers Table A(id), board and then do two selects. The problem is that, the number of selects increase as the number of one-to-many relationships increase. Is there a better way to do this ? I have to extend an existing code so I cannot use any libraries like doctrine Kranthi. http://goo.gl/e6t3 You should look up at SQL joins. They will do what you want. - Matijn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select from multiple tables
Hi, Thanks for the input. I have seen some tutorials on joins, they all suggest that MySql returns multiple rows For example -- School | Board 1 -- School | Board 1 - Now if I have another one-to-many relation --- School | Board 1 | Type 1 --- School | Board 1 | Type 2 --- School | Board 2 | Type 1 --- School | Board 2 | Type 2 Using UNIQUE or something similar (like php.net/array_search ) causes problems when Type 1 = Type 2 etc. Kranthi. http://goo.gl/e6t3 On 29 February 2012 19:43, Michael Stowe mikegst...@gmail.com wrote: Select table1.item1, table2.item1 from table1 inner join table2 on table1.key = table2.foreignKey Where... You can also utilize left and right join to get data if there isn't a direct match (ie customer may not have ordered anything so you want to do a left join on orders as there may not be any order data but you still want to get the customer info). Hope that helps, Mike Sent from my iPhone On Feb 29, 2012, at 8:01 AM, Kranthi Krishna kranthi...@gmail.com wrote: Hi all, Say I have an object like array schoolName = string board = array string string I generally create two MySql tables schools: id PRIMARY KEY, SchoolName boards: id FOREGIN KEY refers Table A(id), board and then do two selects. The problem is that, the number of selects increase as the number of one-to-many relationships increase. Is there a better way to do this ? I have to extend an existing code so I cannot use any libraries like doctrine Kranthi. http://goo.gl/e6t3 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select from multiple tables
Hi, The examples I saw were regarding cartesian join not inner join. I will read about inner joins. Also, the example i mentioned seems to be a mistake. Both school and type will not be similar at the same time Kranthi. http://goo.gl/e6t3 On 1 March 2012 09:26, Kranthi Krishna kranthi...@gmail.com wrote: Hi, Thanks for the input. I have seen some tutorials on joins, they all suggest that MySql returns multiple rows For example -- School | Board 1 -- School | Board 1 - Now if I have another one-to-many relation --- School | Board 1 | Type 1 --- School | Board 1 | Type 2 --- School | Board 2 | Type 1 --- School | Board 2 | Type 2 Using UNIQUE or something similar (like php.net/array_search ) causes problems when Type 1 = Type 2 etc. Kranthi. http://goo.gl/e6t3 On 29 February 2012 19:43, Michael Stowe mikegst...@gmail.com wrote: Select table1.item1, table2.item1 from table1 inner join table2 on table1.key = table2.foreignKey Where... You can also utilize left and right join to get data if there isn't a direct match (ie customer may not have ordered anything so you want to do a left join on orders as there may not be any order data but you still want to get the customer info). Hope that helps, Mike Sent from my iPhone On Feb 29, 2012, at 8:01 AM, Kranthi Krishna kranthi...@gmail.com wrote: Hi all, Say I have an object like array schoolName = string board = array string string I generally create two MySql tables schools: id PRIMARY KEY, SchoolName boards: id FOREGIN KEY refers Table A(id), board and then do two selects. The problem is that, the number of selects increase as the number of one-to-many relationships increase. Is there a better way to do this ? I have to extend an existing code so I cannot use any libraries like doctrine Kranthi. http://goo.gl/e6t3 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select from multiple tables
Dear Kranthi You have to be clear what you decide especially when you are getting multiple rows. To get just a single row you can use LIMIT clause. But it would return only one row. Now you have to decide which row. So i think you decide on what you require and see how can you uniquely identify that row regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna kranthi...@gmail.comwrote: Hi, The examples I saw were regarding cartesian join not inner join. I will read about inner joins. Also, the example i mentioned seems to be a mistake. Both school and type will not be similar at the same time Kranthi. http://goo.gl/e6t3 On 1 March 2012 09:26, Kranthi Krishna kranthi...@gmail.com wrote: Hi, Thanks for the input. I have seen some tutorials on joins, they all suggest that MySql returns multiple rows For example -- School | Board 1 -- School | Board 1 - Now if I have another one-to-many relation --- School | Board 1 | Type 1 --- School | Board 1 | Type 2 --- School | Board 2 | Type 1 --- School | Board 2 | Type 2 Using UNIQUE or something similar (like php.net/array_search ) causes problems when Type 1 = Type 2 etc. Kranthi. http://goo.gl/e6t3 On 29 February 2012 19:43, Michael Stowe mikegst...@gmail.com wrote: Select table1.item1, table2.item1 from table1 inner join table2 on table1.key = table2.foreignKey Where... You can also utilize left and right join to get data if there isn't a direct match (ie customer may not have ordered anything so you want to do a left join on orders as there may not be any order data but you still want to get the customer info). Hope that helps, Mike Sent from my iPhone On Feb 29, 2012, at 8:01 AM, Kranthi Krishna kranthi...@gmail.com wrote: Hi all, Say I have an object like array schoolName = string board = array string string I generally create two MySql tables schools: id PRIMARY KEY, SchoolName boards: id FOREGIN KEY refers Table A(id), board and then do two selects. The problem is that, the number of selects increase as the number of one-to-many relationships increase. Is there a better way to do this ? I have to extend an existing code so I cannot use any libraries like doctrine Kranthi. http://goo.gl/e6t3 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select from multiple tables
Try DISTINCT On Feb 29, 2012, at 10:28 PM, Amit Tandon wrote: Dear Kranthi You have to be clear what you decide especially when you are getting multiple rows. To get just a single row you can use LIMIT clause. But it would return only one row. Now you have to decide which row. So i think you decide on what you require and see how can you uniquely identify that row regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna kranthi...@gmail.comwrote: Hi, The examples I saw were regarding cartesian join not inner join. I will read about inner joins. Also, the example i mentioned seems to be a mistake. Both school and type will not be similar at the same time Kranthi. http://goo.gl/e6t3 On 1 March 2012 09:26, Kranthi Krishna kranthi...@gmail.com wrote: Hi, Thanks for the input. I have seen some tutorials on joins, they all suggest that MySql returns multiple rows For example -- School | Board 1 -- School | Board 1 - Now if I have another one-to-many relation --- School | Board 1 | Type 1 --- School | Board 1 | Type 2 --- School | Board 2 | Type 1 --- School | Board 2 | Type 2 Using UNIQUE or something similar (like php.net/array_search ) causes problems when Type 1 = Type 2 etc. Kranthi. http://goo.gl/e6t3 On 29 February 2012 19:43, Michael Stowe mikegst...@gmail.com wrote: Select table1.item1, table2.item1 from table1 inner join table2 on table1.key = table2.foreignKey Where... You can also utilize left and right join to get data if there isn't a direct match (ie customer may not have ordered anything so you want to do a left join on orders as there may not be any order data but you still want to get the customer info). Hope that helps, Mike Sent from my iPhone On Feb 29, 2012, at 8:01 AM, Kranthi Krishna kranthi...@gmail.com wrote: Hi all, Say I have an object like array schoolName = string board = array string string I generally create two MySql tables schools: id PRIMARY KEY, SchoolName boards: id FOREGIN KEY refers Table A(id), board and then do two selects. The problem is that, the number of selects increase as the number of one-to-many relationships increase. Is there a better way to do this ? I have to extend an existing code so I cannot use any libraries like doctrine Kranthi. http://goo.gl/e6t3 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select from multiple tables
Hi all, SELECT DISTINCT s.Title, b.Board_id, t.type FROM school s, board_entries b, schooltypeentries t WHERE s.School_id = 1698 AND b.School_id = 1698 AND t.schoolid = 1698 this SQL query gives me Kendriya Vidyalaya 15 Kick Boxing Kendriya Vidyalaya 15 Karate Kendriya Vidyalaya 32 Kick Boxing Kendriya Vidyalaya 32 Karate as I stated earlier. Now using php.net/array_search php.net/foreach and php.net/mysql_fetch_assoc I can easily convert that into array name = 'Kendriya Vidyalaya' board_id = array 1 = 15 2 = 32 type = array 1 = 'Kick Boxing' 2 = 'Karate' I am wondering if there is a better way. For example if I am able to get something like Kendriya Vidyalaya 15 Kick Boxing NULLNULLKarate NULL32 NULL NULLNULLNULL I can use php.net/is_null instead of php.net/array_search I dont think this problem is specific to me. Please suggest some best practices in this case. Kranthi. http://goo.gl/e6t3 On 1 March 2012 10:25, Karl DeSaulniers k...@designdrumm.com wrote: Try DISTINCT On Feb 29, 2012, at 10:28 PM, Amit Tandon wrote: Dear Kranthi You have to be clear what you decide especially when you are getting multiple rows. To get just a single row you can use LIMIT clause. But it would return only one row. Now you have to decide which row. So i think you decide on what you require and see how can you uniquely identify that row regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna kranthi...@gmail.comwrote: Hi, The examples I saw were regarding cartesian join not inner join. I will read about inner joins. Also, the example i mentioned seems to be a mistake. Both school and type will not be similar at the same time Kranthi. http://goo.gl/e6t3 On 1 March 2012 09:26, Kranthi Krishna kranthi...@gmail.com wrote: Hi, Thanks for the input. I have seen some tutorials on joins, they all suggest that MySql returns multiple rows For example -- School | Board 1 -- School | Board 1 - Now if I have another one-to-many relation --- School | Board 1 | Type 1 --- School | Board 1 | Type 2 --- School | Board 2 | Type 1 --- School | Board 2 | Type 2 Using UNIQUE or something similar (like php.net/array_search ) causes problems when Type 1 = Type 2 etc. Kranthi. http://goo.gl/e6t3 On 29 February 2012 19:43, Michael Stowe mikegst...@gmail.com wrote: Select table1.item1, table2.item1 from table1 inner join table2 on table1.key = table2.foreignKey Where... You can also utilize left and right join to get data if there isn't a direct match (ie customer may not have ordered anything so you want to do a left join on orders as there may not be any order data but you still want to get the customer info). Hope that helps, Mike Sent from my iPhone On Feb 29, 2012, at 8:01 AM, Kranthi Krishna kranthi...@gmail.com wrote: Hi all, Say I have an object like array schoolName = string board = array string string I generally create two MySql tables schools: id PRIMARY KEY, SchoolName boards: id FOREGIN KEY refers Table A(id), board and then do two selects. The problem is that, the number of selects increase as the number of one-to-many relationships increase. Is there a better way to do this ? I have to extend an existing code so I cannot use any libraries like doctrine Kranthi. http://goo.gl/e6t3 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select from multiple tables
This is just a stab in the dark and may be in the wrong order. If it does not work I apologize. SELECT s.Title, (SELECT DISTINCT b.Board_id), (SELECT DISTINCT t.type), (SELECT s.School_id AND b.School_id AND t.schoolid AS id ) FROM school s, board_entries b, schooltypeentries t WHERE id = 1698 HTW, Best, Karl On Feb 29, 2012, at 11:46 PM, Kranthi Krishna wrote: Hi all, SELECT DISTINCT s.Title, b.Board_id, t.type FROM school s, board_entries b, schooltypeentries t WHERE s.School_id = 1698 AND b.School_id = 1698 AND t.schoolid = 1698 this SQL query gives me Kendriya Vidyalaya 15 Kick Boxing Kendriya Vidyalaya 15 Karate Kendriya Vidyalaya 32 Kick Boxing Kendriya Vidyalaya 32 Karate as I stated earlier. Now using php.net/array_search php.net/foreach and php.net/ mysql_fetch_assoc I can easily convert that into array name = 'Kendriya Vidyalaya' board_id = array 1 = 15 2 = 32 type = array 1 = 'Kick Boxing' 2 = 'Karate' I am wondering if there is a better way. For example if I am able to get something like Kendriya Vidyalaya 15 Kick Boxing NULLNULLKarate NULL32 NULL NULLNULLNULL I can use php.net/is_null instead of php.net/array_search I dont think this problem is specific to me. Please suggest some best practices in this case. Kranthi. http://goo.gl/e6t3 On 1 March 2012 10:25, Karl DeSaulniers k...@designdrumm.com wrote: Try DISTINCT On Feb 29, 2012, at 10:28 PM, Amit Tandon wrote: Dear Kranthi You have to be clear what you decide especially when you are getting multiple rows. To get just a single row you can use LIMIT clause. But it would return only one row. Now you have to decide which row. So i think you decide on what you require and see how can you uniquely identify that row regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna kranthi...@gmail.comwrote: Hi, The examples I saw were regarding cartesian join not inner join. I will read about inner joins. Also, the example i mentioned seems to be a mistake. Both school and type will not be similar at the same time Kranthi. http://goo.gl/e6t3 On 1 March 2012 09:26, Kranthi Krishna kranthi...@gmail.com wrote: Hi, Thanks for the input. I have seen some tutorials on joins, they all suggest that MySql returns multiple rows For example -- School | Board 1 -- School | Board 1 - Now if I have another one-to-many relation --- School | Board 1 | Type 1 --- School | Board 1 | Type 2 --- School | Board 2 | Type 1 --- School | Board 2 | Type 2 Using UNIQUE or something similar (like php.net/array_search ) causes problems when Type 1 = Type 2 etc. Kranthi. http://goo.gl/e6t3 On 29 February 2012 19:43, Michael Stowe mikegst...@gmail.com wrote: Select table1.item1, table2.item1 from table1 inner join table2 on table1.key = table2.foreignKey Where... You can also utilize left and right join to get data if there isn't a direct match (ie customer may not have ordered anything so you want to do a left join on orders as there may not be any order data but you still want to get the customer info). Hope that helps, Mike Sent from my iPhone On Feb 29, 2012, at 8:01 AM, Kranthi Krishna kranthi...@gmail.com wrote: Hi all, Say I have an object like array schoolName = string board = array string string I generally create two MySql tables schools: id PRIMARY KEY, SchoolName boards: id FOREGIN KEY refers Table A(id), board and then do two selects. The problem is that, the number of selects increase as the number of one-to-many relationships increase. Is there a better way to do this ? I have to extend an existing code so I cannot use any libraries like doctrine Kranthi. http://goo.gl/e6t3 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
