Re: [PHP-DB] Re: SELECT
On Mon, Oct 17, 2011 at 12:58 PM, Jim Giner jim.gi...@albanyhandball.com wrote: I would do it this way: Where $sel_d = (the day # you want) $sel_m = (the month # you want) The where clause would be: Where (start_month = $sel_m and start_day = $sel_d) and (end_month = $sel_m and end_day = $sel_d) Hmm, no this won't work. Check this: start_month=10 start_day=15 end_month=1 end_day=1 sel_day=23 sel_month=12 in this case, sel_month IS greater than start_month and sel_day IS greater than start_day, HOWEVER sel_month is ALSO greater than end_month and sel_day is ALSO greater than end_day in this case. (wrap around to new year problem, wrap around to new month problem) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: SELECT
Though the operators are = and =, not = and =. Toby -Original Message- From: Jim Giner [mailto:jim.gi...@albanyhandball.com] Sent: Monday, October 17, 2011 1:58 PM To: php-db@lists.php.net Subject: [PHP-DB] Re: SELECT I would do it this way: Where $sel_d = (the day # you want) $sel_m = (the month # you want) The where clause would be: Where (start_month = $sel_m and start_day = $sel_d) and (end_month = $sel_m and end_day = $sel_d) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] RE: SELECT WHERE length of content question
Do not forget to use TRIM function as well: LENGTH(TRIM(fax)) in order to elliminate leading and trailing spaces. Aurel - Original Message - From: Geoffrey Pitman geoffrey.pit...@gmail.com To: php-db@lists.php.net Sent: Friday, March 11, 2011 7:59 PM Subject: [PHP-DB] RE: SELECT WHERE length of content question You should be able to use the MySQL command LENGTH() command. I'm assuming that your fax field is a varchar or char(10) field. If it's a number (which it probably shouldn't be), I suppose you could write a query to find values equal to or greater than 100,000,000,000. http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_length I hope this helps. Geoff -- Forwarded message -- From: Ron Piggott ron.pigg...@actsministries.org To: php-db@lists.php.net Date: Wed, 9 Mar 2011 17:49:42 -0500 Subject: SELECT WHERE length of content question 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 http://www.theverseoftheday.info/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: SELECT query
On Sun, Dec 21, 2008 at 7:56 PM, Ron Piggott ron.pigg...@actsministries.org wrote: One more thing ... Bible is stored by verses, t is the text of the verse On Sun, 2008-12-21 at 19:52 -0500, Ron Piggott wrote: I am working on a web based Bible searching query. So far I am able to generate: SELECT * FROM `bible_verses` INNER JOIN `bible_books` ON `bible_books`.`id` = `bible_verses`.`b` WHERE `t` IN ( 'Lord' , 'Jesus' ) LIMIT 0 , 10 Is an IN the correct syntax to use? I am trying to take what the user types in (variable is $keyword_search) and allow a search where if the same 2 or 3 words are in the verse of the Bible that verse would be a match, but not necessarily be beside each other. $keyword_search_string = str_replace( , ' , ', $keyword_search); $query .= IN ( ' . $keyword_search_string . ' ) ; How do I do this correctly? Ron -- Acts Ministries Christian Evangelism Where People Matter 12 Burton Street Belleville, Ontario, Canada K8P 1E6 ron.pigg...@actsministries.org www.actsministrieschristianevangelism.org In Belleville Phone: (613) 967-0032 In North America Call Toll Free: (866) ACTS-MIN Fax: (613) 967-9963 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php query code looks correct. are you getting an error? have you tried echoing out the sql and testing in phpmyadmin or some other gui tool? -- Bastien Cat, the other other white meat
Re: [PHP-DB] Re: SELECT query
Bastien Koert wrote: On Sun, Dec 21, 2008 at 7:56 PM, Ron Piggott ron.pigg...@actsministries.org wrote: One more thing ... Bible is stored by verses, t is the text of the verse On Sun, 2008-12-21 at 19:52 -0500, Ron Piggott wrote: I am working on a web based Bible searching query. So far I am able to generate: SELECT * FROM `bible_verses` INNER JOIN `bible_books` ON `bible_books`.`id` = `bible_verses`.`b` WHERE `t` IN ( 'Lord' , 'Jesus' ) LIMIT 0 , 10 Is an IN the correct syntax to use? This will work fine. An IN clause is like multiple OR's: ... where t = 'Lord' OR t = 'Jesus' ... I am trying to take what the user types in (variable is $keyword_search) and allow a search where if the same 2 or 3 words are in the verse of the Bible that verse would be a match, but not necessarily be beside each other. $keyword_search_string = str_replace( , ' , ', $keyword_search); $query .= IN ( ' . $keyword_search_string . ' ) ; This will seem a little long winded but you need to check for sql injection. As it stands, you'll get an error when you search for a name with a quote (o'reilly). Might not be in the bible (I have no idea) but doesn't mean someone won't try it ;) // look at the keywords one by one - they are space separated $keywords = explode(' ', $keyword_search); // now go through them all and escape them. $keyword_searches = array(); foreach ($keywords as $keyword) { $keyword_searches[] = mysql_real_escape_string($keyword); } // then turn it all into an IN string. $keyword_search_string = IN (' . implode(',', $keyword_searches) . '); You may want to eventually look at full text searching, depending on how slow this is and how important search is to your site. The syntax is completely different. http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[Fwd: Re: [PHP-DB] Re: SELECT html table]
-- Best Regards, Shahmat Dahlan Research and Development SAINS Mobile: +(60)16 882 6130 Office: +(60)82 426 733 ext 5512 ---BeginMessage--- (1) i've been using this method for years now, maybe somebody else has a better way to do this? ? sql query $i = 0; while (sql result fetches) { if ($i%2 =! 0) { $bg = 'background=white'; } else { $bg = ''; } echo tr $bg; php codes $i++; } ? (2) check out the limit syntax in the mysql manuals. select * from table limit startindex, number of rows you should work out on how to use the concept of pages, and how many rows in a page, in which you had mentioned 20. count the number of page base on the number of records returned then you get the total number of pages for particular returned result i'm thinking of the function ceil, at least i think it's the ceiling function. e.g. if pg = 1 maxrows=20 start = ($pg-1)*$maxrows select * from table $start, $maxrows pls feel free to correct me JeRRy wrote: From: Ron Piggott (PHP) [EMAIL PROTECTED]To: PHP DB php-db@lists.php.netDate: Sun, 04 Dec 2005 13:38:58 -0500Subject: 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 [input] [input] [input] [input] [input] [input] [input] [input] Visit http://www.mysql.com/ and read a little bit about LIMIT command. :) Jerry - Do you Yahoo!? Take your Mail with you - get Yahoo! Mail on your mobile - Do you Yahoo!? Never miss an Instant Message - Yahoo! Messenger for SMS -- Best Regards, Shahmat Dahlan Research and Development SAINS Mobile: +(60)16 882 6130 Office: +(60)82 426 733 ext 5512 ---End Message--- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: Select
Miguel Guirao wrote: You are right David, I have an auto_increment field!! What about this: $items2 = mysql_query(SELECT DISTINCT rev FROM rev ORDER BY rev, $link); where rev is a revision field for parts in my wharehouse!!! Does it will work in this case? Thanks -Original Message- From: David Robley [mailto:[EMAIL PROTECTED] Sent: Martes, 17 de Mayo de 2005 03:42 p.m. To: php-db@lists.php.net Subject: [PHP-DB] Re: Select Miguel Antonio Guirao Aguilar wrote: 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? Without knowing how many fields you have and what they are, no. But you should realise that DISTINCT looks at _all_ selected fields in a record, so if you have an auto_increment id field in amongst those selected, all the records will be different. David That should work as you expect it to. David -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] RE: select inside a while loop
Sorry, just say the message. Your code looks well. But is the variable $db the name of your database or your link-identifier. When it is the name of your database i'm not really surpised your code wouldn't work. mysql_query requires as second argument a link identifier. Read what Rolf says. Very wise words here. :-) So with all that said here is what i have done that doesn't work, $result = mysql_query(SELECT artist_id FROM songs,$db) or die(mysql_error()); if ($row = mysql_fetch_row($result)){ do { $artist_name = $row[artist_id]; $result_1 = mysql_query(SELECT artist_id,artist_name FROM artists WHERE artist_name = '$artist_name',$db); $row_1 = mysql_fetch_array($result_1); Why don't you make one query with a union between the 2 tables and then pass through the results with a loop and maybe an if inside it? Ypou are making to many connections to the DB. P.D.: Any way, your problem will be solved with Rolfs advice. -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telemática Universidad Nacional del Litoral - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: select from multi tables
thanks, i think your probably right. Another problem I have is trying to sum the totals of F1 over muliple tables, what I would like is to be able to use the UNION statement but my site is hosted and they are not using version 4x of MySQL. Is there any work around for this using a single SQL statement or again will I need to use multiple statments and add up the results with php? thanks again -Original Message- From: Bastian Vogt [mailto:[EMAIL PROTECTED]] Sent: 03 February 2003 13:12 To: [EMAIL PROTECTED] Subject: [PHP-DB] Re: select from multi tables SELECT SUM(T1.F1) AS C1, SUM(T2.F1) AS C2 FROM T1, T2 Hi, T1,T2 is a join of the two tables which means that you combine each value of T1 with each value of T2. This is why you don't get the result you want. In your case I simply would do two querys as it won't make any problems: SELECT SUM T1.F1 FROM T1; SELECT SUM T2.F2 FROM T2; Regards, Bastian -- 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] Re: select date YYYY-MM mysql help
Which solution did you end up using? Just curious, I wasn't sure if mine was correct ... Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Max Clark [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Re: select date -MM mysql help Thanks everyone for their help! Peter Goggin [EMAIL PROTECTED] wrote in message 008f01c2b32a$b50b2500$0301a8c0@petergoggin">news:008f01c2b32a$b50b2500$0301a8c0@petergoggin... If mysql has a between function then you could try: where date between '2002-12-01'and '2002-12-31'. Otherwise try where date '2002-12-01 00:00:00'and date '2002-12-31 23:59:59' Regards Peter Goggin - Original Message - From: Matthew Moldvan [EMAIL PROTECTED] To: 'Max Clark' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 03, 2003 9:38 AM Subject: RE: [PHP-DB] select date -MM mysql help Sounds more like an SQL question than PHP, but try the following: SELECT * FROM table WHERE date LIKE '2002-12%'; Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Max Clark [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 5:40 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] select date -MM mysql help Hi all, I would like to do a query like this from mysql: select * from table where date = '2002-12'; However unless I have the complete date '2002-12-26' the query doesn't work correctly. What else do I need? Thanks in advance, Max -- 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 -- 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] Re: select date YYYY-MM mysql help
I guess for consistencies sake you can use SELECT * FROM table WHERE date LIKE '2003-01%' also ... just a thought. :-) Anyway, glad to help out. Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Max Clark [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 3:35 PM To: 'Matthew Moldvan'; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Re: select date -MM mysql help I ended up using: SELECT * FROM table WHERE date LIKE '2002-12%'; And: SELECT * FROM table_name WHERE date BETWEEN '2003-01-01' AND '2003-01-31'; Thanks again for the help! -Max -Original Message- From: Matthew Moldvan [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 12:16 PM To: 'Max Clark'; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Re: select date -MM mysql help Which solution did you end up using? Just curious, I wasn't sure if mine was correct ... Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Max Clark [mailto:[EMAIL PROTECTED]] Sent: Friday, January 03, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Re: select date -MM mysql help Thanks everyone for their help! Peter Goggin [EMAIL PROTECTED] wrote in message 008f01c2b32a$b50b2500$0301a8c0@petergoggin">news:008f01c2b32a$b50b2500$0301a8c0@petergoggin... If mysql has a between function then you could try: where date between '2002-12-01'and '2002-12-31'. Otherwise try where date '2002-12-01 00:00:00'and date '2002-12-31 23:59:59' Regards Peter Goggin - Original Message - From: Matthew Moldvan [EMAIL PROTECTED] To: 'Max Clark' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 03, 2003 9:38 AM Subject: RE: [PHP-DB] select date -MM mysql help Sounds more like an SQL question than PHP, but try the following: SELECT * FROM table WHERE date LIKE '2002-12%'; Regards, Matthew Moldvan --- System Administrator Trilogy International, Inc http://www.trilogyintl.com/ecommerce/ --- -Original Message- From: Max Clark [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 5:40 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] select date -MM mysql help Hi all, I would like to do a query like this from mysql: select * from table where date = '2002-12'; However unless I have the complete date '2002-12-26' the query doesn't work correctly. What else do I need? Thanks in advance, Max -- 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 -- 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] Re: select distinct with all columns
Perfect! Thanks. I'm still trying to get a handle on the GROUP BY command. It's still a little bit like magic to me. And in the manual, they don't really show how to use it like the way I wanted to use it. Thanks again, russ On Monday 12 August 2002 01:27 pm, Dan Koken wrote: SELECT * FROM equipement WHERE class = microphones GROUP BY type; Russ Arbuthnot wrote: I have a mysql table named equipment with 11 columns named: id, staff_member, class, type, manufacturer, model, description, picture, created, modified, and published. I'm trying to write a select statement similar to this: SELECT DISTINCT type FROM equipement WHERE class = microphones; yet shows all 11 columns of the selected rows rather than just the type column. The only way I know how to show all the columns is to use SELECT *, or to list all the columns manually like SELECT id, type, class, ... etc. But when I tried doing this: SELECT DISTINCT type, id, staff_member, class, manufacturer, model, description, picture, created, modified, published FROM equipment WHERE class = microphones; I just got the exact same answer as if i would have done this: SELECT * FROM equipment WHERE class = microphones; so it didn't help. Can anyone offer a hint? Thanks, russ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php