[Reorder based on group index]

Sorry for the delay. Just wanted to report that everything worked spot on! Thanks very much Barand!

[Reorder based on group index]

In this instance, $deletedCat = 1

UPDATE mytable SET cat = cat - 1 WHERE cat > $deletedCat

But, as Mac_Gyver said, in a production environment, don't.

 

Thanks Barand, I'll give that a try and write back.

 

The actual data I have in my tables is a lookup of data groups. This is not related to any stationary or shop website. I just used the example above so that it made the example clear rather than complicating things with a 4 tier group system.

 

 

[Reorder based on group index]

yes, and what if your category values have been referred/linked/bookmarked to by visitors to your site. they would expect to be able to revisit your site and have their links/bookmarks take them to the same category information if it still exists.

 

unless you are using test data in your tables that you will completely delete and start over with real data, you wouldn't ever alter the referral values between tables after it exists.

Thanks for your input.

 

Unfortantely, your concerns are not relevant in this situation. I used false headings as my actual data is a lot more complex. Just to be clear, this isn't anything to do with a product shop or anything that even touches the URL.

 

My question still stands (please don't worry about the premise of my question but rather the question itself).

 

Many thanks

[How to use optional AND or skip AND condition in mySQL query when user enter submit null value for that condition..]

From what I've gathered you're saying that:

SELECT * FROM tbluser WHERE gender='male' AND mother_tongue='Tamil' AND religion_id='3' AND caste_id='374' AND (age BETWEEN 50 AND 70);

Could be:

SELECT * FROM tbluser WHERE gender='' AND mother_tongue='' AND religion_id='' AND caste_id='374' AND (age BETWEEN 50 AND 70);

Do I have that right?

[Windows GUI for MySQL]

if you're using mysql then use the tool design by the people who made it:

 

http://www.mysql.com/products/workbench/

 

I'm surprised that this topic didn't end at this point. I too went looking for a tool to use with MySQL and was pleased to find Workbench after previously looking at Navicat and running a mile when I saw the price they wanted

[Reorder based on group index]

Hi All

 

I wonder if anyone know of a way around this scenario.

IDCatItem

11Pencil

21Pen

32Ruler

42Rubber

52Stapler

63Paper

73Highlighter

 

If I delete Cat 1 from the list so the data now looks like

IDCatItem

32Ruler

42Rubber

52Stapler

63Paper

73Highlighter

 

How do I rebuild the Cat groups so they would now look like the following?

IDCatItem

31Ruler

41Rubber

51Stapler

62Paper

72Highlighter

 

I've used the following statement in the past for rebuilding indexes where they would be individual but this can't be used when I refer to an index list as a group (i.e. multiples of the same index).

SET @var= 0;
UPDATE `mytable` SET `orders` = (SELECT @var := @var +1) WHERE `cat` = '1' ORDER BY `orders` ASC

Many thanks