CODEWITHSUNDEEP
phpmysqlsearchcheckbox
Wrostran
Wrostran

Reputation: 90

Checkbox php and mysql

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.

Upvotes: 0

Views: 249

Answers (2)

Joshua - Pendo
Joshua - Pendo

Reputation: 4371

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.

Upvotes: 1

Lloyd Banks
Lloyd Banks

Reputation: 36648

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

Upvotes: 0

Related Questions