Re: [PHP-DB] a Count() ?
select cat_id, count(prod_id) from some_table order by cat_id; Best regards, Andrey Hristov - Original Message - From: Dave Carrera [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, March 16, 2002 12:46 PM Subject: [PHP-DB] a Count() ? Hi All I am trying to count how many product names in my db have the same category id and then show it ie: Catid 1 Product 1 Catid 1 Product 2 Catid 2 Product 3 Catid 3 Product 4 Catid 3 Product 5 Result would be Catid1 has 2 products Catid2 has 1 products Catid3 has 2 products I think it has something to do with the GROUP command but the mysql doc dose not make it clear how to achive this task. Code examples, pointers to web resources or any info thankfully received. Thank you in advance Dave Carrera Php Developer http://davecarrera.freelancers.net http://www.davecarrera.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Joining three tables
Try joining parents to parentlog by left using(parent_id) . If you receive 1 row for student that has 2 parents (I say this because I've never been in such situation) try to join but not left - ordinary join. So select * from students left join parentlog using(student_id) left join parents using(parent_id); or select * from parents,parentlog left join students using(student_id) where parents.parent_id=parentlog.parent_id; Hope this helps. Andrey Hristov - Original Message - From: John Hughes, Jomari Works [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, March 16, 2002 8:23 AM Subject: [PHP-DB] Joining three tables SQL QUERY question I have three tables: students has student_id and student_name parents has parent_id and parent_name parentlog has student_id and parent_id I want to search the parentlog WHERE student_id = some_id GROUP BY parent_id (This will bring back two rows when there are two parents) At the same time I want to get the name of the student that matches student_id and the name of the parent. I can LEFT JOIN students with parentlog USING(student_id) but I can't figure how I can join the parents so that I can get the name of match for the parent_id. Can I join three tables and search all in one pass? TIA John Hughes -- 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] a Count() ?
Hi Dave, I am trying to count how many product names in my db have the same category id and then show it ie: Catid 1 Product 1 Catid 1 Product 2 Catid 2 Product 3 Catid 3 Product 4 Catid 3 Product 5 Result would be Catid1 has 2 products Catid2 has 1 products Catid3 has 2 products I think it has something to do with the GROUP command but the mysql doc dose not make it clear how to achive this task. Code examples, pointers to web resources or any info thankfully received. Let's take it a step at a time. First of all assemble the SELECT to produce your first list: SELECT * FROM tblNm; then pull in the GROUP BY clause to collect the row-results together in some like-minded fashion. In this case you want to collect all or the rows pertaining to one category (ID) together. (you will need to be more specific about what in the manual is making you uncertain): SELECT * FROM tblNm GROUP BY Catid1; Oops! All of a sudden we only get one line for each CatId (and the rest of the columns produce fairly unpredictable data taken from only one of the rows with that CatId). Get rid of the * (all columns) and replace it with the CatId colNm. Now follow your instincts and check out COUNT() in the manual, and try something like: SELECT Catid1, count(*) FROM tblNm GROUP BY Catid1; As I said 'follow your instincts' and take it one step at a time: Code the simplest query first, then try making it more complicated by adding/amending one clause at a time, crafting the result until it suits your purposes... Let us know how you get on! =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] a Count() ?
On Sat, 16 Mar 2002 10:46:38 - Dave Carrera [EMAIL PROTECTED] wrote: Hi All I am trying to count how many product names in my db have the same category id and then show it ie: Catid 1 Product 1 Catid 1 Product 2 Catid 2 Product 3 Catid 3 Product 4 Catid 3 Product 5 Result would be Catid1 has 2 products Catid2 has 1 products Catid3 has 2 products I think it has something to do with the GROUP command but the mysql doc dose not make it clear how to achive this task. Code examples, pointers to web resources or any info thankfully received. Simply by a sql query : SELECT count( Catid ) as total, Catid FROM t_product GROUP BY Catid; the result set will be something like that totalCatID 21 12 23 hth pa -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] a Count() ?
On Sat, 16 Mar 2002 11:50:11 - DL Neil [EMAIL PROTECTED] wrote: Hi Dave, SELECT * FROM tblNm; SELECT * FROM tblNm GROUP BY Catid1; SELECT Catid1, count(*) FROM tblNm GROUP BY Catid1; As I said 'follow your instincts' and take it one step at a time: Code the simplest query first, then try making it more complicated by adding/amending one clause at a time, crafting the result until it suits your purposes... Nothing to say except you have to avoid use '*'. That makes your queries easier to read and will safe your db engine :). pa -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Which select is fast,thanks....... ?
Hi,all friends: Would you please tell me which script in MySQL is fast? - 1. select count(*) from bk_data 2. select count(book_name) from bk_data - PS. I have 13000 book records in this table. Fongming from Taiwan, thanks
Re: [PHP-DB] Date Returns
Does anyone know if the date function returns false if it cannot convert
the
specified date/time to the format? If not, does anyone have a rule set
they
use to verify the date is a valid convertible date? I need to update a
row
in MSSQL, and it will fail if the date is incorrect. I've validated for
the
basic things, but just am not sure what the best way would be to validate
the actual date itself. I was thinking of RegExp, but not certain if that
would really work for multiple date formats for example:
March 15, 2002
03/15/2002
3/15/2002
and so on and so forth. The field actually requests a dd/mm/yy format,
but
you know how people follow instructions.
Use MMDD HH:MM:SS (without -) format date when inserts or updates
fields in MSSQL, MYSQL etc.
For example: INSERT INTO tblxxx (fielddatetime,fieldother) VALUES ('20020311
18:33:44','something else')
I use it in that way and it is the best method I think.
Mayby you may use odbc structure {ts '-mm-dd hh:mm:ss'}
For example: INSERT INTO tblxxx (fielddatetime,fieldother) VALUES ({ts
'20020311 18:33:44'},'something else')
good luck with dates ;-)
BTW I recommend you to explore MSSQL Book Online! It has quite good and a
lot of knowledge.
Konrad
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Which select is fast,thanks....... ?
#1 is the fastest with MySQL. (copied from MySQL online docs...) Glenn a.. COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and HEAP tables. This is also done for any NOT NULL expression when used with only one table. . examples of queries that are very fast: mysql SELECT COUNT(*) FROM tbl_name; The following queries are resolved using only the index tree (assuming the indexed columns are numeric): mysql SELECT COUNT(*) FROM tbl_name -WHERE key_part1=val1 AND key_part2=val2; K ®r[ [EMAIL PROTECTED] wrote in message 002b01c1ccfb$c9f94a20$0200a8c0@hinet">news:002b01c1ccfb$c9f94a20$0200a8c0@hinet... Hi,all friends: Would you please tell me which script in MySQL is fast? - 1. select count(*) from bk_data 2. select count(book_name) from bk_data - PS. I have 13000 book records in this table. Fongming from Taiwan, thanks -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] increase date()
i have: $nowdate=date(Y-m-d); and i want to increase it by 6 months,how can i do that?? Rehab M.Shouman - Express yourself with a super cool email address from BigMailBox.com. Hundreds of choices. It's free! http://www.bigmailbox.com - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] increase date()
On Sunday 17 March 2002 14:28, its me wrote: i have: $nowdate=date(Y-m-d); and i want to increase it by 6 months,how can i do that?? Look at the examples in the manual for date(). -- Jason Wong - Gremlins Associates - www.gremlins.com.hk /* True happiness will be found only in true love. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
