CODEWITHSUNDEEP
phpmysqlsqlcountautonumber
greenthunder
greenthunder

Reputation: 727

How to use count in php?

I want to insert data to database. I have a table, named member that has 7 column (ID, User, Password, Address, Phone, Gender, Email). I used count to make auto number like this

$no = "SELECT COUNT(ID)FROM member";
$nors = mysql_query($no);
$nors = $nors + 1;
$query = "INSERT INTO member VALUES (".$nors.",'".$user."','".md5($pass)."','".$name."','".$addr."',".$hp.",'".$gender."','".$email."')";

Why, the result of nors is 6 not 2, although I only have 1 data?

Upvotes: 1

Views: 1158

Answers (4)

Jared
Jared

Reputation: 12524

If your id field is set to be an auto number you don't need to insert it. MySql will handle that for you. Anytime you add a new row the autonumber is incremented. If you delete a row the autonumber does not decrement.

If you currently only have 1 row but you've added and deleted rows then your insert will produce a row with an ID that is not consecutive.

Upvotes: 2

andrewsi
andrewsi

Reputation: 10732

If you're using this to generate the next ID number for a new member, you should look at making ID an auto_increment field instead - as it stands, it's possible that you'll get two members signing up at the same time, and both getting assigned the same ID

Upvotes: 4

adrien
adrien

Reputation: 4439

Replace this line

$nors = mysql_query($no);

By these lines :

$result_handler = mysql_query($no); 
$result = mysql_fetch_array($result_handler);
$nors = $result[0];

Upvotes: 2

ceejayoz
ceejayoz

Reputation: 180065

mysql_query returns a result object, not the value. Your query also lacks a needed space between COUNT(ID) and FROM...

$no = "SELECT COUNT(ID) AS count FROM member";
$result = mysql_query($no);
$row = mysql_fetch_object($result);
$nors = $row->count;

You should consider using something more modern like PDO, though, as mysql_* functions have been deprecated and will eventually go away entirely.

edit: @andrewsi noted in the comments that you really should be using MySQL's built-in auto increment functionality for IDs, anyways. Much better than what you're currently doing.

Upvotes: 6

Related Questions