Rows not added correctly when using 'INSERT'
 

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.

Re: Rows not added correctly when using 'INSERT'
 

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

Re: Rows not added correctly when using 'INSERT'
 

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]

Re: Rows not added correctly when using 'INSERT'
 

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

Re: Rows not added correctly when using 'INSERT'
 

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

Re: Rows not added correctly when using 'INSERT'
 

WHy do you need the number of rows?

Re: Rows not added correctly when using 'INSERT'
 

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.

Re: Rows not added correctly when using 'INSERT'
 

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 :)