Run an insert on an array

PHP
1

I have a table which im trying to run an INSERT statement from both a city and state array, here my php

echo "<pre>";
var_dump($city);
echo "</pre>";
echo "<pre>";
var_dump($state);
echo "</pre>";

The result…

array(2) {
  [0]=>
  string(9) "SAN DIEGO"
  [1]=>
  string(5) "fargo"
}
array(2) {
[0]=>
string(2) "CA"
[1]=>
 string(2) "AL"
}

So, how do I INSERT two (or how many city/states are in the array) records in the table.
Heres what im thinking…

foreach($city AS $name) {
//INSERT into territories (city,state) VALUES ( $name, *how do I grab the state?)

}
Thanks

2

Can you restructure it to use a single array? A single array would make more sense for this as with two arrays what do you do when the city array has 7 elements and the state array has only 4. With a single array with an object in each element containing both the city and the state you only have one array to process through the loop and you guarantee that there are as many cities as there are states.

3

Is this something that works

$city = $_POST['city'];
$state = $_POST['state'];

$territory = array_combine($city,$state);

echo "<pre>";
var_dump($territory);
echo "</pre>";

RESULT

array(2) {
    ["SAN DIEGO"]=>
    string(2) "CA"
    ["fargo"]=>
    string(2) "AL"
}

So the foreach would look more like

foreach($territory as $key => $value) {
//INSERT INTO territories (city,state) VALUES ($key,$value)
//or something like that
}
4

I was hoping this would work

    $conn = mysql_connect('localhost','luke69_luke69', '.CLF8Z6tG76f' );
    mysql_select_db( 'luke69_houastonasp', $conn );

foreach($territory as $key => $value) {
$qry = "INSERT INTO territories (city,state) VALUES (".$key.",".$value.")";
echo $qry;
mysql_query($qry,$conn) or die('Invalid query: ' . mysql_error());;

}

But it seems to not put any records into the table, just the error

INSERT INTO territories (city,state) VALUES (San Diego,CA)Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Diego,CA)’ at line 1

5

As you can see from the error message there are no quotes around either Diego or CA. Both strings need to be within quotes for the query to work.

Note also that the mysql calls you are using are not supported in PHP 7 so you will need to replace them with either mysqli or PDO equivalents before your PHP version gets upgraded to the next version (which is apparently scheduled for release in November).

6

ok, thanks for letting me know
changed the loop to

foreach($territory as $key => $value) {
  $qry = "INSERT INTO territories (city,state) VALUES ('".$key."','".$value."')";
  $sth = $dbh->query($qry); 
}

It worked like a champ!
I also have a junction table (provider_territory) and am trying to figure out how to get the id (PK) inserter in the territories table, something like

INSERT INTO provider_territory (provider_id,territory_id) VALUES ('THE PK FROM THE providers TABLE,THE PK FROM the territories TABLE we just inserted into)

but how do I get those two PKs?

7

you can use LAST_INSERT_ID() (SQL), lastInsertId() (PDO), insert_id (mysqli/mysqli_stmt) to get the newly created PK.

8

thanks

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.