Help me parse values from a delimeted text field (in DB) and update checkboxes on a form

[new2you]

Hello All,

 

Being a newbie at PHP coding I'm at my wits end trying to figure out:

     a) how to pull-in values from a delimeted text field (in a MySQL table) and check/select the appropriate checkboxes, based on the values that were stored in the text field,

     b) how to write any changes (made by the user) back to the tables' text field.

 

Note that this is for an "update.php" file/process.

 

My "create.php" file/process uses the following HTML to display and accept the checkbox values:

                <div class="control-group">
                    <label class="control-label">Limited Time Sale Days:</label>
                    <div class="lts-checkboxes-container">
                        <label class="indent-to-the-left">
                               <input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='F'><span class='no-highlight'>Fri</span>
                        </label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Sa'><span class='no-highlight'>Sat</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Su'><span class='no-highlight'>Sun</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='M'><span class='no-highlight'>Mon</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Tu'><span class='no-highlight'>Tue</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='W'><span class='no-highlight'>Wed</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Th'><span class='no-highlight'>Thu</span></label>
                    </div>
                </div>

And the code (certainly not the best code in the world, but it works) to collect, delemit/concatenate, and save to DB is as follows:

        if(empty($_POST['limited_time_sale']))
            {
                // echo("You didn't select any weekday.");
                $selected_lts = '';
                $limited_time_sale = '';
            }
        else
            {
                $limited_time_sale = $_POST['limited_time_sale'];    
                $N = count($limited_time_sale);
                $selected_lts = '';
                 // echo("You selected $N DoW(s): ");
                for($i=0; $i < $N; $i++)
                    {
                        // echo($limited_time_sale[$i] . " ");
                        if ($i < ($N - 1))
                            {
                                $selected_lts .= $limited_time_sale[$i] . "-";
                            }
                        else
                            {
                                $selected_lts .= $limited_time_sale[$i];
                            }
                    }
            }
 
        if(!empty($selected_lts)) { $limited_time_sale = $selected_lts; }

Now, I've figured out how to bring-in, and separate the stored values using the following code, however I have no idea what to do next...in order to have only the approprite boxes checked/selected (in the event that all boxes were not selected during the create stage).

                $limited_time_sale = isset($values['limited_time_sale']) ? $values['limited_time_sale'] : '';                
                $checked_lts = explode("-", $limited_time_sale);

In my "update.php" file, the HTML for the forms' checkboxes is as follows:

 

 

                <div class="control-group">
                    <label class="control-label">Limited Time Sale Days:</label>
                    <div class="lts-checkboxes-container">
                        <label class="indent-to-the-left">
                               <input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='F' <?php echo $lts1; ?>><span class='no-highlight'>Fri</span>
                        </label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Sa' <?php echo $lts2; ?>><span class='no-highlight'>Sat</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Su' <?php echo $lts3; ?>><span class='no-highlight'>Sun</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='M' <?php echo $lts4; ?>><span class='no-highlight'>Mon</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Tu' <?php echo $lts5; ?>><span class='no-highlight'>Tue</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='W' <?php echo $lts6; ?>><span class='no-highlight'>Wed</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Th' <?php echo $lts7; ?>><span class='no-highlight'>Thu</span></label>
                    </div>
                </div>

 

 

Attached picture shows that selected checkboxes are saved to the text field as/in the format of "F-Sa-Su-M-Tu-W-Th" - if all checkboxes/weekdays were selected....or as "Sa-M-W-Th" - if only Sat, Mon, Wed, and Thu checkboxes were selected

 

Thanks.

 

[Barand]

So not only are you storing a delimited list in a field (which is a definite no-no in itself) you also store numeric values in the same column?

 

Read up on data normalization and redesign your tables.

[new2you]

@Barand,

 

I do know a bit about data normalization...and I've tried my to normalize my tables as best as possible....but I certainly wasn't aware that I could/should normalize them to achieve what I'm asking for.

 

Let me rephrase that: since I don't know what I don't know, I have no idea how I can use normalization to get the answer that I'm seeking. I just have no idea what (more) I should be doing to normalize my tables in order to store the selected values differently.

 

Let me also ask you 2 questions:

 

1) Why is it a no-no to store a delimited list in a field?

2) When you say I'm storing numeric values in the same column, are you refering to the hyphen/minus sign? If yes, I'm curious again to know why that's an issue.

 

Thanks for your reply, and for trying to help.

[Barand]

When you say I'm storing numeric values in the same column, are you refering to the hyphen/minus sign?

 

 

No, these

[Jacques1]

I do know a bit about data normalization...and I've tried my to normalize my tables as best as possible....

 

Um, what? The table is a complete mess. You've stuffed numbers, units of measurement, lists of values, text and whatnot all into the same column. It's like an Excel spreadsheet from hell. Are you even aware that MySQL has different data types besides TEXT?

 

So before you do anything, learn the basics of relational databases.  Wikipedia has a couple of easy-to-read articles with a lot of examples. What's important to understand is that MySQL is not Excel.

 

