SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Removing trailing comma and space in a string

    Hello,

    I came up with the following code to insert a number of rows into a MySQL table in a single query.
    The query would look like this:

    PHP Code:
    $sql "INSERT INTO
     table(id, item) 
    VALUES
     (5,item1), (5, item2), (5, item3),  "

    As you can see the trailing comma and space should be removed.

    Can you please let me know if the following is efficient (is there a better way to remove the last two characters if the string is really long)

    PHP Code:

    $values
    ='';

    /*****
    * $items is an array of values to be inserted
    * into individual rows
    **/
    foreach($items as $item){
    $values .= '('.$row['ID'].'),('.$item.'), ';
    }

    $length strlen($values);
    $values substr($values0$length-2);

    $sql "INSERT INTO table(id, item) VALUES ".mysql_real_escape_string($values);
    $result=$db->query($sql); 
    Have you done this before, and if so, did you do it the same way? I would like to know other ways that this is done.

    Thanks!

  2. #2
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would this be more efficient?

    PHP Code:
    $values{strlen($values)-1}='';
    $values{strlen($values)-2}=''
    as opposed to the code in my previous post:
    PHP Code:
    $length strlen($values);
    $values substr($values0$length-2); 

  3. #3
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I like using an array and an implode:
    $arraything=array()
    while( blargh ) {
    $arraything[] = "(values,go,here)";
    }
    $values = implode(',', $arraything);


    or you can use a strrpos (find the last location of the comma in the string). I think there's actually an example on the strrpos() function page on php.net ( http://www.php.net/strrpos
    PHP Programmer and Systems Administrator
    Argh WebWorks | What's Your PageRank?

  4. #4
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rgilmanhunt View Post
    I like using an array and an implode:
    $arraything=array()
    while( blargh ) {
    $arraything[] = "(values,go,here)";
    }
    $values = implode(',', $arraything);


    or you can use a strrpos (find the last location of the comma in the string). I think there's actually an example on the strrpos() function page on php.net ( http://www.php.net/strrpos
    if I need it in the form "(array1[0], array2[0]), (array1[1], array2[1]), etc."

    I don't hink I can use implode
    $values = implode('), (', $arraything);
    which would give me: "(array2[0]), (array2[1]), (array2[3]), etc."

    I think I would end up with a loop again such as

    for ($i=0; $i < count($values), $i++) {
    $newvaluse[] = $val1[$i] .','.$val2[$i];
    }
    then I would apply the implode using ), (

    and in the SQL statement I would have to add the starting ( and end ).

    it seems like a lot more work.

  5. #5
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the tip rgilmanhunt. I'm not sure either are useful in this case though.
    The implode has issues if I need to insert values into more than one column(like I stated above) since each column is separated by commas, then each row would be separated by braces.

    Quote Originally Posted by rgilmanhunt View Post
    or you can use a strrpos (find the last location of the comma in the string). I think there's actually an example on the strrpos() function page on php.net ( http://www.php.net/strrpos
    I don't think strpos is necessary since I know the position of the last two characters (they are the last two characters in the string)
    Also it finds the first occurance of the pattern that it is searching for... trying to convince it to look for the last comma is like telling it where it is (making the function redundant) and that function alone does not remove the last two characters.

  6. #6
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well.. I managed to answer my own question. I found a script timer.

    after a few tries, it turns out that the second method is a bit more than 10&#37; faster (and the code is a bit cleaner)

    I wonder if anyone else will ever read this.

  7. #7
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $values = array();
    foreach(
    $items as $item){
        
    $values[] = '(' $row['ID'] . ', "' mysql_real_escape_string($item) . '")';
    }
    $values join(','$values);

    $sql 'INSERT INTO table(id, item) VALUES ' $values;
    $result $db->query($sql); 
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  8. #8
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    nice.

  9. #9
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    > As you can see the trailing comma and space should be removed.

    PHP Code:
    rtrim$string', ' ); // would work 


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •