PHP - Dynamic SQL Query from Dynamic POSTs

Question

First time question, long time reader :)

I am building forms dynamically from Columns in a MYSQL DB. These columns are created/ deleted etc.. elsewhere on my App. My form runs a query against a SQL View and pulls in the column names and count of columns. Simple! build the form, with the HTML inputs built with a PHP for loop, and it echos out the relevant HTML for the new form fields. All very easy so far.

Now i want a user to update these dynamically added fields and have the data added to the relevant columns - same table as existing columns. So, as the input fields are named the same as the columns, they are posted to a PHP script for processing.

Problem is, while i have the actual field names inserted in to the SQL INSERT query, i cannot figure out how to extract the POST data from the POST dynamically and add this to the VALUEs section of the query.

Here is my attempt.... The Query works without the variables added to it.

It works like this, first section/ is to retrieve the columns names from earlier created VIEW - as these are identical to POST names from the form. Then output to array and variable for insertion to Query. It looks like the implode function works, in that the relevant column names are correct in the statement, but i fear that my attempt to inject the column names on to the POST variables is not working.

   $custq = "SELECT * FROM customProperties";
       $result = $database->query($custq);
       $num_rows = mysql_numrows($result);

        while (list($temp) = mysql_fetch_row($result)) {
            $columns[] = $temp;
        }

        $query = '';
        foreach($columns as $key=>$value)
        {
        if(!empty($columns[$key]))
        {
        $values .= "'".'$_POST'."['".$value."'], ";
        }
        }

      $q = "INSERT INTO nodes
          (deviceName, 
          deviceInfo,
          ".implode(", ", $columns).", 
          nodeDateAdded,
          status
          ) 
          VALUES 
                ('" . $_POST['deviceName'] . "', 
                '" . $_POST['deviceInfo'] . "', 
                ".$values."
                CURDATE(),
                '1'
                )"; 

$result = $database->query($q)

Any help is much appreciated. I will feed back as much as i can. Please note, relativity new to PHP, so if i am all wrong on this, i will be glad for any tips/ advice

Regards

Stephen

Very very bad approach. Bobby Tables will ruin your life. xkcd.com/327 — GordonM
– GordonM, Commented Dec 30, 2011 at 15:11
Please escape the data from $_POST with mysql_real_escape_string(str). Otherwise someone could inject dangerous SQL code! — ComFreek
– ComFreek, Commented Dec 30, 2011 at 15:12
Thanks guys. This is a POC script right now, as I said.... Still learning. And finished app will be internal use only. Any comments relevant to the question??? — Stacky
– Stacky, Commented Dec 31, 2011 at 8:41
I managed to write a solution. Wil post it as soon as I can here — Stacky
– Stacky, Commented Jan 1, 2012 at 23:54
Look into PDO statements. This is slowly but surely becoming the "defacto" of todays database connectivity. — RaGe10940
– RaGe10940, Commented Jan 17, 2013 at 16:50

mnutsch · Accepted Answer · 2013-11-18 16:39:59Z

If you want to get the values of every POST input without knowing the input names then you can do it this way:

//get all form inputs 
foreach($_POST as $name => $value) 
{ 
    echo $name . " " . $value . "<br>"; 
}

If you want to get the value of certain POST inputs where you know the name of the input field then you can do it this way:

if(isset( $_GET["deviceName"]))
{
    $deviceName = $_POST["deviceName"]; 
}

if(isset( $_GET["deviceInfo"]))
{
    $deviceInfo = $_POST["deviceInfo"]; 
}

To connect to a database and insert the info then you have to do something like this:

$host = "localhost"; 
$dbuser = "username"; 
$pass = "password"; 
$datab = "databasename";

//Create DB connection
$con=mysqli_connect($host, $dbuser, $pass,$datab);

if (mysqli_connect_errno($con))
{
    echo "ERROR: Failed to connect to the database: " . mysqli_connect_error();
}
else
{
    echo "Connected to Database!";
}

//insert into database
mysqli_query($con, "INSERT INTO nodes (deviceName, deviceInfo) VALUES ('$deviceName', '$deviceInfo')");

(Don't forget to add mysql_real_escape_string to the $_POST lines after you get it working.)

Collectives™ on Stack Overflow

PHP - Dynamic SQL Query from Dynamic POSTs

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