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 -1 down vote favorite

I have a script as below. There are some values from $_POST to be inserted to database. But it is not working. Need your help.

<?  
$field          = array(Priority_Rank, Attending_Period, Priority_Point_Low, Priority_Point_High, Other_Consideration);

$fields         = implode(',', $field);

$fieldpost      = array();
for ($i=0; $i<count($field); $i++)  {   
     $fieldpost[]   = $_POST[$i];   }

$fieldposts     = implode (',', $fieldpost);        

$query1        =    "INSERT INTO $maindb    ($mainID,   $fields) VALUES ('$seq',$fieldposts)";      

mysql_query($query1);   ?>

I found out that the problem is in VALUES(...,$fieldposts), because if I change the query become the below, it is working perfectly.

$query1 =   "INSERT INTO $maindb    ($mainID,   $fields) VALUES ('$seq','$_POST[0]','$_POST[1]','$_POST[2]','$_POST[3]','$_POST[4]')";

But since this query will also be used by other script that have different quantity of $_POST, I really need them to be looped in this file.

Note: $field is located in the other file.

share|improve this question
3  
Please move into the 21st century and learn to use MySQLi or PDO with parameterised queries... then you can forget about escaping strings (which you're failing to do anyway) and you don't need to enclose string values in quotes (which you do when you're injecting values into a MySQL query, but you're failing to do and which is the probable cause of your error) –  Mark Baker Dec 29 '13 at 11:59
    
do not use short open tags <? or you are asking fro troubles –  Marcin Orlowski Dec 29 '13 at 12:01
    
You should enclose the values in quotes and mask them. –  iMx Dec 29 '13 at 12:01
    
@MarkBaker Parametrized queries are hard to do with a variable number of fields. –  Barmar Dec 29 '13 at 12:03
    
@Barmar - but there's still plenty of examples showing how to do them on the interweb (or here on SO) if you look, and they would eliminate a lot of the problems in this code –  Mark Baker Dec 29 '13 at 12:04
add comment

3 Answers

up vote 1 down vote accepted

You need to do:

for ($i=0; $i<count($field); $i++)  {   
     $fieldpost[]   = "'" . mysql_real_escape_string($_POST[$i]) . "'";
}

so that the values will be enclosed in quotes and also be escaped properly.

share|improve this answer
    
It is working perfectly. Thank you so much. –  user2555665 Dec 29 '13 at 12:21
add comment
up vote 0 down vote

Let's assume you have an array containing the lines to insert :

<?php
$lines = array(
    array("Value 1A", "Value 2A", "Value 3A", "..."),
    array("Value 1B", "Value 2B", "Value 3B", "..."),
    array("Value 1C", "Value 2C", "Value 3C", "...")
    // ...
);

First of all, you need to escape your values before insterting them in a query, especially if they come from the user ($_POST, etc.). mysql_real_escape_string() does that for you.

<?php
foreach ($lines as &$line) {
    foreach ($line as &$value) {
        $value = '\'' . mysql_real_escape_string($value) . '\'';
    }
}

Then your build your individual value sets :

<?php
$sets = array();
foreach ($lines as $line) {
    $sets[] = '(' . implode(', ', $line) . ')';
}

And only then you build your query

<?php
$query = 'INSERT INTO yourtable (field1, field2, field3) VALUES '
$query .= implode(', ', $sets)

mysql_query($query)

You can of course combine the two first loops, I only separated for the sake of explanation :

<?php
$sets = array();
foreach ($lines as $line) {
    foreach ($line as &$value) {
        $value = '\'' . mysql_real_escape_string($value) . '\'';
    }

    $sets[] = '(' . implode(', ', $line) . ')';
}

$query = 'INSERT INTO yourtable (field1, field2, field3) VALUES '
$query .= implode(', ', $sets)

mysql_query($query)
share|improve this answer
add comment
up vote 0 down vote

Try something like the following to ensure the field name posted is valid and the values correctly escaped.

$fields = array('column_a', 'column_b', 'column_c');

foreach($_POST as $name => $value) {
  if (isset($fields[$name])) {
    $columns[] = $fields[$name];
    $values[]  = "'" . mysql_real_escape_string($value) ."'";
  }
}
if (! empty($columns)) {
  $sql = sprintf('INSERT INTO %s (%s) (%S)', $tableName, implode(',', $columns), implode(',', $values));
}

Really you should be using parameter placeholders (?) for the values and prepare the statement with PDO or equivalent.

share|improve this answer
add comment

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.