CODEWITHSUNDEEP
phpmysql
biscuitstack
biscuitstack

Reputation: 12112

Dynamic calculations while mySQL database updating

I have a table of items that are each individually associated with a category by means of an integer in a row called category. I also have a display order for each item, relevant to its associated category. This is saved in a row called pictureorder. Straightforward?

My problem arrises when the user deletes a category that contains items/pictures that are associated with it. I tell mySQL to search for any items associated with the category and to associate them with a 'default' category which is denoted by 0.

// IF THERE'S ANY PICTURES THAT WERE IN THAT CATEGORY,
// MOVE THEM INTO THE DEFAULT CATEGORY.
$uncategorise = "UPDATE pictures 
    SET category = '0'
    WHERE category = '$categoryID'
    AND username = '$userID'";
$queryUncat = mysql_query($uncategorise) or die(mysql_error());

This works. However, the items' pictureorder is broken as items transferred from the deleted category may create duplicate pictureorders in the default category. I'm aware of how to solve this problem in a manner which would make multiple calls to the database. This feels too expensive. My query is whether I can use some technique I'm unaware of to update an incrementing pictureorder integer to the database as it's called without making that row a key? Something along the lines of:

$uncategorise = "UPDATE pictures 
    SET category = '0',
        pictureorder = 'TotalofDefaultCategory++'
    WHERE category = '$categoryID'
    AND username = '$useID'";
$queryUncat = mysql_query($uncategorise) or die(mysql_error());

Thanks in advance!

Update1

Based on Kyle's suggestion below, I am trying the following without success:

UPDATE pictures 
    SET category = '0',
        pictureorder = (SELECT COUNT(category) + 1 WHERE category='0' AND username='$useID')
    WHERE category = '$categoryID'
    AND username = '$useID';

If I omit pictureorder = (SELECT COUNT(category) + 1 WHERE category='0' AND username='$useID'), the category does indeed get changed to '0' whenever the category matches $categoryID and $useID. However, if I include the addtional line, nothing changes. I don't get any errors but pictureorder doesn't change. Nor does category. Is the syntax correct?

Also, if I replace pictureorder = (SELECT... with pictureorder = 2 it works as expected. So I can't see a possible problem other than the syntax being incorrect (I hope it's not, as I feared, that it was too good to be true that you could include a COUNT in this manner).

Upvotes: 1

Views: 479

Answers (4)

biscuitstack
biscuitstack

Reputation: 12112

I've been scratching my head for two days solid on this one as it turns out none of the solutions were quite right. Marc B led me on the right path although I couldn't add to a user-defined variable in a second query in PHP, though I was able to call on it (hence some misguided trials and errors). There is a way of querying MySQL in PHP that can make multiple queries and connect them but the mysql_query method I was already using was too deeply interwoven in my site to make it an easy change.

Instead, based on the fact that the category to be deleted ($categoryID) had existing pictureorders starting from 1 and incrementing. I was able to use this existing information to add onto the COUNT suggestion made on this page. So, although I had to use two queries, for the second one I simply added the existing pictureorder of the deleted category onto the total count of the default category for the current user.

Upvotes: 0

Kyle
Kyle

Reputation: 4449

Are you trying to get a sum of the default category for that particular user? If you aren't trying to do it by user, remove the AND username='$useID' from the portion I added to your query

UPDATE pictures 
    SET category = '0',
        pictureorder = (SELECT COUNT(category) + 1 WHERE category='0' AND username='$useID')
    WHERE category = '$categoryID'
    AND username = '$useID';

Upvotes: 1

Puggan Se
Puggan Se

Reputation: 5846

if category 0 have orders in the range 1-150, and category 5 that going to be deleted have a range from 1-7, you should be able to just add 150 to the current value, and by that get the numbers 151-157

UPDATE pictures 
SET category = '0',
    pictureorder = pictureorder + '$default_max_pictureorder'
WHERE category = '$categoryID'
AND username = '$userID'"

Upvotes: 0

Marc B
Marc B

Reputation: 360702

You can use a server-side var:

1st query:

select @cnt := 1;

2nd query:

update pictures SET pictureorder := @cnt, @cnt := @cnt + 1
WHERE ...
order by pictureorder, ...

You'll need an appropriate order by clause to ensure that the records with duplicate pictureorder values get put in the right order - do the moved records come before or after the records that were already in the category?

Upvotes: 1

Related Questions