...

View Full Version : count () help



twobyfour
06-16-2010, 03:42 AM
In table A

i have a column called category which will have numbers from 1-10 and a column called approved with 1 if approved or 0 if not approved.

In table B

I have a column called id which is unique and a column called count.

If approved, i need to be able to count how many times the same number appears in the column category in table A and update the column count next to the unique id in table B

I have this so far but it is not working. I also need (($row['category'] == "1") to be in an array because the numbers can be from 1-10


$sql ="SELECT * FROM table A";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while($row = mysql_fetch_array($result)){

if((isset($row['category']) == "1") && (isset($row['is_approved']) == "1")) {

if( isset($_GET['id'] ) ){

$sql ="SELECT * FROM table B";
mysql_query("update table B set count=count+1 where id='{$_GET['id']}'");
}
}
}

thanks in advance

twobyfour
06-16-2010, 07:38 AM
tried this is doesnt work. help please


$sql ="SELECT * FROM table A";
$result = @mysql_query($sql,$connection) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {

if((isset($_post['category']) == "1") && (isset($_post['is_approved']) == "1")) {

$sql ="SELECT * FROM table B where id='{$_GET['id']}'";
$result = @mysql_query($sql,$connection) or die(mysql_error());

if( isset($_GET['id'] ) ){

mysql_query("update table B set count=count+1 where id='{$_GET['id']}'");

}
}
}

I also need the isset($_post['category']) == "1") in an array because there will be other numbers 1-10 that will show up several times.

Dormilich
06-16-2010, 08:43 AM
I’d strive for an SQL solution. something like

SELECT COUNT(`category`) FROM `table` WHERE `is_approved` = 1 AND `category` = $number
this will get you the count for each approved number (if I read yor description right).

twobyfour
06-16-2010, 03:38 PM
thank you,

the code you wrote will not work because i need to check if variables are true in one table and if true connect to another table and update a column in the same row as the id for the row.

could you help me get the category in an array because the column will have numbers from 1-10 and each number may appear several times. I dont want to rewrite the query for each number to search for.

Dormilich
06-16-2010, 03:46 PM
SQL doesn’t give you back an array, if you mean that. besides, what is wrong with querying for each category number* ?

you could even write a Stored Procedure that handles all that for you (without PHP doing any work besides calling it). you might ask the guys in the SQL forum to help you creating this procedure.

* - SELECT DISTINCT `category` FROM `table`

twobyfour
06-16-2010, 04:37 PM
well i guess i dont have a problem with it , i just thought it might slow everything down. i just need to be able check if two variables are true then connect to another table and update a column.

Fou-Lu
06-16-2010, 05:09 PM
What is the relationship between table A and table B? Also, what is your column datatype for A.category? I'm kinda under the impression that your interacting a many to many relationship here without a flattening table (as in, A.category is a text type with something like either serialized data or delimited data). If so, you need to normalize this. This would prevent complex querying of un-normalized database tables.

To me, there appears to be a relationship between A.category and B.id, but I haven't determine yet the normalization level of it.

twobyfour
06-16-2010, 07:54 PM
What is the relationship between table A and table B? Also, what is your column datatype for A.category? I'm kinda under the impression that your interacting a many to many relationship here without a flattening table (as in, A.category is a text type with something like either serialized data or delimited data). If so, you need to normalize this. This would prevent complex querying of un-normalized database tables.

To me, there appears to be a relationship between A.category and B.id, but I haven't determine yet the normalization level of it.

i have no clue what you saying but i will try to break it down.

Table A and Table B has nothing to do which each other except there in the same database name. They hold different information.
Table A has a column called "category" with numbers 1-10 in it. These numbers may appear several times. It also has a column called "is_approved"
like so.

category | is_approved
1 | 1
1 | 1
2 | 1
2 | 1
6 | 0


i need to check both columns, category to see what number it contains and how many times that number show up and on the same row if "is_approved" column has a 1 or a 0 in it.

Then open connection to table B which is like this. Column category in table A is the same as name in table b. Instead of using names i just have numbers inplace of it


