current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm trying to grab data from an XLS file and dump it into an mySQL DB.

I located a php file that will take the contents of the XLS and display them - as tabular data - on a web page. I have attempted to modify this code to insert my data into a DB.

Here is the function that does the work - and that I have been attempting to modify:

function dumpSQL($row_numbers=false,$col_letters=false,$sheet=0) {
        $xlsData = '';
        for($row=1;$row<=$this->rowcount($sheet);$row++) {
            $rowheight = $this->rowheight($row,$sheet);
            for($col=1;$col<=$this->colcount($sheet);$col++) {
                if(!$this->sheets[$sheet]['cellsInfo'][$row][$col]['dontprint']) {
                    // cell value
                    //$val = $this->val($row,$col,$sheet);
                    $xlsData[$row][$col] = $this->val($row,$col,$sheet);
                }
            }
        }
        // Make a MySQL Connection
        mysql_connect("*******", "******", "*******") or die(mysql_error());
        mysql_select_db("***********") or die(mysql_error());
        $sql = "INSERT INTO BEER_LOCATIONS (LOCATION_NAME, LOCATION_STREET, LOCATION_CITY, LOCATION_STATE, LOCATION_ZIPCODE, LOCATION_PHONE, BEER_STYLE ) VALUES ($xlsData)";
        $result = mysql_query("
            $sql
        ") or die(mysql_error());
}

I get the following error when I run this code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

I have also tried to remove the portion where it obtains the multidimensional array and just build a static version:

$xlsData[0][0] = "Hello World";
$xlsData[0][1] = "38380 - Thirteen Coins-Boren";
$xlsData[0][2] = "125 Boren Ave N";
$xlsData[0][3] = "Seattle";
$xlsData[0][4] = "WA";
$xlsData[0][5] = "98109";
$xlsData[0][6] = "2066822513";
$xlsData[0][7] = "34740 - Georgetown Keg - 1/2 Manny Pale";
$xlsData[1][0] = "Hello World";
$xlsData[1][1] = "38380 - Thirteen Coins-Boren";
$xlsData[1][2] = "125 Boren Ave N";
$xlsData[1][3] = "Seattle";
$xlsData[1][4] = "WA";
$xlsData[1][5] = "98109";
$xlsData[1][6] = "2066822513";
$xlsData[1][7] = "34740 - Georgetown Keg - 1/2 Manny Pale";
$xlsData[2][0] = "Hello World";
$xlsData[2][1] = "38380 - Thirteen Coins-Boren";
$xlsData[2][2] = "125 Boren Ave N";
$xlsData[2][3] = "Seattle";
$xlsData[2][4] = "WA";
$xlsData[2][5] = "98109";
$xlsData[2][6] = "2066822513";
$xlsData[2][7] = "WA";
$xlsData[3][0] = "Hello World";
$xlsData[3][1] = "86036 - Aafes Ft Lewis Class Vi";
$xlsData[3][2] = "Bldg 2202";
$xlsData[3][3] = "Fort Lewis";
$xlsData[3][4] = "WA";
$xlsData[3][5] = "98433";
$xlsData[3][6] = "2539644128";
$xlsData[3][7] = "42624 - Georgetown Keg - 1/4 Manny Pale";

I end up with the same error.

I attempted to serialize my data prior to inserting it into my DB:

$xlsData = serialize($xlsData);

Thanks for any help.

I'm having trouble implementing the mysql_real_escape_string(). It seems like it needs to wrap around the implode(), but I get an error when I try that.

$sql = "INSERT INTO BEER_LOCATIONS (LOCATION_NAME, LOCATION_STREET, LOCATION_CITY, LOCATION_STATE, LOCATION_ZIPCODE, LOCATION_PHONE, BEER_STYLE ) VALUES ('" . mysql_real_escape_string(implode("', '", $row)) . "')";

I get this error when I try that: Column count doesn't match value count at row 1

Thanks again.

share|improve this question
    
This isn't actually serialization you're trying to do. Serialization is when you convert an object/array/etc. to a string so you can store it without losing the structure. When you store an array of values in a database, you're really just adapting them to fit into the database structure. –  Compeek Apr 25 '11 at 20:22
    
Thanks for the heads up regarding serialization. I removed that code. –  Mark Apr 25 '11 at 23:50
    
Did you see if my answer works for you yet? :) –  Compeek Apr 26 '11 at 2:52

2 Answers 2

up vote 0 down vote

Try replacing this:

$sql = "INSERT INTO BEER_LOCATIONS (LOCATION_NAME, LOCATION_STREET, LOCATION_CITY, LOCATION_STATE, LOCATION_ZIPCODE, LOCATION_PHONE, BEER_STYLE ) VALUES ($xlsData)";
$result = mysql_query("
    $sql
") or die(mysql_error());

with this:

foreach($xlsData as $row) {
    $sql = "INSERT INTO BEER_LOCATIONS (LOCATION_NAME, LOCATION_STREET, LOCATION_CITY, LOCATION_STATE, LOCATION_ZIPCODE, LOCATION_PHONE, BEER_STYLE ) VALUES ('" . implode("', '", $row) . "')";
    $result = mysql_query($sql) or die(mysql_error());
}

You have to insert each row one at a time, so that's what the foreach loop accomplishes. Also, you can't just put the array itself for the values. The implode() function turns the array of column values into a string.

EDIT: Also, you should probably change $xlsData = ''; near the beginning of the function to $xlsData = array(); I'm not actually sure if the way you had it is a problem, but since $xlsData is supposed to be an array, it's better to set it to an empty array than an empty string.

share|improve this answer
    
Also, if there are any commas in your data, it'll break the SQL query. It doesn't look like there are any in the data you provided, so I don't want to give an overcomplicated answer, but you'd actually want to use mysql_real_escape_string() to escape any characters that would be a problem. –  Compeek Apr 25 '11 at 20:13
up vote 0 down vote

I got the answer from another helpful person. Thanks to everyone who helped me :)

implode("', '", array_map('mysql_real_escape_string', $row))
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.