Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2005
    Posts
    6

    Unanswered: Help to create count for unique id

    Hello All,

    First of all I am a newbie to this forum and second I am learning MySQL so please pardon if I ask some silly questions.

    What I need help is how to display counts (no. of entries for a unique id) in this case store name.

    I have created my website and have listed all the stores at:

    http://indian-town.com/coupon/stores/allstores.php

    All the stores listed have unique ids in mySQL database.

    What i need help is that whwn I enter data for each store through phpMyadmin the updated count should display next to each stores.

    example : store xyz (5)

    where 5 would be the total entries for the store.

    Again please help me step by step how I would create the php script so that it would display.

    Again thanks for the help and your all valuable time.

    regards,
    Indian-Town

  2. #2
    Join Date
    Mar 2005
    Posts
    6

    Here what I have tried

    $query = "SELECT COUNT(dept_id) As repetitions, dept_id FROM `store_item` GROUP BY dept_id";
    $results = mysql_query($query) or die(mysql_error());
    $count = mysql_result ($results,0);

    echo "store($count)";

    it just shows store(5) in results.


    Please help that how should I proceed that I can individual counts for all the stores.

  3. #3
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    22
    Hallo

    Quote Originally Posted by Indian-Town
    $query = "SELECT COUNT(dept_id) As repetitions, dept_id FROM `store_item` GROUP BY dept_id";
    $results = mysql_query($query) or die(mysql_error());
    $count = mysql_result ($results,0);

    echo "store($count)";

    it just shows store(5) in results.
    Your Query returns a number for each dept_id in your table store_item!
    to get all of the results you must loop through the result:

    $query = "select dept_id, count(*) as repetitions from store_item group by dept_id";
    $results = mysql_query($query) or die(mysql_error());
    while ($res = mysql_fetch_object($results)) {
    echo "Store ID ". $res->dept_id . " has ". $res->repetitions ." Items";
    }

    Erich

    Have a bit more sun in your heart

  4. #4
    Join Date
    Mar 2005
    Posts
    6

    I need more help

    Sir,

    Thanks for your time for helping me out.

    I tried your code and i get the following results , I made little changes with ();

    Store ID 7 (5)Store ID 8 (16)Store ID 9 (8)Store ID 10 (3)Store ID 11 (7)Store ID 12 (2)Store ID 13 (6)Store ID 14 (3)Store ID 15 (8)Store ID 16 (4)Store ID 17 (3)Store ID 18 (10)Store ID 20 (6)Store ID 21 (3)Store ID 24 (3)Store ID 25 (2)Store ID 27 (2)Store ID 28 (11)Store ID 29 (3)Store ID 30 (2)Store ID 31 (2)Store ID 32 (2)Store ID 33 (8)Store ID 34 (1)Store ID 35 (1)Store ID 36 (8)Store ID 37 (3)Store ID 38 (14)Store ID 39 (3)Store ID 40 (1)Store ID 41 (9)Store ID 42 (2)Store ID 43 (3)Store ID 45 (14)Store ID 46 (1)Store ID 47 (3)Store ID 48 (10)Store ID 49 (1)Store ID 50 (1)Store ID 51 (2)Store ID 52 (3)Store ID 53 (7)Store ID 54 (1)Store ID 55 (5)Store ID 56 (2)Store ID 57 (3)Store ID 58 (1)Store ID 59 (1)Store ID 60 (1)Store ID 61 (6)Store ID 62 (10)Store ID 63 (7)Store ID 64 (5)Store ID 65 (2)Store ID 66 (2)Store ID 67 (5)Store ID 68 (1)Store ID 69 (13)Store ID 70 (1)Store ID 71 (2)Store ID 73 (3)Store ID 74 (2)Store ID 75 (2)Store ID 76 (4)Store ID 77 (1)Store ID 78 (4)Store ID 79 (6)Store ID 80 (1)Store ID 81 (2)Store ID 83 (3)Store ID 84 (1)Store ID 85 (2)Store ID 87 (8)Store ID 88 (1)Store ID 89 (10)Store ID 91 (1)Store ID 92 (1)Store ID 93 (1)Store ID 94 (1)Store ID 95 (1)Store ID 96 (2)Store ID 97 (1)Store ID 98 (1)Store ID 99 (5)Store ID 100 (4)Store ID 101 (1)Store ID 102 (1)Store ID 103 (2)Store ID 104 (1)Store ID 105 (7)Store ID 106 (3)Store ID 109 (1)Store ID 110 (1)Store ID 111 (1)Store ID 113 (3)Store ID 114 (1)Store ID 115 (2)Store ID 116 (1)Store ID 117 (1)Store ID 118 (4)Store ID 119 (3)Store ID 120 (1)Store ID 121 (1)Store ID 122 (6)Store ID 123 (5)Store ID 124 (1)Store ID 125 (3)Store ID 127 (1)Store ID 128 (1)Store ID 130 (2)Store ID 131 (4)Store ID 132 (2)Store ID 133 (1)Store ID 134 (2)Store ID 135 (1)Store ID 136 (10)Store ID 137 (4)Store ID 143 (1)Store ID 149 (2)Store ID 151 (3)Store ID 152 (1)Store ID 153 (1)Store ID 154 (1)Store ID 155 (3)Store ID 156 (12)Store ID 157 (3)Store ID 158 (3)Store ID 159 (6)Store ID 160 (4)Store ID 162 (3)Store ID 163 (3)Store ID 165 (2)Store ID 173 (2)Store ID 175 (2)Store ID 185 (1)Store ID 188 (2)Store ID 195 (2)Store ID 197 (1)Store ID 199 (3)Store ID 200 (8)Store ID 201 (1)Store ID 202 (1)Store ID 203 (1)Store ID 204 (2)Store ID 205 (1)Store ID 207 (1)Store ID 208 (1)Store ID 209 (5)Store ID 211 (8)Store ID 214 (4)Store ID 215 (2)Store ID 218 (1)Store ID 219 (2)Store ID 221 (2)Store ID 223 (26)Store ID 224 (9)Store ID 225 (17)Store ID 227 (1)Store ID 231 (1)Store ID 234 (3)Store ID 236 (1)Store ID 237 (1)Store ID 238 (5)Store ID 239 (1)Store ID 240 (3)Store ID 241 (2)Store ID 242 (2)Store ID 245 (8)Store ID 246 (15)Store ID 247 (9)Store ID 248 (1)Store ID 254 (27)Store ID 255 (19)Store ID 256 (1)Store ID 257 (5)Store ID 259 (2)Store ID 260 (2)Store ID 261 (1)Store ID 262 (1)Store ID 266 (1)Store ID 275 (2)

    What I need help is that I should now move ahead with incorporating on the page,

    http://indian-town.com/coupon/stores/allstores.php

    for example "Store ID 8 (16)" is for Dell Home under "D", how and what I should do so that (16) shows next to Dell Home and should show Dell Home (16).

    Once again thanks for the help and your valuable time.

    regards

  5. #5
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    22
    In the script which builds the mentioned page (allstores.php) you are probably fetching rows from a table containing the stores.

    the simplest way is to execute the 'count' statement for each dept_id

    processing-loop stores start
    ....
    $dept_id = current dept_id from processing loop
    $query = "select count(*) as cnt from store_item where dept_id = $dept_id";

    The result is the desired count for the current dept_id.

    ....
    processing-loop stores end

    Erich

    Have a bit more sun in your heart

  6. #6
    Join Date
    Mar 2005
    Posts
    6

    Still not able to resolve

    Here is what I have been trying but still not able to display the count;

    $dept_id = $_GET['dept_id'];

    //show counts
    $query = "select count(*) as cnt from store_item where dept_id = ".$dept_id.";
    $results = mysql_query($query) or die(mysql_error());

    if (mysql_num_rows($results) < 1) {
    echo "(0)";
    } else {
    while ($res = mysql_fetch_object($results)) {
    echo "Store ID ". $res->dept_id . " has ". $res->cnt ."
    Items";
    }

    I am trying in the browser with ../../../test.php?dept_id=8 to show the count.

    Please help where I am making mistake.

  7. #7
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    22
    Quote Originally Posted by Indian-Town
    Here is what I have been trying but still not able to display the count;

    $dept_id = $_GET['dept_id'];

    //show counts
    $query = "select count(*) as cnt from store_item where dept_id = ".$dept_id.";
    $results = mysql_query($query) or die(mysql_error());

    if (mysql_num_rows($results) < 1) {
    echo "(0)";
    } else {
    while ($res = mysql_fetch_object($results)) {
    echo "Store ID ". $res->dept_id . " has ". $res->cnt ."
    Items";
    }

    I am trying in the browser with ../../../test.php?dept_id=8 to show the count.

    Please help where I am making mistake.
    else {
    // your result will be ONE row, so no while loop is needed
    $res = mysql_fetch_object($results);
    // the dept_id is NOT included in the result but is the parameter
    echo "Store ID ". $dept_id . " has ". $res->cnt ." Items";
    }

    Have a bit more sun in your heart

  8. #8
    Join Date
    Mar 2005
    Posts
    6

    Still doesn't work

    I tried the final script as shown below, but shows as blank (no result)......... LMK where I am wrong.

    $dept_id = $_GET['dept_id'];

    //show items first
    $query = "select count(*) as cnt from store_item where dept_id = ".$dept_id.";
    $results = mysql_query($query) or die(mysql_error());

    if (mysql_num_rows($results) < 1) {
    echo "(0)";
    } else {
    // your result will be ONE row, so no while loop is needed
    $res = mysql_fetch_object($results);
    // the dept_id is NOT included in the result but is the parameter
    echo "Store ID ". $dept_id . " has ". $res->cnt ." Items";
    }

  9. #9
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    22
    As you posted earlier here the store id 8 has a count of 16 items.

    I think that you have a major problem in writing php code with mysql.

    could you send the entire test.php script code AND the url where it is called as a private message to me?

    Have a bit more sun in your heart

  10. #10
    Join Date
    Mar 2005
    Posts
    6
    I have sent you the PM

Posting Permissions

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