Re: [PHP-DB] Select from multiple tables
I am a little confused It seems to me that you are mixing two semantically different things I agree. But since both of them are attributes of school so I have to. so you get a cartesian join over these tables(within the restrictions in the where clause) If I am not mistaken INNER JOIN is Cartesian join with where clause Still, you want to lump them together in the same result set. You misunderstood me. I never said that I want a single row set. The following query SELECT s.Title, b.Board_id, t.type FROM (SELECT Title FROM school where School_id = 1698) s, (SELECT GROUP_CONCAT(Board_id) AS Board_id FROM board_entries WHERE School_id = 1698) b, (SELECT GROUP_CONCAT(type) AS type FROM schooltypeentries WHERE schoolid = 1698) t returns me a single row set. But I am aware that this is not practical All I want to do is to restore an object with multiple one-to-many relations Kranthi. http://goo.gl/e6t3 On 1 March 2012 21:47, Carl Michael Skog cms...@gmail.com wrote: -- Vidarebefordrat meddelande -- Från: Carl Michael Skog cms...@gmail.com Datum: 1 mars 2012 17:12 Ämne: Re: [PHP-DB] Select from multiple tables Till: Kranthi Krishna kranthi...@gmail.com It seems to me that you are mixing two semantically different things(board_entries and schooltypeentries, both related to school). These have no relation to each other(at least no one shown here, so you get a cartesian join over these tables(within the restrictions in the where clause)). Still, you want to lump them together in the same result set. The question is why ? Den 1 mars 2012 06:46 skrev Kranthi Krishna kranthi...@gmail.com: 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 NULL NULL Karate NULL 32 NULL NULL NULL NULL 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
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
RE: [PHP-DB] SELECT
-Original Message- From: tamouse mailing lists [mailto:tamouse.li...@gmail.com] Sent: 20 October 2011 21:37 On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike m.f...@leedsmet.ac.uk wrote: -Original Message- From: Ron Piggott [mailto:ron.pigg...@actsministries.org] Sent: 17 October 2011 18:38 What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) This is a little bit of a tricky one, as you have to consider both start_month and end_month as special cases - so you need a three- part conditional, for the start month, the end month, and the months in between. Something like this: SELECT * FROM `introduction_messages` WHERE (month`start_month` AND month`end_month`) OR (month=`start_month AND day=`start_day`) OR (month=`end_month` AND day=`end_day`); This still suffers from the problem in Jim's offer -- wrap of year and wrap of month Look again. Month wrap *is* handled by the specific tests for start_month and end_month. As to year-wrap, Ron's original post said: ... The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. so I didn't bother about year-wrap, assuming he would include a range with start_date of 1/1 and another with end_date of 31/12. Cheers! Mike -- Mike Ford, Electronic Information Developer, Libraries and Learning Innovation, Portland PD507, City Campus, Leeds Metropolitan University, Portland Way, LEEDS, LS1 3HE, United Kingdom E: m.f...@leedsmet.ac.uk T: +44 113 812 4730 But in the case of years actually mattering then, yes, the above would not work To view the terms under which this email is distributed, please go to http://disclaimer.leedsmet.ac.uk/email.htm
Re: [PHP-DB] SELECT
On Fri, Oct 21, 2011 at 2:09 AM, Ford, Mike m.f...@leedsmet.ac.uk wrote: -Original Message- From: tamouse mailing lists [mailto:tamouse.li...@gmail.com] Sent: 20 October 2011 21:37 On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike m.f...@leedsmet.ac.uk wrote: -Original Message- From: Ron Piggott [mailto:ron.pigg...@actsministries.org] Sent: 17 October 2011 18:38 What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) This is a little bit of a tricky one, as you have to consider both start_month and end_month as special cases - so you need a three- part conditional, for the start month, the end month, and the months in between. Something like this: SELECT * FROM `introduction_messages` WHERE (month`start_month` AND month`end_month`) OR (month=`start_month AND day=`start_day`) OR (month=`end_month` AND day=`end_day`); This still suffers from the problem in Jim's offer -- wrap of year and wrap of month Look again. Month wrap *is* handled by the specific tests for start_month and end_month. Hmm -- yes, you are right -- it does handle the month wrap problem okay. As to year-wrap, Ron's original post said: ... The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. so I didn't bother about year-wrap, assuming he would include a range with start_date of 1/1 and another with end_date of 31/12. So you are saying it can be easily worked around if there is a particular case that is supposed to wrap over the end of the year and simply include the item twice: one starting on Jan 1 and the other one ending on Dec 31. (I'm not sure if that's what you meant above.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT
On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike m.f...@leedsmet.ac.uk wrote: -Original Message- From: Ron Piggott [mailto:ron.pigg...@actsministries.org] Sent: 17 October 2011 18:38 I need help creating a mySQL query that will select the correct introduction message for a website I am making. The way I have designed the table I can’t wrap my mind around the SELECT query that will deal with the day # of the month. The part of the SELECT syntax I am struggling with is when the introduction message is to change mid month. The reason I am struggling with this is because I haven’t used ‘DATE’ for the column type. The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) This is a little bit of a tricky one, as you have to consider both start_month and end_month as special cases - so you need a three-part conditional, for the start month, the end month, and the months in between. Something like this: SELECT * FROM `introduction_messages` WHERE (month`start_month` AND month`end_month`) OR (month=`start_month AND day=`start_day`) OR (month=`end_month` AND day=`end_day`); This still suffers from the problem in Jim's offer -- wrap of year and wrap of month This might be best handled in a stored procedure, converting the values stored in the table to dates to do the comparison with in the where clause. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT
On Thu, Oct 20, 2011 at 3:36 PM, tamouse mailing lists tamouse.li...@gmail.com wrote: On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike m.f...@leedsmet.ac.uk wrote: -Original Message- From: Ron Piggott [mailto:ron.pigg...@actsministries.org] Sent: 17 October 2011 18:38 I need help creating a mySQL query that will select the correct introduction message for a website I am making. The way I have designed the table I can’t wrap my mind around the SELECT query that will deal with the day # of the month. The part of the SELECT syntax I am struggling with is when the introduction message is to change mid month. The reason I am struggling with this is because I haven’t used ‘DATE’ for the column type. The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) This is a little bit of a tricky one, as you have to consider both start_month and end_month as special cases - so you need a three-part conditional, for the start month, the end month, and the months in between. Something like this: SELECT * FROM `introduction_messages` WHERE (month`start_month` AND month`end_month`) OR (month=`start_month AND day=`start_day`) OR (month=`end_month` AND day=`end_day`); This still suffers from the problem in Jim's offer -- wrap of year and wrap of month This might be best handled in a stored procedure, converting the values stored in the table to dates to do the comparison with in the where clause. In thinking further on this, the OP might consider this problem as well -- it is going to be difficult to determine the correct response if all that is stored is the start and ending month and day of month in the case where the desired time stretch wraps over to the new year. When your start month is 12 and your end month is 1, what do you expect to happen? It can't generally be solved by using current year for the start and current year + 1 for the end. For example, you may want to have something start at current year, month=6 and end as next year, month=8, so simply checking if end month start month won't give you the ability to discern if you've wrapped the year. (I realize this may not be the OP's case, but it is still an issue if seeking a general solution.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SELECT
-Original Message- From: Ron Piggott [mailto:ron.pigg...@actsministries.org] Sent: 17 October 2011 18:38 I need help creating a mySQL query that will select the correct introduction message for a website I am making. The way I have designed the table I can’t wrap my mind around the SELECT query that will deal with the day # of the month. The part of the SELECT syntax I am struggling with is when the introduction message is to change mid month. The reason I am struggling with this is because I haven’t used ‘DATE’ for the column type. The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) This is a little bit of a tricky one, as you have to consider both start_month and end_month as special cases - so you need a three-part conditional, for the start month, the end month, and the months in between. Something like this: SELECT * FROM `introduction_messages` WHERE (month`start_month` AND month`end_month`) OR (month=`start_month AND day=`start_day`) OR (month=`end_month` AND day=`end_day`); Cheers! Mike -- Mike Ford, Electronic Information Developer, Libraries and Learning Innovation, Portland PD507, City Campus, Leeds Metropolitan University, Portland Way, LEEDS, LS1 3HE, United Kingdom E: m.f...@leedsmet.ac.uk T: +44 113 812 4730 To view the terms under which this email is distributed, please go to http://disclaimer.leedsmet.ac.uk/email.htm
Re: [PHP-DB] SELECT
Ron - Mike here is correct. I gave you a start, but it had a problem with it. Hope I didn't have you running around too much. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT
Dear Ron If only day is required u could add another day condition in the where clause e.g. month(current_date) between and day(current_date) between. i think u require something more than this. So could u pls explain your requirement in a little more detail say what would be the output of the query given by u. When would u consider the start date/month and when the end one. regds amit The difference between fiction and reality? Fiction has to make sense. On Mon, Oct 17, 2011 at 11:08 PM, Ron Piggott ron.pigg...@actsministries.org wrote: I need help creating a mySQL query that will select the correct introduction message for a website I am making. The way I have designed the table I can’t wrap my mind around the SELECT query that will deal with the day # of the month. The part of the SELECT syntax I am struggling with is when the introduction message is to change mid month. The reason I am struggling with this is because I haven’t used ‘DATE’ for the column type. The reason I didn’t use ‘DATE’ is because the same message will be displayed year after year, depending on the date range. What I am storing in the table is the start month # (1 to 12) and day # (1 to 31) and then the finishing month # (1 to 12) and the finishing day # (1 to 31) Table structure for table `introduction_messages` -- CREATE TABLE IF NOT EXISTS `introduction_messages` ( `reference` int(2) NOT NULL AUTO_INCREMENT, `start_month` int(2) NOT NULL, `start_day` int(2) NOT NULL, `end_month` int(2) NOT NULL, `end_day` int(2) NOT NULL, `theme` varchar(100) NOT NULL, `message` longtext NOT NULL, PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ; My query so far is: SELECT * FROM `introduction_messages` WHERE 11 BETWEEN `start_month` AND `end_month` 11 is for November. 2 rows have been selected: Row #1: `start_month` 9 `start_day` 16 `end_month` 11 `end_day` 15 Row #2: `start_month` 11 `start_day` 16 `end_month` 12 `end_day` 10 How do I modify the query to incorporate the day #? Ron www.TheVerseOfTheDay.info
Re: [PHP-DB] SELECT syntax
Or something like this?
SELECT * FROM `Bible_trivia` WHERE answer=`answer`;
Then match the results to trivia_answer_1 in php to see if correct.
if($trivia_answer_1 == $results) {
... do this
}
or a switch
switch ($results) {
case $trivia_answer_1:
... do this
case $trivia_answer_2
... do this
Best,
Karl
On Oct 12, 2011, at 11:04 PM, Amit Tandon wrote:
SELECT `trivia_answer_`answer`` FROM `Bible_trivia`
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 syntax
Heh,
Thanks Karthik. Not my post.. :)
But your solution looks dead on..
Here you go Ron. Try this one.
Best,
Karl
On Oct 13, 2011, at 2:42 AM, Karthik S wrote:
Try this,
select
CASE answer
when 1 then trivia_answer_1
when 2 then trivia_answer_2
when 3 then trivia_answer_3
when 4 then trivia_answer_4
END as trivia_answers
from bible_trivia_table
On Thu, Oct 13, 2011 at 1:02 PM, Karl DeSaulniers k...@designdrumm.com
wrote:
Or something like this?
SELECT * FROM `Bible_trivia` WHERE answer=`answer`;
Then match the results to trivia_answer_1 in php to see if correct.
if($trivia_answer_1 == $results) {
... do this
}
or a switch
switch ($results) {
case $trivia_answer_1:
... do this
case $trivia_answer_2
... do this
Best,
Karl
On Oct 12, 2011, at 11:04 PM, Amit Tandon wrote:
SELECT `trivia_answer_`answer`` FROM `Bible_trivia`
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
RE: [PHP-DB] SELECT syntax
Not terribly elegant, but this should work: SELECT `trivia_answer_1` AS `trivia_answer` FROM `Bible_trivia` WHERE `answer`=1 UNION SELECT `trivia_answer_2` AS `trivia_answer` FROM `Bible_trivia` WHERE `answer`=2 UNION SELECT `trivia_answer_3` AS `trivia_answer` FROM `Bible_trivia` WHERE `answer`=3 UNION SELECT `trivia_answer_4` AS `trivia_answer` FROM `Bible_trivia` WHERE `answer`=4; I have to say that it's likely that your design may not be the most optimal. What happens if you want 5 answers? Or 6? Toby -Original Message- From: Ron Piggott [mailto:ron.pigg...@actsministries.org] Sent: Wednesday, October 12, 2011 3:25 PM To: php-db@lists.php.net Subject: [PHP-DB] SELECT syntax In my Bible_Trivia table I have the columns `trivia_answer_1`, `trivia_answer_2`, `trivia_answer_3`, `trivia_answer_4`, `answer` `answer` is an integer always with a value of 1 to 4. Is there a way to use the value of `answer` to only select the correct trivia answer? This doesn’t work, but this is the idea I am trying to achieve: SELECT `trivia_answer_`answer`` FROM `Bible_trivia` Thanks in advance, Ron www.TheVerseOfTheDay.info -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT syntax
Hi In Oracle (and maybe others) you can use select case when answer=1 then trivia_answer_1 when answer=2 then trivia_answer_2 when answer=3 then trivia_answer_3 when answer=4 then trivia_answer_4 else null end answer from bible_trivia_table OR You can select all of them and process in PHP, should not be too hard to come up with a couple of lines of code to display only 1 variable based on the value of variable 5. Overhead should be pretty minimal as well You'll be writing something to display a value anyway Jack van Zanen - This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation On Thu, Oct 13, 2011 at 6:24 AM, Ron Piggott ron.pigg...@actsministries.org wrote: In my Bible_Trivia table I have the columns `trivia_answer_1`, `trivia_answer_2`, `trivia_answer_3`, `trivia_answer_4`, `answer` `answer` is an integer always with a value of 1 to 4. Is there a way to use the value of `answer` to only select the correct trivia answer? This doesn’t work, but this is the idea I am trying to achieve: SELECT `trivia_answer_`answer`` FROM `Bible_trivia` Thanks in advance, Ron www.TheVerseOfTheDay.info http://www.theverseoftheday.info/
Re: [PHP-DB] SELECT syntax
select casehttp://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html works in mysql also regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Oct 13, 2011 at 3:26 AM, Jack van Zanen j...@vanzanen.com wrote: Hi In Oracle (and maybe others) you can use select case when answer=1 then trivia_answer_1 when answer=2 then trivia_answer_2 when answer=3 then trivia_answer_3 when answer=4 then trivia_answer_4 else null end answer from bible_trivia_table OR You can select all of them and process in PHP, should not be too hard to come up with a couple of lines of code to display only 1 variable based on the value of variable 5. Overhead should be pretty minimal as well You'll be writing something to display a value anyway Jack van Zanen - This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation On Thu, Oct 13, 2011 at 6:24 AM, Ron Piggott ron.pigg...@actsministries.org wrote: In my Bible_Trivia table I have the columns `trivia_answer_1`, `trivia_answer_2`, `trivia_answer_3`, `trivia_answer_4`, `answer` `answer` is an integer always with a value of 1 to 4. Is there a way to use the value of `answer` to only select the correct trivia answer? This doesn’t work, but this is the idea I am trying to achieve: SELECT `trivia_answer_`answer`` FROM `Bible_trivia` Thanks in advance, Ron www.TheVerseOfTheDay.info http://www.theverseoftheday.info/
Re: [PHP-DB] SELECT syntax
another examplehttp://mysql-tips.blogspot.com/2005/04/mysql-select-case-example.html regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Oct 13, 2011 at 9:34 AM, Amit Tandon att...@gmail.com wrote: select casehttp://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html works in mysql also regds amit The difference between fiction and reality? Fiction has to make sense. On Thu, Oct 13, 2011 at 3:26 AM, Jack van Zanen j...@vanzanen.com wrote: Hi In Oracle (and maybe others) you can use select case when answer=1 then trivia_answer_1 when answer=2 then trivia_answer_2 when answer=3 then trivia_answer_3 when answer=4 then trivia_answer_4 else null end answer from bible_trivia_table OR You can select all of them and process in PHP, should not be too hard to come up with a couple of lines of code to display only 1 variable based on the value of variable 5. Overhead should be pretty minimal as well You'll be writing something to display a value anyway Jack van Zanen - This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation On Thu, Oct 13, 2011 at 6:24 AM, Ron Piggott ron.pigg...@actsministries.org wrote: In my Bible_Trivia table I have the columns `trivia_answer_1`, `trivia_answer_2`, `trivia_answer_3`, `trivia_answer_4`, `answer` `answer` is an integer always with a value of 1 to 4. Is there a way to use the value of `answer` to only select the correct trivia answer? This doesn’t work, but this is the idea I am trying to achieve: SELECT `trivia_answer_`answer`` FROM `Bible_trivia` Thanks in advance, Ron www.TheVerseOfTheDay.info http://www.theverseoftheday.info/
Re: [PHP-DB] SELECT online store discount %
Ron Have u thought of CASE (in SELECT)http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html. Remebber their is some syntactical difference in CASE for SELECT and CASE in procedures regds amit The difference between fiction and reality? Fiction has to make sense. On Sun, Aug 21, 2011 at 12:55 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am trying to write a database query that determine the customer loyalty discount for an online store. I am wondering if there is a way of doing this as 1 query, instead of multiple and using PHP to do the math? - I want to offer a 10% discount if the person is a subscriber SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = '$client_email' LIMIT 1 - I also want to offer a customer loyalty discount: 10% if this is a purchase within 4 months of the previous purchase, SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` = DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 - OR 5% if the most recent previous purchase is between 4 months and 1 year ago. SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 The discounts possibilities would be: - 20% (a subscriber with a purchase within the past 4 months) - 15% (a subscriber with a purchase between 4 months and a year ago) - 10% (for being a subscriber) - 10% (for a purchase made within the past 4 months) - 5% (for a purchase made between 4 months and a year ago) Is there a way to do this all within the context of 1 query? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] SELECT online store discount %
A variety of if’s and Greatest in conjunction to mySQL math works! SELECT ( ( GREATEST( IF( ( SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` = DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `buyer_email` = '$client_email' AND `paymentstatus` = 'Completed' LIMIT 1 ) , 10, 0 ) , IF( ( SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `buyer_email` = '$client_email' AND `paymentstatus` = 'Completed' LIMIT 1 ) , 5, 0 ) ) ) + ( IF( ( SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = '$client_email' LIMIT 1 ), 10, 0 ) ) ) AS discount_percentage The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info From: Amit Tandon Sent: Monday, August 22, 2011 5:45 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] SELECT online store discount % Ron Have u thought of CASE (in SELECT). Remebber their is some syntactical difference in CASE for SELECT and CASE in procedures regds amit The difference between fiction and reality? Fiction has to make sense. On Sun, Aug 21, 2011 at 12:55 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am trying to write a database query that determine the customer loyalty discount for an online store. I am wondering if there is a way of doing this as 1 query, instead of multiple and using PHP to do the math? - I want to offer a 10% discount if the person is a subscriber SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = '$client_email' LIMIT 1 - I also want to offer a customer loyalty discount: 10% if this is a purchase within 4 months of the previous purchase, SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` = DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 - OR 5% if the most recent previous purchase is between 4 months and 1 year ago. SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 The discounts possibilities would be: - 20% (a subscriber with a purchase within the past 4 months) - 15% (a subscriber with a purchase between 4 months and a year ago) - 10% (for being a subscriber) - 10% (for a purchase made within the past 4 months) - 5% (for a purchase made between 4 months and a year ago) Is there a way to do this all within the context of 1 query? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] SELECT WHERE length of content question
Have a look at this: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_char-length On Thu, Mar 10, 2011 at 9:49 AM, Ron Piggott ron.pigg...@actsministries.org wrote: Is there a command in mySQL that would allow me to SELECT the rows where the `fax` column is more than 11 characters long? OR Do I need to use PHP to assess this? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] SELECT WHERE length of content question
On Wed, Mar 9, 2011 at 17:49, Ron Piggott ron.pigg...@actsministries.org wrote: Is there a command in mySQL that would allow me to SELECT the rows where the `fax` column is more than 11 characters long? There is. SELECT * FROM tblName WHERE CHAR_LENGTH(fax) = 11; (Presuming you meant greater than or equal to eleven, as in an invalid US/Canadian phone number.) -- /Daniel P. Brown Network Infrastructure Manager http://www.php.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT / ORDER BY
On 11 September 2010 07:47, Ron Piggott ron.pigg...@actsministries.org wrote: I wrote the query below to determine the 10 most popular words used: SELECT COUNT( `bible_concordance_usage`.`reference` ) AS word_usage, `bible_concordance_words`.`reference` , `bible_concordance_words`.`word` FROM `bible_concordance_usage` INNER JOIN `bible_concordance_words` ON `bible_concordance_usage`.`bible_concordance_words_reference` = `bible_concordance_words`.`reference` GROUP BY `bible_concordance_usage`.`bible_concordance_words_reference` ORDER BY word_usage DESC, `bible_concordance_words`.`word` ASC, `bible_concordance_usage`.`date_accessed` DESC LIMIT 10 What I don't like about the results is that if 8 words have been used 5 times then the remaining 2 words the query chooses are from words used 4 times. The results are in alphabetical order A to Z for the words used 5 times and back to A to Z for words used 4 times. My question: is there a way to make my query above into a sub query and have a main query order the results of the sub query ORDER BY words ASC so all the words displayed are in alphabetical order? Ron Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Can't you just swap the order of the first 2 columns in the ORDER BY clause? e.g. ORDER BY Name, Age will list all the names alphabetically and where there are more than 1 occurrence of a name in the result set, these would be ordered by age. versus. ORDER BY Age, Name will list all the babies in alphabetical order, followed by the toddlers, children, teenagers, adults, grannies and granddads. So, ORDER BY `bible_concordance_words`.`word` ASC, word_usage DESC,`bible_concordance_usage`.`date_accessed` DESC And as you are grouping by `bible_concordance_words`.`word`, there really is never going to be a duplicate. So, there is no need to order by anything else. So, ORDER BY `bible_concordance_words`.`word` ASC is all you should need. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT / ORDER BY
Hello, This may help. http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html Best, Karl On Sep 11, 2010, at 1:47 AM, Ron Piggott wrote: I wrote the query below to determine the 10 most popular words used: SELECT COUNT( `bible_concordance_usage`.`reference` ) AS word_usage, `bible_concordance_words`.`reference` , `bible_concordance_words`.`word` FROM `bible_concordance_usage` INNER JOIN `bible_concordance_words` ON `bible_concordance_usage`.`bible_concordance_words_reference` = `bible_concordance_words`.`reference` GROUP BY `bible_concordance_usage`.`bible_concordance_words_reference` ORDER BY word_usage DESC, `bible_concordance_words`.`word` ASC, `bible_concordance_usage`.`date_accessed` DESC LIMIT 10 What I don't like about the results is that if 8 words have been used 5 times then the remaining 2 words the query chooses are from words used 4 times. The results are in alphabetical order A to Z for the words used 5 times and back to A to Z for words used 4 times. My question: is there a way to make my query above into a sub query and have a main query order the results of the sub query ORDER BY words ASC so all the words displayed are in alphabetical order? Ron Ron -- 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 the specific user data from the database
Then each record needs to have a user filed where their is stored. Then your access query just adds an additional filter to check this value Select * from data_table where user = $user Bastien Sent from my iPod On Sep 5, 2010, at 7:21, nagendra prasad nagendra802...@gmail.com wrote: Hi Experts, I have a mysql database. What I want is that when a user login he can able to see his entries only, so that he can delete, add or edit his entries only. I have 2 different tables one for user details and another for actual entries. Please help me. Best, Guru. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select the specific user data from the database
PS: Want to check the username from a table and the password from another table. Is it possible ??
Re: [PHP-DB] Select the specific user data from the database
I would suggest that you keep authorization separate from data access Bastien Sent from my iPod On Sep 5, 2010, at 9:19, nagendra prasad nagendra802...@gmail.com wrote: PS: Want to check the username from a table and the password from another table. Is it possible ?? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT with ' in search term
On 13/08/10 13:26, Ron Piggott wrote: If the variable $segment has an ' in it the $query won't work because of having 3 ' 's. Should I be using: $segment = mysql_real_escape_string($segment); before querying the database? Use it in your query. Don't use it anywhere else. Your code may use it after the query and cause weird stuff, ala: $segment = 'this is my segment'; $segment = mysql_real_escape_string($segment); $query = ; echo 'My segment name is ' . htmlspecialchars($segment); So it'll become: $query=SELECT `reference` FROM `bible_concordance_words` WHERE `word` = ' . mysql_real_escape_string($segment) . ' LIMIT 1; Please note: $segment wasn't submitted through a form. Doesn't matter. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT LIKE with % and without %
Eleonora De Marinis eleonora.demari...@garr.it, haber iletisinde sunlari yazdi:49fe92d0.2060...@garr.it... $sql = SELECT * FROM table WHERE ID ='$_GET[id]' AND title LIKE '%$_GET[word]%'; Original Message Subject: [PHP-DB] SELECT LIKE with %' and without %' From: Emiliano Boragina emiliano.borag...@gmail.com To: php-db@lists.php.net Date: 05/03/2009 01:43 AM Hello. I am using this: $sql = SELECT * FROM table WHERE ID LIKE '%$_GET[id]%' AND title LIKE '%$_GET[word]%'; But I want exactlu ID, not one part of many possibles Ids in the DB. How can I do that? Thanks + _ // Emiliano Boragina _ // Diseño Comunicación // + _ // emiliano.borag...@gmail.com / // 15 40 58 60 02 /// + _ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT LIKE with % and without %
$sql = SELECT * FROM table WHERE ID ='$_GET[id]' AND title LIKE '%$_GET[word]%'; Original Message Subject: [PHP-DB] SELECT LIKE with %' and without %' From: Emiliano Boragina emiliano.borag...@gmail.com To: php-db@lists.php.net Date: 05/03/2009 01:43 AM Hello. I am using this: $sql = SELECT * FROM table WHERE ID LIKE '%$_GET[id]%' AND title LIKE '%$_GET[word]%'; But I want exactlu ID, not one part of many possibles Ids in the DB. How can I do that? Thanks + _ // Emiliano Boragina _ // Diseño Comunicación // + _ // emiliano.borag...@gmail.com / // 15 40 58 60 02 /// + _
Re: [PHP-DB] SELECT LIKE with % and without %
On May 2, 2009, at 6:43 PM, Emiliano Boragina wrote: Hello. I am using this: $sql = SELECT * FROM table WHERE ID LIKE '%$_GET[id]%' AND title LIKE '%$_GET[word]%'; But I want exactlu ID, not one part of many possibles Ids in the DB. How can I do that? PLEASE tell me your cleaning that input... http://php.net/mysql_real_escape_string And to answer your question: ?php $id = mysql_real_escape_string ($_GET['id']); $word = mysql_real_escape_string ($_GET['word']); $sql = SELECT * FROM `table` WHERE `ID` = '$id' AND `title` = '$word'; ? Read more on MySQL's LIKE functionality. Google can help you. HTH, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT LIKE with % and without %
Emiliano Boragina wrote: Hello. I am using this: $sql = SELECT * FROM table WHERE ID LIKE '%$_GET[id]%' AND title LIKE '%$_GET[word]%'; This doesnt work? $sql = SELECT * FROM table WHERE ID='some_id' AND title='some_title'; -- Extra details: OSS:Gentoo Linux profile:x86 Hardware:msi geforce 8600GT asus p5k-se location:/home/muhsin language(s):C/C++,VB,VHDL,bash,PHP,SQL,HTML,CSS Typo:40WPM url:http://mambo-tech.net url:http://blog.mambo-tech.net -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select query with Forein key Relation
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 [EMAIL PROTECTED]
To: Nasreen Laghari [EMAIL PROTECTED]
Cc: php-db@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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query from two tables
ministry_directory_listing_categories.ministry_directory_category_reference = 10 AND ministry_directory_listing_categories.ministry_directory_category_reference = 11 Can a record really have a reference for two different id's like this? ie can it be both '10' and '11' at the same time? What's actually in the table for ministry_directory_listing_categories for this record? For long table names, I'd also suggest using a table alias to make it easier to read/write: select * from table1 as a inner join table2 as b using(id) where a.field_name='1' and b.fieldname='5'; saves you having to write out 'ministry_directory_listing_categories' and 'ministry_directory'. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query from two tables
Two different rows Chris. reference ministry_directory_entry ministry_directory_category_reference 13 1 10 14 1 11 What I am trying to do is allow the user to make a more specific search. Ron On Mon, 2008-03-10 at 10:37 +1100, Chris wrote: ministry_directory_listing_categories.ministry_directory_category_reference = 10 AND ministry_directory_listing_categories.ministry_directory_category_reference = 11 Can a record really have a reference for two different id's like this? ie can it be both '10' and '11' at the same time? What's actually in the table for ministry_directory_listing_categories for this record? For long table names, I'd also suggest using a table alias to make it easier to read/write: select * from table1 as a inner join table2 as b using(id) where a.field_name='1' and b.fieldname='5'; saves you having to write out 'ministry_directory_listing_categories' and 'ministry_directory'. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query from two tables
Ron Piggott wrote: Two different rows Chris. That's the problem then. Your query is saying get records with category_reference of 10 and it has to have category_reference of 11 as well. No such rows exist. Maybe that should be an 'or' or 'in' (same thing). ... ministry_directory_listing_categories.ministry_directory_category_reference in (10,11) ... ; so it can get both records 13 14. reference ministry_directory_entry ministry_directory_category_reference 13 1 10 14 1 11 -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query from two tables
I think what you mean to do is use IN(). And I would suggest table aliases. So it could look like this: SELECT * FROM ministry_directory md INNER JOIN ministry_directory_listing_categories mdlc ON md.entry = mdlc.ministry_directory_entry WHERE md.listing_type = 2 AND mdlc.ministry_directory_category_reference IN (10, 11) ORDER BY ministry_directory.name ASC Regards, Bruce Ron Piggott [EMAIL PROTECTED] 10/03/2008 10:33:13 a.m. I am wondering what is wrong with this syntax? SELECT * FROM ministry_directory INNER JOIN ministry_directory_listing_categories ON ministry_directory.entry = ministry_directory_listing_categories.ministry_directory_entry WHERE ministry_directory.listing_type = 2 AND ministry_directory_listing_categories.ministry_directory_category_reference = 10 AND ministry_directory_listing_categories.ministry_directory_category_reference = 11 ORDER BY ministry_directory.name ASC It produces 0 results. In reality there is presently 1 record that should be found that has listing_type = 2 and ministry_directory_category_reference 10 and 11 in the ministry_directory_listing_categories table The table ministry_directory has the main contact information. entry is auto_increment; listing_type is an INT(1) column The table ministry_directory_listing_categories has 3 columns: reference which is auto_increment populated; ministry_directory_entry which is the common field between both tables, showing what the record belongs to ministry_directory_category_reference which is the reference number to how the directory listing was inputted / categorized. (IE If the person who completed the form select 2 of the 10 possible categories 2 records were created.) Is there a different way to word my query so I will be able to retrieve the record with two rows in table ministry_directory_listing_categories and 1 row in ministry_directory ? Thanks for the help guys. Ron -- 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 query from two tables
Thanks On Mon, 2008-03-10 at 12:56 +1300, Bruce Cowin wrote: I think what you mean to do is use IN(). And I would suggest table aliases. So it could look like this: SELECT * FROM ministry_directory md INNER JOIN ministry_directory_listing_categories mdlc ON md.entry = mdlc.ministry_directory_entry WHERE md.listing_type = 2 AND mdlc.ministry_directory_category_reference IN (10, 11) ORDER BY ministry_directory.name ASC Regards, Bruce Ron Piggott [EMAIL PROTECTED] 10/03/2008 10:33:13 a.m. I am wondering what is wrong with this syntax? SELECT * FROM ministry_directory INNER JOIN ministry_directory_listing_categories ON ministry_directory.entry = ministry_directory_listing_categories.ministry_directory_entry WHERE ministry_directory.listing_type = 2 AND ministry_directory_listing_categories.ministry_directory_category_reference = 10 AND ministry_directory_listing_categories.ministry_directory_category_reference = 11 ORDER BY ministry_directory.name ASC It produces 0 results. In reality there is presently 1 record that should be found that has listing_type = 2 and ministry_directory_category_reference 10 and 11 in the ministry_directory_listing_categories table The table ministry_directory has the main contact information. entry is auto_increment; listing_type is an INT(1) column The table ministry_directory_listing_categories has 3 columns: reference which is auto_increment populated; ministry_directory_entry which is the common field between both tables, showing what the record belongs to ministry_directory_category_reference which is the reference number to how the directory listing was inputted / categorized. (IE If the person who completed the form select 2 of the 10 possible categories 2 records were created.) Is there a different way to word my query so I will be able to retrieve the record with two rows in table ministry_directory_listing_categories and 1 row in ministry_directory ? Thanks for the help guys. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query with multiple WHERE Clause
$query = mysql_query(SELECT * from gig WHERE gigName='$gig_name' OR WHERE gig_fdate='$sdate'); This one. I'd suggest you get a book to help you with the basics, something like this should do (first hit in amazon, haven't actually read this particular book): http://www.amazon.com/Learning-MySQL-Seyed-Saied-Tahaghoghi/dp/0596008643/ There's lots of stuff to learn in sql. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query with multiple WHERE Clause
In MySQL, both OR and || are valid logical or operators. You can only have one Where clause, thus your last example is correct. --GREG On Wed, Feb 27, 2008 at 6:44 PM, Nasreen Laghari [EMAIL PROTECTED] wrote: Hi All, Thank you for increasing my knowledge about PHP/MYSQL. I am creating a SEARCH, by only using one table. The search form is same as Inserting item (search has form of all fields in table ), difference is SEARCH page doesnt have validation . Therefore user can enter information in any of field. I would like to know how to write a SELECT query which has multiple where clause with OR operator. shall we write: $query = mysql_query(SELECT * from gig WHERE Name='$name' || WHERE gig_fdate='$sdate'); OR $query = mysql_query(SELECT * from gig WHERE gigName='$gig_name' OR WHERE gig_fdate='$sdate'); OR $query = mysql_query(SELECT * from gig WHERE gigName='$gig_name' || gig_fdate='$sdate'); Regards Nasreen Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
Re: [PHP-DB] SELECT query with multiple WHERE Clause
Greg Bowser wrote: In MySQL, both OR and || are valid logical or operators. You can only have one Where clause, thus your last example is correct. Though in postgresql and db2 (and some other dbs) || means concatenate so stick with using the word OR in this situation otherwise you'll run into portability issues if you ever needed to move to another db. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query with multiple WHERE Clause
On Wed, Feb 27, 2008 at 6:44 PM, Nasreen Laghari
[EMAIL PROTECTED] wrote:
I am creating a SEARCH, by only using one table. The search form is same as
Inserting item (search has form of all fields in table ), difference is
SEARCH page doesnt have validation . Therefore user can enter information in
any of field. I would like to know how to write a SELECT query which has
multiple where clause with OR operator.
SELECT * FROM tableName WHERE (colA LIKE '%value%' OR colB='1');
--- more ---
SELECT fieldA,fieldR,fieldT,fieldX FROM tableName WHERE
(colA='value' OR colB LIKE 'Hello%') AND colC='Active';
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query with multiple WHERE Clause
On 27 Feb 2008, at 23:44, Nasreen Laghari wrote: Thank you for increasing my knowledge about PHP/MYSQL. The question you ask below is basic SQL syntax. Please read the MySQL manual before asking here - answers at this level are all in there. http://mysql.com/doc Oh, and once you have it working try entering ';delete * from gig;select * from gig where Name=' (including quotes) into the gig_name form field. When you get over the loss of all your data go read about sanitising your input: http://php.net/mysql_real_escape_string -Stut -- http://stut.net/ I am creating a SEARCH, by only using one table. The search form is same as Inserting item (search has form of all fields in table ), difference is SEARCH page doesnt have validation . Therefore user can enter information in any of field. I would like to know how to write a SELECT query which has multiple where clause with OR operator. shall we write: $query = mysql_query(SELECT * from gig WHERE Name='$name' || WHERE gig_fdate='$sdate'); OR $query = mysql_query(SELECT * from gig WHERE gigName='$gig_name' OR WHERE gig_fdate='$sdate'); OR $query = mysql_query(SELECT * from gig WHERE gigName='$gig_name' || gig_fdate='$sdate'); Regards Nasreen Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query with multiple WHERE Clause
$query = mysql_query(SELECT * from gig WHERE gigName='$gig_name' or gig_fdate='$sdate'); You can not use more then one WHERE in your sql statement... And SQL accepts OR and AND.. -- Stephen Johnson c | eh The Lone Coder http://www.thelonecoder.com continuing the struggle against bad code http://www.fortheloveofgeeks.com I¹m a geek and I¹m OK! -- From: Nasreen Laghari [EMAIL PROTECTED] Date: Wed, 27 Feb 2008 15:44:23 -0800 (PST) To: php-db@lists.php.net Subject: [PHP-DB] SELECT query with multiple WHERE Clause $query = mysql_query(SELECT * from gig WHERE gigName='$gig_name' || gig_fdate='$sdate'); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT query with multiple WHERE Clause
$query = mysql_query(SELECT * FROM gig WHERE gigName='$gig_name' OR gig_fdate='$sdate'); You only use the WHERE clause once then use parenthesis, AND and OR to create the logical conditions. If you have access to the mysql server, maybe through phpMyAdmin or something, I'd highly recommend forming your SQL statements using that, then creating your PHP once you've perfected your SQL. SQL statements can be very powerful and sometimes dangerous and it's much easier to debug the SQL when you work with it by itself and not have to worry about any PHP issues too. Assuming your MySQL server is on another server, if you have a Windows machine you can use a program like WinSQL Lite or Navicat to connection to the MySQL server (if it allows remote connections). phpMyAdmin is probably the easiest option though. -TG - Original Message - From: Nasreen Laghari [EMAIL PROTECTED] To: php-db@lists.php.net Date: Wed, 27 Feb 2008 15:44:23 -0800 (PST) Subject: [PHP-DB] SELECT query with multiple WHERE Clause Hi All, Thank you for increasing my knowledge about PHP/MYSQL. I am creating a SEARCH, by only using one table. The search form is same as Inserting item (search has form of all fields in table ), difference is SEARCH page doesnt have validation . Therefore user can enter information in any of field. I would like to know how to write a SELECT query which has multiple where clause with OR operator. shall we write: $query = mysql_query(SELECT * from gig WHERE Name='$name' || WHERE gig_fdate='$sdate'); OR $query = mysql_query(SELECT * from gig WHERE gigName='$gig_name' OR WHERE gig_fdate='$sdate'); OR $query = mysql_query(SELECT * from gig WHERE gigName='$gig_name' || gig_fdate='$sdate'); Regards Nasreen -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select...
Hi First off, please create your own thread, do not reply to someone else's and change the subject. I'm having kind of trouble to get done this: Select data from a table, except those data already in a second table. Actually, if there is a rowid in table2, I wont get it from table1, rowid is the key that relates both tables. I just can't express this with a SQL statement!! idequipomed is the key that relates both tables!! So, if idequipomed is already in Table2, I shouldn't get it from Table1. Any suggestions? You need to do a join between the two tables using the common column to make the connection. This should get you started SELECT * FROM Table1 RIGHT JOIN Table2 USING (idequipomed) WHERE Table1.idequipomed IS NULL -- Niel Archer -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select...
Original Message Subject: [PHP-DB] Select... From: Miguel Guirao [EMAIL PROTECTED] To: php-db@lists.php.net Date: 15.1.2008 4:44 Hello List, I'm having kind of trouble to get done this: Select data from a table, except those data already in a second table. Actually, if there is a rowid in table2, I wont get it from table1, rowid is the key that relates both tables. I just can't express this with a SQL statement!! idequipomed is the key that relates both tables!! So, if idequipomed is already in Table2, I shouldn't get it from Table1. Any suggestions? Many ways to do this. Choose the solution that gives you the best performance. Solution 1: SELECT t2.idequipomed FROM table2 t2 WHERE NOT EXISTS ( SELECT 1 FROM table1 WHERE table1.idequipomed = t2.idequipomed ) Solution 2: SELECT idequipomed FROM table2 WHERE idequipomed NOT IN (SELECT idequipomed FROM table1) Solution 3: SELECT table2.idequipomed FROM table2 LEFT JOIN table1 ON table1.idequipomed = table2.idequipomed WHERE table1.idequipomed IS NULL -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] ? SELECT TABLE Command
Please fix your reply-to address.
It
is an SQL query (probably MySQL, but perhaps SQLite or possibly even PGSQL or
mSQL):
The line after $query should tell you what uses it ;)
$query=SELECT TABLE $tablename;;
if (mysql_query($query, $link)) {
echo($indent.The table, '$tablename', was successfully opened.br /\n);
}
To make things even stranger, it works fine in the original program that I put
it in (although what, if anything, it does is beyond me), but fails when I try
it in another program (yes, I took care of $tablename).
What's the exact query that's run? Maybe $tablename contains more than
just a table name.
Different mysql version? Maybe it was in an older version of mysql but
they removed it.
It doesn't work in mysql5 or mysql4.0, maybe it does in an older version
though.
Looks like you're trying to check that the mysql user you connected as
has access to that table.
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] ? SELECT TABLE Command
Chris wrote (in news:[EMAIL PROTECTED]):
It is an SQL query (probably MySQL, but perhaps SQLite or possibly even
PGSQL or mSQL):
The line after $query should tell you what uses it ;)
Sorry, I meant the book may have been about any of those; I was skimming through
a bunch of SQL books at that time and dont know which one I got it from.
$query=SELECT TABLE $tablename;;
if (mysql_query($query, $link)) {
echo($indent.The table, '$tablename', was successfully opened.br
/\n); }
To make things even stranger, it works fine in the original program that I
put it in (although what, if anything, it does is beyond me), but fails
when I try it in another program (yes, I took care of $tablename).
What's the exact query that's run? Maybe $tablename contains more than just a
table name.
Nope, its just
SELECT TABLE lyrics;
Different mysql version? Maybe it was in an older version of mysql but they
removed it.
It doesn't work in mysql5 or mysql4.0, maybe it does in an older version
though.
Looks like you're trying to check that the mysql user you connected as has
access to that table.
It really feels like a command I may have used in the SQLite analyzer or
something. However, Im sure I copied it from an example script in a book. I
ve put holds on all the books on PHP and (My)SQL at the library and will check
them all.
--
Mike W.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SELECT string
Businesses
--
id
businessName
isChristianBookstore
isGift
isHomeDecor
isSkinCareAndCosmetics
isThriftStore
CREATE TABLE `Businesses` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`businessName` VARCHAR( 64 ) NOT NULL ,
`isChristianBookstore` TINYINT( 1 ) NOT NULL ,
`isGift` TINYINT( 1 ) NOT NULL ,
`isHomeDecor` TINYINT( 1 ) NOT NULL ,
`isSkinCareAndCosmetics` TINYINT( 1 ) NOT NULL ,
`isThriftStore` TINYINT( 1 ) NOT NULL ,
PRIMARY KEY ( `id` )
) TYPE = MYISAM ;
?php
$DbFieldByFormFieldMap = array(
'category_1' = 'isChristianBookstore',
'category_42' = 'isGift',
'category_44' = 'isHomeDecor',
'category_43' = 'isSkinCareAndCosmetics',
'category_17' = 'isThriftStore'
);
$sql = SELECT `id`, `businessName` FROM `Businesses` WHERE ;
foreach($_POST as $formField = $formValue){
if(substr($formField, 0, 9) == category_ substr($formValue, -1)
== a){//Handle any Not Selected
$sql .= ` . $DbFieldByFormFieldMap[$formField] . ` = '0'
AND ;
}
if(substr($formField, 0, 9) == category_ substr($formValue, -1)
== b){//Handle any Must Include
$sql .= ` . $DbFieldByFormFieldMap[$formField] . ` = '1'
AND ;
}
}
if(substr($sql, -4) == AND ){
$sql = substr($sql, 0, -4);
}else{
$sql .= 1;
}
echo SQL:$sql;
?
It was getting a tad complicated with the Could Include using OR and
testing for the end of the existing $sql string, but the Could Include's
just need to be omitted from the SQL query altogether.
Regards,
Dwight
God Bless!
-Original Message-
From: Ron Piggott [mailto:[EMAIL PROTECTED]
Sent: Monday, April 23, 2007 11:31 PM
To: PHP DB
Subject: [PHP-DB] SELECT string
I am looking for help to write a SELECT syntax to help me process a
directory searching query tool I am developing.
If you start at
http://www.actsministrieschristianevangelism.org/ministrydirectory/ and
under 'Step 1:' click Business a form is displayed.
My question is how would you generate the SELECT syntax for the search
results Could Include a given category and Must Include a given
category based on what the user has inputted through this form?
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT string
cmiiw.. since i don't the visual what u said bellow - Original Message - From: Ron Piggott [EMAIL PROTECTED] To: PHP DB php-db@lists.php.net Sent: Tuesday, April 24, 2007 11:31 AM Subject: [PHP-DB] SELECT string I am looking for help to write a SELECT syntax to help me process a directory searching query tool I am developing. you have a dir like this?? root -include -main -body --admin --user u want to search a file inside the dir? why don't you create a function that read inside the dir and return query for insert as database 1. read all file inside 2. create an insert query 3. refresh the query (repair the table?) and then.. u can use select but target it to the database not the directory If you start at http://www.actsministrieschristianevangelism.org/ministrydirectory/ and under 'Step 1:' click Business a form is displayed. My question is how would you generate the SELECT syntax for the search results Could Include a given category and Must Include a given must include?? require u mean? category based on what the user has inputted through this form? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT date query
i tend to take the approach of
$next_wed = date(Y-m-d, strtotime(next wednesday));
Bastien
From: Niel Archer [EMAIL PROTECTED]
Reply-To: php-db@lists.php.net
To: php-db@lists.php.net
Subject: Re: [PHP-DB] SELECT date query
Date: Sat, 07 Oct 2006 05:49:36 +0100
Hi Ron
I've made the assumption that if today is Wednesday, you still want next
Wednesday.
Try this:
$offset = array(3,2,1,7,6,5,4);
$date = explode(-, date(Y-n-j));
$ToDay = DayOfWeek($date[0], $date[1], $date[2]);
$NextWed = date(Y-n-j, time() + ($offset[$ToDay] * 24 * 60 * 60));
// Returns a digit in range 0-6. 0 = Sunday, 6 = Saturday
function DayOfWeek($Year, $Month, $Day)
{
$t = array(0, 3, 2, 5, 0, 3, 5, 1, 4, 6, 2, 4);
$Year -= $Month 3;
return ($Year + ($Year / 4) - ($Year / 100) + ($Year / 400) +
$t[$Month - 1] + $Day) % 7;
}
Niel
--
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 date query
Hi!
You can make this easier with date('w').
$correction = array(3, 2, 1, 7, 6, 5, 4);
list($year, $month, $day, $dayOfWeek) = explode('|', date('Y|m|d|w'));
echo date (Y.m.d, mktime
(0,0,0,$month,$day+$correction[$dayOfWeek],$year));
Regards,
Felhő
Niel Archer wrote:
Hi Ron
I've made the assumption that if today is Wednesday, you still want next
Wednesday.
Try this:
$offset = array(3,2,1,7,6,5,4);
$date = explode(-, date(Y-n-j));
$ToDay = DayOfWeek($date[0], $date[1], $date[2]);
$NextWed = date(Y-n-j, time() + ($offset[$ToDay] * 24 * 60 * 60));
// Returns a digit in range 0-6. 0 = Sunday, 6 = Saturday
function DayOfWeek($Year, $Month, $Day)
{
$t = array(0, 3, 2, 5, 0, 3, 5, 1, 4, 6, 2, 4);
$Year -= $Month 3;
return ($Year + ($Year / 4) - ($Year / 100) + ($Year / 400) +
$t[$Month - 1] + $Day) % 7;
}
Niel
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT date query
Hi
You can make this easier with date('w').
Doh, that'll teach me to code at 5 am. I knew there was a better way,
but couldn't think of it, the sound of my bed calling was too
distracting.
Niel
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT date query
Hi Ron
I've made the assumption that if today is Wednesday, you still want next
Wednesday.
Try this:
$offset = array(3,2,1,7,6,5,4);
$date = explode(-, date(Y-n-j));
$ToDay = DayOfWeek($date[0], $date[1], $date[2]);
$NextWed = date(Y-n-j, time() + ($offset[$ToDay] * 24 * 60 * 60));
// Returns a digit in range 0-6. 0 = Sunday, 6 = Saturday
function DayOfWeek($Year, $Month, $Day)
{
$t = array(0, 3, 2, 5, 0, 3, 5, 1, 4, 6, 2, 4);
$Year -= $Month 3;
return ($Year + ($Year / 4) - ($Year / 100) + ($Year / 400) +
$t[$Month - 1] + $Day) % 7;
}
Niel
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select distinct field won't return distinct value
I have a friend called GROUP_CONCAT, he may know what you want but he's only available since MySQL 4.1 2006/6/7, Blanton, Bob [EMAIL PROTECTED]: It is a Sybase vendor function but I was wondering if mysql had something comparable. I don't see anything in the manual. Maybe the subquery is the only way to go. -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 8:50 AM To: Blanton, Bob Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Select distinct field won't return distinct value Blanton, Bob wrote: I'm just learning MySQL so don't know all the syntax. There is a LIST function in Sybase Adaptive Server Anywhere which would do that. Is there an equivalent function in MySQL? Query: SELECT distinct niin, list(serial_number) FROM fmds.maintenance_equipment group by niin order by niin Output: niin list(serial_number) 000213909 B71-11649,B71-11657,B71-11650 000473750 BAF-3750-0001,BAF-3750-0002,BAF-3750-0003 000929062 2341 001139768 2207 Pretty sure that's a sybase specific function. Nothing like that exists in mysql or postgresql. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Select distinct field won't return distinct value
google 'cross tab queries'...there is a php/mysql example (sorry travelling
and don't have the link) on who to create a cross table query which is what
you are looking for
Bastien
From: Blanton, Bob [EMAIL PROTECTED]
To:
[EMAIL PROTECTED],php-db@lists.php.net,[EMAIL PROTECTED]
Subject: RE: [PHP-DB] Select distinct field won't return distinct value
Date: Tue, 6 Jun 2006 23:44:22 -0400
I'm just learning MySQL so don't know all the syntax. There is a LIST
function in Sybase Adaptive Server Anywhere which would do that. Is
there an equivalent function in MySQL?
Query:
SELECT distinct niin, list(serial_number) FROM
fmds.maintenance_equipment
group by niin
order by niin
Output:
niinlist(serial_number)
000213909 B71-11649,B71-11657,B71-11650
000473750 BAF-3750-0001,BAF-3750-0002,BAF-3750-0003
000929062 2341
001139768 2207
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 06, 2006 7:48 PM
To: php-db@lists.php.net
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Select distinct field won't return distinct value
Correct me if I'm wrong, but it sounds like you have something like
this:
123 Joe
124 Joe
125 Sue
126 John
127 Joe
128 Frank
129 Sue
And you want to output something like:
Joe 123, 124, 127
Sue 125, 129
John 126
Frank 128
But what you're getting is:
Joe 123
Joe 124
..etc
You have two ways you can solve this:
1. Do two SQL queries:
SELECT DISTINCT cus_name FROM customers
while ($result) { // forgive the pseudo-code
SELECT cus_id FROM customers WHERE cus_name = $result['cus_name']
while ($result2) {
echo $output;
}
}
Or..
2. Collect data into an array and process 'distinctness' on output
SELECT cus_name, cus_id FROM customers
while ($result) {
$cus_arr[$cus_name][] = $cus_id;
}
foreach ($cus_arr as $cus_name = $cus_idarr) {
echo $cus_name as ids: . implode(, , $cusidarr) . br\n;
}
There may be some tricky ways in SQL to get the data the way you want
it, but ultimately it's not worth the bending over backwards for (do I
remember right that you can do it with crosstab queries? don't even
know if MySQL will do those properly). Easier just to do it with one
of the methods above.
Good luck!
-TG
= = = Original message = = =
I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan
select
distinct customer name and id from the customer table. one customer may
have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so
that
I can print customer name and customer id once.
here is algorithm;
select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do
echoa href=\page?cus=cus_id\costomer name/abr /;
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select distinct field won't return distinct value
Perhaps you should fix your data model...
but with your current set up, try:
select cus_name, cus_id from customers group by cus_name order by
cus_name asc
-Brad
Mohamed Yusuf wrote:
I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan
select
distinct customer name and id from the customer table. one customer
may have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so
that
I can print customer name and customer id once.
here is algorithm;
select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do{
echoa href=\page?cus=cus_id\costomer name/abr /;
}
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select distinct field won't return distinct value
Correct me if I'm wrong, but it sounds like you have something like this:
123 Joe
124 Joe
125 Sue
126 John
127 Joe
128 Frank
129 Sue
And you want to output something like:
Joe 123, 124, 127
Sue 125, 129
John 126
Frank 128
But what you're getting is:
Joe 123
Joe 124
..etc
You have two ways you can solve this:
1. Do two SQL queries:
SELECT DISTINCT cus_name FROM customers
while ($result) { // forgive the pseudo-code
SELECT cus_id FROM customers WHERE cus_name = $result['cus_name']
while ($result2) {
echo $output;
}
}
Or..
2. Collect data into an array and process 'distinctness' on output
SELECT cus_name, cus_id FROM customers
while ($result) {
$cus_arr[$cus_name][] = $cus_id;
}
foreach ($cus_arr as $cus_name = $cus_idarr) {
echo $cus_name as ids: . implode(, , $cusidarr) . br\n;
}
There may be some tricky ways in SQL to get the data the way you want it, but
ultimately it's not worth the bending over backwards for (do I remember right
that you can do it with crosstab queries? don't even know if MySQL will do
those properly). Easier just to do it with one of the methods above.
Good luck!
-TG
= = = Original message = = =
I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan select
distinct customer name and id from the customer table. one customer may have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so that
I can print customer name and customer id once.
here is algorithm;
select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do
echoa href=\page?cus=cus_id\costomer name/abr /;
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select distinct field won't return distinct value
I thank you all. problem solved using two queries as TQ mentioned.
On 6/6/06, [EMAIL PROTECTED] [EMAIL PROTECTED]
wrote:
Correct me if I'm wrong, but it sounds like you have something like this:
123 Joe
124 Joe
125 Sue
126 John
127 Joe
128 Frank
129 Sue
And you want to output something like:
Joe 123, 124, 127
Sue 125, 129
John 126
Frank 128
But what you're getting is:
Joe 123
Joe 124
..etc
You have two ways you can solve this:
1. Do two SQL queries:
SELECT DISTINCT cus_name FROM customers
while ($result) { // forgive the pseudo-code
SELECT cus_id FROM customers WHERE cus_name = $result['cus_name']
while ($result2) {
echo $output;
}
}
Or..
2. Collect data into an array and process 'distinctness' on output
SELECT cus_name, cus_id FROM customers
while ($result) {
$cus_arr[$cus_name][] = $cus_id;
}
foreach ($cus_arr as $cus_name = $cus_idarr) {
echo $cus_name as ids: . implode(, , $cusidarr) . br\n;
}
There may be some tricky ways in SQL to get the data the way you want it,
but ultimately it's not worth the bending over backwards for (do I remember
right that you can do it with crosstab queries? don't even know if MySQL
will do those properly). Easier just to do it with one of the methods
above.
Good luck!
-TG
= = = Original message = = =
I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan
select
distinct customer name and id from the customer table. one customer may
have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so
that
I can print customer name and customer id once.
here is algorithm;
select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do
echoa href=\page?cus=cus_id\costomer name/abr /;
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Select distinct field won't return distinct value
I'm just learning MySQL so don't know all the syntax. There is a LIST
function in Sybase Adaptive Server Anywhere which would do that. Is
there an equivalent function in MySQL?
Query:
SELECT distinct niin, list(serial_number) FROM
fmds.maintenance_equipment
group by niin
order by niin
Output:
niinlist(serial_number)
000213909 B71-11649,B71-11657,B71-11650
000473750 BAF-3750-0001,BAF-3750-0002,BAF-3750-0003
000929062 2341
001139768 2207
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 06, 2006 7:48 PM
To: php-db@lists.php.net
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Select distinct field won't return distinct value
Correct me if I'm wrong, but it sounds like you have something like
this:
123 Joe
124 Joe
125 Sue
126 John
127 Joe
128 Frank
129 Sue
And you want to output something like:
Joe 123, 124, 127
Sue 125, 129
John 126
Frank 128
But what you're getting is:
Joe 123
Joe 124
..etc
You have two ways you can solve this:
1. Do two SQL queries:
SELECT DISTINCT cus_name FROM customers
while ($result) { // forgive the pseudo-code
SELECT cus_id FROM customers WHERE cus_name = $result['cus_name']
while ($result2) {
echo $output;
}
}
Or..
2. Collect data into an array and process 'distinctness' on output
SELECT cus_name, cus_id FROM customers
while ($result) {
$cus_arr[$cus_name][] = $cus_id;
}
foreach ($cus_arr as $cus_name = $cus_idarr) {
echo $cus_name as ids: . implode(, , $cusidarr) . br\n;
}
There may be some tricky ways in SQL to get the data the way you want
it, but ultimately it's not worth the bending over backwards for (do I
remember right that you can do it with crosstab queries? don't even
know if MySQL will do those properly). Easier just to do it with one
of the methods above.
Good luck!
-TG
= = = Original message = = =
I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan
select
distinct customer name and id from the customer table. one customer may
have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so
that
I can print customer name and customer id once.
here is algorithm;
select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do
echoa href=\page?cus=cus_id\costomer name/abr /;
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.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 distinct field won't return distinct value
Blanton, Bob wrote: I'm just learning MySQL so don't know all the syntax. There is a LIST function in Sybase Adaptive Server Anywhere which would do that. Is there an equivalent function in MySQL? Query: SELECT distinct niin, list(serial_number) FROM fmds.maintenance_equipment group by niin order by niin Output: niinlist(serial_number) 000213909 B71-11649,B71-11657,B71-11650 000473750 BAF-3750-0001,BAF-3750-0002,BAF-3750-0003 000929062 2341 001139768 2207 Pretty sure that's a sybase specific function. Nothing like that exists in mysql or postgresql. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Select distinct field won't return distinct value
It is a Sybase vendor function but I was wondering if mysql had something comparable. I don't see anything in the manual. Maybe the subquery is the only way to go. -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 8:50 AM To: Blanton, Bob Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Select distinct field won't return distinct value Blanton, Bob wrote: I'm just learning MySQL so don't know all the syntax. There is a LIST function in Sybase Adaptive Server Anywhere which would do that. Is there an equivalent function in MySQL? Query: SELECT distinct niin, list(serial_number) FROM fmds.maintenance_equipment group by niin order by niin Output: niin list(serial_number) 000213909 B71-11649,B71-11657,B71-11650 000473750 BAF-3750-0001,BAF-3750-0002,BAF-3750-0003 000929062 2341 001139768 2207 Pretty sure that's a sybase specific function. Nothing like that exists in mysql or postgresql. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT
Possibility? WHERE (`date_created` = '$date_90_minutes_ago' AND `time_created` = '$time_90_minutes_ago') or (`date_created` '$date_90_minutes_ago' AND `time_created` '01:30:00') Ade Ron Piggott (PHP) wrote: Yesterday I asked how to get the date time 90 minutes ago and I received several responses. Thanks. I don't think this select statement is working correctly. (Correctly being what I am intending it to do) I took a look at the table this morning. One record remains that was created 2006-01-19 at 23:55:37. These are the values of date_created and time_created. The current values are approximately 2006-01-20 and 05:50:00 This is the select statement I am writing about: SELECT * FROM `table` WHERE `date_created` = '$date_90_minutes_ago' AND `time_created` = '$time_90_minutes_ago' Intellectually I know the problem: 05:50:00 is much earlier than 23:55:37 ... thus my AND is not allowing both conditions to exist together. Is there a way that I may modify this SELECT statement so the present conditions continue to exist and add a second part to the SELECT statement that if the time is 01:30:00 or higher that records from the previous day are selected? This continues to allow the 90 minute time frame for users logged into my web site ... I am not sure how you would add an OR function to the above without messing up what presently works :) (I am writing a SESSION function for my web site using mySQL and a cron. The select statement I quoted above is part of the cron.) Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SELECT
Convert both to unix timestamps...be much easier to wrok with both date and time then bastien From: Ron Piggott (PHP) [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: PHP DB php-db@lists.php.net Subject: [PHP-DB] SELECT Date: Fri, 20 Jan 2006 06:00:33 -0500 Yesterday I asked how to get the date time 90 minutes ago and I received several responses. Thanks. I don't think this select statement is working correctly. (Correctly being what I am intending it to do) I took a look at the table this morning. One record remains that was created 2006-01-19 at 23:55:37. These are the values of date_created and time_created. The current values are approximately 2006-01-20 and 05:50:00 This is the select statement I am writing about: SELECT * FROM `table` WHERE `date_created` = '$date_90_minutes_ago' AND `time_created` = '$time_90_minutes_ago' Intellectually I know the problem: 05:50:00 is much earlier than 23:55:37 ... thus my AND is not allowing both conditions to exist together. Is there a way that I may modify this SELECT statement so the present conditions continue to exist and add a second part to the SELECT statement that if the time is 01:30:00 or higher that records from the previous day are selected? This continues to allow the 90 minute time frame for users logged into my web site ... I am not sure how you would add an OR function to the above without messing up what presently works :) (I am writing a SESSION function for my web site using mySQL and a cron. The select statement I quoted above is part of the cron.) Ron -- 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 html table
On Sun, 2005-12-04 at 13:38 -0500, Ron Piggott (PHP) wrote:
I have two questions.
I would like to display the contents of my table with the first row
being grey in background and the next row being white and the third row
being grey, fourth being white, etc. I am not sure how to do this.
Cool. You must use CSS for this. Your PHP must dish out tr tags with
alternating CSS classes... this allows you to change colors later
without editing code. This done, now for dishing out tr tag with
alternating classes you can use a function built along the following
lines:
function showRow() {
static $row = 1;
print(tr class=\rCol$row\ Your HTML Row goes here. /tr);
if (2 == $row) {
$row--;
}
else {
$row++;
}
}
Secondly I only want the first 20 records to be displayed at a time and
then I want to create a NEXT link for the next 20 records (21-40) ...
any idea how you would use the SELECT command to do this?
This should be plain simple... Try reading more about SELECT. You need
to use the LIMIT clause with SELECT.
Have fun,
ah
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SELECT html table
There's a couple of ways to do this. For the row color you would set that
as you looped through the returned rows. Using the pear db class:
$count = 0;
echo table\n;
while($db-fetchInto($data)) {
$count++;
if($count % 2 == 0) {
$bgColor = background-color:#dcdcdc;
}
else {
$bgColor = background-color:transparent;
}
echo tr style='$dgColor'\n;
foreach($data as value) {
echo td . htmlentities($value) . /td\n;
}
echo /tr\n;
}
As for the limiting of rows, you would use the limit statement (assuming
you're using a DBMS that supports it).
Hopefully this helps,
Robbert van Andel
-Original Message-
From: Ron Piggott (PHP) [mailto:[EMAIL PROTECTED]
Sent: Sunday, December 04, 2005 10:39 AM
To: PHP DB
Subject: [PHP-DB] SELECT html table
I have two questions.
I would like to display the contents of my table with the first row
being grey in background and the next row being white and the third row
being grey, fourth being white, etc. I am not sure how to do this.
Secondly I only want the first 20 records to be displayed at a time and
then I want to create a NEXT link for the next 20 records (21-40) ...
any idea how you would use the SELECT command to do this?
Ron
--
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 Performance and INDEXing
Hey, this is my first question. So if you could just reply to say it reached the php-db list, that would be terrific. Of course, answering the questions would be awesome as well. I meant 'Analyze table' and 'the composite key field2 field3 would be unique' - Original Message - From: Dwight Altman [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Wednesday, August 10, 2005 4:23 PM Subject: [PHP-DB] SELECT Performance and INDEXing I have a MyISAM table holding images with field types bigint(20), mediumblob, varchar(255), blob and tinyint(3). The table has grown to over 800 MB and over 6,000 rows. In the past week, performance has been about 15-20 seconds to run the following select statement which pulls only 16 maximum rows: SQL1) SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = $aField2Value OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC Basically I always pull the first 2 images in the table via the primary key field1 and upto 14 additional images depending on a foreign key field2. field2 can have up to 14 repeated/duplicate entries. My working solution is that I have since split this into 2 select statements: SQL2) SELECT field1, field2, andUptoField10 FROM theTable WHERE field1 = 1 OR field1 = 2 ORDER BY field1 ASC SQL3) SELECT field1, field2, andUptoField10 FROM theTable WHERE field2 = $aField2Value ORDER BY field1 ASC and performance is back to instantaneous (as far as web responsiveness is concerned). Can someone explain why SQL1 took so long to run as compared with running SQL2 and SQL3 ? Before splitting the statements, I also tried from phpmyadmin (Check table, Aalyze table, Optimize table) and creating an INDEX on field2, but I noticed no performance increase. Before explicitly adding an INDEX, the space usage in phpmyadmin already showed Type:Index using several bytes. The table still has the INDEX I explicitly created. Can someone explain to me INDEXing ? I was thinking of field2 and field3 for an INDEX (since field3 holds a number from 1 - 14 and the composite key field1 field2 would be unique), but I seem to be home free already. I would just like to know why performance slowed and then why it improved with my solution. Regards. -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT Performance and INDEXing
I think no one answered it because it doesn't make a whole lot of sense. Breaking a condition out into a second SQL statement would force the DB to rescan the table, so it should take longer rather than shorter. There's nothing suggesting that it's doing an internal self-join or other time-consuming function off the bat, which might explain it. Granted a second query would benefit from caching from the first to some degree. Can you run EXPLAIN on the first statement and post the results? -Micah On Thursday 18 August 2005 1:02 pm, Dwight Altman wrote: Hey, this is my first question. So if you could just reply to say it reached the php-db list, that would be terrific. Of course, answering the questions would be awesome as well. I meant 'Analyze table' and 'the composite key field2 field3 would be unique' - Original Message - From: Dwight Altman [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Wednesday, August 10, 2005 4:23 PM Subject: [PHP-DB] SELECT Performance and INDEXing I have a MyISAM table holding images with field types bigint(20), mediumblob, varchar(255), blob and tinyint(3). The table has grown to over 800 MB and over 6,000 rows. In the past week, performance has been about 15-20 seconds to run the following select statement which pulls only 16 maximum rows: SQL1) SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = $aField2Value OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC Basically I always pull the first 2 images in the table via the primary key field1 and upto 14 additional images depending on a foreign key field2. field2 can have up to 14 repeated/duplicate entries. My working solution is that I have since split this into 2 select statements: SQL2) SELECT field1, field2, andUptoField10 FROM theTable WHERE field1 = 1 OR field1 = 2 ORDER BY field1 ASC SQL3) SELECT field1, field2, andUptoField10 FROM theTable WHERE field2 = $aField2Value ORDER BY field1 ASC and performance is back to instantaneous (as far as web responsiveness is concerned). Can someone explain why SQL1 took so long to run as compared with running SQL2 and SQL3 ? Before splitting the statements, I also tried from phpmyadmin (Check table, Aalyze table, Optimize table) and creating an INDEX on field2, but I noticed no performance increase. Before explicitly adding an INDEX, the space usage in phpmyadmin already showed Type:Index using several bytes. The table still has the INDEX I explicitly created. Can someone explain to me INDEXing ? I was thinking of field2 and field3 for an INDEX (since field3 holds a number from 1 - 14 and the composite key field1 field2 would be unique), but I seem to be home free already. I would just like to know why performance slowed and then why it improved with my solution. Regards. -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT Performance and INDEXing
Thanks for your reply. Here are the results of EXPLAIN: EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = 1 OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC idselect_typetabletypepossible_keyskeykey_len refrowsExtra 1 SIMPLE theTable ALL PRIMARY,field2 NULL NULL NULL 6400 Using where; Using filesort === Here are the results of EXPLAIN on the separate statements: EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field1 =1 OR field1 =2 ORDER BY field1 ASC id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE theTable range PRIMARY PRIMARY 8 NULL 2 Using where -- EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 =1 ORDER BY field1 ASC idselect_typetabletypepossible_keyskeykey_len refrowsExtra 1 SIMPLE theTable ref field2 field2 9 const 10 Using where; Using filesort - Original Message - From: Micah Stevens [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Thursday, August 18, 2005 9:49 AM Subject: Re: [PHP-DB] SELECT Performance and INDEXing I think no one answered it because it doesn't make a whole lot of sense. Breaking a condition out into a second SQL statement would force the DB to rescan the table, so it should take longer rather than shorter. There's nothing suggesting that it's doing an internal self-join or other time-consuming function off the bat, which might explain it. Granted a second query would benefit from caching from the first to some degree. Can you run EXPLAIN on the first statement and post the results? -Micah On Thursday 18 August 2005 1:02 pm, Dwight Altman wrote: Hey, this is my first question. So if you could just reply to say it reached the php-db list, that would be terrific. Of course, answering the questions would be awesome as well. I meant 'Analyze table' and 'the composite key field2 field3 would be unique' - Original Message - From: Dwight Altman [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Wednesday, August 10, 2005 4:23 PM Subject: [PHP-DB] SELECT Performance and INDEXing I have a MyISAM table holding images with field types bigint(20), mediumblob, varchar(255), blob and tinyint(3). The table has grown to over 800 MB and over 6,000 rows. In the past week, performance has been about 15-20 seconds to run the following select statement which pulls only 16 maximum rows: SQL1) SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = $aField2Value OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC Basically I always pull the first 2 images in the table via the primary key field1 and upto 14 additional images depending on a foreign key field2. field2 can have up to 14 repeated/duplicate entries. My working solution is that I have since split this into 2 select statements: SQL2) SELECT field1, field2, andUptoField10 FROM theTable WHERE field1 = 1 OR field1 = 2 ORDER BY field1 ASC SQL3) SELECT field1, field2, andUptoField10 FROM theTable WHERE field2 = $aField2Value ORDER BY field1 ASC and performance is back to instantaneous (as far as web responsiveness is concerned). Can someone explain why SQL1 took so long to run as compared with running SQL2 and SQL3 ? Before splitting the statements, I also tried from phpmyadmin (Check table, Aalyze table, Optimize table) and creating an INDEX on field2, but I noticed no performance increase. Before explicitly adding an INDEX, the space usage in phpmyadmin already showed Type:Index using several bytes. The table still has the INDEX I explicitly created. Can someone explain to me INDEXing ? I was thinking of field2 and field3 for an INDEX (since field3 holds a number from 1 - 14 and the composite key field1 field2 would be unique), but I seem to be home free already. I would just like to know why performance slowed and then why it improved with my solution. Regards. -- -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT Performance and INDEXing
Okay, well, I guess that EXPLAINS it.. (bad joke.. sorry.) Not being a huge expert on the inner working on MySQL, I'm at a loss to explain why this is happening exactly, but it's clear that MySQL is choosing to do a filesort over 6400 rows with the first statement. This is probably due to it's inability to index based on your where statement. The other statements can index out much smaller result sets, then order then and deliver (8 10 respectively) therefore accomplishing much faster. What I would do at this point, is play with reordering, or rewriting the SQL for the first statement to see if that makes a difference. I know if JOIN statements, the order of the WHERE condition can make a huge difference. Here's an example that assumes Field1 will never be less than 1. This may be an incorrect assumption, but it illustrates my point: SELECT field1, field2, andUpToField10 FROM theTable WHERE field1 3 OR field2 = 1 ORDER BY field1 ASC -or- SELECT field1, field2, andUpToField10 FROM theTable WHERE field1 = 1 OR field1 = 2 OR field2 = 1 ORDER BY field1 ASC (this is just subtly different, but I'd be curious if it affects the outcome) Also, add an index on field1 and field2 if you don't already, as that may help. (sometimes it doesn't, depending on data type) I hope that helps, -Micah On Thursday 18 August 2005 4:30 pm, Dwight Altman wrote: Thanks for your reply. Here are the results of EXPLAIN: EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = 1 OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC idselect_typetabletypepossible_keyskeykey_len refrowsExtra 1 SIMPLE theTable ALL PRIMARY,field2 NULL NULL NULL 6400 Using where; Using filesort === Here are the results of EXPLAIN on the separate statements: EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field1 =1 OR field1 =2 ORDER BY field1 ASC id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE theTable range PRIMARY PRIMARY 8 NULL 2 Using where --- --- EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 =1 ORDER BY field1 ASC idselect_typetabletypepossible_keyskeykey_len refrowsExtra 1 SIMPLE theTable ref field2 field2 9 const 10 Using where; Using filesort - Original Message - From: Micah Stevens [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Thursday, August 18, 2005 9:49 AM Subject: Re: [PHP-DB] SELECT Performance and INDEXing I think no one answered it because it doesn't make a whole lot of sense. Breaking a condition out into a second SQL statement would force the DB to rescan the table, so it should take longer rather than shorter. There's nothing suggesting that it's doing an internal self-join or other time-consuming function off the bat, which might explain it. Granted a second query would benefit from caching from the first to some degree. Can you run EXPLAIN on the first statement and post the results? -Micah On Thursday 18 August 2005 1:02 pm, Dwight Altman wrote: Hey, this is my first question. So if you could just reply to say it reached the php-db list, that would be terrific. Of course, answering the questions would be awesome as well. I meant 'Analyze table' and 'the composite key field2 field3 would be unique' - Original Message - From: Dwight Altman [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Wednesday, August 10, 2005 4:23 PM Subject: [PHP-DB] SELECT Performance and INDEXing I have a MyISAM table holding images with field types bigint(20), mediumblob, varchar(255), blob and tinyint(3). The table has grown to over 800 MB and over 6,000 rows. In the past week, performance has been about 15-20 seconds to run the following select statement which pulls only 16 maximum rows: SQL1) SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = $aField2Value OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC Basically I always pull the first 2 images in the table via the primary key field1 and upto 14 additional images depending on a foreign key field2. field2 can have up to 14 repeated/duplicate entries. My working solution is that I have since split this into 2 select statements: SQL2) SELECT field1, field2, andUptoField10 FROM theTable WHERE field1 = 1 OR field1 = 2 ORDER BY field1 ASC SQL3) SELECT field1, field2, andUptoField10 FROM theTable WHERE field2 = $aField2Value ORDER BY field1 ASC and performance is back to instantaneous (as far as web responsiveness is concerned). Can someone explain why SQL1 took so long to run as compared
Re: [PHP-DB] SELECT Performance and INDEXing
No change on either variation. I also tried WHERE field2 = 1 OR field1 3
as well as omitting the ORDER BY clause.
Regarding INDEX: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html If a
multiple-column index exists on col1 and col2, the appropriate rows can be
fetched directly. If separate single-column indexes exist on col1 and col2,
the optimizer tries to find the most restrictive index by deciding which
index finds fewer rows and using that index to fetch the rows.
From the docs, I would have thought that an INDEX on field2 is all I needed,
but perhaps I need an index on (field1, field2) since they are both in the
where clause?
I didn't add the INDEX on field1. Would you explain a little more about
them? field1 is already a PRIMARY key and listed in the Indexes box in
phpmyadmin as Type:PRIMARY. I did create an INDEX on field2 and it is
listed in the Indexes box in phpmyadmin as Type:INDEX. But apparently they
are both indexes. Also, do you mean create a separate index for field1 or
some kind of composite index {i.e. multiple-column index} on (field1,
field2)? (field2, field3) would actually be a unique composite key, so I
was wondering if some type of composite index on (field2, field3) would be
better. Then again, any combination with field1 would also be unique.
- Original Message -
From: Micah Stevens [EMAIL PROTECTED]
To: php-db@lists.php.net
Sent: Thursday, August 18, 2005 12:33 PM
Subject: Re: [PHP-DB] SELECT Performance and INDEXing
Okay, well, I guess that EXPLAINS it.. (bad joke.. sorry.)
Not being a huge expert on the inner working on MySQL, I'm at a loss to
explain why this is happening exactly, but it's clear that MySQL is
choosing
to do a filesort over 6400 rows with the first statement. This is probably
due to it's inability to index based on your where statement.
The other statements can index out much smaller result sets, then order
then
and deliver (8 10 respectively) therefore accomplishing much faster.
What I would do at this point, is play with reordering, or rewriting the
SQL
for the first statement to see if that makes a difference. I know if JOIN
statements, the order of the WHERE condition can make a huge difference.
Here's an example that assumes Field1 will never be less than 1. This may
be
an incorrect assumption, but it illustrates my point:
SELECT field1, field2, andUpToField10
FROM theTable
WHERE field1 3
OR field2 = 1
ORDER BY field1 ASC
-or-
SELECT field1, field2, andUpToField10
FROM theTable
WHERE field1 = 1
OR field1 = 2
OR field2 = 1
ORDER BY field1 ASC
(this is just subtly different, but I'd be curious if it affects the
outcome)
Also, add an index on field1 and field2 if you don't already, as that may
help. (sometimes it doesn't, depending on data type)
I hope that helps,
-Micah
On Thursday 18 August 2005 4:30 pm, Dwight Altman wrote:
Thanks for your reply. Here are the results of EXPLAIN:
EXPLAIN SELECT field1, field2, andUpToField10
FROM theTable
WHERE field2 = 1
OR field1 = 1
OR field1 = 2
ORDER BY field1 ASC
idselect_typetabletypepossible_keyskeykey_len
refrowsExtra
1 SIMPLE theTable ALL PRIMARY,field2 NULL NULL NULL 6400 Using
where; Using filesort
===
Here are the results of EXPLAIN on the separate statements:
EXPLAIN SELECT field1, field2, andUpToField10
FROM theTable
WHERE field1 =1
OR field1 =2
ORDER BY field1 ASC
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE theTable range PRIMARY PRIMARY 8 NULL 2 Using where
---
--- EXPLAIN SELECT field1, field2,
andUpToField10
FROM theTable
WHERE field2 =1
ORDER BY field1 ASC
idselect_typetabletypepossible_keyskeykey_len
refrowsExtra
1 SIMPLE theTable ref field2 field2 9 const 10 Using where; Using
filesort
- Original Message -
From: Micah Stevens [EMAIL PROTECTED]
To: php-db@lists.php.net
Sent: Thursday, August 18, 2005 9:49 AM
Subject: Re: [PHP-DB] SELECT Performance and INDEXing
I think no one answered it because it doesn't make a whole lot of
sense.
Breaking a condition out into a second SQL statement would force the DB
to rescan the table, so it should take longer rather than shorter.
There's nothing suggesting that it's doing an internal self-join or
other
time-consuming function off the bat, which might explain it.
Granted a second query would benefit from caching from the first to
some
degree.
Can you run EXPLAIN on the first statement and post the results?
-Micah
On Thursday 18 August 2005 1:02 pm, Dwight Altman wrote:
Hey, this is my first question. So if you could just reply to say it
reached the php-db list, that would be terrific. Of course, answering
the
questions would be awesome as well.
I
Re: [PHP-DB] SELECT Performance and INDEXing
I meant 'Analyze table' and 'the composite key field2 field3 would be unique' - Original Message - From: Dwight Altman [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Wednesday, August 10, 2005 4:23 PM Subject: [PHP-DB] SELECT Performance and INDEXing I have a MyISAM table holding images with field types bigint(20), mediumblob, varchar(255), blob and tinyint(3). The table has grown to over 800 MB and over 6,000 rows. In the past week, performance has been about 15-20 seconds to run the following select statement which pulls only 16 maximum rows: SQL1) SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = $aField2Value OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC Basically I always pull the first 2 images in the table via the primary key field1 and upto 14 additional images depending on a foreign key field2. field2 can have up to 14 repeated/duplicate entries. My working solution is that I have since split this into 2 select statements: SQL2) SELECT field1, field2, andUptoField10 FROM theTable WHERE field1 = 1 OR field1 = 2 ORDER BY field1 ASC SQL3) SELECT field1, field2, andUptoField10 FROM theTable WHERE field2 = $aField2Value ORDER BY field1 ASC and performance is back to instantaneous (as far as web responsiveness is concerned). Can someone explain why SQL1 took so long to run as compared with running SQL2 and SQL3 ? Before splitting the statements, I also tried from phpmyadmin (Check table, Aalyze table, Optimize table) and creating an INDEX on field2, but I noticed no performance increase. Before explicitly adding an INDEX, the space usage in phpmyadmin already showed Type:Index using several bytes. The table still has the INDEX I explicitly created. Can someone explain to me INDEXing ? I was thinking of field2 and field3 for an INDEX (since field3 holds a number from 1 - 14 and the composite key field1 field2 would be unique), but I seem to be home free already. I would just like to know why performance slowed and then why it improved with my solution. Regards. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] select * from table where column 'CONTAINS' more than one value (how?)
I have no problem creating a table, using a query from my dbase table news: SELECT * FROM table where column = VALUE. However, now that most of our articles have more than one column type (i.e. instead of just technology, the column can now contain technology, politics, local. You need to normalize your tables. Make a table called columntypes, then another called articlecolumntypes. If you have an article with ID 456, and it has column types of technology (ID 5), politics (ID 15), and local (ID 34), you tie the articles table to the columntypes table using the articlecolumntypes table: articleid columntypeid - 4565 45615 45634 --df -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] select * from table where column 'CONTAINS' more than one value (how?)
On Tuesday 21 June 2005 10:35 pm, Dan Fulbright wrote: I have no problem creating a table, using a query from my dbase table news: SELECT * FROM table where column = VALUE. However, now that most of our articles have more than one column type (i.e. instead of just technology, the column can now contain technology, politics, local. You need to normalize your tables. Make a table called columntypes, then another called articlecolumntypes. If you have an article with ID 456, and it has column types of technology (ID 5), politics (ID 15), and local (ID 34), you tie the articles table to the columntypes table using the articlecolumntypes table: Good point, this would be the 'Proper' way to do it. -Micah -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] select * from table where column 'CONTAINS' more than one value (ho
use full text searches http://dev.mysql.com/doc/mysql/en/fulltext-search.html bastien From: [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] select * from table where column 'CONTAINS' more than one value (how?) Date: Tue, 21 Jun 2005 20:49:12 EDT Hello all, I have no problem creating a table, using a query from my dbase table news: SELECT * FROM table where column = VALUE. However, now that most of our articles have more than one column type (i.e. instead of just technology, the column can now contain technology, politics, local. My question is now that I have more than one value, what format can I use for a contain command. EXAMPLE: articles needed local political articles: SELECT * FROM table where column contains politics, local. Does anyone know how to format the above command into a proper SQL query language? Thank you in advance. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] select * from table where column 'CONTAINS' more than one value (how?)
You can use LIKE and wildcards, which is faster than fulltext searches, which will provide you a lot of information you don't need. Do this: SELECT * FROM table where column LIKE %politics% OR column LIKE %local% to find if the field contains politics or local.. -Micah On Tuesday 21 June 2005 05:49 pm, [EMAIL PROTECTED] wrote: Hello all, I have no problem creating a table, using a query from my dbase table news: SELECT * FROM table where column = VALUE. However, now that most of our articles have more than one column type (i.e. instead of just technology, the column can now contain technology, politics, local. My question is now that I have more than one value, what format can I use for a contain command. EXAMPLE: articles needed local political articles: SELECT * FROM table where column contains politics, local. Does anyone know how to format the above command into a proper SQL query language? Thank you in advance. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Select
Hi, Instead of doing a SELECT *, specify the fields that you require. Allthough you may think the rows are exactly the same, one might be empty () and one null which is not the same. You are more likely to get the result that you need if you specify the fields: SELECT DISTINCT somedata, rev, andthis FROM rev ORDER BY rev By the way, having rev as a name for your table AND as a fieldname is confusing and not advisable. Rgds, Jos -Original Message- From: MIGUEL ANTONIO GUIRAO AGUILAR [mailto:[EMAIL PROTECTED] Sent: 17 May 2005 04:26 To: php-db@lists.php.net Subject: [PHP-DB] Select Hi!! I have this query in PHP: $items2 = mysql_query(SELECT DISTINCT * FROM rev ORDER BY rev, $link); I have three rows with the same data on it, and DISTINCT seems to be not working, since I got all the rows, any ideas of what is going wrong? -- MIGUEL GUIRAO AGUILERA Logistica R8 - Telcel -- 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
distinc needs a field select distinct(name) from table bastien From: MIGUEL ANTONIO GUIRAO AGUILAR [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Select Date: Mon, 16 May 2005 19:26:20 -0700 Hi!! I have this query in PHP: $items2 = mysql_query(SELECT DISTINCT * FROM rev ORDER BY rev, $link); I have three rows with the same data on it, and DISTINCT seems to be not working, since I got all the rows, any ideas of what is going wrong? -- MIGUEL GUIRAO AGUILERA Logistica R8 - Telcel -- 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 text from a text file
Hi, Thanks for answering the question. I'm going to make it write to a database rather than text file after searching google and coming accross common problems with text files. I've now just got to figure out how to mark the message as read in the database so it does not re-appear next time the user clicks update. I'm going to build a function that will put the message in the correct table using an if clause, is that the best method? Ed - Original Message - From: Andrew Kreps [EMAIL PROTECTED] To: Ed [EMAIL PROTECTED]; php-db@lists.php.net Sent: Thursday, January 06, 2005 6:55 PM Subject: Re: [PHP-DB] select text from a text file On Wed, 5 Jan 2005 11:58:59 -, Ed [EMAIL PROTECTED] wrote: Happy new year folks! The titlemight make this seem like an easy to answer question However here's the complicated bit (well for me anyway). In my text file that is written to by the users in a chatroom it looks like this: nickname||color||what the user is saying||user how can i make it so that if they have a private message when they press update it pulls the message from the text file and displays it in the frame but also deletes the text? You should be using a database for this, it makes things so much easier. That being said, here's one way to go about the text file version: Open the file, read through it line by line. As you read it, push the lines into an array. If you find a private message for the user, store that in a variable, and do not push it into the array. Finish reading the file. If there's a private message, you've got it in a variable, and you can overwrite the private message file with the array you've stored, which is all of the current private messages minus the one you're about to display. Please note, this does not scale at all, especially in the fast-paced world of chat rooms. You will likely end up with file locking problems if you proceed with the flat-file method. Also, how can i make it so that if in a drop down menu they select the word everybody it goes to a file called messages.txt and if they select user or user2 or user3 from the list it writes to private.txt is this at all possible? user and user2 etc arent hardcoded it's pulling the names from a list of online users. Are you talking about appending messages to a text file? In that case, you can have the dropdown submit with the message, and in the PHP code have a case for 'everybody' where it writes to messages.txt, and if it's not 'everybody', write it to private.txt with the username that was selected from the dropdown as part of the row. Does that answer your question? -- 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 text from a text file
Ed wrote: Hi, Thanks for answering the question. I'm going to make it write to a database rather than text file after searching google and coming accross common problems with text files. I've now just got to figure out how to mark the message as read in the database so it does not re-appear next time the user clicks update. TIMESTAMP? you can save in the session when a use last grabbed messages (clicked update) and only return newer messages (i.e. message have a 'created' timestamp) then update the timestamp in the session. there is a window in which it is possible to have a request inserts a new message in between another request's selecting of all new messages and updating the session timestamp value (in such cases the person at the client end of the second request will never see the new message insert in the first request) - to handle that you have to implement some kind of locking mechanism. good luck is all can say. (PHP implements a 'share nothing' architecture - not, perse, the easiest or most efficient type of system to build realtime multi-user environments in - AFAICT) I'm going to build a function that will put the message in the correct table using an if clause, is that the best method? your the best judge of that - heh if it works that's the main thing - making it work fast that's something to look at later... and then there is the issue of writing code that neat, tidy, well commented and readable 6 months down the line. Ed - Original Message - From: Andrew Kreps [EMAIL PROTECTED] To: Ed [EMAIL PROTECTED]; php-db@lists.php.net Sent: Thursday, January 06, 2005 6:55 PM Subject: Re: [PHP-DB] select text from a text file On Wed, 5 Jan 2005 11:58:59 -, Ed [EMAIL PROTECTED] wrote: Happy new year folks! The titlemight make this seem like an easy to answer question However here's the complicated bit (well for me anyway). In my text file that is written to by the users in a chatroom it looks like this: nickname||color||what the user is saying||user how can i make it so that if they have a private message when they press update it pulls the message from the text file and displays it in the frame but also deletes the text? You should be using a database for this, it makes things so much easier. That being said, here's one way to go about the text file version: Open the file, read through it line by line. As you read it, push the lines into an array. If you find a private message for the user, store that in a variable, and do not push it into the array. Finish reading the file. If there's a private message, you've got it in a variable, and you can overwrite the private message file with the array you've stored, which is all of the current private messages minus the one you're about to display. Please note, this does not scale at all, especially in the fast-paced world of chat rooms. You will likely end up with file locking problems if you proceed with the flat-file method. Also, how can i make it so that if in a drop down menu they select the word everybody it goes to a file called messages.txt and if they select user or user2 or user3 from the list it writes to private.txt is this at all possible? user and user2 etc arent hardcoded it's pulling the names from a list of online users. Are you talking about appending messages to a text file? In that case, you can have the dropdown submit with the message, and in the PHP code have a case for 'everybody' where it writes to messages.txt, and if it's not 'everybody', write it to private.txt with the username that was selected from the dropdown as part of the row. Does that answer your question? -- 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 text from a text file
Hi, The timestamp idea is an intresting idea and i'll give that method some thought. I only want it to pull one private message at a time out of the private message field so it might start getting very messy doing that method - but it certainly does make it worth considering. Ed - Original Message - From: Jochem Maas [EMAIL PROTECTED] To: Ed [EMAIL PROTECTED] Cc: php-db@lists.php.net Sent: Friday, January 07, 2005 11:37 AM Subject: Re: [PHP-DB] select text from a text file Ed wrote: Hi, Thanks for answering the question. I'm going to make it write to a database rather than text file after searching google and coming accross common problems with text files. I've now just got to figure out how to mark the message as read in the database so it does not re-appear next time the user clicks update. TIMESTAMP? you can save in the session when a use last grabbed messages (clicked update) and only return newer messages (i.e. message have a 'created' timestamp) then update the timestamp in the session. there is a window in which it is possible to have a request inserts a new message in between another request's selecting of all new messages and updating the session timestamp value (in such cases the person at the client end of the second request will never see the new message insert in the first request) - to handle that you have to implement some kind of locking mechanism. good luck is all can say. (PHP implements a 'share nothing' architecture - not, perse, the easiest or most efficient type of system to build realtime multi-user environments in - AFAICT) I'm going to build a function that will put the message in the correct table using an if clause, is that the best method? your the best judge of that - heh if it works that's the main thing - making it work fast that's something to look at later... and then there is the issue of writing code that neat, tidy, well commented and readable 6 months down the line. Ed - Original Message - From: Andrew Kreps [EMAIL PROTECTED] To: Ed [EMAIL PROTECTED]; php-db@lists.php.net Sent: Thursday, January 06, 2005 6:55 PM Subject: Re: [PHP-DB] select text from a text file On Wed, 5 Jan 2005 11:58:59 -, Ed [EMAIL PROTECTED] wrote: Happy new year folks! The titlemight make this seem like an easy to answer question However here's the complicated bit (well for me anyway). In my text file that is written to by the users in a chatroom it looks like this: nickname||color||what the user is saying||user how can i make it so that if they have a private message when they press update it pulls the message from the text file and displays it in the frame but also deletes the text? You should be using a database for this, it makes things so much easier. That being said, here's one way to go about the text file version: Open the file, read through it line by line. As you read it, push the lines into an array. If you find a private message for the user, store that in a variable, and do not push it into the array. Finish reading the file. If there's a private message, you've got it in a variable, and you can overwrite the private message file with the array you've stored, which is all of the current private messages minus the one you're about to display. Please note, this does not scale at all, especially in the fast-paced world of chat rooms. You will likely end up with file locking problems if you proceed with the flat-file method. Also, how can i make it so that if in a drop down menu they select the word everybody it goes to a file called messages.txt and if they select user or user2 or user3 from the list it writes to private.txt is this at all possible? user and user2 etc arent hardcoded it's pulling the names from a list of online users. Are you talking about appending messages to a text file? In that case, you can have the dropdown submit with the message, and in the PHP code have a case for 'everybody' where it writes to messages.txt, and if it's not 'everybody', write it to private.txt with the username that was selected from the dropdown as part of the row. Does that answer your question? -- 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 text from a text file
Ed wrote: Hi, The timestamp idea is an intresting idea and i'll give that method some thought. I only want it to pull one private message at a time out of the private message field so it might start getting very messy doing that method - but it certainly does make it worth considering. I see (I think) - the timestamp idea was aimed at a public general list of messages. in the case of private messages - they are always aimed at one particular user therefore in such a case a simple flag (true/false) to state whether its been viewed would probably work - or you could just delete the last grabbed message after you have selected it for output. have fun! Ed - Original Message - From: Jochem Maas [EMAIL PROTECTED] To: Ed [EMAIL PROTECTED] Cc: php-db@lists.php.net Sent: Friday, January 07, 2005 11:37 AM Subject: Re: [PHP-DB] select text from a text file Ed wrote: Hi, Thanks for answering the question. I'm going to make it write to a database rather than text file after searching google and coming accross common problems with text files. I've now just got to figure out how to mark the message as read in the database so it does not re-appear next time the user clicks update. TIMESTAMP? you can save in the session when a use last grabbed messages (clicked update) and only return newer messages (i.e. message have a 'created' timestamp) then update the timestamp in the session. there is a window in which it is possible to have a request inserts a new message in between another request's selecting of all new messages and updating the session timestamp value (in such cases the person at the client end of the second request will never see the new message insert in the first request) - to handle that you have to implement some kind of locking mechanism. good luck is all can say. (PHP implements a 'share nothing' architecture - not, perse, the easiest or most efficient type of system to build realtime multi-user environments in - AFAICT) I'm going to build a function that will put the message in the correct table using an if clause, is that the best method? your the best judge of that - heh if it works that's the main thing - making it work fast that's something to look at later... and then there is the issue of writing code that neat, tidy, well commented and readable 6 months down the line. Ed - Original Message - From: Andrew Kreps [EMAIL PROTECTED] To: Ed [EMAIL PROTECTED]; php-db@lists.php.net Sent: Thursday, January 06, 2005 6:55 PM Subject: Re: [PHP-DB] select text from a text file On Wed, 5 Jan 2005 11:58:59 -, Ed [EMAIL PROTECTED] wrote: Happy new year folks! The titlemight make this seem like an easy to answer question However here's the complicated bit (well for me anyway). In my text file that is written to by the users in a chatroom it looks like this: nickname||color||what the user is saying||user how can i make it so that if they have a private message when they press update it pulls the message from the text file and displays it in the frame but also deletes the text? You should be using a database for this, it makes things so much easier. That being said, here's one way to go about the text file version: Open the file, read through it line by line. As you read it, push the lines into an array. If you find a private message for the user, store that in a variable, and do not push it into the array. Finish reading the file. If there's a private message, you've got it in a variable, and you can overwrite the private message file with the array you've stored, which is all of the current private messages minus the one you're about to display. Please note, this does not scale at all, especially in the fast-paced world of chat rooms. You will likely end up with file locking problems if you proceed with the flat-file method. Also, how can i make it so that if in a drop down menu they select the word everybody it goes to a file called messages.txt and if they select user or user2 or user3 from the list it writes to private.txt is this at all possible? user and user2 etc arent hardcoded it's pulling the names from a list of online users. Are you talking about appending messages to a text file? In that case, you can have the dropdown submit with the message, and in the PHP code have a case for 'everybody' where it writes to messages.txt, and if it's not 'everybody', write it to private.txt with the username that was selected from the dropdown as part of the row. Does that answer your question? -- 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 text from a text file
simple, add a 'read flag' column to the table, when the record is viewed, activate this flag (say 0 for unread and 1 for read) (Note: read is a mysql keyword don't use that for a column name) and then only show the ones unread bastien From: Ed [EMAIL PROTECTED] To: Andrew Kreps [EMAIL PROTECTED] CC: php-db@lists.php.net Subject: Re: [PHP-DB] select text from a text file Date: Fri, 7 Jan 2005 08:58:18 - Hi, Thanks for answering the question. I'm going to make it write to a database rather than text file after searching google and coming accross common problems with text files. I've now just got to figure out how to mark the message as read in the database so it does not re-appear next time the user clicks update. I'm going to build a function that will put the message in the correct table using an if clause, is that the best method? Ed - Original Message - From: Andrew Kreps [EMAIL PROTECTED] To: Ed [EMAIL PROTECTED]; php-db@lists.php.net Sent: Thursday, January 06, 2005 6:55 PM Subject: Re: [PHP-DB] select text from a text file On Wed, 5 Jan 2005 11:58:59 -, Ed [EMAIL PROTECTED] wrote: Happy new year folks! The titlemight make this seem like an easy to answer question However here's the complicated bit (well for me anyway). In my text file that is written to by the users in a chatroom it looks like this: nickname||color||what the user is saying||user how can i make it so that if they have a private message when they press update it pulls the message from the text file and displays it in the frame but also deletes the text? You should be using a database for this, it makes things so much easier. That being said, here's one way to go about the text file version: Open the file, read through it line by line. As you read it, push the lines into an array. If you find a private message for the user, store that in a variable, and do not push it into the array. Finish reading the file. If there's a private message, you've got it in a variable, and you can overwrite the private message file with the array you've stored, which is all of the current private messages minus the one you're about to display. Please note, this does not scale at all, especially in the fast-paced world of chat rooms. You will likely end up with file locking problems if you proceed with the flat-file method. Also, how can i make it so that if in a drop down menu they select the word everybody it goes to a file called messages.txt and if they select user or user2 or user3 from the list it writes to private.txt is this at all possible? user and user2 etc arent hardcoded it's pulling the names from a list of online users. Are you talking about appending messages to a text file? In that case, you can have the dropdown submit with the message, and in the PHP code have a case for 'everybody' where it writes to messages.txt, and if it's not 'everybody', write it to private.txt with the username that was selected from the dropdown as part of the row. Does that answer your question? -- 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] select text from a text file
Hi, I've been looking at ways todo this, and as yet haven't figured out howto pull something from the database plus at the sametime update the record, can someone tell me how this is done or where i could learn? i've looked all over phpbuilder and a few other php forums but can't seem to find the answer. Ed - Original Message - From: Bastien Koert [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: php-db@lists.php.net Sent: Friday, January 07, 2005 2:06 PM Subject: Re: [PHP-DB] select text from a text file simple, add a 'read flag' column to the table, when the record is viewed, activate this flag (say 0 for unread and 1 for read) (Note: read is a mysql keyword don't use that for a column name) and then only show the ones unread bastien From: Ed [EMAIL PROTECTED] To: Andrew Kreps [EMAIL PROTECTED] CC: php-db@lists.php.net Subject: Re: [PHP-DB] select text from a text file Date: Fri, 7 Jan 2005 08:58:18 - Hi, Thanks for answering the question. I'm going to make it write to a database rather than text file after searching google and coming accross common problems with text files. I've now just got to figure out how to mark the message as read in the database so it does not re-appear next time the user clicks update. I'm going to build a function that will put the message in the correct table using an if clause, is that the best method? Ed - Original Message - From: Andrew Kreps [EMAIL PROTECTED] To: Ed [EMAIL PROTECTED]; php-db@lists.php.net Sent: Thursday, January 06, 2005 6:55 PM Subject: Re: [PHP-DB] select text from a text file On Wed, 5 Jan 2005 11:58:59 -, Ed [EMAIL PROTECTED] wrote: Happy new year folks! The titlemight make this seem like an easy to answer question However here's the complicated bit (well for me anyway). In my text file that is written to by the users in a chatroom it looks like this: nickname||color||what the user is saying||user how can i make it so that if they have a private message when they press update it pulls the message from the text file and displays it in the frame but also deletes the text? You should be using a database for this, it makes things so much easier. That being said, here's one way to go about the text file version: Open the file, read through it line by line. As you read it, push the lines into an array. If you find a private message for the user, store that in a variable, and do not push it into the array. Finish reading the file. If there's a private message, you've got it in a variable, and you can overwrite the private message file with the array you've stored, which is all of the current private messages minus the one you're about to display. Please note, this does not scale at all, especially in the fast-paced world of chat rooms. You will likely end up with file locking problems if you proceed with the flat-file method. Also, how can i make it so that if in a drop down menu they select the word everybody it goes to a file called messages.txt and if they select user or user2 or user3 from the list it writes to private.txt is this at all possible? user and user2 etc arent hardcoded it's pulling the names from a list of online users. Are you talking about appending messages to a text file? In that case, you can have the dropdown submit with the message, and in the PHP code have a case for 'everybody' where it writes to messages.txt, and if it's not 'everybody', write it to private.txt with the username that was selected from the dropdown as part of the row. Does that answer your question? -- 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] select text from a text file
On Wed, 5 Jan 2005 11:58:59 -, Ed [EMAIL PROTECTED] wrote: Happy new year folks! The titlemight make this seem like an easy to answer question However here's the complicated bit (well for me anyway). In my text file that is written to by the users in a chatroom it looks like this: nickname||color||what the user is saying||user how can i make it so that if they have a private message when they press update it pulls the message from the text file and displays it in the frame but also deletes the text? You should be using a database for this, it makes things so much easier. That being said, here's one way to go about the text file version: Open the file, read through it line by line. As you read it, push the lines into an array. If you find a private message for the user, store that in a variable, and do not push it into the array. Finish reading the file. If there's a private message, you've got it in a variable, and you can overwrite the private message file with the array you've stored, which is all of the current private messages minus the one you're about to display. Please note, this does not scale at all, especially in the fast-paced world of chat rooms. You will likely end up with file locking problems if you proceed with the flat-file method. Also, how can i make it so that if in a drop down menu they select the word everybody it goes to a file called messages.txt and if they select user or user2 or user3 from the list it writes to private.txt is this at all possible? user and user2 etc arent hardcoded it's pulling the names from a list of online users. Are you talking about appending messages to a text file? In that case, you can have the dropdown submit with the message, and in the PHP code have a case for 'everybody' where it writes to messages.txt, and if it's not 'everybody', write it to private.txt with the username that was selected from the dropdown as part of the row. Does that answer your question? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] select particular columns in query
INSERT INTO `dbname`.`newtablename` SELECT * FROM `dname`.`oldtablename` ; bastien From: blackwater dev [EMAIL PROTECTED] Reply-To: blackwater dev [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [PHP-DB] select particular columns in query Date: Wed, 8 Dec 2004 10:46:44 -0500 Hello, I want to create a new row in the db by copying an existing one. My db has an auto incrementing id so I can't simply do insert into cars select * from cars where id=$id as this throws the primary key error. How can I do this with out specifying each column? Thanks! -- 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 particular columns in query
The manual is your friend. You cannot execute the SQL statement you provided because mysql specifically disallows it: http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html Same reference, your presumption about auto-increment columns is also wrong. Doug blackwater dev wrote: Hello, I want to create a new row in the db by copying an existing one. My db has an auto incrementing id so I can't simply do insert into cars select * from cars where id=$id as this throws the primary key error. How can I do this with out specifying each column? Thanks! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] select query across multiple tables
I'm trying to pull all the records from the table class where classID is not equal to the value of classID in the table assignment. Currently, I have 'select class.classID, class.classDesc from class, assignment where assignment.classID = class.classID and assignment.assignmentID=$assidn'. $assidn is value of assignment.assignmentID. When I do this query, I am missing a good chunk of records. What am I doing wrong? You're only getting the rows that are less then or equal to class.classID, i think you want or not equal to. HTH Jeff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] select query across multiple tables
Thanks! It works Cole On Thu, 2004-08-26 at 16:45, [EMAIL PROTECTED] wrote: I'm trying to pull all the records from the table class where classID is not equal to the value of classID in the table assignment. Currently, I have 'select class.classID, class.classDesc from class, assignment where assignment.classID = class.classID and assignment.assignmentID=$assidn'. $assidn is value of assignment.assignmentID. When I do this query, I am missing a good chunk of records. What am I doing wrong? You're only getting the rows that are less then or equal to class.classID, i think you want or not equal to. HTH Jeff -- This message has been scanned for viruses and dangerous content by MailScanner on mail.ashcraftfamily.net, and is believed to be clean. Please report any deviance from this condition immediately to the AFN Administrator at [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT problem between MySQL 3.23 and MySQL 4
Chris Payne wrote:
I'm using Booleans in my searches (New to it) but it works perfectly
on my
local 3.23 version of MySQL, but on the main server which uses version 4 of
MySQL I get an error so there's an error in my Syntax. Here's what I
currently use:
[snip]
LENGTH(REPLACE(LOWER(def),LOWER('as'),'')))
[snip]
And here's the error I receive on the remote MySQL 4 server:
Warning: Bad arguments to implode() in
/var/www/html/www.planetoxygene.com/htdocs/funcs_mysql_boolean.php on line
45
You have an error in your SQL syntax. Check the manual that corresponds to
your MySQL server version for the right syntax to use near ') -
LENGTH(REPLACE(LOWER(),LOWER('as'),''))) /LENGTH('as'
This is not a MySQL 3 vs. MySQL4 issue. If it was, this is the wrong
list, anyhow.
Take a look at the line of the query you _say_ you're running that I've
included above and take a look at the last line of the error that you're
getting from MySQL.
See the difference? Good... now troubleshoot.
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select news based on dates
From: T. H. Grejc [EMAIL PROTECTED]
I would like to display my news like this:
*10.04.2004.*
- news 1
- news 2
- news 3
*14.04.2004.*
- news 4
*15.04.2004.*
- news 5
...
I'm thinking of some while loop but I'm not sure that it will work nor I
know how to create that query.
SELECT your data with the date and only display the date if it changes.
$query = SELECT * FROM table ORDER BY datecolumn ASC;
$result = mysql_query($query) or die(mysql_error());
$prevdate = '';
while($row = mysql_fetch_assoc($result))
{
if($row['datecolumn'] != $prevdate)
{
echo '*' . $row['datecolumn'] . '*br /';
$prevdate = $row['datecolumn'];
}
echo '- ' . $row['news'] . 'br /';
}
---John Holmes...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select news based on dates
Hi!
You can use order by date and such method:
code
$result = mysql_query(blablabla);
$date = foo;
while($news = mysql_fetch_array($result)){
if ($date==$news[date]){
echo new date;
}
echo $news[text];
$date = $news[date];
}
Monday, May 17, 2004, 3:50:04 PM, T. wrote:
THG Hello,
THG I would like to display my news like this:
THG *10.04.2004.*
THG - news 1
THG - news 2
THG - news 3
THG *14.04.2004.*
THG - news 4
THG *15.04.2004.*
THG - news 5
THG ...
THG I'm thinking of some while loop but I'm not sure that it will work nor I
THG know how to create that query.
THG TNX
--
Best regards,
Mikhail U. Petrov
mailto:[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select news based on dates
ORDER BY date, text ?
Hi!
You can use order by date and such method:
code
$result = mysql_query(blablabla);
$date = foo;
while($news = mysql_fetch_array($result)){
if ($date==$news[date]){
echo new date;
}
echo $news[text];
$date = $news[date];
}
Monday, May 17, 2004, 3:50:04 PM, T. wrote:
THG Hello,
THG I would like to display my news like this:
THG *10.04.2004.*
THG - news 1
THG - news 2
THG - news 3
THG *14.04.2004.*
THG - news 4
THG *15.04.2004.*
THG - news 5
THG ...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Select Query--Need help urgently
Irin said: $sql = mysql_query(SELECT * FROM class where timetable_day='Monday'); $row = mysql_fetch_array($sql); $result = $db-query($sql); $numofrows = mysql_num_rows($sql); From the PHP manual: array mysql_fetch_array ( resource result [, int result_type]) You can't send the function mysql_fetch_array a string as a parameter. It needs a result resource. Assuming that your $db class returns a result resource, you can try this: $sql = SELECT * FROM table; $result = $db-query($sql); $row = mysql_fetch_array($result); $numofrows = mysql_num_rows($result); If for some reason you don't know what that class function query is returning, you can do it this way and it will work for sure: $sql = SELECT * FROM table; $result = mysql_query($sql); $row = mysql_fetch_array($result); $numofrows = mysql_num_rows($result); HTH, Katie Dewees Web Developer E-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Select Query--Need help urgently
Irin said: $sql = mysql_query(SELECT * FROM class where timetable_day='Monday'); Oops, I see that he DID do a mysql_query and save the result resource in $sql. Sorry, guys! I haven't had my coffee yet this morning! Katie Dewees Web Developer E-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
