adding and subtracting

[slimjim]

I have an interesting problem.

How would I subtract a count from one field and add to another ?

What I am doing is this :

Member has a link listed in category ( hosting ) and in that category table it would look like this :

catid | category | count

1 | hosting | 24

24 being the total number of links listed in that category

Now i want to change the members category to hosting-tools

Problem is if i change the category, it will add to the count of the new category, but how do i get it to subrtract from the previous one.

So that 24 would be 23 and the new would be count = count + 1

Any help please

[tsinka]

Hi,

you can use these queries to do that:

increase count:
UPDATE category SET count=count+1 WHERE id=theid

decrease count:
UPDATE category SET count=count-1 WHERE id=theid

Thomas

[slimjim]

Thats true, but i do not want to hard code the previous id to subtract the count, I need some way of it storing the last category id for me to update it, otherwise if i just use :
UPDATE category SET count=count-1 WHERE catid=$catid

If keeps finding the new cateid I just upated it too instead of old one.

[drawmack]

With proper normalization this problem goes away

categories
   cat_id
   cat_name
   cat_description

links
    link_id
    link_url
    link_name
    link_description
    link_cat -> links to cat_id

Then you get the number of links in a category like this:

<?php
$sql  = "SELECT a.cat_id as id,a.cat_name as name,";
$sql .= "count(b.link_id) as num_links FROM ";
$sql .= "categories as a, links as b WHERE a.cat_id = ";
$sql .= "b.link_cat GROUP BY b.link_cat";
$query = mysql_query($sql);
while($row = mysql_fetch_assoc($query)) {
    echo $row['id'] . ' - ' . $row['name'] . ' - ';
    echo $row['num_links'] . "<br />";
} //end while
?>

then when you create a new category or move links around within existing categories your number of links will automatically update. The idea is never store something you can calculate.

[slimjim]

I understand what your saying. But showing links isnt the problem i am having. Or displaying the count.

Problem has to do with changing the count in the previous category after i edit the persons category to a new one.

[tsinka]

Hi,

I think I don't understand exactly what you want to do. It might help if you post the layout of the tables.

Let's assume that the member's category id is 10 and you want to change it to 12.
So with a script you can first read out the original id then the new id. After that you change the count on each of the two row by increasing the count of the new category and decreasing the count of the old category or do I misunderstand that ? It might help if you describe the application a little bit.

Thomas

[slimjim]

Layout is like this for the table.

Table name "categories'

id | category | count

I have like 10 categories
Example :
internet
hosting
hosting-tools

Ok now lets say i have a member that listed a link and he chose internet for the category his link will be posted in.

So the count for internet increases by one

Now lets say i want to change his category

I would change it to hosting-tools
Now the category hosting-tools would increase by one

Now the problem :

I still have the category "internet' with a count of one.

I can't figure out how to update them both , cause no matter what i try, i can't seem to hold on to the old category id to subtract one from the count.

I hope someone can help me, not sure how else to explain this.

[tsinka]

Hi,

I think I understand it better now (at least I hope that I do) 🙂

Is that link stored in another table. If yes, do you have a column in that table that references to the category id ? If you don't have such a column, I'd suggest to add the column and to store the category id the link refers to in that column.

If you want to change the category later you can first fetch the old id, decrease the old count by one and then update the id to the new id and increase the new count by one.

Thomas

[drawmack]

If you read my previous post carefully you'll see that I'm demonstrating how you can get the count dynamically from the links table without actually storing it in the database. That way just changing the category will automatically update your counts.

[slimjim]

Ok can anyone tell me how to fix this : after i do a count, i want to take the results and update the colum count.

if the count finds three groups then only those three groups get the count added and the others stay at zero

I am stuck if anyone can help me, i have changed things so much that this seems like the best way now if i can get the category counts to stay at zero that are not listed in subcat.

Not sure if i am saying this right !!

I want it to count all and group it, then update all in colum count in table category. This way i will always have a true count of how many links in each category.

$query3 = "SELECT categoryg, count(*) as `count` FROM subcat GROUP BY categoryg"; 
        $result3 = mysql_query($query3) or die( "ERROR: " . mysql_error() . "\n");
	    while($row = mysql_fetch_array($result3)) { 
        $catcount = $row['count']; 
        print($catcount); 
} 

    $query4 = "UPDATE category SET count='$catcount' WHERE category='$categoryg'"; 
    $result4 = mysql_query($query4) or die( "ERROR: " . mysql_error() . "\n");

    //query4 is my issue, query3 works fine. query4 updates the count for the groups that are counted but i need the other category counts to stay at zero
}

[tsinka]

Hi,

try this one:

$query3 = "SELECT categoryg, COUNT(*) AS count FROM subcat GROUP BY categoryg";  

  $result3 = mysql_query($query3) or die( "ERROR: " . mysql_error() . "\n"); 
  while($row = mysql_fetch_array($result3)) {  

    $categoryg = $row['categoryg'];
    $catcount = $row['count'];  

    print"categoryg: $categoryg --- catcount: $catcount<br>");  

    $query4 = "UPDATE category SET count='$catcount' WHERE category='$categoryg'";  

    $result4 = mysql_query($query4) or die( "ERROR: " . mysql_error() . "\n"); 
  }  

}

Thomas

[slimjim]

Well that worked to a point.
It did update all of the counts in the right categorys but..

It still leaves the old count in a category after i change a category

I somehow need it to put all other categorys to zero if there is none listed in subcat categoryg

[tsinka]

Hi,

this can be done with an additional query:

$sql = <<< EOSQL
SELECT c.category AS category
FROM category c LEFT JOIN subcat s
ON c.category = s.categoryg
WHERE s.categoryg IS NULL
EOSQL;

$res = mysql_query($sql) or die ("Error: ".mysql_error());

while ($row = mysql_fetch_array($res)) {
  $category = $row['category'];

  $sql = "UPDATE category SET count='0' WHERE category='$category'";
  $res2 = mysql_query($sql) or die("Error: ".mysql_error());
}

This should get all categories of category that don't have any entries in subcat and set count to 0.

The line $sql = <<< EOSQL must end with L ... there mustn't be any spaces after EOSQL.

Thomas

[slimjim]

Do i put this after the other 2 q, not really sure.

[tsinka]

Yes,

do this as last step since you have the most actual data after executing the other queries.

Thomas

[slimjim]

Must not be understanding something, cause now i am getting a parse error at the line <<< EOSQL

I copied it just like you had it.

[tsinka]

Hi,

please make sure that the line ends just after EOSQL without any white spaces after EOSQL. PHPBuilder added some whitespaces to that line after submission of the code.

Thomas

[slimjim]

That solved my problem, been at that all night and day and yo help me out alot, now i can move on and study what you did.

can you explain to me how that last code works ?