get values from database and work on them

Question

<?php
include('connection.php');

/* code for id goes here */

$q2= "select MAX(id) from usertable";   
echo mysqli_query($sql,$q2);

$name=$_POST['name'];
$username=$_POST['usrname'];
$password=$_POST['psw'];
$dob=$_POST['date'];
$query="insert into usertable(name, username, password, dateofbirth) values('$name', '$username','$password','$dob')";
if(mysqli_query($sql,$query))
{

    echo "Registered successfully";
}
?>

This is my insert command to register a user in my database. However the database contains a column named id which is the primary key. How do I fetch the id before executing the insert query so that it fetches the last id and increments it by 1 and inserts it in the db along with the other data. The id is numeric and I want the program to perform the operation itself rather than the user entering the data of the id. Please help.

Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, May 01 '18 at 14:33
This already looks unsafe to be used in a live environment. I hope you realize that. — Funk Forty Niner, May 01 '18 at 14:34
You dont need to do ANYTHING with the `id` column if it is defined as **AUTO INCREMENT** key. MySQL looks after that all for you — RiggsFolly, May 01 '18 at 14:34

score 1 · Answer 1 · answered May 01 '18 at 14:49

1

You need to set your id field to auto incement and your database will do it automatically:

ALTER TABLE usertable MODIFY COLUMN id INT auto_increment;

answered May 01 '18 at 14:49

Antti A

410
4
12

score -1 · Answer 2 · edited May 01 '18 at 14:43

-1

You need to write a query

SELECT MAX(id) FROM usertable

and get the result from that and then increment that value by 1 and then set that value in id field.

edited May 01 '18 at 14:43

Bentaye

9,403
5
32
45

answered May 01 '18 at 14:35

Anil Yadav

11
5

Please review the updated code. it is generating the following error Object of class mysqli_result could not be converted to string. – Priyanka Banerjee May 01 '18 at 15:51
You can use following code : $rs= mysqli_query($con,"select MAX(id) as max_id from users"); $row =mysqli_fetch_row($rs); $max_id = $row[0]; $max_id++; $query="insert into usertable(id,name, username, password, dateofbirth) values('$max_id','$name', '$username','$password','$dob')"; – Anil Yadav May 02 '18 at 06:32

get values from database and work on them

2 Answers2