Update two entries at once...

[LordHart]

I'm trying to edit two entries at once, like so:

mysql_query ("UPDATE clients SET spotlight= 'no' WHERE spotlight= 'yes'");
mysql_query ("UPDATE clients SET spotlight= 'yes' WHERE c_name= '{$_POST['c_name']}' AND spotlight='no'");

What i've been trying to do is a form for a user to set one particular person as a 'spotlight' which allows only that person to appear up on a particular section of the site. The code above, what I wanted it to do is to essentially unset every entry that has yes in spotlight, and then resets only one entry to have yes, which is selected from a form by the user.

It unsets everything fine, its just the resetting one person to the spotlight where i'm having the problem. Any help would be appreciated.

[bradgrafelman]

So in otherwords, every time the 'spotlight' changes, the SQL server has to go through every row in the table once, as well as once more for the new user. Sounds like a lot of work for such a simple task.

Why not create a seperate table, a "config" table, that lists some current settings. For example, you'd make a column called "spotlight" in that table and insert the client ID in that field. When the spotlight changes, you simply update the config table with the new ID.

[LordHart]

Now i've got another problem along the same lines. I'm doing what was suggested and just have a seperate table, and it works fine when it comes to getting info, but it still won't update the table when the user selects a name from a form.

Reading the spotlighted client:

mysql_query("SELECT * FROM spotlight, clients WHERE spotlight.spotlight=clients.id");

There's no problem with that bit, as when I put the details into the spotlight table manually through MySQL, it works fine. The following is the bit i'm having trouble with now.

Updating the spotlighted client:

mysql_query("UPDATE spotlight SET spotlight='{$_POST['id']}' ");

Any suggestions what to do about it? 😕

I managed to fix it. The problem ended up not being the SQL stuff at all, and I mislabelled the select field. I feel so stupid. :p

Thanks for the help anyway.