SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2002
    Location
    Madison, WI
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Basic MySQL query

    Hi,

    I am using a premade script that loads in a .csv file and outputs it to a table. I cannot find how I can add a statement to the $sql variable

    PHP Code:
    DELETE FROM 'liaisons'
    Which will remove all the data in the table before the main script runs. I tried adding this before the line
    PHP Code:
    $sql "LOAD DATA LOCAL INFILE 
    , but it gave an error.

    Sorry for the long file.

    PHP Code:
    <?php

    /*
     Quick_CSV_import class provides interface to a quick CSV file to MySQL database import. Much quicker (10-100 times) that line by line SQL INSERTs.
     version: 1.5
     author: Skakunov Alexander <i1t2b3@gmail.com>
     date: 23.8.2006
     description:
       1. Before importing, you MUST: 
         - establish connection with MySQL database and select database;
         - define CSV filename to import from.
       2. You CAN define several additional import attributes:
         - use CSV header or not: if yes, first line of the file will be recognized as CSV header, and all database columns will be called so, and this header line won't be imported in table content. If not, the table columns will be calles as "column1", "column2", etc
         - separate char: character to separate fields, comma [,] is default
         - enclose char: character to enclose those values that contain separate char in text, quote ["] is default
         - escape char: character to escape special symbols like enclose char, back slash [\] is default
         - encoding: string value which represents MySQL encoding table to parse files with. It's strongly recomended to use known values, not manual typing! Use get_encodings() method (or "SHOW CHARACTER SET" query) to ask the server for the encoding tables list
       3. You can read "error" property to get the text of the error after import. If import has been finished successfully, this property is empty.
    */

    mysql_connect("example.com""uuuuuuuu""ppppppp");
    mysql_select_db("ip78790z"); //your database

    class Quick_CSV_import
    {
      var 
    $table_name//where to import to
      
    var $file_name;  //where to import from
      
    var $use_csv_header//use first line of file OR generated columns names
      
    var $field_separate_char//character to separate fields
      
    var $field_enclose_char//character to enclose fields, which contain separator char into content
      
    var $field_escape_char;  //char to escape special symbols
      
    var $error//error message
      
    var $arr_csv_columns//array of columns
      
    var $table_exists//flag: does table for import exist
      
    var $encoding//encoding table, used to parse the incoming file. Added in 1.5 version
      
      
    function Quick_CSV_import($file_name="")
      {
        
    $this->file_name $file_name;
        
    $this->arr_csv_columns = array();
        
    $this->use_csv_header true;
        
    $this->field_separate_char ",";
        
    $this->field_enclose_char  "\"";
        
    $this->field_escape_char   "\\";
        
    $this->table_exists false;
      }
      
      function 
    import()
      {
        if(
    $this->table_name=="")
          
    $this->table_name "liaisonsNew";
        
        
    $this->table_exists false;
        
    $this->create_import_table();
        
        if(empty(
    $this->arr_csv_columns))
          
    $this->get_csv_header_fields();
        
        
    /* change start. Added in 1.5 version */
        
    if("" != $this->encoding && "default" != $this->encoding)
          
    $this->set_encoding();
        
    /* change end */
        
        
    if($this->table_exists)
        {
         
    /* Line below is originally "load data infile" */
          
    $sql "LOAD DATA LOCAL INFILE '".@mysql_escape_string($this->file_name).
                 
    "' INTO TABLE `".$this->table_name.
                 
    "` FIELDS TERMINATED BY '".@mysql_escape_string($this->field_separate_char).
                 
    "' OPTIONALLY ENCLOSED BY '".@mysql_escape_string($this->field_enclose_char).
                 
    "' ESCAPED BY '".@mysql_escape_string($this->field_escape_char).
                 
    "' ".
                 (
    $this->use_csv_header " IGNORE 1 LINES " "")
                 .
    "(`".implode("`,`"$this->arr_csv_columns)."`)";
          
    //editing by hms 11152006
        //$res = @mysql_query($sql);
          
    if(!$res = @mysql_query($sql))
            die(
    "Bad Query: $sql".mysql_error());
          
    //end hms edit

          
    $this->error mysql_error();
        }
      }
      
      
    //returns array of CSV file columns
      
    function get_csv_header_fields()
      {
        
    $this->arr_csv_columns = array();
        
    $fpointer fopen($this->file_name"r");
        if (
    $fpointer)
        {
          
    $arr fgetcsv($fpointer10*1024$this->field_separate_char);
          if(
    is_array($arr) && !empty($arr))
          {
            if(
    $this->use_csv_header)
            {
              foreach(
    $arr as $val)
                if(
    trim($val)!="")
                  
    $this->arr_csv_columns[] = $val;
            }
            else
            {
              
    $i 1;
              foreach(
    $arr as $val)
                if(
    trim($val)!="")
                  
    $this->arr_csv_columns[] = "column".$i++;
            }
          }
          unset(
    $arr);
          
    fclose($fpointer);
        }
        else
          
    $this->error "file cannot be opened: ".(""==$this->file_name "[empty]" : @mysql_escape_string($this->file_name));
        return 
    $this->arr_csv_columns;
      }
      
      function 
    create_import_table()
      {
        
    $sql "CREATE TABLE IF NOT EXISTS ".$this->table_name." (";
        
        if(empty(
    $this->arr_csv_columns))
          
    $this->get_csv_header_fields();
        
        if(!empty(
    $this->arr_csv_columns))
        {
          
    $arr = array();
          for(
    $i=0$i<sizeof($this->arr_csv_columns); $i++)
              
    $arr[] = "`".$this->arr_csv_columns[$i]."` TEXT";
          
    $sql .= implode(","$arr);
          
    $sql .= ")";
          
    $res = @mysql_query($sql);
          
    $this->error mysql_error();
          
    $this->table_exists ""==mysql_error();
        }
      }
      
      
    /* change start. Added in 1.5 version */
      //returns recordset with all encoding tables names, supported by your database
      
    function get_encodings()
      {
        
    $rez = array();
        
    $sql "SHOW CHARACTER SET";
        
    $res = @mysql_query($sql);
        if(
    mysql_num_rows($res) > 0)
        {
          while (
    $row mysql_fetch_assoc ($res))
          {
            
    $rez[$row["Charset"]] = ("" != $row["Description"] ? $row["Description"] : $row["Charset"]); //some MySQL databases return empty Description field
          
    }
        }
        return 
    $rez;
      }
      
      
    //defines the encoding of the server to parse to file
      
    function set_encoding($encoding="")
      {
        if(
    "" == $encoding)
          
    $encoding $this->encoding;
        
    $sql "SET SESSION character_set_database = " $encoding//'character_set_database' MySQL server variable is [also] to parse file with rigth encoding
        
    $res = @mysql_query($sql);
        return 
    mysql_error();
      }
      
    /* change end */

    }

    ?>
    Last edited by r937; Nov 23, 2006 at 08:48.
    :::::::::::::::::::::::::::::::::::::::::::::::::::
    Versa Studio
    ExpressionEngine experts
    bas (at) versa studio dot com

  2. #2
    malloc
    Join Date
    Nov 2006
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't add the delete statement to the $sql variable, (pre)execute a separate statement for the delete. I don't know if this is your problem - but it makes more sense.

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2002
    Location
    Madison, WI
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. Can you tell me how to execute this, and where? I cannot decipher the file.

    Cheers.
    :::::::::::::::::::::::::::::::::::::::::::::::::::
    Versa Studio
    ExpressionEngine experts
    bas (at) versa studio dot com

  4. #4
    malloc
    Join Date
    Nov 2006
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    ...
    if(
    $this->table_exists)
        {
          
    mysql_query('DELETE FROM '.$this->table_name);
    ... 

  5. #5
    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 4midori
    mysql_connect("example.com", "uuuuuuu", "ppppppp");
    I sure hope that's not your real hostname/login/password. If it is, you should change it right now!
    Last edited by r937; Nov 23, 2006 at 08:48.

  6. #6
    SitePoint Wizard dreamscape's Avatar
    Join Date
    Aug 2005
    Posts
    1,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken
    I sure hope that's not your real hostname/login/password. If it is, you should change it right now!
    Supposing it is, then it wouldn't matter if he changed it since you just quoted it.
    <.smarter.web.development.>
    PHP Stuff: Plexus | Chocolate (BDD Framework... coming soon)
    Graphite

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    moderator edit: obscured the username/password

    also, moved thread to php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    malloc
    Join Date
    Nov 2006
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Supposing it is, then it wouldn't matter if he changed it since you just quoted it.
    He meant change the login details (on the server) not edit the post!

    moderator edit: obscured the username/password
    Would it not have been doing him a favour not to do this, as an incentive to change it?!

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by JonSiddle
    Would it not have been doing him a favour not to do this, as an incentive to change it?!
    no

    between the time he posted, and the time he received notification and re-visited this thread, someone could have read the unobscured info and done bad things

    but now this window of opportunity has been closed

    he should still change them anyway, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Oct 2002
    Location
    Madison, WI
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the code, and for reminding me to obscure pwds!
    :::::::::::::::::::::::::::::::::::::::::::::::::::
    Versa Studio
    ExpressionEngine experts
    bas (at) versa studio dot com


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
  •