[PHP-DB] Re: Auto Increment Problems....
[straying OT, but...] > Instead of incrementing to find the next row to count them, you dont have to > set the ID if it is auto increment. MySQL will do it for you (and i think it > might fill the holes too). Also, to get the num. of rows just do this - > > $get_rows = mysql_query("SELECT * FROM `table`"); > $num_rows = mysql_num_rows($get_rows); Oh my, don't do that! If you only want to get the number of rows in a table, don't bog down the MySQL server by doing a 'SELECT *' on it. This could be a *ton* of data... Why not use MySQL's built-in counting functionality: $result = mysql_query('SELECT count(*) AS count FROM table'); $count_arr = mysql_fetch_assoc($result); $count = $count_arr['count']; MySQL will be able to simply count rows a lot faster than actually SELECTing all of the data. Of course, if you're counting rows just to find out what the next value for an ID is, then, as you already said, you're doing the wrong thing. Joel -- [ joel boonstra | [EMAIL PROTECTED] ] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Auto Increment Problems....
Instead of incrementing to find the next row to count them, you dont have to set the ID if it is auto increment. MySQL will do it for you (and i think it might fill the holes too). Also, to get the num. of rows just do this - $get_rows = mysql_query("SELECT * FROM `table`"); $num_rows = mysql_num_rows($get_rows); "Georgie Casey" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > rite, > > my primary key column ("id") is set to auto_increment as usual which is very > handy. But when I delete a row, the auto_increment just keeps incrementing > and there's this 'hole' left where I deleted the row! > > Apart from this looking ugly, it poses another problem. In my PHP script > where I can add new rows, I query the table, checking how many rows in the > table altogether and set the new id as the next number, but this doesnt work > if theres 'holes' in the id field, as the new record tries to overwrite > another id. > > So I've 2 questions > 1) Can the next auto_increment value be 'set' by a SQL query > 2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID > column?? > > TIA > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Auto Increment Problems....
Firstly, don't cross post unless the question *really* has to do with both (in this case, it should be just db). Secondly, your id field should only be used as a reference to a row (not showing order of record). Auto increments are exactly that, the rdbms will take care of creating the increment. eg. you have this table tblName ID | field1 | field2 Your insert sql should be something like this: INSERT INTO tblName VALUES ('','value1', 'value2') OR INSERT INTO tblName SET field1 = 'value1', field2 = 'value2' The reason why the 'hole' is there, is to maintain data integrity. Say you have two tables and they relate to each other (through the id). If you delete a record from one and it relates to something in the other table, if you add a new record using the old id, it will join with the second table, when it shouldn't. Confusing? yeah its just cause i can't explain it right. If you want to use numbering for your records, create it dynamically when you display the data. Adam --- Original Message --- rite, my primary key column ("id") is set to auto_increment as usual which is very handy. But when I delete a row, the auto_increment just keeps incrementing and there's this 'hole' left where I deleted the row! Apart from this looking ugly, it poses another problem. In my PHP script where I can add new rows, I query the table, checking how many rows in the table altogether and set the new id as the next number, but this doesnt work if theres 'holes' in the id field, as the new record tries to overwrite another id. So I've 2 questions 1) Can the next auto_increment value be 'set' by a SQL query 2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID column?? TIA