update query question and problem ...

[imarockstar]

I have a site which can have multiple site logos stored in a directory. The site admin can choose from a drop-down which logo he wants to use. (good for having logos for different holidays).

 

I run 2 different scripts, one to which the admin can upload the logos nd the other to where he can set one of those logos to be "active" (the one that show up in the site header).

 

I have the script working which updates the DB and places that logo to active. This is my DB structure for the "logos" table :

 

id (logo id)

logoname (logo name)

filename (file name)

active (1 = yes 0 = no)

 

I use this form to update the database for the active logo :

 

<form method="post" action="scripts/logoset_go.php"> 

<select class=generic name="id">
<option value="">--- Select ---</option>
<?
// Get records from database (table "name_list").
$list=mysql_query("select * from logos");

// Show records by while loop.
while($row_list=mysql_fetch_assoc($list)){
?>
<option value="<? echo $row_list['id']; ?>"><? echo $row_list['logoname']; ?></option>
<?
// End while loop.
}
?>
</select>



<input class=submit type="submit" name="logoset" value="Change Logo" />
</form>

 

This is the script to which updates the DB :

 

//convert all the posts to variables:
$id = $_POST['id'];


mysql_query("UPDATE logos SET active = '1' WHERE id = '$id' ");
$result = mysql_query($sql) or die("ERROR: " . mysql_error() . "<Br>SQL: " . $sql);

 

pretty simple and it works fine .. but HERE IS MY PROBLEM ..

 

If the user updates the DB to show logo a to be active. set to "1", then I need it to make ALL other rows (logos) non active, set to "0".

 

I have no clue how t write this query to make this happen ?

 

anyone have a suggestion ?

 

thx

 

 

 

 

 

[premiso]

One way to do it, and probably not the best way but it should work is:

 

<?php
//convert all the posts to variables:
$id = $_POST['id'];

mysql_query("UPDATE logos SET active = '0' WHERE active = '1'"); // set all active logos to inactive
mysql_query("UPDATE logos SET active = '1' WHERE id = '$id' "); // set logo with id $id to active.
$result = mysql_query($sql) or die("ERROR: " . mysql_error() . "<Br>SQL: " . $sql);
?>

 

That should do the trick.

 

[imarockstar]

thanks man .. ya I though of that way but didnt want to run a query like that. someone who uses this might decide to upload 100 logos, who knows lol, and I dont want the server to crash ..

 

but if I cant figure out another way I will use that way ..

 

thanks man ..

b

[premiso]

Even if there were a 1,000 logos, it shouldnt crash the server at all. It would probably only had a second or two onto the processing time if that...

 

And if only 1 logo is active at a time, this will find the 1 row and just update that one row.

 

SQL is very very efficient.