Checkbox checked-status based on MySQL and PHP

Question

I have a basic PHP form (a few fields and 2 checkboxes, I want to add more checkboxes later, but for now I'm just using 2 for testing) in PHP. The fields and checkboxes save to a MySQL database and it works great.

I also have an 'edit' form that mirrors the original input form except that the values for the fields are loaded from the MySQL database based on the rows ID as is pretty standard.

The problem is that, at the moment, the last field of the edit form is just another text input field. What I would like to do is change that for checkboxes that will either be checked or unchecked based on the input from the original form.

I'm thinking there is a need to use ischecked but I'm unsure if I need to change the logic for the original form, or if I need to change the database itself (I hope not!).

The database is laid out thusly:

-------------------------------------------------------------------------------------------
| ID (int) | ARTICLEAUTHOR (varchar) | ARTICLEORGANIZATION (varchar) | ARTICLETAGS (varchar)|
 -------------------------------------------------------------------------------------------

And the code for the two forms is below.

First, the new entry form:

            // addnew.php

            <?php
             function renderForm($articletitle, $articleorganization, $articledate, $articleurl, $articletags )
            . . .
            </head>

            <body>
            . . .
                  <form id="form" name="form" action="" method="post">
                    <h1>Create a new entry in the database</h1>
                    <table width="100%" border="0" cellpadding="6">
                      <tr>
                        <td colspan="2"><legend>Article details</legend></td>
                      </tr>
                      <tr>
                        <td width="20%" align="right"><span class="field">Article Title:</span></td>
                        <td width="80%" align="left"><span class="field">
                          <input name="articletitle" type="text" value="<?php echo $articletitle; ?>" size="50"/>
                        </span></td>
                      </tr>
                      <tr>
                        <td align="right"><span class="field">Article Author:</span></td>
                        <td align="left"><span class="field">
                          <input name="articleorganization" type="text" value="<?php echo $articleorganization; ?>" size="50"/>
                        </span></td>
                      </tr>
                      <tr>
                        <td align="right"><span class="field">Article Date:</span></td>
                        <td align="left"><span class="field">
                          <input name="articledate" type="text" value="<?php echo $articledate; ?>" size="50"/>
                        </span></td>
                      </tr>
                      <tr>
                        <td align="right"><span class="field">Article URL:</span></td>
                        <td align="left"><span class="field">
                        <input name="articleurl" type="text" value="<?php echo $articleurl; ?>" size="50"/>
                        </span></td>
                      </tr>
                    </table>
            . . .
                      <input type="checkbox" name="articletags[]" value="checkbox" id="articletags_0" />
                      <input type="checkbox" name="articletags[]" value="checkbox 2" id="articletags_1" />

                      </div>
                    </fieldset>
                    <footer><input type="submit" name="submit" value="Add this Article"></footer>
            . . .
                    </form>
                </div>
              </div>
            . . .
            </body>
            </html>
            <?php 
             }

             // connect to the database
             include('settings.php');

             if(count($articletags) > 0)
            {
             $articletags_string = implode(",", $articletags);
            }
             // check if the form has been submitted. If it has, start to process the form and save it to the database
             if($_SERVER['REQUEST_METHOD'] == 'POST')
             { 
             // get form data, making sure it is valid
             $articletitle = mysql_real_escape_string(htmlspecialchars($_POST['articletitle']));
             $articleorganization = mysql_real_escape_string(htmlspecialchars($_POST['articleorganization']));
             $articledate = mysql_real_escape_string(htmlspecialchars($_POST['articledate']));
             $articleurl = mysql_real_escape_string(htmlspecialchars($_POST['articleurl']));
             $articletags = implode(',', $_POST['articletags']);

            . . .

             mysql_query("INSERT articles SET articletitle='$articletitle', articleorganization='$articleorganization', articledate='$articledate', articleurl='$articleurl', articletags='$articletags' ")
             or die(mysql_error()); 

             // once saved, redirect to success page
             header("Location:addsuccess.php"); 
             }
             }
             else
             // if the form hasn't been submitted, display the form
             {
             renderForm('','','','');
             }
            ?>

