Posted by Rik on 05/30/06 12:12

Lets say I have:
table 'tbl_houses':
- id
- adress
- etc....

and:
table 'tbl_house_photos'
- id
- filename
- house_id
- default (tinyint(1))

It used to be that only one of the photos was 'default' (i.e. '1'), the rest
of the photos was '0'. Now we want to change this to an order, default image
as the first, the rest in order of the filename for starters, afterwards the
order can be changed.

I can simply change the 'default' to tinyint(2) (there will never be more
then 99 photos, a lot less actually), and loop in PHP on every known house,
and update the value accordingly.

I was actually wondering wether I could to this in a direct query, like:
UPDATE tbl_images SET 'order' = (IF(`house_id`=@hd,@count+1,'1'), ORDER BY
`house_id`, `default`, `filename`

and then somehow some code to set @hd & @count....

Grtz,
--
Rik Wasmus

[Back to original message]