#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Location
    Calcutta, West Bengal, India
    Posts
    25
    Rep Power
    0
    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 thu 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.
  2. #2
  3. No Profile Picture
    Carpe Diem
    Guest
    Devshed Newbie (0 - 499 posts)
    The version of PHP you are using is out dated and un-supported.

    Try upgrading to PHP4.0.2


    ------------------

    Regards
    Darren
    --
    http://www.php4hosting.com/ $
    http://www.php4hosting.co.uk/
    PHP/MySQL Enabled Hosting/Dedicated Servers
  4. #3
  5. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Not likely. The query is passed directly as written (substituting variables of course) by the API. Why don't you post the queries 1) exactly as written in PHP and 2)as you put it on the command line.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Location
    Calcutta, West Bengal, India
    Posts
    25
    Rep Power
    0
    Thanks for not brushing it aside an as old version. Most people still use versions lower than PHP4.

    The PHP code is use is as below :
    ----------------------------
    $sql="";
    $sql = "SELECT distinct (category1) FROM $tbl_yellowpages 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 and $tbl_yellowpages is the variable containing the tablename currentlly used being passed from index.php.


    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.

  8. #5
  9. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    Okay...just for the record, I'm also the guy who is posting on PHPbuilder..so I'm just going to pick a forum and help..

    I don't know why it's not working. I ran your code, your sql came out correct (mostly) and it returned the right values from the command line. I can't figure out why it'd be different going through PHP..??

    A few comments about your code:

    1. You don't need $sql=""; if you're going to set $sql in the next line.

    2. Why not say if ($category != "")? You don't need the extra (), either.

    3. Your missing a semi-colon in $sql = $sql." AND "

    4. If you want to add a string to the end of a string, you can use $sql .= " ADD "; which means the same as $sql = $sql . " ADD ";

    5. Shouldn't the last to additions to $sql be within your if statement? If category=="", then you end up with an sql statement like

    SELECT distinct (category1) FROM your_table WHERE 1=1 (trim(category1) like '%') group by category1 order by category1 asc

    which isn't formatted correctly.

    ---JH

    [This message has been edited by SepodatiCreations (edited October 09, 2000).]
  10. #6
  11. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Yeah, what John said. ;-)

    Also, DISTINCT is a keyword NOT a function, so do not use () with it. (Interesting how you did when generating the query with PHP and not directly on the command line.)

    Remember, the query works EXACTLY the same if it's passed thru PHP or directly via a shell. It's just your generation of the query that's wrong. If you know what you want the query to be on the command line, use:

    print $sql;
    exit;

    before the query is executed to make sure it is being generated correctly.


    BTW, Darren has no idea what he's talking about. 3.0.16 IS not unsupported. Bug reporting is ALWAYS supported in the last two revisions of PHP3 as well as PHP4. Since 3.0.16 is the latest (and possibly last) revision of PHP3 it is still supported.

    That said, you are not experiencing a bug.

Similar Threads

  1. Help to solve this bug with Javascript and Opera Cache Managing
    By mapg in forum JavaScript Development
    Replies: 0
    Last Post: January 19th, 2004, 09:11 PM
  2. Replies: 0
    Last Post: January 19th, 2004, 07:42 PM
  3. IE positioning bug and its solution
    By delerious in forum HTML Programming
    Replies: 3
    Last Post: December 23rd, 2003, 04:42 AM
  4. Replies: 1
    Last Post: September 24th, 2003, 09:20 AM
  5. Very strange bug, vars ignored.
    By Kristten in forum PHP Development
    Replies: 7
    Last Post: June 25th, 2003, 12:26 PM

IMN logo majestic logo threadwatch logo seochat tools logo