And now the edit form:

            <?php
             . . .
             function renderForm($id, $articletitle, $articleorganization, $articledate, $articleurl, $articletags)
             {
             ?>
            . . .
              <div class="content">
                <div id="stylized" class="myform">
                  <form id="form" name="form" action="" method="post">
                  <input type="hidden" name="id" value="<?php echo $id; ?>"/>
                    <h1>Edit Details for &nbsp; &nbsp;<?php echo $articletitle; ?></h1>
                    <table width="100%" border="0" cellpadding="6">
                      <tr align="center" valign="middle">
                        <td colspan="2"><legend>Article details</legend></td>
                      </tr>
                      <tr>
                        <td width="26%" align="right"><span class="field">Article Title</span></td>
                        <td width="74%" align="left"><span class="field">
                          <input name="articletitle" type="text" value="<?php echo $articletitle; ?>" size="50"/>
                        </span></td>
                      </tr>
                      <tr>
                        <td align="right"><span class="field">Article Author</span></td>
                        <td align="left"><span class="field">
                          <input name="articleorganization" type="text" value="<?php echo $articleorganization; ?>" size="50"/>
                        </span></td>
                      </tr>
                      <tr>
                        <td align="right"><span class="field">Article Date</span></td>
                        <td align="left"><span class="field">
                          <input name="articledate" type="text" value="<?php echo $articledate; ?>" size="50"/>
                        </span></td>
                      </tr>
                      <tr>
                        <td align="right"><span class="field">Article Url:</span></td>
                        <td align="left"><span class="field">
                          <input name="articleurl" type="text" value="<?php echo $articleurl; ?>" size="50"/>
                        </span></td>
                      </tr>
                      <tr>
                        <td align="right"><span class="field">Article Tags:</span></td>
                        <td align="left"><span class="field">
                          <input name="articletags" type="text" value="<?php echo $articletags; ?>" size="50"/>
                        </span></td>
                      </tr>
                      <tr align="center" valign="middle">
                        <td colspan="2"><input type="submit" name="submit" value="Submit" /></td>
                      </tr>
                    </table>
             . . .
                    </fieldset>
                    <footer></footer></form>
                </div>
              </div>
              <div class="footer">
            . . .
                <!-- end .footer --></div>
            </body>
            </html>
            <?php
             }



             // connect to the database
             include('settings.php');

             // check if the form has been submitted. If it has, process the form and save it to the database
             if (isset($_POST['submit']))
             { 
             // confirm that the 'id' value is a valid integer before getting the form data
             if (is_numeric($_POST['id']))
             {
             // get form data, making sure it is valid
             $id = $_POST['id'];
             $articletitle = mysql_real_escape_string(htmlspecialchars($_POST['articletitle']));
             $articleorganization = mysql_real_escape_string(htmlspecialchars($_POST['articleorganization']));
             $articledate = mysql_real_escape_string(htmlspecialchars($_POST['articledate']));
             $articleurl = mysql_real_escape_string(htmlspecialchars($_POST['articleurl']));
             $articletags = mysql_real_escape_string(htmlspecialchars($_POST['articletags']));

             . . .

             mysql_query("UPDATE articles SET articletitle='$articletitle', articleorganization='$articleorganization', articledate='$articledate', articleurl='$articleurl', articletags='$articletags' WHERE id=$id")
             or die(mysql_error()); 

            . . .
             {

             // get the 'id' value from the URL (if it exists), making sure that it is valid (checing that it is numeric/larger than 0)
             if (isset($_GET['id']) && is_numeric($_GET['id']) && $_GET['id'] > 0)
             {
             // query db
             $id = $_GET['id'];
             $result = mysql_query("SELECT * FROM articles WHERE id=$id")
             or die(mysql_error()); 
             $row = mysql_fetch_array($result);

             // check that the 'id' matches up with a row in the databse
             if($row)
             {

             // get data from db
             $articletitle = $row['articletitle'];
             $articleorganization = $row['articleorganization'];
             $articledate = $row['articledate'];
             $articleurl = $row['articleurl'];
             $articletags = $row['articletags'];

             // show form
             renderForm($id, $articletitle, $articleorganization, $articledate, $articleurl, $articletags, '');
             }
            . . .
            ?>

Generally a checkbox in the form will be associated with a boolean value in the database. From your code it looks like you're trying to use the associate checkboxes with the articletags field. Is that right? — mwotton
– mwotton, Commented Jun 15, 2012 at 1:18

mwotton · Accepted Answer · 2012-06-15 01:47:23Z

1

You would be better off having two new tables to track tags.

Your article table would lose the tags field

Tags table with the following structure:

Tags Table
-----
id (int)
tagname varchar(64)

ArticleHasTags crossmap table with the following structure

ArticleHasTags
----
article_id (int)
tag_id (int)

Then you build checkboxes based on the tags table entries. When storing an article, you are also going to add entries to the ArticleHasTags table based on the checkboxes in the entry form.

When displaying the edit form, you pull the data from both the Article table and the ArticleHasTags table, either with multiple joins or it might be easier to do a second select:

select * from ArticleHasTags where article_id = $id

(where $id is the id of the article you are editing)

You then pull all the rows from the Tags database again to show your checkboxes. As you show each checkbox you can check when the article has that tag by checking for Tag id against your retrieved ArticleHasTag results using in_array().

I can go into greater depth, however I would judge that it will be of more value to you in the long term to use these pointers and figure the details out yourself ;)

This is all very procedural, but without being patronizing, this looks like the level you're at with your existing code. Be encouraged and keep learning! You've come to the right spot.

answered Jun 15, 2012 at 1:47

mwotton

2,20018 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zrb0529 Over a year ago

This looks like an interesting thing to do. I would have thought that the additional complexity would be bad, though? If there are 2 articles, both written by Sylvester Stallone, one with title One, and one with title Two, and both are tagged with "weight lifting", but the first is tagged with "free weights" and the 2nd is tagged with "weight machines" then wouldn't this structure, in terms of search ability, be added complexity?

Collectives™ on Stack Overflow

Checkbox checked-status based on MySQL and PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related