MySQL Re-Ordering

[ibinod]

I have a menu table which contains column like this

   

id

       

name

       

link

       

position

       

   

1

       

Home

       

home.php

       

1

       

   

2

       

About

       

about.php

       

2

       

   

3

       

Contact

       

contact.php

       

3

       

   

4

       

Faq

       

faq.php

       

4

       

   

5

       

Message

       

msg.php

       

5

       

   

6

       

Downloads

       

down.php

       

6

       

 

now let's say i want to change the position column value to 2 of id column that has id 1 and at the same time i want to change the position column value to 1 which has position value 1

can you pls suggest me how do i do this using php

 

using mysql i was able to do like this but pls suggest me a better way to do it using php

UPDATE menu SET position = 1 WHERE id = 2;
UPDATE menu SET position = 2 WHERE position = 1;

 

[Boo-urns]

Are you going to be doing it often? I'm not an expert but you can do it through loops / switches if you're going to be updating a lot. I'm not sure what you're going for.

[fenway]

Just re-save the new order

[ibinod]

Well this won't be done often cause it will be in the backend side of the site to rearrange the menus.

 

can you pls provide me sample code using php to execute a such query

[Boo-urns]

If it won't be happening often when will it be happening?

[Mchl]

As far as I can tell, this

 

UPDATE menu SET position = 1 WHERE id = 2;
UPDATE menu SET position = 2 WHERE position = 1;

 

will in end give you two rows with position = 2

 

For samples of sending queries to MySQL from php see mysql_query or preferably mysqli_query()

[Boo-urns]

If you use mysqli_query() is the best practice to still use sprintf and mysql_real_escape_string? I just found about mysqli_query() now.

[Mchl]

I never use sprintf() myself... just don't see use for it. You can use it if you wish. It doesn't really have anything to do with database.

For mysqli extension there's mysqli_real_escape_string()

[PFMaBiSmAd]

The second query was probably supposed to be - WHERE id = 1;