- Joined
- Aug 2, 2016
- Posts
- 1,518
- Reaction
- 5,977
- Points
- 1,101
One way of doing this is to create a sql query that contains the field choice name and the count number.<?php
$result = mysqli_query($connection, "SELECT * FROM tbl_choices")or die(mysqli_error());
while($row = mydqli_fetch_array($result)){
$choice = $row['fld_choice'];
?>
select countrycode, count(*) as count from city group by countrycode;
+-------------+-------+
| countrycode | count |
+-------------+-------+
| WSM | 1 |
| YEM | 6 |
| YUG | 8 |
| ZAF | 44 |
| ZMB | 7 |
| ZWE | 6 |
$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);
}
}
ABW 1/20
AFG 4/20
AGO 5/20
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)
$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);
}
}
}
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());
You can use a stored procedure or triggers but in MySQL it's called events.
Code: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..
When it is May 6, 2019 the Not yet expired(fld_prodcutstatus) of Cheese(fld_productname) will change to Expired(fld_productstatus)
Ok sir.The query I gave to you is meant to spark your imaginationIt was NOT meant to be copied and pasted.
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?