Optimizing multiple INSERTs

Optimizing INSERTs and concatenating strings into variable names

[matbergman]

Greetings,

I have a set of text fields (named "link1" through "link5") and a kludgy script that writes each field to a row if the field is not empty:

if (!empty($link1)) {
$query = "INSERT INTO table (field) VALUES ('$var1')";
$result = mysql_query($query);
}

if (!empty($link2)) {
$query = "INSERT INTO table (field) VALUES ('$var2')";
$result = mysql_query($query);
}

...continued for 5 rows...

I attempted to optimize this code by iterating through each choice by concatenating a string into the variable name:

for ($numLinks=1; $numLinks<6; $numLinks++) {
$linkUrl = "$link.$numLinks";
if (!empty($_POST[$linkUrl])) {
$query = "INSERT INTO table (field) VALUES ('$linkUrl')";
$result = mysql_query($query);
}

but I have been unable to create a valid variable name with this method (the iteration seems to work OK).

I would greatly value the expertise of this forum for ideas to simplify my php.

Thanks,
-Mat

[mcibor]

You should use what coop or jatar loves (I don't recollect now who leves that :P ), that is variable variables:

It should be

for ($numLinks=1; $numLinks<6; $numLinks++) {
$linkUrl = ${'link'.$numLinks};
if (!empty($_POST[$linkUrl])) {
$query = "INSERT INTO table (field) VALUES ('$linkUrl')";
$result = mysql_query($query);
}

PS. Why don't you use mysql optimized insert?
INSERT INTO table (field) VALUES ('1 value'), ('2 value'), ('3 value');

Michal Cibor

[matbergman]

Thanks Michal for this most elegant solution. It solved my confusion about assembling variable names from strings and variables.

I originally tried the mysql optimized insert, but I don't know how to exclude an INSERT if a field is empty. Is it possible?

-Mat

[mcibor]

It is possible - you need to prepare the query in the loop and then ask, however it's not worth for just six, and there would have to be an "if" if there are no queries at all. So just do it the non optimized way! (i don't see a clean and nice way

BTW I'm not sure now, but it may be so, that that query is forbidden through php.
Michal Cibor

[coopster]

Welcome to WebmasterWorld, matbergman.

I agree with Michal in that you could initialize your values list and then append based on the empty or not empty variable.

$values = ''; 
for (...) { 
 ... 
 if (!empty($_POST[$linkUrl])) { 
   $values .= "('$linkUrl'),"; 
 } 
} 
if ($values = rtrim($values, ',')) { 
 $query = "INSERT INTO table (field) VALUES $values"; 
 $result = mysql_query($query); 
}

[mcibor]

aaa, I didn't know about the rtrim [php.net] function

It solves quite a problem there (I didn't want to write the ifs to check if any var was true )

Thanks coop!
Michal