[PHP-DB] Select distinct field won't return distinct value
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 /;
}
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
[PHP-DB] 【身体契約】
【身体契約】→ 再度貴方の生活をサポートします！！！ 今まで似たようなメールも有ったかも知れませんが、ご利用する 前に規約の確認は重要です。当番組は契約希望者に十分ご理解を頂 く為、登録費用・年会費・契約紹介費などを全額免除と致しまして、 大変好評を得ております。契約宣伝拡大の為、貴方をご紹介させて 頂きます。 【身体契約】とは？ 『貴方の体を○金で買いたい・・・』と希望している女性と直接交渉 して頂き、女性の要望を満たせると契約金額をゲットする方法であ る。当番組は無料で契約仲介を行っております。男性から費用を一 切頂いておりませんので、ご安心下さい。 契約希望の方は早急、下記リンクより無料申し込みを済ませて下さ い。 ［希望者用 http://sruq.com/?a16］ 申し込み次第、紹介相手(地域一致女性会員)の連絡方法を差し上げ ますので(希望女性から直接メールが届く場合も多い)、即金額交渉 をお勧めします。 メルいらないのぉ？ [EMAIL PROTECTED]
