0

I am trying to write a function that will insert data into a MySQL table like this:

for ($i = 0; $i < $num; $i++){
    if ($header[$i] == $user_table_property->name) {
        $import = "
INSERT into testing (
    $header[$i]
) values (
    '$data[$i]'
)";
    }
}

If I have something like this, it will just insert first data into first column, can I know what should I change or add? I Googled some examples and edited it myself, but it's still not working.

Here is the longer part of the codes.

if (isset($_POST['submit'])) {

if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
    echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "<br></h1>";
    echo "<h2>Displaying contents:</h2>";
    readfile($_FILES['filename']['tmp_name']);
    echo "<br>";
    echo $headers;
}


$handle = fopen($_FILES['filename']['tmp_name'], "r");

    $header = fgetcsv($handle);


    while(! feof($handle)){


while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

                $num = count($data);

               mysql_select_db("EMC", $db);

               $sql = "SELECT * from CSVtest";
               $result = mysql_query($sql,$db);

                   while ($user_table_property = mysql_fetch_field($result)) 
                            {

                  for($i=0; $i<$num; $i++){

                                  if($header[$i] == $user_table_property->name )
                                               {

                                               $import = "insert into CSVtest ( `" . $header[$i] . "`) values ('" . $data[$i] . "')";

                                               }

                                           }

                              mysql_query($import) or die(mysql_error()) ; 
                            }

                                                       } 
               }

fclose($handle);

print "Import done";
Improve this question
16
  • Are you trying to pick off the first value in the list as the header to insert into the table? As in, userid,aaa,aku,pwk, etc., with each representing a new row with one column? Commented Oct 29, 2011 at 22:54
  • Also, your code formatting is very distracting. Commented Oct 29, 2011 at 22:55
  • @Jared Farrish User will need to put a header such as "FirstName,LastName,Cell..." at the first row,and I will take the values as the column names to be inserted.Even if there are other column names which is not exist in table, it will just ignore it.Yes I know...so sorry about it... Commented Oct 29, 2011 at 22:58
  • Have you thought about LOAD DATA INFILE in MySQL? Commented Oct 29, 2011 at 23:18
  • What I would imagine you'd need to do (if LOAD DATA INFILE won't work for some reason) is turn CSV file row data into a $columns and $values arrays as you read in the file, checking and adding to each array a confirmed header and it's row contents as necessary. Then using those two arrays, create INSERT statements per row by iterating over $values, then do the mysql_query($import). You have to turn your current table-layout CSV format into arrays that can be iterated over without losing the referential integrity of each row and column, though. Commented Oct 29, 2011 at 23:25

3 Answers 3

Reset to default
4

let's say you have, 

$headers = array(..);
$data = array();

try this 

$sql = "insert into table_name(".implode(",",$headers).") values(".implode(",",$data).")";

Surely data should be escaped and order of headers should match order of data.

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

1 Comment

Not a bad idea. Two issues: One, the if check is not demonstrated, and the values list needs ' around the values (most likely, or at least it can't be assumed otherwise).
1

I can tell you that you're not interpolating the array values properly:

$import = "
INSERT into testing (
    {$header[$i]}
) values (
    '{$data[$i]}'
)";

When they're array values, you have to use {} around the variable and index (or indices) for it to expand the variable value.

Also, it should probably be doing something like this:

$value = mysql_real_escape_string($data[$i]);
$import = "
INSERT into testing (
    `{$header[$i]}`
) values (
    '$value'
)";

You don't have any mysql_query() or whatnot in the if statement, so it's not apparent the code is going to work (you can't run $import after the for loop or it will only have the last $import set).

The whole technique you're using here doesn't seem correct anyway; you probably actually want to build the column and value lists, then after the for loop, implode() them into a string for $import.

Improve this answer

3 Comments

Thanks for your reply.I'm actually doing something related to csv import..and this is part of the codes,but I'm not sure which part is wrong...
Right, I see now your note about only the first value. What does this: $user_table_property->name represent? It appears this doesn't change. I would suggest putting more of the code in question into your answer.
I will put more of the code,will be greatly appreciated if you willing to take a look. It's about when a user import a csv,it will just pick the column needed and store into database,someone suggest me to get the table column list before build the query,so I try something like this...
1

Try 

"INSERT INTO `testing` ( `$header[$i]` ) 
           VALUES
            ('$data[$i]');"

Also, it's not very recommended to write it this way. You better write it like: 

"INSERT INTO testing( `" . $header[$i] . "` ) 
           VALUES
            ('" . $data[$i] . "');"
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.