SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    add single quotes while building insert query

    I had originally been importing data into mysql with single quotes - since I was the one who was doing it. Ex: 'fname','lname',email'

    Well, now I want to just go ahead and insert directly from a csv via copy/paste. So now I need to add the single quotes into the php script. I'm having a hell of a time trying to figure out something that works.

    PHP Code:
    $post trim($_POST['sub_fields']);
    $new_fields str_replace(" ","",$post);
    $fields preg_split('/[\r\n]+/'$new_fields);
    $ok = array();
    foreach(
    $fields as $data) {
        
        
    // break apart the data so we can get the email address
        
    list($fname$lname$email) = explode("','"$data);
        
    $email $email;
        
        
    $ok[] = '('.$data.')';
        
        
    // check for duplicate email addresses
        
    $email_check email_check($email);
        
        if(
    $email_check 0) {
            
    // email address already exists!
            
    $dup_error 1;
            
    $dup_email[] = $email;
            
    $dup_count count($dup_email);
        }
        
        
    $insert_count++;
        
    $new_email $email;
        
    }
        
    // emails passed inspection, build the insert query
        
    if(!empty($ok)) {
            
    $insert_query "INSERT INTO subscribers_temp(fname,lname,email) VALUES " join(','$ok);
        } 
    If I var_dump($insert_query); I'll get
    PHP Code:
    INSERT INTO subscribers_temp (fname,lname,emailVALUES(fname,lname,email
    I need it to be
    PHP Code:
    INSERT INTO subscribers_temp (fname,lname,emailVALUES('fname','lname','email'

  2. #2
    SitePoint Enthusiast mrsmiley's Avatar
    Join Date
    Jul 2004
    Location
    Melbourne
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What on earth is this supposed to do ?

    Code:
    $email = $email;
    The code seems OK to me, I'm guess you have a problem with magic quotes, or something nasty like that. Because the data is being fed in from a post value instead of directly through a file and fgetcsv or some equivalent.

    The fact that you are just adding the raw data into the $ok array via:

    Code:
    $ok[] = '('.$data.')';
    Indicates to me the quotes were never there to begin with. Try var_dump'ing the $data value to prove that.

    Also, I'm not sure if this is a logic error, but it would seem you should be adding items to the $ok array only after they pass the email check, otherwise you are adding every record into your SQL that exists in the CSV.

  3. #3
    Resident Code Monkey Chris Corbyn's Avatar
    Join Date
    Nov 2005
    Location
    Melbourne, Australia
    Posts
    713
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't forgt to mysql_real_escape_string() unless you want to get hacked, or give your visitors error messages

  4. #4
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mrsmiley View Post
    What on earth is this supposed to do ?

    Code:
    $email = $email;
    The code seems OK to me, I'm guess you have a problem with magic quotes, or something nasty like that. Because the data is being fed in from a post value instead of directly through a file and fgetcsv or some equivalent.

    The fact that you are just adding the raw data into the $ok array via:

    Code:
    $ok[] = '('.$data.')';
    Indicates to me the quotes were never there to begin with. Try var_dump'ing the $data value to prove that.

    Also, I'm not sure if this is a logic error, but it would seem you should be adding items to the $ok array only after they pass the email check, otherwise you are adding every record into your SQL that exists in the CSV.
    The quotes aren't there. That's the whole point of the post

    I should actually change that to INSERT IGNORE, because the email field is unique, so there won't be any duplicates anyway. The email check is more for alerting the user that duplicates were found.

    I've rebuilt PHP without magic quotes and am still having the problem. It's actually a design error. I just can't figure out the correct way to write it so that for every addition it's ('field','field,'field'),('field','field,'field'),('field','field,'field').

    Currently it's (field,field,field),(field,field,field),(field,field,field). I need to get those single quotes in there...

  5. #5
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    double quote it
    PHP Code:
    $ok[] = "('$data')"
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  6. #6
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tgavin View Post
    If I var_dump($insert_query); I'll get
    PHP Code:
    INSERT INTO subscribers_temp (fname,lname,emailVALUES(fname,lname,email
    I need it to be
    PHP Code:
    INSERT INTO subscribers_temp (fname,lname,emailVALUES('fname','lname','email'
    PHP Code:
    if (!empty($ok)) {
      
    $escaped = Array();
      foreach (
    $ok as $value) {
        
    $escaped[] = "'".mysql_escape_string($value)."'";
      }
      
    $insert_query "INSERT INTO subscribers_temp(fname,lname,email) VALUES " join(','$escaped);

    Edit:


    Oh ... I missed that $ok was an array of multiple values ... scratch that, then ...

  7. #7
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    double quote it
    PHP Code:
    $ok[] = "('$data')"
    Been there, done that. no dice.

  8. #8
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it figured out. I needed to add EVERY field into the $ok array

    PHP Code:
    $ok[] = "('$fname','$lname','$email')"
    Thanks for your replies


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
  •