Better way to implode a query string? [SOLVED]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Better way to implode a query string? [SOLVED]

Post 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
Last edited by incubi on Fri Feb 12, 2010 4:32 pm, edited 1 time in total.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Better way to implode a query string?

Post 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 )
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Better way to implode a query string?

Post by Eran »

are you trying to insert multiple rows simultaneously or just one?
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Better way to implode a query string?

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Better way to implode a query string?

Post 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/
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Better way to implode a query string?

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Better way to implode a query string?

Post 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)
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Better way to implode a query string?

Post 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."'";
}
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Better way to implode a query string?

Post 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) . ")";
}
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Better way to implode a query string?

Post 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");
}   
 
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Better way to implode a query string?

Post by Eran »

Did you try the suggestion I showed you in the previous post?
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Better way to implode a query string?

Post 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)."'";
}
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Better way to implode a query string?

Post by Eran »

this is not the same as what I've shown you.
incubi
Forum Contributor
Posts: 119
Joined: Mon Dec 07, 2009 1:47 pm

Re: Better way to implode a query string?

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Better way to implode a query string?

Post by Eran »

Look again, the $allrows assignment should occur inside the loop. it should work - if not, please detail exactly what isn't working
Post Reply