SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2000
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am trying to count the number of records in a table. Below is what I was able to think of but it doesn't help me What am I supposed to do to get the result I would like to get?

    thank you

    The code:
    $query = "select count(*) from table_name";
    if ($howmany = mysql_query($query)) {
    echo("<P><b>table_name/b> $howmany</P>");
    }

    The result:
    table_name: Resource id #2

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In your example $howmany only points to a result identifier plus in your query you should probably give the count(*) function an alias it will make things easier. Also when using count(*) you use more processing power than necesary try just counting one filed like if you have an id field named ID or whatever replace the * with the name of the id field. To achieve what you want you should probably use something like:

    $result = mysql_query("select count(id) as totalnum from table_name");
    $howmany = mysql_result($result, 0, 0);
    print $howmany;

    Remember to replace id with a valid fieldname from your table, preferrably an indexed field
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Member
    Join Date
    Feb 2001
    Location
    Iowa
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could also do something like:

    $result = mysql_query( "select id from employees" );
    $num_rows = mysql_num_rows( $result );
    print $num_rows;

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Justin unfortunately that would be the slowest way to achieve this. First because you are retrieving all the id numbers then you are using another function to count up all those id numbers. It is a valid way of doing it just not thhe optimal. In my example, MySQL returns one number not all the ids like your method does, can you see how it would be much slower. And again your method uses mysql_num_rows() which counts up all records returned while my method simply grabs the one record returned which is the number of records in the table. Again your way does work but it is probably best to get into the habit early of coding for effiency and speed.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Member
    Join Date
    Feb 2001
    Location
    Iowa
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're absolutely right, I guess on a shared server that stuff is very important, MySQL syntax baffles me though so I usually take the easiest way out I can find .

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Helsingborg, Sweden
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When should I use the function mysql_result() in favor of mysql_fetch_row() and/or mysql_fetch_array()?
    "Some people play hard to get - I play hard to want."

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you are fetching one field from a query, for multiple fields use mysql_fetch_array() for multiple fields and multiple rows use while() in conjunction with mysql_fetch_array().

    Examples

    One field one row
    $result = mysql_query("SELECT COUNT(*) as totalnum from tablename");
    mysql_result($result, 0);

    multiple fields one row
    $result = mysql_query("SELECT * from tablename WHERE userid = '$userid'");
    $row = mysql_fetch_array($result);

    multiple fields multiple rows
    $result = mysql_query("SELECT * from tablename");
    while($row = mysql_fetch_array($result)) {

    }
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Helsingborg, Sweden
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, but why did you do "... as totalnum ..." and why did you do mysql_result( $result, 0, 0 ) the "first time" and mysql_result( $result, 0 ) the "second time"?
    "Some people play hard to get - I play hard to want."

  9. #9
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    because COUNT(*) is a function and needs an alias name to refer to the result of.

    mysql_result(result_id, row offset, field offset);

    So

    $result = mysql_query("SELECT COUNT(*) as totalnum from tablename");

    This would point to the first row and first field returned. you can leave the second 0 off optionally if you know only one field is being returned
    mysql_result($result, 0, 0);


    For this example
    mysql_result($result, 0);

    would produce the same result.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  10. #10
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Helsingborg, Sweden
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry if I sound like an idiot, but I still don't get it.


    What is the difference between

    $result = mysql_query( 'SELECT count(*) FROM users' );

    and

    $result = mysql_query( 'SELECT count(*) AS totalusers FROM users' );

    They both work.


    And what is the difference between using count(*) and count(somefield)?
    "Some people play hard to get - I play hard to want."

  11. #11
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is no difference in the two when using mysql_result() I just prefer to give my functions an alias , personal preference
    $result = mysql_query( 'SELECT count(*) FROM users' );
    $result = mysql_query( 'SELECT count(*) AS totalusers FROM users' );




    And what is the difference between using count(*) and count(somefield)?
    You probably should use count(somefield) as opposed to count(*) that way mysql only has to count one row and not all rows, if you use an indexed column its even faster.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  12. #12
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Helsingborg, Sweden
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, thanks. Now it's crystal clear!

    BTW. Do you know of any good tutorial on "high level" SQL (indexes, joins etc)? I only know very basic SQL.
    "Some people play hard to get - I play hard to want."

  13. #13
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There aren't too many out there. I usually just try stuff in MySQL and when I get stuck I hit the manual
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  14. #14
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about a whole book?

    Go to www.informit.com and register to gain access to their free library and search for Sams Teach Yourself SQL in 21 Days, Second Edition by Ryan Stephens.

    Also, this is a handy 21 page PDF tutorial:
    http://www.highcroft.com/highcroft/sql_intro.pdf
    (edited - I had the wrong link previously - oops )

    Caveat: I don't have much experience with MySQL, and from reading at these forums I've learnt that MySQL is not very ANSI SQL92 compliant. So a lot of things you might read in general SQL tutorials and books may not work in MySQL.
    Last edited by freakysid; Feb 9, 2001 at 08:54.


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
  •