Page 1 of 1

MySQL update

Posted: Mon Sep 27, 2004 2:58 pm
by vigge89
YAY! Another question from me :P

I'm trying to update all mysql fields which have been submitted in an post-form.

The problem is that I don't know if the following is possible, or how it's done;

The query i try to run which (obviously) doesn't work:

Code: Select all

UPDATE `settings` SET `value`='404' WHERE `key`='404', `value`='start' WHERE `key`='homepage', `value`='0' WHERE `key`='last_update', `value`='tpl/v2.tpl' WHERE `key`='main_template', `value`='1' WHERE `key`='open', `value`='ї vigge.net - {%title%} ]' WHERE `key`='title'
Here's the code which generates the query:

Code: Select all

<?php
############### updating ###############
if (!empty ($_POST['edit_settings'])) { // edit form has been submitted

###### format form and set variables
foreach ($_POST as $key => $value) $_POST[$key] = addslashes ($_POST[$key]); #### add slashes to data

########## update data ##########
foreach ($_POST as $key => $value) if ($key != 'edit_settings') $query['usettings'] .= ", `value`='$value' WHERE `key`='$key'";
$query_set = ltrim ($query['usettings'], ",");

if (mysql_query ("UPDATE `settings` SET $query_set")) {
	$msg .= "Succesfully updated settings<br />";
} else {
	$msg .= "<span style='color: red;'>Could not update settings</span><br />";
}
######### /update data ##########

} // edit form has been submitted
############## /updating ###############
?>
Any ideas?

Posted: Mon Sep 27, 2004 3:05 pm
by timvw
here is a little snipped i use to generate update query

Code: Select all

// returns a query for update - 
    //
   // $what contains  key-value pairs
   // 
   //  primary keys can't be updated, they are used for the where clause
   //
    function compileUpdate($what)
    {
        $definitions = $this->getDefinitions();
        $primary_keys = $definitions['primary_keys'];

        $q1 = "";
        $q2 = "";
        foreach($what as $attribute => $value)
        {
            $value = $this->escape($value);
            if (array_key_exists($attribute, $primary_keys))
            {
                $q2 = "{$attribute}={$value} AND ";
            }
            else
            {
                $q1 = "{$attribute}={$value}, ";
            }
        }
        $q1 = rtrim($q1, ", ");
        $q2 = rtrim($q2, " AND ");

        $query = "UPDATE $this->sql_table SET $q1 WHERE $q2";
        return $query;
    }

Posted: Mon Sep 27, 2004 3:07 pm
by vigge89
yeah, but do I have to do one query for each update? that would be kinda slow, it isn't possible to update different fields in the same query?

Posted: Mon Sep 27, 2004 3:13 pm
by timvw

Code: Select all

$what = array(
     'field1' => 'new value1',
     'field2' => 'new value2',
      'field3' => 'silly'
);

$query = compileUpdate($what)
echo $query;
would output something like

Code: Select all

update foo set field1='new value1', field2='new value2' WHERE field3='silly';

Posted: Mon Sep 27, 2004 3:35 pm
by vigge89
I think I described it wrong, what I mean is that I need to update different rows. The table looks like this:
Image
I want to update all the rows (which's key have been submitted trough a form) `value` fields in the same query, is this possible?

Posted: Mon Sep 27, 2004 3:44 pm
by feyd
the manual doesn't say you can do it.. so I'm wary that it'd be possible. I know everything I've seen always does multiple queries for stuff like that, unless they are mathematic and related in nature.

Posted: Mon Sep 27, 2004 3:49 pm
by timvw
basic SQL update syntax, http://www.1keydata.com/sql/sqlupdate.html

If you want to set col="bar" for key1 and key2
-> update set col='bar' where othercol=key1 or othercol=key2

more recent mysql versions allow you to have more than one instruction in a query http://be2.php.net/manual/en/function.m ... -query.php
update set col='bar' where othercol=key1
update set col='foo' where othercol=key2