CODEWITHSUNDEEP
phpmysqlsqldatabaseinsert
Bart Bos
Bart Bos

Reputation: 11

MySQL affected rows -1 when inserting data into database

I want to create a simple piece of code that will put data into the database form a PHP script, everything works fine except putting the data into the database! (I am running a server with PHP7)

The output of the affected rows shows -1 (strange), I double checked my code, compared it with others, tried searching for a common issue on the internet, even tried on a local server with no avail.

You can see it here:
https://leer.bosvision.nl/register.php

My code:

<?php

$conn = mysqli_connect("localhost", "-user-", "-pass-", "-db-");

if(!$conn) {
    $msg = die('connection error');

} else {

    $msg = 'Connection success.';
}

echo $msg;

?>

<?php

$query = 'INSERT INTO users_two (ID, username, password) VALUES (1, gfd, gfd)';

if(mysqli_query($conn, $query)) {
    $result = 'Data saved';
} else {
    $result = 'No data saved';
}

$affected = mysqli_affected_rows($conn);

    echo $result . '.' . ' Affected rows: ' . $affected;

?>

Upvotes: 0

Views: 1825

Answers (4)

user2103237
user2103237

Reputation: 69

Read this page for more info: PHP insert statement

$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', '[email protected]')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Normally you shouldn't be inserting an ID yourself because it should be auto increment.

Upvotes: 0

Andrew Arscott
Andrew Arscott

Reputation: 176

<?php

	$conn = mysqli_connect("localhost", "-user-", "-pass-", "-db-");

if(!$conn) {
	$msg = die('connection error');
	
} else {
	
	$msg = 'Connection success.';
}

echo $msg;
	
?>

	
	
<?php
	
$query = "INSERT INTO users_two (username, password) VALUES ('gfd', 'gfd')";
	
if($result= mysqli_query($conn, $query)) {
	$result = 'Data saved';
} else {
	$result = 'No data saved';
}
	
$affected = mysqli_affected_rows($conn);
	
	echo $result . '.' . ' Affected rows: ' . $affected;
	
?>

One assumes ID is auto increment, so that doesn't need to be in there, or is it not and the issue you are encountering is that its a duplicate entry for key. Also you need to wrap your var data in ' '

I would guess that this is an SQL issue. Can you run your query directly on your database? That would give you the error.

Upvotes: 0

Onifade Oluwaseyi
Onifade Oluwaseyi

Reputation: 1

try adding quotes to the string values, as in:

"INSERT INTO users_two (ID, username, password) VALUES (1, 'gfd', 'gfd')"

Upvotes: -2

Mureinik
Mureinik

Reputation: 311808

To quote the documentation:

-1 indicates that the query returned an error.

And your insert statement indeed errors out, since you don't have a gfd column. If you meant to use that as a value, it should be surrounded by single quotes:

$query = "INSERT INTO users_two (ID, username, password) VALUES (1, 'gfd', 'gfd')";
# Here -------------------------------------------------------------^---^--^---^

Upvotes: 4

Related Questions