MySQL update

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
vigge89
Forum Regular
Posts: 875
Joined: Wed Jul 30, 2003 3:29 am
Location: Sweden

MySQL update

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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;
    }
User avatar
vigge89
Forum Regular
Posts: 875
Joined: Wed Jul 30, 2003 3:29 am
Location: Sweden

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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';
User avatar
vigge89
Forum Regular
Posts: 875
Joined: Wed Jul 30, 2003 3:29 am
Location: Sweden

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Post Reply