current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have two arrays with anywhere from 1 to 5 set values. I want to insert these values into a table with two columns.

Here's my current query, given to me in another SO question:

INSERT INTO table_name (country, redirect) 
VALUES ('$country[1]', '$redirect[1]'), 
       ('$country[2]', '$redirect[2]'), 
       ('$country[3]', '$redirect[3]'),
       ('$country[4]', '$redirect[4]'),
       ('$country[5]', '$redirect[5]')
ON DUPLICATE KEY UPDATE redirect=VALUES(redirect)

I'm a little concerned however with what happens if some of these array values aren't set, as I believe the above assumes there's 5 sets of values (10 values in total), which definitely isn't certain. If a value is null/0 does it automatically skip it?

Would something like this work better, would it be a lot more taxing on resources?

for($i = 0, $size = $sizeof($country); $i <= size; $i++) {
$query = "INSERT INTO table_name (country, redirect) VALUES ('$country[$i]', '$redirect[$i]) ON DUPLICATE KEY UPDATE redirect='$redirect[$i]'";
$result = mysql_query($query);
}

Questions highlighted in bold ;). Any answers would be very much appreciated :) :)!!

share|improve this question
 
Are you sanitizing your input to prevent a SQL injection? –  mc10 Aug 22 '11 at 21:45
 
Yes, sorry, I didn't make that clear. –  Avicinnian Aug 22 '11 at 21:53
add comment

2 Answers

up vote 1 down vote accepted

Do something like this:

$vals = array()
foreach($country as $key => $country_val) {
    if (empty($country_val) || empty($redirect[$key])) {
       continue;
    }
    $vals[] = "('" . mysql_real_escape_string($country_val) . "','" . mysql_real_escape_string($redirect[$key]) . "')";
}
$val_string = implode(',', $vals);

$sql = "INSERT INTO .... VALUES $val_string";

That'll built up the values section dynamically, skipping any that aren't set. Note, however, that there is a length limit to mysql query strings, set by the max_allowed_packet setting. If you're building a "huge" query, you'll have to split it into multiple smaller ones if it exceeds this limit.

share|improve this answer
 
Thanks, just what I'm looking for, I'll adapt this to my needs, as I've already got the first half of the code you've got constructed in a different manner, bottom line is to create an array with all the VALUES in, then implode each array value with a comma, then insert that as a single string? –  Avicinnian Aug 22 '11 at 21:59
 
yep. that'll handle the missing pairs option as well, since they'd never be added to this new temporary array in the first place. –  Marc B Aug 22 '11 at 22:13
add comment
up vote 0 down vote

If you are asking whether php will automatically skip inserting your values into the query if it is null or 0, the answer is no. Why dont you loop through the countries, if they have a matching redirect then include that portion of the insert statement.. something like this: (not tested, just showing an example). It's one query, all values. You can also incorporate some checking or default to null if they do not exist.

 $query = "INSERT INTO table_name (country, redirect) VALUES ";
 for($i = 0, $size = $sizeof($country); $i <= size; $i++) {
  if(array_key_exists($i, $country && array_key_exists($i, $redirect)
   if($i + 1 != $size){
     $query .= "('".$country[$i]."', '".$redirect[$i]).",";
   }  else $query .= "('".$country[$i]."', '".$redirect[$i].")";
  }
 }
 $query .= " ON DUPLICATE KEY UPDATE redirect=VALUES(redirect);"
 $result = mysql_query($query);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.