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:
Thanks.

[PHP]
$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); [/PHP]

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:
[PHP]
$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);
[/PHP]

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....

WHy do you need the number of rows?

Um, when you say the list is shown as:
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