PHP store and display option from dropdown menu

Question

I'm trying to create a basic PHP script where I can insert data into a MYSQL database and then, if the option already exists, to increment it by one. Example:

         Happy      Bored
Name1:     1          0
Name2:     0          1

And if I pick happy again for Name1 and for Name2, the result should change to:

         Happy      Bored
Name1:     2          0
Name2:     1          1

The HTML code:

<select name="moods">
<option value="happy" selected="selected">Happy</option>
<option value="bored">Bored</option>
</select>

[You can use `auto_increment`.](https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html) — deEr., Apr 15 '18 at 08:14
Possible duplicate of ["INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"](https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update) — Mickaël Leger, Apr 15 '18 at 08:24
@Anfath Hifans the table does not exist yet, please feel free to invent one. — Jacob K., Apr 15 '18 at 15:41

score 0 · Accepted Answer · answered Apr 16 '18 at 07:20

0

Create a Table with following codes

CREATE TABLE `tmp` (
  `id` int(11) NOT NULL,
  `Name1` varchar(256) NOT NULL,
  `Name1_count` int(11) NOT NULL,
  `Name2` varchar(256) NOT NULL,
  `Name2_count` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `tmp` (`id`, `Name1`, `Name1_count`, `Name2`, `Name2_count`) VALUES
(1, 'Happy', 0, 'Happy', 0),
(2, 'Bored', 0, 'Bored', 0);

after created, the table will be like attached image

Next,

add following sql query in your codes.

if you are using PDO connection, use like below,

$value = 'Happy';

$sql_1 = "UPDATE `tmp` SET Name1_count = Name1_count+1 WHERE `Name1` = ?";
$exec_1 = $pdo->prepare($sql_1)->execute([$value]);

$sql_2 = "UPDATE `tmp` SET Name2_count = Name2_count+1 WHERE `Name2` = ?";
$exec_2 = $pdo->prepare($sql_2)->execute([$value]);

if you are using codeigniter framework, use like below

$this->db->set("Name1_count", "Name1_count+1", false)->where(array('Name1' => $value))->update('tmp');
$this->db->set("Name2_count", "Name2_count+1", false)->where(array('Name2' => $value))->update('tmp');

answered Apr 16 '18 at 07:20

Anfath Hifans

1,588
1
11
20

Thank you so much. I did created the tables as you said, unfortunately I'm stuck with trying to figure it out how to implement it. My index.php is a mess at the moment: https://pastebin.com/Vf5p1zqS – Jacob K. Apr 16 '18 at 09:20
@JacobK update your submit validation by following codes https://pastebin.com/zf3dBcnB – Anfath Hifans Apr 16 '18 at 09:49
Thank you! I added that, and now it does update the database. But he problem is if I select Happy for Name1 and Bored for Name2, it doesn't update. Sorry for bothering. Current index: https://pastebin.com/sZMiAgYC – Jacob K. Apr 16 '18 at 10:04
@JacobK. now you are using 2 select boxes, so we can't assign same name for any field, we can change the name of select boxes or we can make select box as array field.. so here im making select box as an array field. i have added the codes.. replace the codes with here. : https://pastebin.com/NP0Ev1rc – Anfath Hifans Apr 16 '18 at 10:23
Thank you sir, you are a genious. One more question, please. After I press the submit button, how do I get the stored results for each usernames? Just like the example from the main post. Thanksss!!! – Jacob K. Apr 16 '18 at 11:08
@JacobK. here is the answer for your last question : https://pastebin.com/ZgRqLM0i I hope i have answered for your all question. please mark my answer is useful if it useful to you :) – Anfath Hifans Apr 16 '18 at 12:28
I did marked it already. Thank you so much and sorry, but could you please answer me a last question? I'm trying to display the informations in the plugin called DataTables. I used something like this but... I don't know how to grab the infos from the database to populate the rows. Thanks!!! Index.php: https://pastebin.com/FVGD2xLt – Jacob K. Apr 16 '18 at 14:02
@JacobK. check out here, you will get the idea: https://www.w3schools.com/php/php_mysql_select.asp – Anfath Hifans Apr 16 '18 at 14:43
Unfortunately, I wasn't able to display the database inside the datatables. :( – Jacob K. Apr 21 '18 at 13:13
@JacobK. i didn't get you. could you please explain it – Anfath Hifans Apr 21 '18 at 13:18
" I'm trying to display the informations in the plugin called DataTables. I used something like this but... I don't know how to grab the infos from the database to populate the rows. Thanks!!! Index.php: https://pastebin.com/XYVPSsnJ " – Jacob K. Apr 21 '18 at 13:27
here is the code for listing the values to show the results like you have mentioned in the question : https://pastebin.com/xdAecDtb – Anfath Hifans Apr 21 '18 at 13:56
Thank you for the answer, but unfortunately, it doesn't show anything. Nor any errors in the console. =/ – Jacob K. Apr 21 '18 at 14:21
Still doesn't work, but my file connects to the db like this: https://pastebin.com/rD2TWip2 I don't know if it matters or not, but the other infos (statics) are being displayed into the table. Only the one from the db are not. – Jacob K. Apr 21 '18 at 14:37

PHP store and display option from dropdown menu

1 Answers1