How to insert multiple values into a table in php

Question

I have learnt and been taught how to insert a single value into a column in a table but i cant get my head around inserting multiple values into multiple columns in the same table

I want to be able to add an id, first name, second name and email, can anyone help? please.

I have a php file to connect to the database along with the function to insert into the database.

db.php

<?php

function getSQLConnection() {
$mysqlConnection = new PDO('mysql:host=localhost;dbname=isad235_100000', "root", "");


return $mysqlConnection;
}

function insertSingleValue($tableName, $columnName, $value) {
$sql = 'INSERT into ' . $tableName . '(' . $columnName . ') VALUES(:Value)';

$mySqlConnection = getSQLConnection();
$sqlStatement = $mySqlConnection->prepare($sql);
$sqlStatement->bindValue(":Value", $value, PDO::PARAM_STR);

$bReturn = false;
try {
    $sqlStatement->execute();
    //if we get to here it's all worked.
    $bReturn = true;
} catch (PDOException $e) {
    echo $e->getMessage();
}
return $bReturn;
}

function getResults($tablename) {
$sql = "SELECT * FROM " . $tablename;

$mysqlConnection = getSQLConnection();

$ResultSet = $mysqlConnection->query($sql);
return $ResultSet;
}

  ?>

and a webpage file which gets the data and calls the function to insert.

index.php

<?php
include_once 'db.php';

if (isset($_POST['first_name'])) {
$success = insertSingleValue("members", "first_name", $_POST['first_name']);
if (!$success)
    echo 'Sorry, the insert failed';
}
$Results = getResults("members");
?>

 <!DOCTYPE html>
<html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title></title>
</head>
<body>

    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

        <label id="lblName" for="first_name">First Name</label>

        <input type="text" name="first_name" />

        <input type="submit" name="submit" value="Submit" />
    </form>
    <table border="1">
        <tr><th>Name</th>
        </tr>

<?php
if (isset($Results)) {
foreach ($Results as $row) {
    echo '<tr><td>';
    echo $row['first_name'];
    echo '</td></tr>';
    }
}
?>

    </tr>
</table>
</body>
</html>

@MarcB I know, but it's still not a bad place for beginners. Despite what they say, the coding information is not faulty. Just don't trust a certificate from there as W3 does not get into advanced mechanics or style of programming — SpYk3HH, Nov 27 '13 at 14:44

CompuChip · Accepted Answer · 2013-11-27T16:07:14.630

3

The syntax is

INSERT INTO table( col1, col2, col3, ..., coln ) VALUES( val1, val2, ..., valn)

to insert a single record. For example, use

$sql = 'INSERT INTO ' . $tableName . '( firstname, lastname, salary ) VALUES( :FirstName, :LastName, :Salary )';

as the SQL and then bindValue the values to the three parameters:

$mySqlConnection = getSQLConnection();
$sqlStatement = $mySqlConnection->prepare($sql);
$sqlStatement->bindValue(":FirstName", $firstname, PDO::PARAM_STR);
$sqlStatement->bindValue(":LastName", $lastname, PDO::PARAM_STR);
$sqlStatement->bindValue(":Salary", $salary, PDO::PARAM_STR);

$bReturn = false;

I will leave it up to you to get the right data in the function (e.g. instead of a single $column and $value you could pass in an array as a single parameter, like array( "firstName" => "Compu", "lastName" => "Chip", "salary" => 65000), and use that to construct a dynamic SQL statement.

edited Nov 27 '13 at 16:07

answered Nov 27 '13 at 14:44

CompuChip

9,143
4
24
48

Does this go into the function or the isset bit? – Bob Marks Nov 27 '13 at 14:52
I kind of understand but i dont could you edit the bits in my code to show me what you mean?? – Bob Marks Nov 27 '13 at 14:54
1

the code he posted has to go into your `$sql` variable. Basically if you do a `echo $sql` and it prints the second query he posted it should be ok. After that you have to pass the 3 parameters to the `bindValue()` function you use when executing the query. – Naryl Nov 27 '13 at 15:18
@BobMarks what Naryl said - I have updated the example a bit to help you along, but I won't completely spoonfeed it so I'll leave it up to you to generalize to what you want. – CompuChip Nov 27 '13 at 15:40
Thanks a bunch! :) would these all be sent in through the parameter? im guessing it would be, but im just asking for clarification – Bob Marks Nov 27 '13 at 15:47

score 2 · Answer 2 · edited May 23 '17 at 11:56

2

Do you mean multiple row? If so, this question is already answered in:

insert multiple rows via a php array into mysql

Inserting multiple rows in a table using PHP

I hope it helps

edited May 23 '17 at 11:56

Community

1
1

answered Nov 27 '13 at 14:43

Bet

39
8

How to insert multiple values into a table in php

2 Answers2

Linked