Hi, I have a client with a database of around 17k entries. Now due to powers out of my control the table structure looks like:
CREATE TABLE londonhotelsallphotos ( HotelID double default NULL, active_hotel_photo_Name varchar(255) default NULL, URL varchar(255) default NULL, Number varchar(50) default NULL, Name varchar(255) default NULL ) TYPE=MyISAM; and a few example rows look like: (105304,NULL,'http://blah/photos/105304/BAB105304.jpg','1','Cairn Hotel'); (105304,NULL,'http://blah/photos/105304/CAB105304.jpg','2','Cairn Hotel'); (105304,NULL,'http://blah/photos/105304/DAB105304.jpg','3','Cairn Hotel'); (105304,NULL,'http://blah/photos/105304/EAB105304.jpg','4','Cairn Hotel'); However the client has recently updated the database and now all entries look something like: (105304,NULL,'http://blah/photos/105304/AAB105304.jpg',NULL,NULL); (105304,NULL,'http://blah/photos/105304/DAB105304.jpg',NULL,NULL); (105304,NULL,'http://blah/photos/105304/BAB105304_2.jpg',NULL,NULL); (105304,NULL,'http://blah/photos/105304/BAB105304_3.jpg',NULL,NULL); Now you will notice that the last 3 fields have changed... The client wanted to change the URL field, but also changed the Number & Name fields.... With the current coding it appears they require the Number field to be set to 1, 2, 3, 4 etc... However as you can see the number field are all NULL now, this is meaning the rows aren't being shown on the PHP page due to the way the page was coded.. Now what I'm asking is for a SQL Query I can use to re-number all the rows (17,000 ish). The table has many different HotelIDs in it, with at most 5 rows with the same ID meaning that the Number field won't be higher than 5. The URL field I think is always unique for all the rows.... Also I don't mind that the Name field is left NULL. A few example rows would be: (105304,NULL,'http://blah/photos/105304/BAB105304.jpg','1','Cairn Hotel'); (105304,NULL,'http://blah/photos/105304/CAB105304.jpg','2','Cairn Hotel'); (105304,NULL,'http://blah/photos/105304/DAB105304.jpg','3','Cairn Hotel'); (105304,NULL,'http://blah/photos/105304/EAB105304.jpg','4','Cairn Hotel'); (105356,NULL,'http://blah/photos/105356/EAB105356.jpg','1','Ramada Jarvis Bolton'); (105356,NULL,'http://blah/photos/105356/CAB105498.jpg','2','Ramada Jarvis Bolton'); If I can't do this with some quick and dirty SQL, I'll write some PHP to do the process, but since I'm not being paid to fix this problem, and the client caused it himself I thought I'll take the easier option of using SQL before I wrote some code out of kindness... Thanks very much Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
