Create update query for dynamic database

Question

I am trying to update a row in a mysql database. To do this, I would use a simple query like this:

    "UPDATE  `contactinfo` SET  `Company` =  'Google', WHERE `id` =  '1';"

The problem is that the database is dynamic and users can add columns at any time. So I do not know the names of the columns. To find the names and create a form to post to the page that will actually do the mysql work uses this code:

    <?php
            $result = mysql_query("select * from contactinfo WHERE `id` = '".$rid."';");

            if (!$result) {
                die('Query failed: ' . mysql_error());
            }
            $i = 0;
            while ($i < mysql_num_fields($result)) {
                $meta = mysql_fetch_field($result, $i);
                if (!$meta) {
                    echo "ERROR";
                }
                $name = $meta->name;

                $r = mysql_fetch_array(mysql_query("select * from contactinfo WHERE `id` = '".$rid."';"));
                $content = $r[$name];

                if($name != 'id') {
                    echo "<tr><td align='center'><div align='left'>Edit ".$name."</div></td></tr>";
                    echo "<tr><td align='center'><input type='text' value='" . $content . "' /></td></tr>";
                }
                $i++;
            }
            mysql_free_result($result);
        ?>

This creates a nice little table with input boxes that allow the user to edit the content of the row that has been selected. The row id number($rid) is used to identify which row needs to be changed.

My question is, how can I get the new content for the row from the posted form and create a query to update it? I can't seem to figure out how to dynamically get the names of the form as well as the new content to the write the query.

If any clarification is needed just let me know and all help is appreciated.

Thanks.

I attempted to write a while loop to get the posts and give them all respected variables but that failed due to me not knowing what to use in the $_POST['name'] — Olokoo
– Olokoo, Commented Dec 5, 2012 at 22:42
@Ian34 - Remember that $_POST is just an associative array, so you can get all the keys (field names) as well even if you don't know what they are. Like so foreach($_POST as $key => $value) — Eric Petroelje
– Eric Petroelje, Commented Dec 5, 2012 at 22:47

Mihai P. · Accepted Answer · 2012-12-05 23:02:42Z

2

The easiest way is to name the fields in the form exactly how the name of the fields in the database are. Lets say you have this form

<form action="">
   <input name="field[firstname]">
   <input name="field[lastname]">
   <input name="field[address]">
</form>

You should probably be able to create the form based on the fields names too, you are probably already doing this. In the file that processes the response you can do something like this:

foreach($_POST['field'] as $field_name => $field_value) {
   $sql_str[] = "{$field_name} = '{$field_value}'";
}

This just goes through the 'field' array that comes from post and puts the proper update text into another array. Then just do a

mysql_query("UPDATE contactinfo SET ".implode(',', $sql_str)." WHERE `id` = '".$rid."';")

To put it into the database.

answered Dec 5, 2012 at 23:02

Mihai P.

9,3673 gold badges40 silver badges50 bronze badges

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

2 Comments

Olokoo Over a year ago

Wow, this works perfectly. Never would have done it like that. Thanks!

Mihai P. Over a year ago

I do recommend you use a framework, it might make things a lot easier for you :). There are some good ones out there that will help you a lot.

Mic1780 · Accepted Answer · 2012-12-05 22:44:05Z

0

What you can do is add a column in the database with a bit flag and if the user is new or old, the flag will reflect it. that way you can update the user to say if it is new or old.

hope this helps.

answered Dec 5, 2012 at 22:44

Mic1780

1,80410 silver badges23 bronze badges

Comments

LSerni · Accepted Answer · 2012-12-05 23:16:57Z

You might do well to investigate whether there is an ORM framework that you could use.

Anyway, the simplest way to do what you want is to pass the name of the field in the INPUT field.

$content = AddSlashes($content); // You may need HTMLEntities() here

$field = <<<FIELD
    <input type="text" value="$content" />
FIELD;

Also, another useful trick to employ is to either supply an array of "protected" fields, or specify a prefix that makes the field unchangeable. For example, you almost certainly do not want a user to be able to change the primary keys.

So you could generate the form with

if ('id' == $meta->name or 'pk' == $meta->name or (0 === strpos($meta->name, 'pk_')))
{
    // This is a primary key field
    $html .= <<<PKFIELD
    <input type="hidden" name="{$meta->name}" value="{$contents}" />
PKFIELD;
    continue;
}
if (0 === strpos($meta->name, 'hf_'))
{
    // hidden field, ignored (can't be changed)
    continue;
}
if (0 === strpos($meta->name, 'ro_'))
{
    // read-only field, shown but without even the name
    $html .= <<<ROFIELD
    <input type="text" readonly="readonly" class="readonly" value="{$contents}" />
ROFIELD;
    continue;
}

Or you could use a fixed-size prefix and an array of templates:

$fieldtpls = array(
    'pk_' => '<input type="hidden" name="{NAME}" value="{VALUE}" />',
    'ta_' => '<textarea name="{NAME}">{VALUE}</textarea>',
    'ro_' => '<input type="text" value="{VALUE}" readonly="readonly" />',
);

then you analyze each field and apply a default unless a known prefix is used.

Your users would then have to name the fields accordingly. Another possibility is to allow for a "meta-table" to hold the type and handling of each table and field, but in that case you would need to complicate a bit the user interface by creating a sort of "admin editor" for the Meta table:

table  field  type   rw   parameters
users  name   text   yes  
users  id     hidden no
users  role   text   no
users  notes  area   yes  class:"widearea"

...and that way be ~~dragons~~ ORM frameworks.

Collectives™ on Stack Overflow

Create update query for dynamic database

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related