Insert a combination of strings and arrays into MYSQL

Question

I have found similar questions on here, but nothing quite right for my situation. I need to make multiple entries to a database from a combination of values from a set of arrays and repeated strings. To give an example:

$sql = "INSERT INTO sonch_MAIN.Concert (venue_id, date, ensemble_id, info, title, repertoire, time)
VALUES ('$venue', '$date', '1', '$info', '$title', '$repertoire_formatted', $time)";

$venue, $time, AND $date are arrays. '1' should be added to EACH entry to the database without change. $info, $title, AND $repertoire_formatted are strings that should be repeated, i.e., inserted without any variation, for each entry to the database.

So the following example shows what the contents of each variable might be:

$venue = array('venue1', 'venue7', 'venue50');
$date = array('2019-01-01', '2019-02-02', '2019-03-03');
$time = array('20:00:00', '19:00:00', '18:00:00');
$info = 'General info about this event';
$repertoire_formatted = 'Music that people will play at this event';

My SQL database is set up to take the different types of data for each input variable.

HERE is the code I have (not working):

session_start();
$_SESSION["servername"] = "localhost";
$_SESSION["username"] = "sonch_nB";
$_SESSION["password"] = 'hello';
$_SESSION["dbname"] = "sonch_MAIN";
date_default_timezone_set('Europe/Zurich');


$venue = ($_POST['venue']);
$date = ($_POST['date']);
$ensemble_id = '1'; //THIS WILL BE SET VIA LOGIN
$info = ($_POST['info']);
$title = ($_POST['title']);
//FORMAT INCOMING VARS CODE SKIPPED//

// Create connection
    $conn = new mysqli($_SESSION['servername'], $_SESSION['username'], $_SESSION['password'], $_SESSION['dbname']);
// Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
        } 

//NEED TO LOOP INPUT TO MYSQL NUMBER OF VALUES IN ARRAY
$stmt = $conn->prepare("INSERT INTO sonch_MAIN.Concert (venue_id, date, ensemble_id, info, title, repertoire, time) VALUES (?, ?, '1', ?, ?, ?, ?)");
$stmt->bind_param("ssssss", $v, $d, $info, $title, $repertoire_formatted, $t);
for ($i = 0; $i < count($venue); $i++) {
    $v = $venue[$i];
    $d = $date[$i];
    $t = $time[$i];
    $stmt->execute();
}

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

$stmt->close();

What error did you get back? You know you're executing the query twice. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 25, 2018 at 14:17
Problem is sorted now.... the below code fixed it. It was a silly mistake on my part. — Paul Clift
– Paul Clift, Commented Nov 25, 2018 at 14:19

Nick · Accepted Answer · 2018-11-25 12:38:04Z

1

You should use a prepared statement. In MySQLi (assuming your connection is $conn):

$stmt = $conn->prepare("INSERT INTO sonch_MAIN.Concert (venue_id, date, ensemble_id, info, title, repertoire, time)
VALUES (?, ?, '1', ?, ?, ?, ?)");
$stmt->bind_param("ssssss", $v, $d, $info, $title, $repertoire_formatted, $t);
for ($i = 0; $i < count($venue); $i++) {
    $v = $venue[$i];
    $d = $date[$i];
    $t = $time[$i];
    if ($stmt->execute() === TRUE) {
        echo "New record created successfully";
    } else {
         echo "Error: " . $conn->error;
    }
}
$stmt->close();

edited Nov 25, 2018 at 12:38

answered Nov 25, 2018 at 12:02

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Paul Clift Over a year ago

Sorry, I left those vars out in my original post; have corrected it now...

Nick Over a year ago

Ok, I've edited the code to allow $time to be an array as well.

Paul Clift Over a year ago

Hmm, this is coming up with an error, but no other info is given from my echo "Error: " . $sql . "<br>" . $conn->error;

Nick Over a year ago

@PaulClift where are you doing the echo? if it is after $stmt->close() it will be empty

Paul Clift Over a year ago

No, I added the following before $stmt->close() --

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

|

Collectives™ on Stack Overflow

Insert a combination of strings and arrays into MYSQL

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related