How to use count in php?

Question

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?

`$nors = mysql_query($no);` this doesn't return the actual count number of your query — ingvar harjaks, May 04 '12 at 16:17
You might want to use something else besides md5 for storing your user passwords. Its very insecure. Also your code is vulnerable to SQL injections — CountMurphy, May 04 '12 at 16:17
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. — andrewsi, May 04 '12 at 16:18
@andrewsi Add that as an answer. I totally missed that and it's hugely important. — ceejayoz, May 04 '12 at 16:19
@CountMurphy yup, I know that md5 is insecure. I'm noob in php so I just make localhost to practice before real hosting :) — greenthunder, May 04 '12 at 16:26

score 6 · Accepted Answer · answered May 04 '12 at 16:16

6

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.

answered May 04 '12 at 16:16

ceejayoz

176,543
40
303
368

Also it's recommended for id to be a primary key and insert like this: `"INSERT INTO member(User, Password, Address, Phone, Gender, Email) VALUES ('".$user."','".md5($pass)."','".$name."','".$addr."',".$hp.",'".$gender."','".$email"')"` – Ahmed Jolani May 04 '12 at 16:19
I've just learned php from yesterday, sorry if my code went to simple or old.. I'm trying to build up my mind. What should I do with nors then when I want to make autonumber? Should I $nors +=1; under $nors = $row->count; or just put $nors into insert? – greenthunder May 04 '12 at 16:23
Don't generate `$nors` yourself. Let MySQL do it for you. No code needed, and it solves many problems your current approach. See @andrewsi's answer. – ceejayoz May 04 '12 at 16:25
**as mysql_* functions have been deprecated** where did you learn this? – Lawrence Cherone May 04 '12 at 16:26
@ceejayoz what should I write then in insert? $query = "INSERT INTO member VALUES ("'".$user."','".md5($pass)."','".$nama."','".$alamat."',".$hp.",'".$gender."','".$email."')" like that? – greenthunder May 04 '12 at 16:31
@greenthunder use my comment (the first comment here). – Ahmed Jolani May 04 '12 at 16:32
@AhmedJolani ID is the primary key and auto increment. I used both your code and ceejayoz. But why in my database after ID 1, it's jump into 7 not 2? – greenthunder May 04 '12 at 16:39
@ceejayoz I'm seconding Lawrence's comment here. The mysql_* functions HAVE NOT been deprecated. It has been discussed, and certain functions (i.e. `mysql_db_query`) have been deprecated, but the extension remains supported. – Ben D May 04 '12 at 16:43
@BenD really mysql_query deprecated? what best I use then? – greenthunder May 04 '12 at 16:46
Thanks all, it works. the number jump into 7, because I deleted previous data so it continue numbering from the last deleted ID – greenthunder May 04 '12 at 16:47
@greenthunder no, mysql_query is NOT deprecated (ceejayoz suggested that is was). However, it's not considered best practice. mysqli and PDO are generally preferred. – Ben D May 04 '12 at 16:49

score 4 · Answer 2 · answered May 04 '12 at 16:21

4

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

answered May 04 '12 at 16:21

andrewsi

10,807
132
35
51

yes, I'm using autoincrement for ID. But I don't know how to use it anyway :( – greenthunder May 04 '12 at 16:29
@greenthunder Just don't insert anything for Id. The DB will do it for you. Very easy – CountMurphy May 04 '12 at 16:41
your id row in database should look like: id INT(11) unsigned NOT NULL auto_increment – debianek May 04 '12 at 16:43
@CountMurphy oooh I understand now. So I don't have to write ID again in insert query. thanks :) – greenthunder May 04 '12 at 16:48

score 2 · Answer 3 · answered May 04 '12 at 16:18

2

Replace this line

$nors = mysql_query($no);

By these lines :

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

answered May 04 '12 at 16:18

adrien

4,399
26
26

score 2 · Answer 4 · answered May 04 '12 at 16:21

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.

How to use count in php?

4 Answers4