When that's done, the easiest solution for your current problem is to load the saved values and turn them into an associative array with this format:

$selected_weekdays = array(
    'Th' => true,
    'Sa' => true,
)

The values are irrelevant, what's important is that you can check the presence or absence of a particular weekday simply by checking if the key exists:

if (isset($selected_weekdays['Th'])) echo 'selected';    // for the "Th" checkbox

1) Why is it a no-no to store a delimited list in a field?

2) When you say I'm storing numeric values in the same column, are you refering to the hyphen/minus sign? If yes, I'm curious again to know why that's an issue.

 

The problem is that you've degraded MySQL to a dumb text store, breaking almost all of its features:

• You cannot properly query the data. Even trivial tasks require weird string function hacks, because you cannot access the values directly.
• You cannot make sure that the data actually makes sense. Those text strings can contain anything, so there's a huge risk of ending up with tons of garbage data.

Edited November 5, 2014 by Jacques1

[new2you]

@Barand and @Jacques1,

 

Perhaps I should have mentioned, and I'm sorry that I didn't but I've setup most of those fields as VARCHAR, including the field titled LT.S....and therfore, during the testing stage, I've only been typing-in numbers, to make date-input quick and easy. I know it's not the right thing to do. but I must admit my fault.

 

@Jacques1, I will certainly look into what you've suggested, and I'll even need to digest the little bit of coding help you've provided, before I come back with more questions.

 

Thanks

[new2you]

Hello again folks,

 

Thanks for "opening my eyes" to the fact that I should have first thought (and used) "Database Normalization", as opposed to "Storing concatenated/delimited values in a text field". It now makes sense to me, and I now have some more questions and a further plea for coding help.

 

So, based on your suggestions, what I've now done is that I've created two new tables as follows:

 

1) A master file (named weekdays_master), which has 7 rows, one for each day of the week (structure displayed in first attached image)

2) A transaction file (named selected_weekdays), which may contain upto 7 rows per transactionID (depending on how many days are selected/checked in the data-entry form) - structure displayed in the second attached image

 

First question: does the structures for the two new tables look good, or have I got it all wrong?

 

Second question: Can/should I use the weekdays_master table (in some way or the other) to display and/or accept the checkbox values on the form...or should I just hard-code values as part of the form HTML? If the former is suggested, how would I go about doing it? Appreciate if I could receive some code help.

 

The HTML code for the checkboxes is shown below:

 

 

                <div class="control-group">
                    <label class="control-label">Limited Time Sale Days:</label>
                    <div class="lts-checkboxes-container">
                        <label class="indent-to-the-left">
                               <input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='F' <?php echo $lts1; ?>><span class='no-highlight'>Fri</span>
                        </label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Sa' <?php echo $lts2; ?>><span class='no-highlight'>Sat</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Su' <?php echo $lts3; ?>><span class='no-highlight'>Sun</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='M' <?php echo $lts4; ?>><span class='no-highlight'>Mon</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Tu' <?php echo $lts5; ?>><span class='no-highlight'>Tue</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='W' <?php echo $lts6; ?>><span class='no-highlight'>Wed</span></label>
                        <label><input class='lts-checkbox' type='checkbox' name='limited_time_sale[]' value ='Th' <?php echo $lts7; ?>><span class='no-highlight'>Thu</span></label>
                    </div>
                </div>

 

Now, upon clicking the "Submit/Update" button, I would obviously also have to create (or delete) a transaction each (in the selected_weekdays table) for each weekday that was checked/selected (or unchecked/unselected) on the form, with the main transaction_ID as the foreign key. And that of course is in addition to the main transaction record itself being updated (with any changes made by the user).

 

So, how would I go about doing that i.e. writing (or deleting) many (up to 7) records in the selected_weekdays table, for the one transaction_id? Again, providing the necessary code will be most helpful, since I'm still very new to this, and this is certainly way beyond my current skills.

 

Much thanks again for all the help and advice you wonderful people are giving me. Appreciate it!

 

 

 

 

[Barand]

Those table look OK now, provided TransactionID is the primary key of your original table, which I'll assume is called "transaction".

 

Don't use "." in column names, SQL will think that is database.tablename.columnname, change it to "LTS"

 

For the check boxes I would do something like this

SELECT wm.weekday_name
   , sw.weekday_id
FROM weekdays_master wm
   LEFT JOIN selected_weekdays sw USING (weekday_ID)
WHERE sw.transactionID = ?
ORDER BY wm.weekday_id

This will give you all the weekdays showing which were selected (those not selected will have NULL weekday_id

+---------+-----+
|  Mon    | NULL|
|  Tue    | 2   |
|  Wed    | 3   |
|  Thu    | 4   |
|  Fri    | NULL|
|  Sat    | NULL|
|  Sun    | NULL|
+---------+-----+

So now you can loop through the results, output the checkboxes and labels and set as "checked" if the id is not null