title: Importing CSV data using PHP/MySQL - Mysqli syntax
body: <p>IN THE BOTTOM OF THIS QUESTION THE FINAL CODE THAT FINALLY WORKED!</p>
  
  <p>Trying to implement this (<a href="https://stackoverflow.com/questions/11448307/importing-csv-data-using-php-mysql">Importing CSV data using PHP/MySQL</a>). I must be almost there...</p>
  
  <p>notes1: my $sql came straight from copy/paste phpmyadmin (generate php code) and ran just fine in the phpmyadmin.</p>
  
  <p>note2: If I comment the line $sql="DELETE FROM dbase" the code runs just fine (and the table is cleaned).</p>
  
  <p>So if i know my sql is right and my code can run other sqls, why does the below does not run?! Im getting: </p>
  
  <p>Call to a member function execute() on a non-object - for the line </p>
  
  <pre><code>$stmt-&gt;execute();
  </code></pre>
  
  <p>Full code:</p>
  
  <pre><code>&lt;?php
  $mysqli  =  new mysqli('localhost','root','pass','dbase');
  /* check connection */
  if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
  }
  
  $sql = "LOAD DATA INFILE \'./myfile.csv\' INTO TABLE tab\n"
  . " FIELDS TERMINATED BY \',\'\n"
  . " LINES TERMINATED BY \'\\r\\n\'\n"
  . " IGNORE 1 LINES";
  
  //$sql="DELETE FROM dbase";
  
  $stmt=$mysqli-&gt;prepare($sql);
  $stmt-&gt;execute(); 
  $stmt-&gt;close();
  $mysqli-&gt;close();
  ?&gt;
  </code></pre>
  
  <p>tks in advance!</p>
  
  <p>EDIT:</p>
  
  <p>Made below changes and still not working!</p>
  
  <p>new code:</p>
  
  <pre><code>&lt;?php
  
  $mysqli  =  new mysqli('localhost','root','pass','dbase');
  /* check connection */
  if (mysqli_connect_errno()) {
      printf("Connect failed: %s\n", mysqli_connect_error());
      exit();
  }
  
  /* return name of current default database */
  if ($result = $mysqli-&gt;query("SELECT DATABASE()")) {
      $row = $result-&gt;fetch_row();
      printf("Default database is %s.\n", $row[0]);
      $result-&gt;close();
  }
  
  $sql = "LOAD DATA INFILE 'C:/xampp/htdocs/myfile.csv' INTO TABLE tab
          FIELDS TERMINATED BY ','
      LINES TERMINATED BY '\\r\\n'
      IGNORE 1 LINES";
  
  echo "&lt;br&gt;";
  echo "&lt;br&gt;";
  echo $sql;
  echo "&lt;br&gt;";
  echo "&lt;br&gt;";
  $stmt=$mysqli-&gt;prepare($sql);
  
  /* Prepared statement, stage 1: prepare */
  if (!($stmt = $mysqli-&gt;prepare($sql))) 
  {    echo "Prepare failed: (" . $mysqli-&gt;errno . ") " . $mysqli-&gt;error;
  }
  
  
  // NOTE HERE WE'RE DUMPING OUR OBJ TO SEE THAT IT WAS 
  // CREATED AND STATUS OF PREPARE AND THEN KILLING SCRIPT   
  var_dump($mysqli);
  exit();
  
  //$sql="DELETE FROM intrasdump
  
  $stmt=$mysqli-&gt;prepare($sql);
  $stmt-&gt;execute(); 
  $stmt-&gt;close();
  $mysqli-&gt;close();
  ?&gt;
  </code></pre>
  
  <p>What i see in my browser when I ran this is the following:</p>
  
  <p>Default database is dbname. </p>
  
  <p>LOAD DATA INFILE 'C:/xampp/htdocs/myfile.csv' INTO TABLE tab FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES</p>
  
  <p>Prepare failed: (1295) This command is not supported in the prepared statement protocol yetobject(mysqli)#1 (19) { ["affected_rows"]=> int(-1) ["client_info"]=> string(79) "mysqlnd 5.0.11-dev - 20120503 - $Id: 40933630edef551dfaca71298a83fad8d03d62d4 $" ["client_version"]=> int(50011) ["connect_errno"]=> int(0) ["connect_error"]=> NULL ["errno"]=> int(1295) ["error"]=> string(68) "This command is not supported in the prepared statement protocol yet" ["error_list"]=> array(0) { } ["field_count"]=> int(1) ["host_info"]=> string(20) "localhost via TCP/IP" ["info"]=> NULL ["insert_id"]=> int(0) ["server_info"]=> string(6) "5.6.11" ["server_version"]=> int(50611) ["stat"]=> string(133) "Uptime: 7993 Threads: 2 Questions: 865 Slow queries: 0 Opens: 75 Flush tables: 1 Open tables: 68 Queries per second avg: 0.108" ["sqlstate"]=> string(5) "00000" ["protocol_version"]=> int(10) ["thread_id"]=> int(117) ["warning_count"]=> int(0) }</p>
  
  <p>Note: If I copy paste the sql string echoed above in to mysql prompt, it runs just fine. That should mean that both the file location issue and the sql string itself are fine, no???</p>
  
  <p>how can this be so hard?!</p>
  
  <p>EDIT 3.</p>
  
  <p>Tks for all answers and comments. Final code version below works:</p>
  
  <pre><code>&lt;?php
  
  $mysqli  =  new mysqli('localhost','root','pass','dbname');
  /* check connection */
  if (mysqli_connect_errno()) {
      printf("Connect failed: %s\n", mysqli_connect_error());
      exit();
  }
  
  $sql = "LOAD DATA INFILE 'C:/xampp/htdocs/myfile.csv' INTO TABLE tab
          FIELDS TERMINATED BY ','
          LINES TERMINATED BY '\\r\\n'
          IGNORE 1 LINES";
  
  //Try to execute query (not stmt) and catch mysqli error from engine and php error
  if (!($stmt = $mysqli-&gt;query($sql))) {
      echo "\nQuery execute failed: ERRNO: (" . $mysqli-&gt;errno . ") " . $mysqli-&gt;error;
  };
  ?&gt;
  </code></pre>
  
  <p>useful notes:</p>
  
  <ul>
  <li><p>note the file path uses <strong>frw-slash</strong> instead of windows-default back-slash. Orderwise will just note work. God knows how I figured that one out...</p></li>
  <li><p>take advantage of the many debugging codes offered in the answers. i guess one effective way to check if your sql is right to echo (<code>echo $sql</code>) it and copy/paste in your sql prompt. don't trust phpmyadmin 'create php PHP code' functionality. </p></li>
  <li><p>keep in mind 'Prepared stmts don't support LOAD DATA'</p></li>
  </ul>