Help, please. I am missing something

Help
Dan Davis
2009-08-20
2013-05-02
  • Dan Davis
    Dan Davis
    2009-08-20

    I am missing something here and need an extra set of eyes (or maybe a dozen extra). I based this code from the following devzone article: http://devzone.zend.com/article/3336 and am recieving the following error for every row in the spreadsheet:

    Array ( [0] => 00000 ) ERROR: Could not execute query: INSERT INTO BSAProviderRawData (TAX_ID, LAST_NAME, MI, FIRST_NAME, DEGREE, FACILITY_NAME, SPECIALTY, ADDRESS, CITY, STATE, ZIP, TELEPHONE, FAX, EFFECTIVE_DATE, TERM_DATE, FEE_SCHEDULE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?). 1

    My source code follows, the database table is designed to match the spreadsheet exactly with the exception of the auto-increment ID field:

    <?php

    $filename = $_GET['filename'];

    require("common.php");

    if ($user != "" and $password != "") {
        //set up SQL connection
        $link = mysql_connect ($server, $user, $password);
       
        if (! $link) {
            die ("Couldn't connect to mySQL server");
        }
       
        if (!mysql_select_db ($db, $link) ) {
            die ("Couldn't open $db: ".mysql_error() );
        }
    } else { }

    mysql_query("CREATE TABLE IF NOT EXISTS BSAProviderRawData(
    ID INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`ID`),
    TAX_ID varchar(125) NOT NULL,   
    LAST_NAME varchar(125) NOT NULL,   
    MI varchar(125) NOT NULL,   
    FIRST_NAME varchar(125) NOT NULL,   
    DEGREE varchar(125) NOT NULL,   
    FACILITY_NAME varchar(125) NOT NULL,
    SPECIALTY varchar(125) NOT NULL,
    ADDRESS varchar(125) NOT NULL,
    CITY varchar(125) NOT NULL,   
    STATE varchar(125) NOT NULL,   
    ZIP varchar(125) NOT NULL,   
    TELEPHONE varchar(125) NOT NULL,
    FAX varchar(125) NOT NULL,
    EFFECTIVE_DATE varchar(125) NOT NULL,
    TERM_DATE varchar(125) NOT NULL,
    FEE_SCHEDULE varchar(125) NOT NULL)");

    mysql_query("TRUNCATE TABLE BSAProviderRawData");

    // read from excel into a MySQL database using phpexcelreader

    // include class file
    include 'reader.php';

    // initialize reader object
    $excel = new Spreadsheet_Excel_Reader();

    // read spreadsheet data
    $excel->read($filename);

    // attempt a connection
    try {
    //   $pdo = new PDO('mysql:dbname=test;host=localhost', 'user', 'pass');
       $pdo = new PDO('mysql:dbname=ddcsltd_nexcaliber;host=localhost', 'ddcsltd', 'dd0326');

      
    } catch (PDOException $e) {
       die("ERROR: Could not connect: " . $e->getMessage());
    }

    // iterate over spreadsheet rows and columns
    // convert into INSERT query
    $sql = "INSERT INTO BSAProviderRawData (TAX_ID, LAST_NAME, MI, FIRST_NAME, DEGREE, FACILITY_NAME, SPECIALTY, ADDRESS, CITY, STATE, ZIP, TELEPHONE, FAX, EFFECTIVE_DATE, TERM_DATE, FEE_SCHEDULE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    if ($stmt = $pdo->prepare($sql)) {
      $x=2;
      while($x<=$excel->sheets[0]['numRows']) {
        $stmt->bindParam(1, $excel->sheets[0]['cells'][$x][1]);
        $stmt->bindParam(2, $excel->sheets[0]['cells'][$x][2]);
        $stmt->bindParam(3, $excel->sheets[0]['cells'][$x][3]);
        $stmt->bindParam(4, $excel->sheets[0]['cells'][$x][4]);
        $stmt->bindParam(5, $excel->sheets[0]['cells'][$x][5]);
        $stmt->bindParam(6, $excel->sheets[0]['cells'][$x][6]);
        $stmt->bindParam(7, $excel->sheets[0]['cells'][$x][7]);
        $stmt->bindParam(8, $excel->sheets[0]['cells'][$x][8]);
        $stmt->bindParam(9, $excel->sheets[0]['cells'][$x][9]);
        $stmt->bindParam(10, $excel->sheets[0]['cells'][$x][10]);
        $stmt->bindParam(11, $excel->sheets[0]['cells'][$x][11]);
        $stmt->bindParam(12, $excel->sheets[0]['cells'][$x][12]);
        $stmt->bindParam(13, $excel->sheets[0]['cells'][$x][13]);
        $stmt->bindParam(14, $excel->sheets[0]['cells'][$x][14]);
        $stmt->bindParam(15, $excel->sheets[0]['cells'][$x][15]);
        $stmt->bindParam(16, $excel->sheets[0]['cells'][$x][16]);
        if (!$stmt->execute()) {
          echo "ERROR: Could not execute query: $sql. " . print_r($pdo->errorInfo());
        } 
        $x++;
      }
    } else {
      echo "ERROR: Could not prepare query: $sql. " . print_r($pdo->errorInfo());
    }

    // close connection
    unset($pdo);
    ?>

     
    • George Loeppky
      George Loeppky
      2009-08-21

      Your SQL looks correct, your code looks good too.  Well at least I can't spot a problem.  I think I would check the data to be sure that you have the right formats going into each field.

       
      • Dan Davis
        Dan Davis
        2009-08-21

        all the fields are VARCHAR 125 and all the spreadsheet data is text, I don't think this is the issue but I will recheck it.

         
        • George Loeppky
          George Loeppky
          2009-08-21

          Your fields are defined to not allow nulls, any blanks in your cells will be seen as nulls and therefore will fail on INSERT.  You could change the definition of the table to allow the fields to be NULL.  Or you will have to check the data and put in an empty string.

           
      • George Loeppky
        George Loeppky
        2009-08-21

        Oh it's the spreadsheet code you wanted me to look at, sorry.  Again the code all looks right.  One thing I would do though is check if the row is set.  You may have one empty row somewhere.  I would check it with something like

        if(!isset($excel->sheets[$sheetNum]['cells'][$x]))

        Also, empty cells will often be undefined which is different than empty.  You may need to test each cell to see if it's undefined, if so set that field to empty.