Checkbox php and mysql

Question

I am building a website with PHP, I am not so good at php but i have found good tutorial online.

The website is for people to register as a dog walker, so when someone registers i want them to check the box of the areas that corresponds to them but there are many areas (100+) so I do not know whats the best way to do it using php and mysql. (i dont know anything about JS but i cant look for tutos)

Should all the names of the areas be store in one fild in the database or i have to build one field for each area?

I want people to be able to update the areas.

Then i wanted to have a select form with the search button.

I just want to know in words what is the best way to do it.

Thank you.

each field should be a one to one ratio for each column in the database. — Sablefoste
– Sablefoste, Commented Nov 7, 2013 at 22:53
@SableFoste: I might misunderstood you, but I hope you are not telling him to create a column in the user table for each area. — Joshua - Pendo
– Joshua - Pendo, Commented Nov 7, 2013 at 23:09
@PENDO, I guess it depends upon what the OP means by "areas"; looking at your solution below, I would agree with your assessment if "area" is a location for walking (like a city). But if you take "area" as criteria (like "comfort level", "dog size", etc.) which can't be characterized by a common field, then 1 to 1 is the way to go. — Sablefoste
– Sablefoste, Commented Nov 7, 2013 at 23:16
Didn't look at it that way, but one way or another, having so many table fields usualy is a sign that there are other better options. Mainly due to the efforts it would take to change your application for adding a single field. My example is easy extendable with grouping of area's and having an input option besides the checkbox which can be saved in the table as well. Oh well, guess the OP needs to clarify himself a bit. — Joshua - Pendo
– Joshua - Pendo, Commented Nov 7, 2013 at 23:21

Joshua - Pendo · Accepted Answer · 2013-11-07 23:08:22Z

I think the best practice is to get a one-to-many relationship in your database. Which means in a simplified way '1 walker can have many areas'.

If I understond Sable Foste's comment correct, he's telling you to create one column in your user table for each area, this would require a massive database table. Besides, updating your script would be a huge pain in the ass since you would have to add each area manually. Instead I would suggest you to create two tables:

table users

user_id
user_name

table areas

area_id
area_name

Fill the areas table with all options you have. Now, when a user wants to register on your page, you can perform an SQL query which fetches all areas like so:

$areas = $database->query("SELECT * FROM areas ORDER BY area_name ASC");
if( $database->num_rows( $areas ) > 0 ) {
   foreach( $database->fetch_array($areas) as $area ) {
       echo '<input type="radio" name="area[]" value="'.$area['area_id'].'" /> '.$area['area_name'].'<br />';
   }
}

$database illustrates a database wrapper in this example. You can also use mysql_ functions, however, they are about to be deprecated from PHP, so instead try to find tutorials on mysqli_ functions or PDO database layer.

As you can see I've named the fields area[], by doing this we get an array after we submit our register form. This array can be looped and contains all checked radio buttons. Each radio button contains the area_id:

if( isset( $_SERVER['REQUEST_METHOD'] == "POST" )) {
   // Make sure you check data here and insert the user in the database first before proceeding
   foreach( $_POST['areas'] as $area_id ) {
      // Do something with the $area_id
   }
}

Since we still have no option to connect the area_id to the user_id. To do so, we create another table that saves the connections:

table users_to_areas

area_id
user_id

In this database you will store each $area_id together with the $user_id of a newly registered or logged in user. NOTE: if you are updating a userprofile you can simply delete all previous records (DELETE FROM users_to_areas WHERE user_id = $user_id) and insert them again using the same looping method as above.

On the edit profile page, you can use the same script to list all areas again, to see if a user is connected to the area you can use a simple query (SELECT COUNT(*) FROM users_to_areas WHERE user_id = $user_id AND area_id = $area_id), if the num_rows() function returns 1 then the checkbox should be checked, otherwise it should be unchecked.

Hope this kicks you off in the right direction, good luck.

Thanks i will try that very helpful. i wast expecting to get any code! SO THANK YOU that will help me to get it faster.

Lloyd Banks · Accepted Answer · 2013-11-07 22:58:39Z

0

You should store all of your fields in separate rows. So if you have 100 areas, you should have 100 1 column rows (you can have more columns if there are other corresponding information you want to store).

The rest of your question is much more open-ended. You'll need to show some examples of what you are working with or have built to make it more clear

answered Nov 7, 2013 at 22:58

Lloyd Banks

36.9k58 gold badges172 silver badges264 bronze badges

Collectives™ on Stack Overflow

Checkbox php and mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related