PHP/MySQL Adding Data with Arrays

Question

I have written some PHP that is supposed to add data to a database through a text file. The database is connection properly but it is not executing the task. here is my code:

{ //Connect and test MySQL and specific DB (return $dbSuccess = T/F)

$hostname = "localhost";
$username = "root";
$password = "";

$databaseName = "alphacrm";

$dbConnected = @mysql_connect($hostname, $username, $password);
$dbSelected = @mysql_select_db($databaseName, $dbConnected);

$dbSuccess = true;
if ($dbConnected) {
    if (!$dbSelected) {
        echo "DB connection FAILED<br><br>";
        $dbSuccess = false;
    } else {
        echo "DB connection SUCCESSFUL<br><br>";
        $dbSuccess = true;
    }
} else {
    echo "mySQL connection FAILED<br><br>";
    $dbSuccess = false;
}
}

// Execute code ONLY if connections were successful

if ($dbSuccess) {

{ // setup ARRAY of field names

    $personField = array(
        "Salutation" => "Salutation",
        "FirstName" => "FirstName",
        "LastName" => "LastName",
        "CompanyID" => "CompanyID"
    );
}

{ // setup ARRAY of data ROWS


    // Read CSV data file

    $file = fopen("datafile.txt", "r"); // Open the 'datafile' for 'r'eading

    $i = 0;

    while (!feof($file)) { // While NOT the End Of File
        $thisLine = fgets($file); // Gets the next line from "datafile"
        $personData[$i] = explode(",", $thisLine); // Sets
                                                           // $personData[$i] = array( $thisLine );
                                                        // Whatever's in $thisLine separated by commas.
        $i++;                                           //Increment $i
    }

    fclose($file);                                      // Close the file

    $numRows = sizeof($personData);

}

{ // SQL statement with ARRAYS

    // Fieldnames part in INSERT statement
    $person_SQLinsert = "INSERT INTO tPerson (
        ".$personField["Salutation"].",
        ".$personField["FirstName"].",
        ".$personField["LastName"].",
        ".$personField["CompanyID"]."
    ) ";

    // VALUES part of INSERT statement
    $person_SQLinsert .= "VALUES ";

    $indx = 0;

    while($indx < $numRows) {
        $person_SQLinsert .= "(
            ".$personData[$indx][0].",
            ".$personData[$indx][1].",
            ".$personData[$indx][2].",
            ".$personData[$indx][3]."
            )";

        if ($indx < ($numRows - 1)) {
            $person_SQLinsert .= ", ";
        }

        $indx++;

    }

} 

{ // Echo and execute the SQL and test for success

    echo "<strong><u>SQL:<br></u></strong>";
    echo $person_SQLinsert."<br><br>";

    if (@mysql_query($person_SQLinsert)) {
        echo "was SUCCESSFUL.<br><br>";
    } else {
        echo "FAILED.<br><br>";
    }

}

} // END ($dbSuccess)

?>

As the code closer to the bottom of the script makes it echo out "FAILED" if it wasn't able to do the function properly, all I get is

DB connection SUCCESSFUL

SQL: INSERT INTO tPerson ( Salutation, FirstName, LastName, CompanyID ) VALUES ( Mr, Mike, Freighn, 4 ), ( Mrs, Kathie, Arnott, 2 ), ( Ms, Zeta, Flowers, 1 ), ( M, Guy, Donnet, 3 ), ( Mrs, Harriet, Hennesey, 3 ), ( Dr, George, Terry, 2 ), ( Sir, Geoffrey, Paul, 1 ), ( Mrs, Laura, Winalott, 0 ), ( Mr, Peter, Bellows, 1 )

FAILED.

Please reply with any ideas on how I can fix this error as it has stumped me.

do not suppress the mysql_query error so you can see why!!

mamdouh alramadan
– mamdouh alramadan

2014-04-20 22:35:47 +00:00
Commented Apr 20, 2014 at 22:35 — mamdouh alramadan
– mamdouh alramadan, Commented Apr 20, 2014 at 22:35

Eugen Rieck · Accepted Answer · 2014-04-20 22:35:52Z

2

You need quotes!

$person_SQLinsert .= "(
            '".$personData[$indx][0]."',
            '".$personData[$indx][1]."',
            '".$personData[$indx][2]."',
            '".$personData[$indx][3]."'
            )";

Mind that this breaks, if a field contains a single quote - escape accordingly!

answered Apr 20, 2014 at 22:35

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP/MySQL Adding Data with Arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related