Is there any reason, why the query in my php script doesnt work?

Question

I got the instruction to make a database in mysql which stores data from co-workers. The database works perfectly. I made the table which reads the database and theres no problem. However i want to go on and make the formular now.

$sql = "INSERT INTO personaldaten (id, pid, user_status, ue_vorname, ue_nachname, ue_position, ue_firma) VALUES ('3', '$pnr', '1', '$vn', '$nn', '$pst', '$firma')";

This is the query i am using, i have looked for a long time but i haven't found anything thats wrong. Maybe its in the other code?

$db = new mysqli('XXXXX', 'XXXXX', 'XXXXX', 'XXXXX');

    if($db->connect_error){
      die('Connect Error('.$db->connect_error.')'.$db->connect_error);
    }

    $sql = "INSERT INTO personaldaten (id, pid, user_status, ue_vorname, ue_nachname, ue_position, ue_firma) VALUES ('3', '$pnr', '1', '$vn', '$nn', '$pst', '$firma')";

    if (mysqli_query($db, $sql)) {
      echo "Erfolg!";
    } else {
      echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }

  mysqli_close($db);

Would be glad if someone could help :D

UPDATE:

The Error i get is:

Duplicate entry '3-123456' for key 'PRIMARY'

What's the error you get? Does an entry with `id=3` already exist? — brombeer, Jul 12 '19 at 07:26
The error which shows up on the php then ist just de $sql... Error: INSERT INTO personaldaten (id, pid, user_status, ue_vorname, ue_nachname, ue_position, ue_firma) VALUES ('3', '123456', '1', 'Max', 'Mustermann', 'Musterposition', 'Musterfirma') — kevwpl, Jul 12 '19 at 07:27
your code is wide open to **SQL injection attacks** - use parameterised statements to secure your data, and possibly solve your problem on the side. — Franz Gleichmann, Jul 12 '19 at 07:28
A duplicate entry already exists in your database. Either clear your database or remove the `id` parts from the query. Your `id` should be set to autoincrement, so every new entry gets a "unique" value not used yet. — brombeer, Jul 12 '19 at 07:32
It might be helpful to also include the code where you create the form. On another note your code is vulnerable to [SQL Injections](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Rain336, Jul 12 '19 at 07:33

score 0 · Answer 1 · answered Jul 12 '19 at 07:31

Change your code

 $sql = "INSERT INTO personaldaten (id, pid, user_status, ue_vorname, ue_nachname, ue_position, ue_firma) VALUES ('3', '$pnr', '1', '$vn', '$nn', '$pst', '$firma')";

to

 $sql = "INSERT INTO personaldaten ( pid, user_status, ue_vorname, ue_nachname, ue_position, ue_firma) VALUES ( '$pnr', '1', '$vn', '$nn', '$pst', '$firma')";

score 0 · Accepted Answer · answered Jul 12 '19 at 07:31

0

Remove ID from your query and should be fine.

$sql = "INSERT INTO personaldaten (pid, user_status, ue_vorname, ue_nachname, ue_position, ue_firma) VALUES ('$pnr', '1', '$vn', '$nn', '$pst', '$firma')";

answered Jul 12 '19 at 07:31

TOMBA

205
1
11

That's only if the **id** column is set to a type of integer and auto increment – Kuya Jul 12 '19 at 07:35

score 0 · Answer 3 · answered Jul 12 '19 at 07:37

$sql = "INSERT INTO personaldaten (id, pid, user_status, ue_vorname, ue_nachname, ue_position, ue_firma) VALUES ('3', '$pnr', '1', '$vn', '$nn', '$pst', '$firma')";

if id is auto increment in your Db then don't send it, it will add auto matically. It will handle by DB.

If not, then send every time incremental value. It will not be repeated value.

score 0 · Answer 4 · answered Jul 12 '19 at 10:49

If your column id in the table is set to auto-increment then you can omit it when inserting the data. The next ID will be generated for you.

Here is how your code should look like. I have also removed the SQL injection bug for you.

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli('XXXXX', 'XXXXX', 'XXXXX', 'XXXXX');

$sql = "INSERT INTO personaldaten (pid, user_status, ue_vorname, ue_nachname, ue_position, ue_firma) VALUES (?, 1, ?, ?, ?, ?)";
$stmt = $db->prepare($sql);
$stmt->bind_param('sssss', $pnr, $vn, $nn, $pst, $firma);
$stmt->execute();

You do not need to display mysqli errors if you use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

You do not need to close the connection either.

Is there any reason, why the query in my php script doesnt work?

4 Answers4