Page 1 of 2
Better way to implode a query string? [SOLVED]
Posted: Fri Feb 12, 2010 11:36 am
by incubi
The array below will work for doing an insert query using implode but is there a better way?
Code: Select all
$allrows[] = "('".$csv_line[0]."', '".$csv_line[1]."', '".$csv_line[2]."', '".$csv_line[3]."',
'".$csv_line[4]."', '".$csv_line[5]."', '".$csv_line[6]."', '".$csv_line[7]."', '".$csv_line[8]."',
'".$csv_line[9]."', '".$csv_line[10]."', '".$csv_line[11]."', '".$csv_line[12]."', '".$csv_line[13]."',
'".$csv_line[14]."', '".$csv_line[15]."','".$csv_line[16]."', '".$csv_line[17]."', '".$csv_line[18]."',
'".$csv_line[19]."', '".$csv_line[20]."', '".$csv_line[21]."', '".$csv_line[22]."',
'".$opv."' )";
If I try using foreach I can't see the way to get starting and ending brackets ( ) on each line for the correct query format.
Code: Select all
foreach($csv_line as $qvals)
$allrows[] = "'".$qvals."'";
$query = "INSERT INTO table (`all`, `my`,`fields` ) values ".implode(',', $allrows) ;
Thanks
incubi
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 12:03 pm
by josh
Code: Select all
$query = "INSERT INTO table (`all`, `my`,`fields` ) values (".implode(',', $allrows) .")";
If you use something off the shelf like PHP's PDO (portable data objects) extension, or Zend Framework's database adapter, it could be further reduced to
Code: Select all
$bind = array( 'field1' => 1, 'field2' => 2 ) ;
$db->insert( 'table', $bind);
Its the way to go. Or roll your own:
to build the keys string
implode( ',', array_keys( $bind ))
to build the values string
implode( ',', $bind )
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 1:26 pm
by Eran
are you trying to insert multiple rows simultaneously or just one?
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 1:45 pm
by incubi
Thanks for the replies.
I'm inserting a column of a table with many rows over 400.
I was able to get this far but can't see how to add the ending bracket
Code: Select all
$flg=0;
foreach($csv_line as $vals)
{
if(!$flg)
{
$allrows[] = "('".$vals."'";
$flg=1;
}
else
$allrows[] = "'".$vals."'";
}
This gets me the first bracket in each line.
josh, in this case I would like to avoid the add-on's because it may simplify my code but puts a lot in the background. I need to learn to thin out my code first.
Thanks,
incubi
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 1:51 pm
by Eran
If $vals contains string values they would have to be quoted separately. And you need to close the brackets for each row.
This article I wrote sometime ago might be helpful for you (check the second example)
http://www.techfounder.net/2009/05/14/m ... mysql-php/
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 2:05 pm
by incubi
I could be missing something but, In that example isn't it in effect the same as calling out each element in the array like in my first example $allrows[] = "('".$csv_line[0]."', '".$csv_line[1]."',......
The loop is putting each element/csv field, in the array $allrows not the row of csv data.
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 2:11 pm
by Eran
Yes, it's exactly the same as the first part. But then you showed the following code:
Code: Select all
foreach($csv_line as $qvals)
$allrows[] = "'".$qvals."'";
$allrows should contain only complete rows wrapped in brackets and not separate values. If you follow the example in the article exactly, it should work without a hitch. Are you receiving any relevant error messages?
As an aside, if you know you have only string values inside the csv you don't have to specify all the values one by one. Use something like:
Code: Select all
$allrows[] = "('" . explode("','",$csv_line) . "')";
(The explode contains single quotes inside the separator)
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 2:38 pm
by incubi
ok, I see where the gap may be. My code (below) is already in a while loop, looping through the CSV file. $allrows[] gets each field in each row now. It is working code.
Code: Select all
$allrows[] = "('".$csv_line[0]."', '".$csv_line[1]."', '".$csv_line[2]."', '".$csv_line[3]."',
'".$csv_line[4]."', '".$csv_line[5]."', '".$csv_line[6]."', '".$csv_line[7]."', '".$csv_line[8]."',
'".$csv_line[9]."', '".$csv_line[10]."', '".$csv_line[11]."', '".$csv_line[12]."', '".$csv_line[13]."',
'".$csv_line[14]."', '".$csv_line[15]."','".$csv_line[16]."', '".$csv_line[17]."', '".$csv_line[18]."',
'".$csv_line[19]."', '".$csv_line[20]."', '".$csv_line[21]."', '".$csv_line[22]."',
'".$opv."' )";
In your example $values[] get each field in the row of $data did I say that right?
In my foreach I'm trying to avoid calling out every element in the $allwrow[].
If I use your example
Code: Select all
$values[] = "(" . $row['user_id'] . "," . $row['content'] . ")";
its the same as $allrows[].
So to sum up my code works with $allrows[] as above but I would like to do away with that and use
a loop to build $allrows[].
In this code (below) the only thing that would be missing doing it in a loop is the closing bracket for each row.
Code: Select all
$flg=0;
foreach($csv_line as $vals)
{
if(!$flg)
{
$allrows[] = "('".$vals."'";
$flg=1;
}
else
$allrows[] = "'".$vals."'";
}
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 2:44 pm
by Eran
I think I understand what you tried to do. Your mistake is trying to append the values directly to the $allrows variable. Could you show your code including the while loop, you show different segments and its hard to determine in what scopes each occurs.
Code: Select all
while($csv_line = ...) {
$row = array();
foreach($csv_line as $value) {
$row[] = "'" . mysql_real_escape_string($value) . "'";
}
$allrows[] = "(" . implode(',',$row) . ")";
}
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 2:53 pm
by incubi
Here is the routine. The rem part it just because I'm testing the other way to do it.
Code: Select all
$allrows = array();
if (($handle = fopen($csvfile, "r")) !== FALSE)
{
while (($csv_line = fgetcsv($handle, 1000, ",")) !== FALSE)
{
/*
$allrows[] = "('".$csv_line[0]."', '".$csv_line[1]."', '".$csv_line[2]."', '".$csv_line[3]."',
'".$csv_line[4]."', '".$csv_line[5]."', '".$csv_line[6]."', '".$csv_line[7]."', '".$csv_line[8]."',
'".$csv_line[9]."', '".$csv_line[10]."', '".$csv_line[11]."', '".$csv_line[12]."', '".$csv_line[13]."',
'".$csv_line[14]."', '".$csv_line[15]."','".$csv_line[16]."', '".$csv_line[17]."', '".$csv_line[18]."',
'".$csv_line[19]."', '".$csv_line[20]."', '".$csv_line[21]."', '".$csv_line[22]."',
'".$opv."' )";
*/
// Testing
$flg=0;
foreach($csv_line as $vals)
{
if(!$flg)
{
$allrows[] = "('".$vals."'";
$flg=1;
}
else
$allrows[] = "'".$vals."'";
}
// End testing
}
$query = "INSERT INTO table (`all`, `my`,`fields` ) values (".implode(',', $allrows) .")";
mysql_query($query) or die(mysql_error());
}
fclose($handle) or die("can't close file");
}
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 2:58 pm
by Eran
Did you try the suggestion I showed you in the previous post?
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 3:08 pm
by incubi
Just tried it but no luck. Everything is perfect in the row but the opening and closing brackets ()
Code: Select all
foreach($csv_line as $vals)
{
$allrows[] = "'".mysql_real_escape_string($vals)."'";
}
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 3:12 pm
by Eran
this is not the same as what I've shown you.
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 3:32 pm
by incubi
You're right my bad. Ok so now I have
Code: Select all
while(....
{
$row = array();
foreach($csv_line as $vals)
{
$row[] = "'".mysql_real_escape_string($vals)."'";
}
}
$allrows[] = "(" . implode(',',$row) . ")";
$query = "INSERT TO table ( stuff) values ".implode(',', $allrows) ;
The result is nothing I can send in a query. I can't sent the printed result because it's proprietary but its just a bunch of lines with INSERT INTO and garbage. Am I still missing something? It's been a long day.
Re: Better way to implode a query string?
Posted: Fri Feb 12, 2010 3:34 pm
by Eran
Look again, the $allrows assignment should occur inside the loop. it should work - if not, please detail exactly what isn't working