changing multiple row values

[gozza]

Hi all, I have a dynamic slideshow presentation created in php and mysql.

 

The php currently displays the information from the database based on a variable passed through the url called 'page'. ie admin.php?page=2.

 

The presentation is very simple with next and previous buttons simply adding or subtracting the 'page' value and therefore displaying different content.

 

Now my problem is that I want to be able to add and remove pages within the presentation. I can easily add or remove a page from the end as it does not affect the order of the pages. It simply adds a new row to the database.

 

So for example if the presentation has 9 pages, then the ID's in the database are consecutive 1-9. But, if I am on page 4 and want to add a new page between 4 and 5. How would I do this, as the new row in the database would have an ID of 10, and obviously would not appear until the end.

 

I have thought about adding another column for position, so that it does not rely on the ID column, but Im still getting the same issue. How do I tell the database that what is currently position 5, needs to be position 6 and so on for every other page.

 

I hope this makes sense, and appologize for the lengthy explination. I have not provided any code here, as I am looking for a suggestion of what I actually need to do, as I cant figure out a logical way of doing it. But I can provide all the code if required.

 

Thanks!

 

I guess I need to say something like find all positions that are >4 and increase their value +1?

 

[jon23d]

One possible solution would be to give a parent id to each record; alternatively, an array of elements could be stored, with 'page' being the numeric index of the object.

[gozza]

Thanks for the reply, but I am not sure how having a parent id would solve the problem.

 

I think the best way is to upadte all the subsequent row. So how do I edit multiple rows with different information. Do I need to do somekind of for loop around the Query.

 

Any other ideas suggestions or examples would be greatly appreciated

[purpleshadez]

I reckon you'd be better off sticking your SQL result in an array you could then use sizeof() to get the total number of pages and calculate what to show next rather than replying on an id field in the database.

 

You can however, as someone else mentioned in another post, run an sql update query that subtracts 1 from all the id's higher than the one you've just deleted.

[jon23d]

Using a parent ID (leave the first record with a parent id of null), allows you to update only 1 row instead of all subsequent rows on reorder.

 

For example, assume we have a table with id, title, parent_id, that has the following info:

 

1, Fred, null

2, Mary, 1

3, Mark, 2

 

SELECT `title` FROM `people` ORDER BY `parent_id` ASC will return: Fred, Mary, Mark

 

Now we want to insert George between Mary and Mark:

 

INSERT INTO `people` (`title`,`parent_id`) VALUES ('George', '2');

 

Then, update Mark (which will effectively update all subsequent rows):

 

UPDATE `people` SET `parent_id` = 4 WHERE id = 3 (you get the new ID from mysql_insert_id)

 

Now, when you select title ordered by parent_id asc, you will get the new record order.  This should be a bit more efficient.