Inserting two values from one variable into database

Question

I've got two different tables 'user' and 'candidates' and I'm trying to add values from one table to another by user selection.

This is the code displaying 'name' and 'surname' rows from 'user' table:

    echo "<select class='form-control custom-select' name='candid'>";
     while ($row = $result->fetch_assoc())
     {
      $candid = $row['name'] .' '. $row['surname'];
      echo "<option value='.$candid.'>".$candid."</option>";
     }
     echo "</select>";

I concatenated two columns and assigned their values to $candid variable. Now I need to add these values to the 'name' and 'surname' columns in 'candidates' table. I also tried other version of this code:

     while ($row = $result->fetch_assoc())
     {
      echo "<option value='candid'>".$row['name'] .' '. $row['surname']."</option>";
     }

With this SQL query:

    $sql = ("INSERT INTO candidates (name, surname) VALUES ('$row[name]', '$row[surname]')");
      
    if ($conn->query($sql) === TRUE)
    {
        echo "New record created successfully";
    }
    else
    {
        // $conn->error only for testing purposes
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

But it doesn't work, so there is my question. Is it possible to do this using only PHP and SQL?

It is a very bad idea to use `$conn->error` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Jun 30 '20 at 18:09
If you're trying to use user input, you should be using `$_POST` variables. `$row` was used when you were creating the HTML, it's not available when the user submits the form. — Barmar, Jun 30 '20 at 18:10
@Barmar Yes, I know that, it works fine with tag but I need to use it somehow with — Miki, Jun 30 '20 at 18:17

score 1 · Answer 1 · answered Jun 30 '20 at 18:14

1

When the user submits the form, the selected value is in $_POST['candid']. You can split that at the space and then insert into the table.

list($name, $surname) = explode(' ', $_POST['candid'], 2);
$stmt = $conn->prepare("INSERT INTO candidates (name, surname) VALUES(?, ?)");
$stmt->bind_param("ss", $name, $surname);
if ($stmt->execute()) {
    echo "New record created successfully";
} else {
    echo "Record not created";
    error_log("candidate record not created: $conn->error");
}

answered Jun 30 '20 at 18:14

Barmar

741,623
53
500
612

I tried your solution with various changes in my code but it doesn't seem to work. All I get is this error message: Notice: Undefined index: candid in D:\xampp\htdocs\adminlte\scripts\add_candidate.php on line 19 Notice: Undefined offset: 1 in D:\xampp\htdocs\adminlte\scripts\add_candidate.php on line 19 Line 19 in my code: list($name, $surname) = explode(' ', $_POST['candid'], 2); – Miki Jun 30 '20 at 18:55
This code should only run when you submit the form. It should be inside something like `if(isset($_POST['submit']))` -- replace `submit` with the name of the form's submit button. This is standard PHP form processing, you should read a tutorial. – Barmar Jun 30 '20 at 18:57

Inserting two values from one variable into database

1 Answers1