CODEWITHSUNDEEP
phpmysql
Dariko77
Dariko77

Reputation: 141

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

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

?>

Upvotes: 2

Views: 64

Answers (2)

The One and Only ChemistryBlob
The One and Only ChemistryBlob

Reputation: 7884

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)

Upvotes: 0

Davide Visentin
Davide Visentin

Reputation: 741

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.

Upvotes: 1

Related Questions