mysql database structure (unique id)

Question

I think most people had serious problems with inserting a value/data into a database (mysql). When I'm inserting a value into a database, I assign an unique id (INT) for that line. When I query the database, easily I can read/modify/delete that line.

With function for() (in php) I easily can read values/data from the database. The problem occurs when I delete a line (in the middle for example).

E.g:

DB:

ID | column1 | column2 | ... | columnN
--------------------------------------
1 | abcdefgh | asdasda | ... | asdasdN
2 | asdasddd | asdasda | ... | asdasdN
...
N | asdewfew | asddsad | ... | asddsaN

php:

for($i = 0; $i <= $n; $i++){
   $sql = mysql_query("SELECT * FROM db WHERE ID = '$i' ");
   //Code;
}

*$n = last column value from ID

---

Am I need to reorganize the entire database to have a correct "flow" (1, 2, 3, .. n)? Or am I need to UPDATE the each cell?

Answer 1

What you're doing here is unnecessary thanks to AUTO_INCREMENT in mysql. Run this command from PhpMyAdmin (or another DB management system):

ALTER TABLE db MODIFY COLUMN ID INT NOT NULL AUTO_INCREMENT;

Now when insert a row into db mysql will assign the ID for you:

INSERT INTO db (id, column1, column2) VALUES(NULL, 'abc', 'def');

SELECT * FROM db;:

+--+-------+-------+
|id|column1|column2|
+--+-------+-------+
|1 |old    |oldrow |
+--+-------+-------+
|2 |abc    |def    | <--- Your newly inserted row, with unique ID
+--+-------+-------+

If you delete a row, it is true that there will be an inconsistency in the order of the ID's, but this is ok. IDs are not intended to denote the numeric position of a row in a table. They are intended to uniquely identify each row so that you can perform actions on it and reference it from other tables with foreign keys.

---

Also if you need to grab a group of ID's (stored in an array, for example), it is much more efficient to perform one query with an IN statement.

$ids = array(1,2,3,4,5,6);
$in = implode(',', $ids);
mysql_query('SELECT * FROM db WHERE id IN ('.$in.')');

However, if you want all rows just use:

SELECT * FROM dbs;

But be weary of bobby tables.

Answer 2

You can select all the rows with query

SELECT * FROM db

and do whatever you want after

Answer 3

You can never ensure, that the ids are continous. As you noticed yourself there are gaps after you delete a row, but even for example when you insert rows within a transaction and don't commit it, because something failed and you need to revert the transaction. An ID is an identifier and not row number.

For example if you want to select X items from somewhere (or such) have a look at LIMIT and OFFSET

SELECT * FROM mytable ORDER BY created DESC LIMIT 10 OFFSET 20;

This selects the rows 21 to 30 ordered by their creation time. Note, that I don't use id for ordering, but you cannot rely on it (as mentioned).

If you really want to fetch rows by their ID you definitely need to fetch the IDs first. You may also fetch a range of IDs like

SELECT * FROM mytable WHERE id IN (1,2,3,4);

But don't assume, that you will ever receive 4 rows.

Answer 4

Ids are surrogate keys–they are in no way derived from the data in the rest of the columns and their only significance is each row has a unique one. There's no need to change them.

If you need a specific range of rows from the table, use BETWEEN to specify them in the query:

SELECT id, col1, col2, ..., coln
  FROM `table`
  WHERE id BETWEEN ? AND ?
  ORDER BY id

Answer 5

You do not have to reorganize the entire database in order to keep the index. But if you feel like it, you'd have to update each cell.

BTW, look at mysql_fetch_array(), it will ease the load on the SQL server.

Answer 6

If you need all the rows and all columns for that table use:

$query = mysql_query("SELECT * FROM table_name");

and then loop through each row with a while statement:

while ($row = mysql_fetch_array($query )) 
{
       echo $row['column_name'];
}