How to put the output query from mySQL to php int variable

Question

I want to do a query to get the last id (int) in a table to create a new row with that last id + 1 but actually this just put all rows with the same id

my code:

<?php
$servername = "localhost";
$user = "root";
$pass = "dbpass";
$dbname = "site";
$mail = $_POST['mail'];
$password = $_POST['password'];

// Create connection
$conn = mysqli_connect($servername, $user, $pass, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sqlID = "SELECT MAX(id) FROM `login`;";

if ($result = mysqli_query($conn, $sqlID)) {
    $id = mysqli_fetch_row($result);
}

settype($id, "int");
$id = $id + 1;


$sql = "INSERT INTO login (`id`,`mail`,`password`)
             VALUES ('".$id."','".$mail."','".$password."');";



if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);

?>

WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST or $_GET data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Aug 30, 2016 at 15:31
Make id AUTO_INCREMENT. What you're doing here is going to cause severe trouble when you run into a race condition and have an id conflict. From the perspective of the computer a tiny eternity passes between getting your MAX(id) value and doing the insert. — tadman
– tadman, Commented Aug 30, 2016 at 15:32

Davide Visentin · Accepted Answer · 2016-08-30 13:51:59Z

1

mysqli_fetch_row returns always an array, also if there is only 1 element. So the MAX(id) in in $row[0].

Fixing this, you also don't need to use settype.

answered Aug 30, 2016 at 13:51

Davide Visentin

7516 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tadman Over a year ago

PHP doesn't care about a lot of things, especially the type of a variable. It will auto-convert as necessary.

The One and Only ChemistryBlob · Accepted Answer · 2016-08-30 13:54:15Z

0

If your id is autoincrement, change this:

$sql = "INSERT INTO login (`id`,`mail`,`password`)
         VALUES ('".$id."','".$mail."','".$password."');";

to:

$sql = "INSERT INTO login (`mail`,`password`)
         VALUES ('".$mail."','".$password."');";

Then get rid of all code from $sqlID to $id + 1; (for tidyness)

answered Aug 30, 2016 at 13:54

The One and Only ChemistryBlob

7,90211 gold badges41 silver badges76 bronze badges

Collectives™ on Stack Overflow

How to put the output query from mySQL to php int variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related