Can this MySQL be done with 1 query?

[JonD]

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

 

[JonD]

Absolutely not. Bad. Can open, worms everywhere. That sort of thing.

Really should have spotted those $POST variables.

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 )

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

 

[JonD]

Ive come accross a problem.

Say I have the following:

$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

 

[JonD]

I managed to sort it using eval:

$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.

 

[JonD]

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