PHP form to edit database entries

Question

I have a MySQL database of my movie collection and I'm working on a little website for searching and editing the database. The whole project is more of learning thing then just trying to get it done the easiest way. So I am writing it all from scratch, just plain text files, and I am using mostly PHP.

So I want a page where I can edit the information about a movie. Adding new information is fairly straightfoward, but what about removing information. I have several checkboxes for genres. When the form comes up, any genre currently related to the movie is checked. I want to be able to uncheck a box and then that genre will be dropped from that movie. The only way I can think to do this is create an initial array of values when the form opens, and get the POST array when the form is submitted. Then delete any genres in the first but not the second array. This seems kind of messy, and I feel like there should be a more elegant way to do this. This seems like a pretty standard thing to so maybe people have a nice way to do this?

I usually solve this with ``, checked means the result for genre with id `123` is `1`, unchecked it comes back as `0`. — Wrikken, Jun 03 '13 at 17:54
If you are wanting changes to be done on the fly when the box un-checks rather than click a 'submit' button you could use a jQuery AJAX call to POST to a separate PHP script that handles the action for you. — Ryan, Jun 03 '13 at 17:55
I hadn't been using AJAX because I haven't worked with it much, but maybe this is a good opportunity. I could use AJAX with my PHP functions to update the database at every change? — Dan, Jun 03 '13 at 20:17

Diogo Baracho · Answer 1 · 2013-06-03T20:17:56.100

You can use input checkbox click event to call an ajax action.

Using jquery (http://api.jquery.com/jQuery.ajax/):

$(document).ready(function() {
  $("#form input[type=checkbox]").click(function() {
        $.ajax({
            url : '/sample/'+$(this).attr('any_id')+'/'+$(this).attr('other_id'),
            error: function(){alert('error')},
            success : function(data){alert('success')}
        });
  });
});

And than parse the url to get what you need, and on success function update your form and/or page information using jquery functions like $('element').html() and others.

Another option if you prefer pass the serialized data option for this same function:

$(document).ready(function() {
  $("#form.checkboxes input[type=checkbox]").click(function() {
        $.ajax({
            url : '/sample-form-action',
            type: "POST",
            data: $("#form.checkboxes").serialize(),
            error: function(){alert('error')},
            success : function(data){alert('success')}
            },
  });
});

you can create the form tag around the checkboxes, in this case you can ajax post just the information you really need:

<form name="checkboxes" class="checkboxes">
 <input type="checkbox" />
</form>

I like this solution. I am definitely going to start looking in to AJAX and trying to implement it. I also need to make some search capabilities and I think AJAX is going to be my best bet for that. — Dan, Jun 03 '13 at 20:52

score 1 · Accepted Answer · answered Jun 03 '13 at 20:35

1

The best way I can think of is to have a many-to-many relationship between genres and movies (meaning a movie can have multiple genre and one genre can appear on many movies).

This involves adding two more tables, one to define the genres, and one to associate them with movies:

genres
---------
id | name

Here's the slightly tricky part:

genres-movies
-------------------
movie_id | genre_id

Where movie_id is an actual ID from the movies table, and genre_id is an actual ID from the genres table.

For instance, genre with the ID of 1 is Science Fiction, and the movie with the ID of 42 is Hitchhiker's Guide to the Galaxy (see what I did there?), this means that if I have the entry 42 | 1 at the genres-movies, will mean that Hitchiker's Guide to the Galaxy is a Science Fiction. Note that neither movie_id nor genre_id are unique, both can appear multiple times, so the following is possible:

movie_id | genre_id
---------+---------
1        | 2
1        | 3
2        | 2
3        | 1

To find all the genres of a movie:

SELECT `genre_id` FROM `genres-movies` WHERE `movie_id` = <ID>;

After all the background, to your question. With that approach, you need multiple queries, remove all genres from a movie, then each to add the ones you selected. You have to do that because checkboxes that were not selected are not sent to the server (pseudo code)

DELETE FROM `genres-movies` WHERE `movie_id` = <ID>; --Remove all genres

foreach ($genres_from_checkboxes as $genre) {
    INSERT INTO `genres-movies` (`movie_id`, `genre_id`) VALUES (:movie_id, :genre_id)
}

NOTE! Always sanitize (or better yet prepare!!!) queries with data that came from the user!

Phew! That was long, hope you learned a thing or two :)

answered Jun 03 '13 at 20:35

Madara's Ghost

172,118
50
264
308

Thanks for the answer. I was already doing what you said regarding the many-to-many relationship, but thanks for the confirmation that I did it right. In the mapping table I also have a combo column that is a concatenation of the two ids; and this IS unique (for the most part).\n>remove all genres from a movie, then each to add the ones you selected.\n\nI was thinking about doing the update this was, but wasn't sure it was the best solution. But I'm becoming convinced that it is the most elegant solution. – Dan Jun 03 '13 at 20:40
@dan08: You don't need that third column, you can have a [**UNIQUE KEY for both columns**](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) (Then the combination of them is unique :P). Also, you don't want to start messing with UPDATEs and what to update, what to remove and what to insert. It will all go a lot smoother if you just remove everything, and re-add it all. It's also not that big a deal, it's only what, 5-10 insertions tops? – Madara's Ghost Jun 03 '13 at 20:46
Ok, so I have this same kind of situation with other fields, like actors, and collections. It would just be best to clear all mappings and re-add them? Also, what I'm doing is very small scale and performance and speed is no issue. But at a larger scale is this still the way it would be done? – Dan Jun 03 '13 at 20:51
@dan08: Yes, and yes. This approach is definitely much better than trying to figure out which already exist, and which are not. Also, with the correct use of prepared statements, you can gain a nice performance boost when executing the same query with different parameters over and over. (You prepare the query once, and execute the resulting **statement** multiple times, as opposed as executing a query multiple times). You can read about it in the PDO part of the PHP manual. – Madara's Ghost Jun 03 '13 at 20:53
Oh and I had the third column because I was searching to see if the mapping existed before adding one, so I wouldn't add multiple mapping. But with this solution I don't need to do that. – Dan Jun 03 '13 at 20:55
@dan08: Not only that, but be advised that it *won't let you*, it will result in an error in the query (UNIQUE KEY CONSTRAINT FAIL), that you need to handle. If you do it right though (remove properly before trying to add), you should never have these sorts of errors. – Madara's Ghost Jun 03 '13 at 20:57

score 1 · Answer 3 · answered Apr 25 '19 at 11:16

You can use php if statement to check the value in database column and display checked

<input name="column" type="radio" value="1" <?php if($table['column'] = 1) echo "checked" ?>>
<input name="column" type="radio" value="0" <?php if($table['column'] = 0) echo "checked" ?>>

OR if you are using checkbox

<input name="column" type="checkbox" value="1" <?php if($table['column'] = 1){ echo "checked" }elseif($table['column'] = ){ echo "" } ?>>

i hope that will work for you

PHP form to edit database entries

3 Answers3