Closed Auto count the Values in Dropdown list

[The Lazy Juan]

Paano po iAutoCount yung values sa Dropdown List (naka auto populate).
PHP at MySQL database

Example:
(eto na yung nafetch na values na nanggaling sa dabatase para sa dropdown list)

CHOICE #1 -> 10
CHOICE #2 -> 8
CHOICE #3 -> 9

Yung naka kulay RED eh yun yung bilang ng mga choices sa database.

 

[pixkit]

I saw your message.

I don't fully understand the question. So let's say the db returns 10 records on first call, then 9 records next and 8 subsequently. You wanna know how many records are in there?

When you make that query from the db, you can include the count as part of the query or you can user you backend service to count the record. The UI can also count the records. I'd prefer though that the count comes from the backend db.

 

[pixkit]

Or do you mean the db returns 10 items and you want those 10 items to auto-populate the dropdown so it shows those 10 items as the possible items in the dropdown?

 

[The Lazy Juan]

pixkit lets say i have a field called fld_choice (which has a record CHOICE#1, CHOICE#2, CHOICE#3) in table tbl_choices and i populate the dropdown list using the fld_choice so when i click the dropdown list the CHOICE#1, CHOICE#2, CHOICE#3 will appear with the numbers how many of them are in the field called fld_selected and fld_selected is on table called tbl_selectone so it will be like this when i click the dropdown list

CHOICE#1 - 10/20
CHOICE#2 - 9/20
CHOICE#3 - 8/20

<select>
<option>SELECT YOUR CHOICE</option>
<?php
$result = mysqli_query($connection, "SELECT * FROM tbl_choices")or die(mysqli_error());
while($row = mydqli_fetch_array($result)){
$choice = $row['fld_choice'];
?>

<option value="<?php echo $choice;?>">
</option>
</select>

Lets just say that CHOICE#1 is saved in the fld_selected 10 times, CHOICE#2 isa saved 9 times and CHOICE#3 is saved 8 times in the fld_selected.
So the output when i click the dropdown list should be

CHOICE#1 - 10/20
CHOICE#2 - 9/20
CHOICE#3 - 8/20

 

[pixkit]

<?php
$result = mysqli_query($connection, "SELECT * FROM tbl_choices")or die(mysqli_error());
while($row = mydqli_fetch_array($result)){
$choice = $row['fld_choice'];
?>

One way of doing this is to create a sql query that contains the field choice name and the count number.

So something like "SELECT fld_choice, count(*) as count from tbl_choices"

Then on your $choice, you can have
$choice = $row['fld_choice'] - $row['count']/20

You might need to fix the syntax since I did not verify this answer with my console or IDE. But this should give you a possible solution.

 

[pixkit]

Ok, I run a similar query on my test database;

select countrycode, count(*) as count from city group by countrycode;

I got the following results:

+-------------+-------+
| countrycode | count |
+-------------+-------+
| WSM         |     1 |
| YEM         |     6 |
| YUG         |     8 |
| ZAF         |    44 |
| ZMB         |     7 |
| ZWE         |     6 |

Imagine the countryside is fld_choice and the count is the number of times the field was saved.

 

[pixkit]

In PHP,

$sql = "select countrycode, count(*) as count from city group by countrycode";

...
$template = '$field $count/20 <br/>';
    if ($statement->execute()) {
        while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
            $vars = array(
                '$field' => $row['countrycode'] ,
                '$count' => $row['count'],
            );
            echo strtr($template, $vars);
        }
    }

This outputs:

ABW 1/20 
AFG 4/20 
AGO 5/20

 

[The Lazy Juan]

pixkit thank you! I searched and found helpful query.

And i'll ask for another help pixkit CHOICE#1 reached 20/20 (full),

CHOICE#1 20/20
CHOICE#2 10/15
CHOICE#3 8/10

Id like to hide CHOICE#1 from the dropdown list because it is full. How can i do that?

And by the way i have a field that is called fld_slot in tbl_choices where the '10 or 15 or 20' is stored. The fld_slot can be 10 or 15 or 20.

table_choices
fld_choiceid(auto increment)
fld_choice (CHOICE#1,CHOICE#2,CHOICE#3)
fld_slot(20,15,10)

table_selectone
fld_selectedid(auto increment)
fld_selected(20 records of CHOICE#1, 10 records of CHOICE#2, 8 records of CHOICE#3)

 

[pixkit]

pixkit thank you! I searched and found helpful query.

And i'll ask for another help pixkit CHOICE#1 reached 20/20 (full),

CHOICE#1 20/20
CHOICE#2 10/15
CHOICE#3 8/10

Id like to hide CHOICE#1 from the dropdown list because it is full. How can i do that?

And by the way i have a field that is called fld_slot in tbl_choices where the '10 or 15 or 20' is stored. The fld_slot can be 10 or 15 or 20.

table_choices
fld_choiceid(auto increment)
fld_choice (CHOICE#1,CHOICE#2,CHOICE#3)
fld_slot(20,15,10)

table_selectone
fld_selectedid(auto increment)
fld_selected(20 records of CHOICE#1, 10 records of CHOICE#2, 8 records of CHOICE#3)

The concept is really simple. A caveman approach is just add an if condition

$max = 20;
$template = '$field $count/$max <br/>';
    if ($statement->execute()) {
        while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
            if ($row['count'] < $max) {
                       $vars = array(
                           '$field' => $row['countrycode'] ,
                           '$count' => $row['count'],
                       );
                       echo strtr($template, $vars);
           }
        }
    }

 

[pixkit]

However if I were you I won't do the caveman approach as is.

An alternative solution is to update the SQL query so you never get a result that goes beyond the max records:

select countrycode, count(*) as count from city group by countrycode having count(*) < 20;

 

[The Lazy Juan]

Thank you very much for answering all my questions..

How about auto change value in database if it like this..
(Just an example)

tbl_products

fld_productid >>> 1
fld_productname >>> Cheese
fld_datecreated >>> April 29, 2019
fld_expirydate >>> May 6, 2019
fld_productstatus >>> Not yet expired

When it is May 6, 2019 the Not yet expired(fld_prodcutstatus) of Cheese(fld_productname) will change to Expired(fld_productstatus)

 

[pixkit]

You can use a stored procedure or triggers but in MySQL it's called events.

CREATE EVENT IF NOT EXISTS event_foo_whatever
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
  INSERT INTO city (countryside, created_at)
  VALUES('PHI', NOW());

But I don't think this is a good solution. Just because you can does not mean you should.

The question is when does the "trigger" happens. Does it happen nightly at 12am? Or when someone clicks something on the UI and your backend discovers that the condition you presented is true?

If it's nightly, you can setup a scheduler service like Quartz to auto update at certain periods of the day, month, etc.

If it's via click, then this is just your typical CRUD interaction. I am guessing your problem is not really the auto-updating but you are really asking about the SQL statement

 

[The Lazy Juan]

pixkit thank you! I think i should try that events because my teacher said that it is auto update.

 

[The Lazy Juan]

You can use a stored procedure or triggers but in MySQL it's called events.

CREATE EVENT IF NOT EXISTS event_foo_whatever
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
  INSERT INTO city (countryside, created_at)
  VALUES('PHI', NOW());

But I don't think this is a good solution. Just because you can does not mean you should.

The question is when does the "trigger" happens. Does it happen nightly at 12am? Or when someone clicks something on the UI and your backend discovers that the condition you presented is true?

If it's nightly, you can setup a scheduler service like Quartz to auto update at certain periods of the day, month, etc.

If it's via click, then this is just your typical CRUD interaction. I am guessing your problem is not really the auto-updating but you are really asking about the SQL statement

pixkit how can i apply this to every product that have different expiry date every time i save a new product in table_products? Can you give me the query using my tables and fields.. please.. thanks..

 

[pixkit]

pixkit how can i apply this to every product that have different expiry date every time i save a new product in table_products? Can you give me the query using my tables and fields.. please.. thanks..

Right now, I have no idea how. And even if I knew, I won't do it. It's a code smell. Something is wrong with the way you address the problem if you are relying on this MySQL feature.

 

[pixkit]

Why do you need this?

When it is May 6, 2019 the Not yet expired(fld_prodcutstatus) of Cheese(fld_productname) will change to Expired(fld_productstatus)

So when it changes to Expired, how is this new status used?

So let's say a user wants to check an item and verify if it's expired. All you need to do is:
1. Get the item
2. Check if today's date is beyond the expiry date of May 6 2019
3. If it's expired, change the status to expired
4. Return the response to the user that the item has expired.

So you only do these steps if the user wants to check an item. There is no need to set a scheduler.

 

[The Lazy Juan]

pixkit how about the hiding all CHOICES in a dropdown list if they are full(with different capacity 10 to 20)

Because the query you gave me it only hides 1 CHOICE in the dropdown list if 2 choices are full.

CHOICE#1 > 19/20
CHOICE#2 > 15/15
CHOICE#3 > 9/10

if CHOICE#2 is 15/15 full then the output is

CHOICE#1 > 19/20
CHOICE#3 > 9/10

but if CHOICE#1 and CHOICE#2 are full the output is

CHOICE#2 > 15/15
CHOICE#3 > 9/10

it only hides one choice even if the othet choices are full. And i think that query results to blank dropdown list if the table_selectone is empty(there is no data in the fields of table_selectone).

 

[pixkit]

The query I gave to you is meant to spark your imagination It was NOT meant to be copied and pasted.

 

[The Lazy Juan]

The query I gave to you is meant to spark your imagination It was NOT meant to be copied and pasted.

Ok sir.