Select distinct bug in PHP-Mysql

Anon

I am using PHP3.0.16 on Apache 1.3.12 and Mysql 3.22.25 on a Linux machine (Redhat) with downloaded and compiled softwares as mentioned above.

In PHP, the select distinct <fieldname> clause doesnt reurn distinct record on one field. But in Mysql when thru Mysql Client I give the same distinct query, it returns correctly.
Why ?

Moroever instead of distinct, group by <field1> order by <field1> also doesnt return unique records on <field1> and they are not sorted either in that order.

Is this a serious BUG in php-mysql functions or somewhere. I mean it seems to work correctly in Mysql alone but not thru PHP.

What other reason could there be ?
This needs examination, If this is true for others as well.

Anon

I've never seen or heard of any bug...

Are you only using one fieldname in your query? what is the command you are issuing, exactly..??

---JH

Anon

the query I am using is
"select distinct category from tablename group by category order by category" ;

This should say return unique categories, but actually returns duplicate rows too.
The tablename table contains multiple values for field category. Hence I want to selewct unique values of category.

The point is this qury works fine from Mysql directly , but from PHP (Msql_query(qury string)), it returns erroneous row set.

This seems to me like a bug, even though all documentation says distinct should return unique values of a field.

I am lost.
Please help

Anon

Can you show your PHP code?

There's no reason why something at the command line and something passed in PHP should return different values. All PHP does is take the string, and give it to MySQL. It doesn't do anything to it...

wierd...

---JH

Anon

The PHP code is use is as below :

$sql="";
$sql = "SELECT distinct (category1) FROM mypages WHERE 1=1 ";

              // ---- Check for classifications matching USER ENTRY

              if ((!$category == "")) {
              $sql = $sql." AND "
              }

              $sql = $sql. "(trim(category1) like '" . $category. "%')";
              $sql = $sql . " group by category1 order by category1 asc";

              $result = mysql_query($sql);
              if ($debug==1) { echo "**[".$sql."]**"; }
              $rows = MYSQL_NUM_ROWS($result);
              --------------------------------------
              where $category1 is the user input on previous user form page.


              The direct query in Mysql is as follows
              ---------------------------------------
              select distinct category1 from mytable where category1 like 'compute%' group by category1 ;
              ---------------------------------------
              Even without the group by clause (since I am using a where caluse), the distinct works in Mysql but not
              thru PHP.

              The nos of rows returned thru MYsql is always lower (since it is unique) than thru PHP (since it returns
              duplicate values too).

              This is a serious problem it seems, but you will know better.

              Thankx for the help. Looking forward to a solution.

Anon

John Holmes wrote:

There's no reason why something at the command line and something passed in PHP should return different values. All PHP does is take the string, and give it to MySQL. It doesn't do anything to it...

I'm not sure if this thread is still alive, but the same issue has hit me:

installed php4.3, and now ALL of the select distinct statments fail from pages that previously worked in php3. A statement as simple as "select distinct field from tablename where userid = 'blah' " fails when passed from php, but when the table is queried from the command line, the result set is fine. Strange!

Anon

I've just recently run into the same problem, using PHP3 and MySQL 3.22.21 .... using DISTINCT does NADA!!

Can anyone suggest a workaround for this, that will allow you to filter out rows having the same value for the given field?

Anon

Hi,
this is a big problem.

One solution is to select and dump reccs into an array and filter in that stage in array itself.
Since PHP has pretty exhaustive array manupulation functions, so thats easy to do.

Its one more step and layer between database and web page translation. But its an OK downtime to pay for a solution to this problem (seems to me to be a bug).

Allbest

Anon

Yeah, it seems best at this point to do the filtering in PHP itself...in my case I'm doubly screwed because it's PHP3, which is missing some of the Array functions of PHP4...doh!

At any rate I got it to work, thanks for the help. 🙂

Mike

Anon

I hope the bigwigs in PHP and mYsql world see this problem and address it correctly.
I mean this thing IS a problem and there is no reason why this should be the case.

I think this kind of a programmatic situation in called a BUG.
Will someone examine this correctly. Lets live up to the sipirit of Opens Source

Allbest

Anon

hi all,

i don't if this thread is still active, but i want to say something...

i had this problem, too.

dev-server:

Win2000 Advanced Server, IIS5, PHP4, MySQL - no problem...

web-server:

suse linux, apache, php4, MySQL - DISINCT not working...

i agree with nilch. This seems to me beeing a MySQL, because i heard of different PHP-Versions related to problems with this.

Sometimes e.g. PHP-Version 4.06 works and somtimes it doesn't work, just a matter of the MySQL versions.

I also heard of problems using GROUP BY in some MySQL versions.

i don't know yet which versions work incorectly and which work fine, but i never heard of a win32-MySQL-Version having any of these problems.

I'll write some more about this soon on www.devking.com

Akinova

Anon

Akinova, you are right.

I havent yet found this problem to be documented anywhere. If this is seriously a bug, will someone rectify this ?

I will have to do a chek on the Win2k Mysql-PHP combination, but yes, on Linux (I tried on Red Hat), the DISTINCT clause doesnt work.

I hope the open source spirit rises to the occasion and rectifies this anomay.
I feel sure that there is a bug somewhere.

Anyway, this problem Akinova came up a long time after I had posted it initially.
So it seems after such a long time also the problem persists (of course with the same versions of the softwre, it will I guess).

I think the problem (even if minor) has to be highleted i other forums also. Let us all do the necessary.

Allbest
Nilch

Anon

Hi Guys
most of you are right but I think the query is not written the right way also
SELCT DISTINCT FIELD1, FIELD2 ...would not yield same results like SELECT FIELD1, FIELD2 where the first field is unique because the set would be indexed differently

The best solution is to use GROUP BY because this is what you are trying to reinvent. I have used GROUP BY on many mysql- PHP conf from PHP 3 till now and I update mysql regularly

.b
http://aliwaa.com