Hi,
I'm trying to connect to a database using MySQL. Very new at this.
I already have an existing table consisting of 2 rows created using phpMyAdmin.
The problem (not exactly) I'm facing is when i insert a new row, it seems to insert after the 2nd row, not at the bottom.
By right, i should have:
1
2
3
4
5
but i get:
1
2 < -- seems to insert after this.
5
4
3
Although I can sort the table, i just want to know why this is happening.
Here's the code:

$link = mysql_connect('localhost', '', '');
$db_list = mysql_list_dbs($link);
mysql_select_db('test');
$result = mysql_query('SELECT * FROM myTable);

if(isset($_POST['submit']) && $_POST["submit"] == "Insert"){
  $rowCount = mysql_num_rows($result)+1;
  $sql = "Insert into myTable (id, Name, Gender, Age, Year)
  values ($rowCount, 'John', 'Male', 12, 1999)";
  mysql_query($sql);
  header("location:data.php");
}
echo printResult($result);
mysql_free_result($result);
mysql_close($link);

Thanks.

$link = mysql_connect('localhost', '', '');
$db_list = mysql_list_dbs($link);
mysql_select_db('test');
$result = mysql_query('SELECT * FROM myTable);

if(isset($_POST['submit']) && $_POST["submit"] == "Insert"){
  $rowCount = mysql_num_rows($result)+1;
  $sql = "Insert into myTable (id, Name, Gender, Age, Year)
  values ($rowCount, 'John', 'Male', 12, 1999)";
  mysql_query($sql);
  header("location:data.php");
}
echo printResult($result);
mysql_free_result($result);
mysql_close($link);

It could be caused by $rowCount = mysql_num_rows($result)+1;
Because it is counted to 2 before it does insertion.

Hello qazs,

Do you use the option AUTO_INCREMENT for the id column ? Perhaps that he can explain the result ...

The option AUTO_INCREMENT is very nice. When you use it isn't necessary to count the number of rows, an counter increment automaticaly.

fpepito


example:
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT

here your code with modification:

$link = mysql_connect('localhost', '', '');
$db_list = mysql_list_dbs($link);
mysql_select_db('test');

// $result = mysql_query('SELECT * FROM myTable);

if(isset($_POST['submit']) && $_POST["submit"] == "Insert"){
  // $rowCount = mysql_num_rows($result)+1;
  $sql = "Insert into myTable (id, Name, Gender, Age, Year)
  values (NULL, 'John', 'Male', 12, 1999)";
  mysql_query($sql);
  header("location:data.php");
}
echo printResult($result);
mysql_free_result($result);
mysql_close($link);

Thanks fpepito.
Didnt know there's this thing called AUTO_INCREMENT.
It works the way I want now.

Hey, maybe I replied too fast. Just found out something.
When I used the AUTO_INCREMENT feature, the value just keeps adding,
even after I delete a row.
For example, i have:

1
2
3
4

And I delete away row 4, so its left with:

1
2
3

But after adding a new row, I get:

1
2
3
5

So I think I still have to check for the number of rows... and that sets me
back to my prev problem....

Um, when you say the list is shown as:

1
2 < -- seems to insert after this.
5
4
3

Do you mean this is what you see in phpMyAdmin? If so, then that is technically how MySQL works, normally; the data can come in any random order (but I'm not certain). It happens to me sometimes - unless you specify a sort method, then don't expect the data to be in an order that you want it to be.

Why not alter the table and make id auto_increment as mentioned above.

ALTER TABLE myTable MODIFY id NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (id);

That way you can use MySQL features and save CPU and boring code writing doing it manually :)

This article has been dead for over six months. Start a new discussion instead.