SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    Nov 2003
    Location
    United Kingdom
    Posts
    2,118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to count the number of mysql records that have the same title?

    Hi,

    I am working on a project and have become very stuck at a counting query using mysql.

    What I have is a column in my MySQL database that has a few duplicate records. Well I would like to count ever row so that say a row is not duplicated then it will count it as 1 and if a row is duplicated 2 then it will count it as 2 and if a row is duplicated 3 times then it will count it has 3 and so on.

    I won't to do this as I have a number of listings and I won't to put figures by them so people know how many of the same records in a certain column are in my database.

    Could somebody please help me on this one.

    So far I have something like the following, but it is just showing the number 1 by every listing when I know that I have a few duplicates and they are still giving the figure of 1.

    What I have so far is:

    $com=mysql_query("select COUNT(id) from cash where Title like '$compk'");$comfig=@mysql_num_rows($com);

    I hope that somebody can help me.

    Thanks!

  2. #2
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You were very nearly there.
    Try this

    PHP Code:
      $sql 'SELECT'
           
    ' COUNT(*) as total'
           
    ' FROM '
           
    ' cash'
           
    ' WHERE'
           
    ' Title like \'' $compk '\''
           
    ;
      
      
    /* ***** START DIAGNOSTIC ***** */
      
    echo $sql;
      
    /* ***** END DIAGNOSTIC ***** */
      
      
    $com mysql_query($sql) or die ('Failed to execute ' $sql ' due to ' mysql_error());
      
    $duplicate mysql_fetch_result($com0);
      
      if (
    == $duplicate)
      {
        echo 
    'No titles found for ' $compk;
      }
      elseif (
    == $duplicate)
      {
        echo 
    '1 title found for ' $compk;
      }
      else
      {
        echo 
    $duplicate ' titles found for ' $compk;
      } 
    Last edited by swdev; Jan 29, 2005 at 05:15. Reason: typo

  3. #3
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you mean duplicate entry in one column only? I'm not sure about a whole row but
    try this:

    SELECT <column you want to check>,COUNT(<column you want to check>) AS duplicates FROM cash GROUP BY <column you want to check>

    That would return rows with the each value of that column and the number of times it appears

  4. #4
    SitePoint Wizard
    Join Date
    Nov 2003
    Location
    United Kingdom
    Posts
    2,118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I have tried both of the examples, but neither of them worked.

    The one that gavwvin gave me only seemed to had given me the amount of none duplicate rows.

    Could someone please help me on this as I am having problems trying to do it.

    Thanks!

  5. #5
    SitePoint Wizard
    Join Date
    Nov 2003
    Location
    United Kingdom
    Posts
    2,118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK!

    I want to try something else now. I have done the following:

    PHP Code:
    $sql mysql_query("select Record, count(*) as Num from cars group by Record having count(*) > 1");
    $list mysql_num_rows($sql);

    while (
    $i $list) {$linesmysql_fetch_array($sql);
    $record $lines["Record"];{
    echo 
    "$record<br>";
    $i++;}} 
    The above lists all of the duplicate rows in one column of all the rows in my database, but I can't seem to get it to list how many duplicate records there are, which are meant to be shown by each listing.

    Could someone please take a look and see why it's not doing this.

    Thanks!

  6. #6
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how about showing us the schema of the table?

    and maybe like the first 5-10 rows...

  7. #7
    SitePoint Wizard
    Join Date
    Nov 2003
    Location
    United Kingdom
    Posts
    2,118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK!

    At last, it seems as if I may be getting somewhere. I can now get the count to work with a column that I don't want to use for this. This column is BIGINT(21) in the mysql database. But when I try actual column that I want it to count, which is TEXT and has a FULLTEXT indxe in the mysql database, it doesn't want to work. So I was just wondering if I have to change this column to something else. If I have to then I need it to be quite long and also have word capability.

    Could someone please help me here

    Thanks!

  8. #8
    SitePoint Wizard
    Join Date
    Nov 2003
    Location
    United Kingdom
    Posts
    2,118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi All,

    Thanks for all of your support.

    It now works and it was nothing to do with the Bigint and Text within the mysql database. I had forgot to change something within the mysql query when I was changing it from the Records colum to the Title Column.

    Thanks all again.


Bookmarks

Posting Permissions

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