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.

qazs

Newbie Poster

7 posts since Oct 2004

Reputation Points: 10

Solved Threads: 0

7 Years Ago

0

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

PoA

Posting Whiz in Training

237 posts since Jul 2004

Reputation Points: 26

Solved Threads: 9

7 Years Ago

0

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]

fpepito

Newbie Poster

14 posts since Oct 2004

Reputation Points: 10

Solved Threads: 1

7 Years Ago

0

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

qazs

Newbie Poster

7 posts since Oct 2004

Reputation Points: 10

Solved Threads: 0

7 Years Ago

0

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

qazs

Newbie Poster

7 posts since Oct 2004

Reputation Points: 10

Solved Threads: 0

7 Years Ago

0

WHy do you need the number of rows?

Roberdin

Supreme Evil Overlord

Team Colleague

282 posts since Feb 2003

Reputation Points: 63

Solved Threads: 6

7 Years Ago

0

Um, when you say the list is shown as:
1
2 < -- seems to insert after this.
5
4
3Do 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.

Gary King

PHP/vBulletin Guru

Team Colleague

417 posts since Nov 2003

Reputation Points: 53

Solved Threads: 5

7 Years Ago

0

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

ReDuX