SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 33 of 33
  1. #26
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, it's me again.
    I've just noticed, that the ID isn't deleted when I delete someone from my list.

    For example when I delete the member with ID 5, the next member I add to my database has ID 6.

  2. #27
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you delete an row with an autoincrement value then the value isn't 're-used'.
    I.e if you have :
    1 fred
    2 barney
    3 bill
    4 john

    then delete bill, you'll end up with :
    1 fred
    2 barney
    4 john
    and the next one inserted will be, for example :
    5 harry

    That's expected behaviour and the autoincrement value shouldn't really be important to your code.

  3. #28
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by markl999
    When you delete an row with an autoincrement value then the value isn't 're-used'.
    I.e if you have :
    1 fred
    2 barney
    3 bill
    4 john

    then delete bill, you'll end up with :
    1 fred
    2 barney
    4 john
    and the next one inserted will be, for example :
    5 harry

    That's expected behaviour and the autoincrement value shouldn't really be important to your code.
    okay. But the one big disadvantage I see is, that you can't see how many entries there are in the table by the last given id.
    So is there a way to count the rows in a table and give out the value?

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    select count(*) from thetable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #30
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    select count(*) from thetable
    PHP Code:
    <?
    $allmembers 
    mysql_query("select count(*) from mitgliederdatenbank");
    ?>
    Derzeit sind <? print $allmembers ?> Mitglieder in der Datenbank eingetragen.
    What's wrong? It gives me "Resource id #4"

  6. #31
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't know php

    perhaps you need to refer to the column, in which case you should use an alias name:
    Code:
    select count(*) as Mitglieder 
      from mitgliederdatenbank
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #32
    SitePoint Enthusiast Rouven's Avatar
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mhm. doesn't work either.

    EDIT:

    Got it. the correct code is:

    PHP Code:
    <?php 
    $allmembers 
    mysql_query("select count(*) from mitgliederdatenbank"); 
    $allmembers mysql_fetch_array($allmembers); 
    $allmembers $allmembers[0]; 
    ?>
    Last edited by Rouven; Feb 23, 2004 at 09:19.

  8. #33
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    vta,ca,usa
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Rouven
    mhm. doesn't work either.

    EDIT:

    Got it. the correct code is:

    PHP Code:
    <?php 
    $allmembers 
    mysql_query("select count(*) from mitgliederdatenbank"); 
    $allmembers mysql_fetch_array($allmembers); 
    $allmembers $allmembers[0]; 
    ?>

    Right. You can't simply echo out the result of mysql_query(), that's the resource ID for the resultset between PHP & MySQL. In your case you can also use mysql_result() to grab the single record, e.g.
    PHP Code:
    $count_result mysql_query("select count(*) AS count from mitgliederdatenbank"); 
    $allmembers mysql_result($count_result0'count'); 
    You'll note I prefer to not reuse the variable over; to me anyway, makes the script alot more clear as to whats going on if you assign a seperate variable to the result than to your actual count value. I'm also using a MySQL alias 'count' to refer to the resulting data. Otherwise you'd have to refer to it as COUNT(*) and that's just messy.


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
  •