Results 1 to 6 of 6

Thread: Count as 1

  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: Count as 1

    This code will count the numbers of rows each name have and add up.

    $pqfinal=0
    $pq="select * from cmp where cid='$cid'";
    $pqresult = mysql_query ($pq);

    while($pqlist = mysql_fetch_array($pqresult)) {

    $q="select * from cmp where pid='$pqlist[pmid]'";
    $results = mysql_query ($q);
    $pqcount = mysql_num_rows($results); //I have multiple rows from these results I want to add up
    the rows from these results.
    echo $pqlist['pmid']." = have ".$pqcount." rows<br>\n";
    $pqfinal=$pqfinal+$pqcount;

    }
    echo "<br>total ".$pqfinal." rows";

    My problem was, if the results have 2 same name how can I count as 1 results (mean only 7 rows instead of 14 rows based on the example}

    name1 = have 7 rows
    name1 = have 7 rows
    name3 = have 6 rows
    name4 = have 9 rows

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    may i ask why you are doing the counting in php and not in sql?
    Code:
    select count(*) as finalcount
      from cmp 
     where cid='$cid' or pid='$pqlist[pmid]'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    5
    Oh that's because I am getting the results from 2 different tables.

    Is this correct?

    $query=select count(*) as pqfinal from cmp where pid='$pqlist[pmid]'


    I don't need $cid anymore.

    Something like if $pqlist[pmid] = $pqlist[pmid] than count as one... but how?


    results:

    name is from $pqlist[pmid]

    name1 = have 7 rows
    name1 = have 7 rows
    name3 = have 6 rows
    name4 = have 9 rows
    Last edited by coolman; 02-02-04 at 13:50.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select count(*) as pqfinal from cmp where pid='$pqlist[pmid]'

    this will return exactly 1 row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Posts
    5
    Only return 1 row? I have many results from the the fetch,

    examples:

    name1 = have 7 rows
    name1 = have 7 rows
    name3 = have 6 rows
    name4 = have 9 rows


    name3 and name 4 have to count also not just name 1, but name 1 only count as one.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if $pqlist[pmid] is one value, then select count(*) will return 1 row

    if $pqlist[pmid] is multiple values, then you cannot say pid=$pqlist[pmid], you have to say pid IN ($pqlist[pmid]), and even then, select count(*) returns only 1 row

    perhaps you could show the query that is returning multiple rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •