Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: count () help

  1. #1
    Regular Coder
    Join Date
    May 2010
    Posts
    146
    Thanks
    8
    Thanked 0 Times in 0 Posts

    count () help

    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

    PHP Code:
    $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
    Last edited by twobyfour; 06-16-2010 at 04:59 AM. Reason: add php code

  • #2
    Regular Coder
    Join Date
    May 2010
    Posts
    146
    Thanks
    8
    Thanked 0 Times in 0 Posts
    tried this is doesnt work. help please

    PHP Code:
    $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.

  • #3
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,473
    Thanks
    13
    Thanked 361 Times in 357 Posts
    I’d strive for an SQL solution. something like
    Code:
    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).
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #4
    Regular Coder
    Join Date
    May 2010
    Posts
    146
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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.
    Last edited by twobyfour; 06-16-2010 at 03:46 PM.

  • #5
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,473
    Thanks
    13
    Thanked 361 Times in 357 Posts
    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`
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #6
    Regular Coder
    Join Date
    May 2010
    Posts
    146
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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.

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #8
    Regular Coder
    Join Date
    May 2010
    Posts
    146
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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:

    PHP Code:
    $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
    PHP Code:
    ((isset($row['category']) == "1") && (isset($row['is_approved']) == "1")) 
    and it echoed true.
    Last edited by twobyfour; 06-16-2010 at 08:15 PM.

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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:
    Code:
    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:
    Code:
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #10
    Regular Coder
    Join Date
    May 2010
    Posts
    146
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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
    Last edited by twobyfour; 06-16-2010 at 09:22 PM.

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Then this is what you want:
    Code:
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #12
    Regular Coder
    Join Date
    May 2010
    Posts
    146
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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?
    Last edited by twobyfour; 06-16-2010 at 10:48 PM.

  • #13
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #14
    Regular Coder
    Join Date
    May 2010
    Posts
    146
    Thanks
    8
    Thanked 0 Times in 0 Posts
    PHP Code:
    SELECT B.Namecount(*) 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.
    Last edited by twobyfour; 06-17-2010 at 01:24 AM.

  • #15
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by twobyfour View Post
    PHP Code:
    SELECT B.Namecount(*) 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?
    Code:
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    twobyfour (06-17-2010)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •