Re: Re[2]: [PHP-DB] MySQL: Random select with specific count of a column
Pablo M. Rivas [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hello Torsten,
TR thanks for your help. Unfortunately it's always returning the same row
for
TR each category (maybe because of the group by) and only one row for
each
TR category. I need to select 2 random rows for each category. Any more
ideas?
I'ts returning 2 rows each RECORD, but the fields have
the same name, so if you do a mysql_fetch_array, you'll find only
one...
Change your sql to:
select a.category as categorya, a.name as namea, b.name as
nameb from tablename as a left join tablename as b on
a.category = b.category and a.nameb.name and
a.language=b.language where a.language='de' and b.name is not
null group by a.category
and you'll do:
echo trthlanguajetdcategorytdname\n;
while ($data=mysql_fetch_array($result)) {
echo trtddetd . $data[categorya] . td . $data[namea];
echo trtddetd . $data[categorya] . td . $data[nameb];
}
ok?...
EACH ROW CONTAINS 2 RECORDS...but.. we need something more, 'cause
you're needing RANDOM rows...
If you need RANDOM, i think mysql will not help you, and you must
change your query to:
select a.category as categorya, a.name as namea, b.name as
nameb from tablename as a left join tablename as b on
a.category = b.category and a.nameb.name and
a.language=b.language where a.language='de' and b.name is not
null order by a.category
and then do something in php like store everything in an array, and take
some
randomly...
srand(time());
$lastcat=0;
echo trthlanguajetdcategorytdname\n;
while ($data=mysql_fetch_array($result)) {
if (($lastcat) and ($lastcat$data[categorya])) {
echo trtddetd . $lastcat . td .
$myarray[$lastcat][rand()%count($myarray[$lastcat])];
echo trtddetd . $lastcat . td .
$myarray[$lastcat][rand()%count($myarray[$lastcat])];
}
$myarray[$data[categorya]][]=$data[namea];
$myarray[$data[categorya]][]=$data[nameb];
$lastcat=$data[categorya];
}
echo trtddetd . $lastcat . td .
$myarray[$lastcat][rand()%count($myarray[$lastcat])];
echo trtddetd . $lastcat . td .
$myarray[$lastcat][rand()%count($myarray[$lastcat])];
mhhh... i don't like... but i think this will work and you can make it
better!!
Hi Pablo,
thank you very much for your extensive efforts! This will definitely help
me.
Best regards, Torsten
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL query, using DISTINCT...
I have a system that tracks downloads, capturing loadsa info, but of interest here, is email and filename. Simply put, I wanna show all results where file name AND email are unique. (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a table, I want to only see it once.) What am I doing wrong...? SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE `bu` = 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' Tris... * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: MySQL query, using DISTINCT...
Tristan Pretty [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] com... I have a system that tracks downloads, capturing loadsa info, but of interest here, is email and filename. Simply put, I wanna show all results where file name AND email are unique. (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a table, I want to only see it once.) What am I doing wrong...? SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE `bu` = 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' Tris... Hi Tristan, I'm not an MySQL expert but DISTINCT removes duplicate *rows* from your result set. As you select email AND file_name you will always have a unique row (combination of email and file_name). Try without specifying file_name: SELECT DISTINCT email FROM `completed_downloads` WHERE `bu` = 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' Regards, Torsten Roehr -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: MySQL query, using DISTINCT...
I have a system that tracks downloads, capturing loadsa info, but of interest here, is email and filename. Simply put, I wanna show all results where file name AND email are unique. (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a table, I want to only see it once.) What am I doing wrong...? SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE `bu` = 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' Tris... Hi Tristan, I'm not an MySQL expert but DISTINCT removes duplicate *rows* from your result set. As you select email AND file_name you will always have a unique row (combination of email and file_name). Try without specifying file_name: SELECT DISTINCT email FROM `completed_downloads` WHERE `bu` = 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' Regards, Torsten Roehr Ah... But if the user has downloaded 3 files, I need to know that. distinct email alone, wouldn't pick that up... Cheers for your help though, I'm getting there...! Any other ideas? * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: MySQL query, using DISTINCT...
Hi, using group by can also be usefull. zareef ahmed --- Torsten Roehr [EMAIL PROTECTED] wrote: Tristan Pretty [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] com... I have a system that tracks downloads, capturing loadsa info, but of interest here, is email and filename. Simply put, I wanna show all results where file name AND email are unique. (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a table, I want to only see it once.) What am I doing wrong...? SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE `bu` = 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' Tris... Hi Tristan, I'm not an MySQL expert but DISTINCT removes duplicate *rows* from your result set. As you select email AND file_name you will always have a unique row (combination of email and file_name). Try without specifying file_name: SELECT DISTINCT email FROM `completed_downloads` WHERE `bu` = 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' Regards, Torsten Roehr -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php = Zareef Ahmed :: A PHP Developer in Delhi(India). Homepage :: http://www.zasaifi.com __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] Re: MySQL query, using DISTINCT...
Hello Tristan, Select count(id) as howmany, file_name, email from `completed_downloads` where bu='reech' and date BETWEEN '2004-06-01' AND '2004-06-30' group by file_name, email I have a system that tracks downloads, capturing loadsa info, but of interest here, is email and filename. Simply put, I wanna show all results where file name AND email are TPrsc unique. (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a TPrsc table, I want to only see it once.) What am I doing wrong...? SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE TPrsc `bu` = 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' Tris... -- Best regards, Pablo -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL query, using DISTINCT...
On Tuesday 06 July 2004 17:58, [EMAIL PROTECTED] wrote: I have a system that tracks downloads, capturing loadsa info, but of interest here, is email and filename. Simply put, I wanna show all results where file name AND email are unique. (so if email '[EMAIL PROTECTED]' has filename 'word.doc' 5 times in a table, I want to only see it once.) What am I doing wrong...? Well what did you expect your code to do when you run it, and what is it actually doing? Please spend a little time describing your problem clearly and succintly when posting. In the absence of more information, my guess is that your biggest problem is that you're not doing sufficient error checking in your code. AFAICS ... SELECT DISTINCT(file_name, email) FROM `completed_downloads` WHERE `bu` = 'reech' AND date BETWEEN '2004-06-01' AND '2004-06-30' would give a mysql error. The correct syntax is: SELECT DISTINCT col1, col2, etc FROM table ... Please follow the examples in the manual and incorporate error checking when using mysql. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* Nondeterminism means never having to say you are wrong. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Warning
Hi all, I received a mail today subject URGENT ASSISTANCE NEEDED This is a scam, I relieved it to my mail address were I only receive mail from the PHP DB List so it seems that they are now scanning our list for e-mail addy's You can read more on this type of fraud at www.419eater.com http://www.419eater.com/ makes some good reading Please guys if you get mails offering millions in exchange for use of your bank account then trust me it's a scam Kenny
[PHP-DB] mysql versus mysqli versus db abstraction
Hello, I was wondering how many of you have made the switch to mysqli from the mysql library? I teach a class in PHP/MySQL and want to keep current with what industry folks are doing. I did not immediately adopt mysqli into my classroom and am wondering if it's time. I was also wondering how many of you are working strictly with DB abstraction classes at this point. Any input you can share is most appreciated. Cheers, Kimberlee Jensen