Name | id | count
foo1 | 1 | 2
foo2 | 2 | 2
foo6 | 6 | 0

Each time in table A that category has a 1 and is-approved has a 1 then count it in table B. If category has a 1 and is_approved has a 0 then dont count it in table B

Similar to this but does not work:


$sql ="SELECT * FROM table A";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while($row = mysql_fetch_array($result)){

if((isset($row['category']) == "1") && (isset($row['is_approved']) == "1")) {

if( isset($_GET['id'] ) ){

$sql ="SELECT * FROM table B";
mysql_query("update table B set count=count+1 where id='{$_GET['id']}'");
}
}
}

I think it is not working when because it cant switch tables to update the count column. But i echoed a true false statement if found if
((isset($row['category']) == "1") && (isset($row['is_approved']) == "1")) and it echoed true.

Fou-Lu
06-16-2010, 09:06 PM
Then the relationship is A.category and B.id. But, I don't understand why you are doing it in this way; I see no reason why B.count should exist, and I'm not sure why A can contain multiple records for category. Since B.count is represented directly by A.is_approved, there is no reason to create the count in B at all.
For example, we can count how many approved records there are related to foo2 by using:


SELECT count(*)
FROM A
INNER JOIN B ON (B.id = A.category)
WHERE B.name = 'foo2' AND A.is_approved = 1

Or, to get all names (and counts) for any is_approved records:


SELECT B.Name, count(*)
FROM A
INNER JOIN B ON (B.id = A.category)
WHERE A.is_approved = 1
GROUP BY B.Name

for examples.

Aside from the unnecessary B.count, these two tables are already mostly normalized. They are normalized if A is more information, like say an Articles table. If its just the Category and is_approved, then I see no reason for A at all, and you would be better off leaving the count in B and incrementing / decrementing it as necessary.

twobyfour
06-16-2010, 09:16 PM
Table A has a lot more information then Table. Table B is just name, url, id, count. I want to display how many approved rows from table A based on the number category in table A next to the name as it is being displayed on the page. Thats it. If i dont need count in table B then please simplify this for me.

example

Foo (5)
Foo2 (2)

Foo is category 1 with 5 approved rows
Foo2 is category 2 with 2 approved rows

Fou-Lu
06-16-2010, 09:28 PM
Then this is what you want:


SELECT B.Name, count(*) AS `count`
FROM A
INNER JOIN B ON (B.id = A.category)
WHERE A.is_approved = 1
GROUP BY B.Name


Drop the count off of the B table. The resultset will contain two records, name and count, use these to display your string.

twobyfour
06-16-2010, 10:45 PM
thank you,

if was to open another connection to a different table based on if a variable exist in the current table, how would i code that. Also how many connections to different tables can i have in one page? Is there a limit?

Fou-Lu
06-16-2010, 11:18 PM
Best I know your limited only by the structure of the table and the memory available for the system. Largest join I've ever made was 12 tables I believe it was. Too big for my liking.

twobyfour
06-17-2010, 01:13 AM
SELECT B.Name, count(*) AS `count`
FROM A
INNER JOIN B ON (B.id = A.category)
WHERE A.is_approved = 1
GROUP BY B.Name

this code only shows the category if there is an approved row that goes with it. how can i show all categories but only count the approved rows.

Fou-Lu
06-17-2010, 01:28 AM
SELECT B.Name, count(*) AS `count`
FROM A
INNER JOIN B ON (B.id = A.category)
WHERE A.is_approved = 1
GROUP BY B.Name

this code only shows the category if there is an approved row that goes with it. how can i show all categories but only count the approved rows.

Ah, so you need more than just the approved ones then?


SELECT B.Name, (SELECT count(A.category) FROM A WHERE A.category = B.id AND A.is_approved = 1) AS count
FROM B


Feels like this can be done without a nested query though.

twobyfour
06-17-2010, 01:35 AM
thank you that works perfectly. i didnt now i can echo a variable that doesnt exist in a table but in a query. I learned something new again.


thanks again



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum