0

I thought I would edit my question as by the comment it seems this is a very insecure way of doing what I am trying to acheive.

What I want to do is allow the user to import a .csv file but I want them to be able to set the fields they import.

Is there a way of doing this apart from the way I tried to demonstrate in my original question?

Thank you Daniel

This problem I am having has been driving me mad for weeks now, everything I try that to me should work fails.

Basically I have a database with a bunch of fields in.

In one of my pages I have the following code

$result = mysql_query("SHOW FIELDS FROM my_database.products"); 

while ($row = mysql_fetch_array($result)) { 

    $field = $row['Field'];

    if ($field == 'product_id' || $field == 'product_name' || $field == 'product_description' || $field == 'product_slug' || $field == 'product_layout') {
    } else {
        echo '<label class="label_small">'.$field.'</label>
        <input type="text" name="'.$field.'" id="input_text_small" />';
    }
}

This then echos a list of fields that have the label of the database fields and also includes the database field in the name of the text box.

I then post the results with the following code

$result = mysql_query("SHOW FIELDS FROM affilifeed_1000.products"); 

$i = 0; 

while ($row = mysql_fetch_array($result)) { 

    $field = $row['Field'];

    if ($field == 'product_name' || $field == 'product_description' || $field == 'product_slug' || $field == 'product_layout') {

    } else {

        $input_field = $field;
        $output_field = mysql_real_escape_string($_POST[''.$field.'']);
    }

    if ($errorcount == 0) {
        $insert = "INSERT INTO my_database.products ($input_field)
        VALUES ('$output_field')";

        $result_insert = mysql_query($insert) or die ("<br>Error in database<b> ".mysql_error()."</b><br>$result_insert");

    }

}

if ($result_insert) {

    echo '<div class="notification_success">Well done you have sucessfully created your product, you can view it by clicking here</div>';

} else {

    echo '<div class="notification_fail">There was a problem creating your product, please try again later...</div>';

}

It posts sucessfully but the problem is that it creates a new "row" for every insert.

For example in row 1 it will post the first value and then the rest will be empty, in row 2 it will post the second value but the rest will be empty, row 3 the third value and so on...

I have tried many many many things to get this working and have researched the foreach loop which I haven't been familiar with before, binding the variable, imploding, exploding but none of them seem to do the trick.

I can kind of understand why it is doing it as it is wrapped in the while loop but if I put it outside of this it only inserts the last value.

Can anyone shed any light as to why this is happening?

If you need any more info please let me know.

Thank you Daniel

Improve this question
3
  • Could you perhaps shorten the question to what's relevant to a specific problem? Commented Jul 11, 2013 at 16:39
  • And the whole idea of iterating over columns and setting it one by one is just wrong. That will have a TERRIBLE performance. Commented Jul 11, 2013 at 16:42
  • Start with a csv file on your server and import it all into your database. Then allow the user to upload a csv. Then allow them to not do certain fields. Commented Jul 11, 2013 at 16:54

2 Answers 2

Reset to default
2

You're treating each field you're displaying as its own record to be inserted. Since you're trying to create a SINGLE record with MULTIPLE fields, you need to build the query dynamically, e.g.

foreach ($_POST as $key => $value);
    $fields[] = mysql_real_escape_string($key);
    $values[] = "'" . msyql_real_escape_string($value) . "'";
} // build arrays of the form's field/value pairs

$field_str = implode(',', $fields); // turn those arrays into comma-separated strings
$values_str = implode(',', $values);

$sql = "INSERT INTO yourtable ($field_str) VALUES ($value_str);"
// insert those strings into the query

$result = mysql_query($sql) or die(mysql_error());

which will give you

INSERT INTO youtable (field1, field2, ...) VALUES ('value1', 'value2', ...)

Note that I'm using the mysql library here, but you should avoid it. It's deprecated and obsolete. Consider switching to PDO or mysqli before you build any more code that could be totally useless in short order.

On a security basis, you should not be passing the field values directly through the database. Consider the case where you might be doing a user permissions management system. You probably wouldn't want to expose a "is_superuser" field, but your form would allow anyone to give themselves superuser privileges by hacking up their html form and putting a new field saying is_superuser=yes.

This kind of code is downright dangerous, and you should not be using it in a production system, no matter how much sql injection protect you build into it.

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

3 Comments

Hi Marc, Thank you for your answer and letting me know about the dangers of using it. Is it dangerous however if it is in a different table to anything that would grant a user permission?
It is dangerous it involves your database in anyway.
Your code is basically vacuuming up a list of all fields in a table, and allowing a user to directly change them. This can let a user trash vast swaths of a database. e.g. consider foreign keys with cascaded deletes. One incorrect record change and vast swaths of your db could be automatically wiped out because the user changed one record that orphaned a huge number of other records.
0

Alright....I can't say that I know exactly whats going on but lets try this...

First off....

$result = mysql_query("SHOW FIELDS FROM my_database.products"); 
$hideArray = array("product_id","product_name","product_description", "product_slug","product_layout");
while ($row = mysql_fetch_array($result)) { 
    if (!in_array($row['Field'], $hideArray)){
        echo '<label class="label_small">'.$field.'</label>
        <input type="text" name="'.$field.'" id="input_text_small" />';
    }
}

Now, why you would want to post this data makes not sense to me but I am going to ignore that.....whats really strange is you aren't even using the post data...maybe I'm not getting something....I would recommend using a db wrapper class...that way you can just through the post var into....ie. $db->insert($_POST) ....but if you ware doing it long way...

$fields = "";
$values = "";
$query = "INSERT INTO table ";
foreach ($_POST as $key => $data){
   $values .= $data.",";
   $fields .= $fields.",";
}
substr($values, 0, -1);
substr($fields, 0, -1);

$query .= "(".$fields.") VALUES (".$values.");";

This is untested....you can also look into http://php.net/manual/en/function.implode.php so you don't have to do the loop.

Basically you don't seem to understand what is going on in your script...if you echo the sql statements and you can a better idea of whats going....learn what is happening with your code and then try to understand what the correct approach is. Don't just copy and paste my code.

Improve this answer

2 Comments

Hi Hendr1, If you now look at the original post you will see what I am trying to achieve but am unsure now if it is the right way to go about it :(
Ot if it's an array your lookin to insert for later use. You can use php.net/serialize to insert the array how it is

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.