asked on

Insert Array into MySQL via PHP

Hello Experts,

How would I insert an array of data into a MySQL database, using PHP? I am able to successfully enter one row of data into each table (renters and rental_agreement), but I get confused when a renter rents more than one car seat. How do I in essence duplicate all of the necessary information into the rental_agreement table, but have the serial_number change to the next number in the series of seats rented?

Attached are three images depicting two tables and one example entry from my database.

I have the following code which inserts data into the renters table. The last_id is then inserted into the rental_agreement table. For the time being, I am using test data and not the $_POST data:

$first_name="HOMER";
$last_name="SIMPSON";
$dl="S123-456-54-367-1";
$dl_street="123 SESAME STREET";
$dl_unit=" ";
$dl_city="I-DON'T-KNOW";
$dl_state="KENTUCKY";
$dl_zip="12345";
$current_street="123 SESAME STREET";
$current_unit=" ";
$current_city="I-DON'T-KNOW";
$current_state="KENTUCKY";
$current_zip="12345";
$phone="1234567891";
$email="test@yahoo.com";

$sql = 'INSERT INTO `renters` 
(renters_first_name, renters_last_name, 
renters_dl_number, renters_dl_street, renters_dl_unit, renters_dl_city, renters_dl_state, renters_dl_zip,
renters_current_street, renters_current_unit, renters_current_city, renters_current_state, renters_current_zip,
renters_phone, renters_email)
 VALUES ("' . $first_name . '", "' . $last_name . '", "'. $dl . '", "'. $dl_street . '", "'. $dl_unit . '", "'. $dl_city . '", "'. $dl_state .'", "'. $dl_zip . '", "'. 
 $current_street . '", "'. $current_unit .'", "'. $current_city .'", "'. $current_state .'", "'. $current_zip .
'", "'. $phone .'", "'. $email . '")';
 $result = mysqli_query($db_connect, $sql);
 $last_id = mysqli_insert_id($db_connect);
 if (!$result){
	 die('Could not execute query' . mysqli_error($db_connect));
 }else{
	 echo "The query was executed. ";
	 echo "New record has ID: " . $last_id; 
 }
 
 $seatnumbers=123456;
 $invoice=5736;
 $acknowledged=1;
 $deposit=1;
 $check =123678;
 $refunded=1;

 
 $sql = 'INSERT INTO `rental_agreement` 
 (renters_id, seat_serial, invoice_number, 
 renters_acknowledgement,  date_rented, deposit_type, check_number, 
 date_returned, deposit_refunded, date_deposit_refunded)
 VALUES ("' . $last_id . '", "' . $seatnumbers . '", "'. $invoice . '", "'
 . $acknowledged . '", "'. "2017-09-07" . '", "'. $deposit . '", "'. $check .'", "'
 . "2017-10-07" . '", "'. $refunded . '", "'. "2017-09-07" . '")';
  
  $result = mysqli_query($db_connect, $sql); 
  if (!$result){
	 die('Could not execute rental agreement query' . mysqli_error($db_connect));
 }else{
	 echo "The query was executed. ";
	 echo "The rental agreement table was updated"; 
 }

Open in new window

Here is an excerpt from my HTML, which collects the data and then does the following:

<input type="checkbox" name="seat_rented[]" value="<?php echo $row['seat_serial'];?>">

$seat = isset($_POST['seat_rented']) ? $_POST['seat_rented'] : false;
if(is_array($seat))
{
foreach ($seat as $value)
	{		//pass-by reference to change actual array values
	$value = preg_replace("/[^a-zA-Z0-9]+/", "", $value);
	}
	unset($value);
	$seatnumbers = implode("','", $seat);

	$sql = "SELECT * FROM `car_seats` WHERE `seat_serial` IN ('{$seatnumbers}') ORDER BY `seat_type`,`seat_serial` ASC ";
	$result = mysqli_query($db_connect, $sql);

Open in new window

Would I change the SQL statement from a "SELECT" to an "INSERT" statement?

Chris Stanyon

Basically, you would just loop through the array of seat numbers and execute the INSERT query for each entry:

foreach ($seatNumbers as $seatNumber) {
// run the insert query
}

For a case like this it makes sense to use a prepared query - the query is prepared once but executed lots of time - each time with different values. It's the fastest way to execute the same query over and over again

Julian Hansen

Order your array records by renter
Set up a variable to mark which renter you are dealing with
Create the renters record an store the ID

Create a query like thisl

// Get your unique id
$renterid = $db->insertid;
$query = "INSERT INTO <replace_with_agreementstable> (RenterID, <field2>, ...) VALUES";

Open in new window

Now loop through the agreements data and add to this string

foreach($agreement as $a) {
  $query .= "('{$renter_id}','{$a->value2}',...),";
}

Open in new window

In the end you will have a string that looks something like this

INSERT INTO <replace_with_agreementstable> (RenterID, <field2>, ...) VALUES
('{$renter_id}','{$a->value2}',...),
('{$renter_id}','{$a->value2}',...),
('{$renter_id}','{$a->value2}',...),
('{$renter_id}','{$a->value2}',...),

Open in new window

This is a batch insert - one query that does multiple inserts.

Trim the trailing ',' and execute.

$query = trim($query, ',');
$db->query($query);

Open in new window

The above is obviously pseudo code you will need to adapt for your situation.

Chris Stanyon

Here's some code to do it with the prepared queries:

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

// Prepare your queries and bind the parameters
$renterQry = $con->prepare("INSERT INTO renters (renters_first_name, renters_last_name, renters_dl_number) VALUES (?, ?, ?)");
$renterQry->bind_param("sss", $firstName, $lastName, $dlNum);

$seatQry = $con->prepare("INSERT INTO rental_agreement (renters_id, seat_serial, invoice_number) VALUES (?, ?, ?)");
$seatQry->bind_param("iii", $id, $seat, $invoice);


// Set the values for the renter query
$firstName="HOMER";
$lastName="SIMPSON";
$dlNum="S123-456-54-367-1";

// Execute the Renter query 
$renterQry->execute();

// Get the auto id
$id = $con->insert_id;

// Set the values for the Agreement query
$seats = array(1, 2, 3, 4, 5, 6);
$invoice = 5736;

// Execute the Agreement query for each seat
foreach ($seats as $seat) {
    $seatQry->execute();
}

Open in new window

Joseph Longo

ASKER

Braving Hurricane Irma at the moment, as I live in Tampa, FL. Once life returns back to normal, I'll test these solutions out.

Joseph Longo

ASKER

@Julian Hansen,

In your statement, for procedural PHP, would I modify this:

$a->

Open in new window

to this:

$a=>

What would I modify this portion to?

value2

Open in new window

Chris Stanyon

The $a->value2 is the object notation to get a property from an object. To do it with arrays instead (nothing to do with procedural, you would instead do this:

$a["value2"]

In the code, $a represents an $agreement and the value2 part is a key or property on that agreement, so it depends on how your array is structured. If you array has keys called firstName and lastName, then you would access them like so:

$a["firstName"]
$a["lastName"]

Joseph Longo

ASKER

@Chris Stanyon

Where is value2 coming from? What would it be replaced with in my code?

Chris Stanyon

In your original question, you asked about inserting an array into the database, so I guess you'd get that array from the HTML page before submitting it to your script. Looking at your the code above, you say you have this:

<input type="checkbox" name="seat_rented[]" value="<?php echo $row['seat_serial'];?>">

When the form is submitted, the $_POST['seat_rented'] variable would be an array containing all the Seat Serial Numbers that were ticked, so you would loop through that and get the value. The value2 that was used was just an example - you can call it whatever you like. If you want to call it seat_serial, then call it seat_serial:

foreach($_POST['seat_rented'] as $seat_serial) {
$query .= "('{$renter_id}','{$seat_serial}',...),";
}

Julian Hansen

I suspect your question goes back to this bit of code I posted

foreach($agreement as $a) {
  $query .= "('{$renter_id}','{$a->value2}',...),";
}

Open in new window

This was meant to represent a loop going through your agreements and building a query for the insert.
If I remember correctly you needed to insert multiple agreements for a specific renter.

First you would insert your new renter into the database. This would result in a new auto_number (id) for the record which you could access like this

$renterid = $db->insertid;

Open in new window

$renterid = mysqli_insert_id($db_connect);

Open in new window

Depending on whether you are using the object or procedural version of the MySQLi library.

This code would be placed immediately after your INSERT QUERY that added the renter to the database.

This renter ID will be used for each subsequent insert that you do so that you can link the agreements back to the original renter record.

This is what is happening on lines 17-32 of the code in your OP with $last_id storing the value of the last insert id. So in my example just replace $renterid with $last_id and we are on the same page.

Now, on lines 42-50 of your code you are creating your rental agreement inserts.
The opening line of your question was

How would I insert an array of data into a MySQL database, using PHP?

So, I am guessing you have an array of agreements you want to add to the database linked to the renter you just created.

To do that you loop through your array using a foreach. I don't know what your array contains - I assumed an object but lets go with an array of arrays
You have not provided us with a sample array so I am going to show you a generic solution

$query = "INSERT INTO [agreementstable] (renterid, ... [rest of fields here]) VALUES ";
foreach($a in $yourarray) {
   // $a is now the nth element in the array we are looping over
  // build an insert query made up of the $last_id and the values in $a
  // except instead of creating a whole new query each time just add on a VALUES
  // section (without the VALUES) to a single INSERT so you can do it as a batch

  $query .= "({$last_id}, {$a['field1']}, {$a['field2']}, ...),";
}

// remove the trailing comma
$query = trim($query,',');

// Now you can execute your query here
$result = mysqli_query($db_connect, $query);
// All agreement records liked to renter should be inserted in the dB

Open in new window

The value2 was meant as a placeholder for a field name from your array - so if your array had fields
seatnumber
invoice
acknoweleged

Then instead of $a->value2 (in the case of an array of objects) or $a['field1'] (as in the above array example you would have

$a->seatnumber (for array of objects)   $a['seatnumber'] (for array of arrays)
$a->invoice (for array of objects)   $a['invoice'] (for array of arrays)
...

Open in new window

I don't know if your array contains objects or arrays so I am providing both accessor methods - you need to choose one based on what your arrays contain.

Hopefully that has cleared things up a bit.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.