Can this MySQL be done with 1 query?

JonD

JonD

JonD

Associate
Joined
26 Jun 2003
Posts
1,140
Location
North West
I have 5 queries, but can this be done with 1 query using PHP & MySQL. IE can I edit 5 rows at the same time?

$db->query("UPDATE settings SET value = '".$_POST['siteactivation']."' WHERE id = 1");
$db->query("UPDATE settings SET value = '".$_POST['offmessage']."' WHERE id = 2");
$db->query("UPDATE settings SET value = '".$_POST['websitetitle']."' WHERE id = 3");
$db->query("UPDATE settings SET value = '".$_POST['keywords']."' WHERE id = 4");
$db->query("UPDATE settings SET value = '".$_POST['description']."' WHERE id = 5");

Thx
 
I take it this a table that has site configuration settings in it..

It may be better to have a table where each field represents a configuration value and the table only contains one record, rather than having a table full of parameter -> value records like what you're doing. The advantange of that is you can load and save the whole configuration with one sql statement, and the table size will be fairly small. Also you can tailor each field to a specific data type, whereas with your method you'd have to cater for the biggest data type that you might encounter. But, if you're building a CMS you'll probably need the flexibility of having generic parameter -> value types.

The disadvantage really is it's not quite as easy to scale if you want extra configuration parameters, but it's just a case of adding on an extra field.

To answer your question though, I think you're gonna have to execute one sql statement per configuration parameter, since you're keying each one by a unique identifier.

Edit: But if it's execution speed you're worrying about, I wouldn't worry - this should be at the back-end of the site and so it's not really a time-critical operation. I mean, how many configuration parameters are you likely to need?
 
Last edited:
While this won't help reduce the number of UPDATE queries, it will make things easier if you don't like the option of putting everything in one row.

Change your rows from id, value to name, value, and use the same names in the database as you do in PHP. Less code, more extendable, and generally cleaner. :)
 
Absolutely not. Bad. Can open, worms everywhere. That sort of thing. :eek:

Really should have spotted those $POST variables. :eek:
 
You could probably hack together an utter abomination based on something like this:

Code: 
INSERT INTO table
(id, foo)
VALUES
("1", "bar"),
("2", "splooble"),
("3", "ghghgh")
ON DUPLICATE KEY
    UPDATE foo = "bar"

But it wouldn't be faster and is way less intuitive. Just do it with multiple queries :)
 
Berserker said:
Absolutely not. Bad. Can open, worms everywhere. That sort of thing. :eek:

Really should have spotted those $POST variables. :eek:
Click to expand...

Lol im not that stupid, the variables are cleaned before I just havnt coded it in yet.

What im trying to do is create a CMS that to have a new admin option is not to create a new PHP page, but to create one entry in the database. (Ill give you more details and a demo when its done, just bare with me :p)

Basically when something happens, ie a form is submited, a certain SQL query stored in the database is executed, but I need to update several fields at once. If it cannot be done with one query. I could put the query as one entry, seperating each query by a marker of somekind.

Then when I have the seperations, I can execute each query in turn with a loop.

I can never remember the php function to seperate strings into sections seperated by a character. I think " ; " should do?

EDIT* explode() is the function im looking for. Can never remember that one.

Thx
 
Last edited:
JonD said:
Lol im not that stupid, the variables are cleaned before I just havnt coded it in yet.
Click to expand...
I'm only teasing you, I know you're not a noob - fear not :)

(Or am I just being nice? Who knows? Only me.....:D)
 
Ive come accross a problem.

Say I have the following:

PHP: 
$variable = '3';

// Get a query
$db->query("SELECT query FROM queries WHERE queryid = 2");
$result = $db->fetchArray();
$query = $result['query']; # This Query in the DB is: SELECT * FROM settings WHERE groupname = '$variable'
$db->query($query); # No results - **** have 5

but the query is not picking up the results, when I hardcoded the $variable value into the query it works fine. I cant see why this wouldn't work?

Anyone know the fix?

Thx
 
Not sure what your fetchArray() function does, but you'll want to either use $result[0]->query, write a new function that uses mysql_fetch_row() so you don't have to, or ditch your current DB layer and just use ez_sql, in which case you could just do:

Code: 
$query = $db->get_var("SELECT query FROM queries WHERE queryid = '2'");

Edit: That's not what you meant at all, sorry.

That just doesn't work, sorry. You can either write your own variable parsing, which would mean something like this:

Code: 
$query = "SELECT foo FROM bar WHERE baz = '%baz%'";
$variables = array(
    '%baz%' => $baz
);

$query = str_replace(array_keys($variables), array_values($variables), $query);
$db->query($query);

Or you could use sprintf, in which case you make things easier but on the flipside lose a lot of flexibility, since the variables have to appear in the same order in the query as they are when passed to sprintf.

Code: 
$query = "SELECT foo FROM bar WHERE baz = '%s'";

$query = sprintf($query, $baz);
$db->query($query);
 
MySQL 5.0 and above do, previous versions don't. Stored procedures are overkill unless you're going to do the same query many times.
 
I managed to sort it using eval:

PHP: 
$query = $result['query'];

eval("\$query = \"$query\";");

$db->query($query);

Works great, no problems so far.

robmillier the fetchArray function just does excally the same as mysql_fetch_array / mysql_fetch_assoc.
 
How about doing a simple str_replace on the string pulled from the db?

eval() is really, really, really bad practice in 90% of cases in my opinion :)
 
I need a method to use it, but Its a good idea to create a function to return an array with all the results!

I really didnt think of that.

Thx
 
You must log in or register to reply here.
Back
Top Bottom