Possible to populate a "checkbox from a mysql database?

[suttercain]

Good morning fellow php freaks,

 

I was wondering if it is possible to populate a checkbox from the MySQL database?

 

If NULL the check box is unchecked. If checked that means it has data in that column in the database.

 

CODE:

<?php
require ('get_connected.php');
    $sql = "SELECT * FROM canada";
    if ($result = mysql_query($sql)) {
      if (mysql_num_rows($result)) {
        
      }
    }
?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>NAME:</td>
    <td>YEAR:</td>
    <td>MAKE:</td>
    <td>MODEL:</td>
    <td>VIN:</td>
<td>LETTER SENT:</td>
  </tr>
  
<?php
while($row = mysql_fetch_array($result)) {
echo "<tr>
    <td>" . $row['first_name'] . " " . $row['$last_name'] . "</td>
    <td>" . $row['vehicle_year'] . "</td>
    <td>" . $row['vehicle_make'] . "</td>
    <td>" . $row['vehicle_model'] . "</td>
    <td>" . $row['vin'] . "</td>
<td><input name='letter_sent' type='checkbox' value='" . $row['letter_sent'] . "' /></td>
  </tr>";
  }

?>
</table>

 

The column name is letter_sent and as you can see towards the end of the code that is where I would like the check box to be populated.

 

Also does anyone know what data will be entered if I do an update via the checkbox? Does it enter TRUE or something into that column?

 

 

Thanks for the help guys.

 

SC

[pikemsu28]

yes you can have a database populate a checkbox.

 

just compare the database value with the checkbox value

 

<input name="lettersent" id="lettersent" type="checkbox" value="Yes" <?php if($row['letter_sent'] == "Yes"){echo 'checked="checked"';}?> />

 

if you want to update the database with a checkbox, the value of the checkbox will be the value posted to the database.

 

i can elaborate more if needed

[boo_lolly]

here's an example:

<?php
        while($row = mysql_fetch_array($result)){
                echo "
                <tr>
                        <td>" . $row['first_name'] . " " . $row['last_name'] . "</td>
                        <td>" . $row['vehicle_year'] . "</td>
                        <td>" . $row['vehicle_make'] . "</td>
                        <td>" . $row['vehicle_model'] . "</td>
                        <td>" . $row['vin'] . "</td>
                <td><input name=\"checkboxes[]\" type=\"checkbox\" value=\"{$row['letter_sent']}\"";
                        (($row['letter_sent'] != NULL) ? (" CHECKED") : (""));
                echo ">{$row['letter_sent']}</td>
                        </tr>";
        }
?>

 

EDIT: pike's code is wrong. php has no way of recognizing all the checkboxes in your form. that's why you put name="checkboxes[]". you can now access all the checked values by using $_POST['checkboxes'].

[suttercain]

Thank you for replying,

 

Let's reverse it. Let's say I have a check box option that I need entered into the database. Is that the value that is entered into the database?

 

So if I don't check the box, obviously it stays NULL. But If I do check it... what is intered into that cell in the database?

 

Thanks guys.

 

UPDATE: What value would have to be in the database to echo the checkbox checked? I entered CHECKED and it echoed the word next to the check box

[boo_lolly]

yes it is the value that is being inserted.

 

<?php
        foreach($_POST['checkboxes'] as $key => $val){
                mysql_query("INSERT INTO your_table (letter_sent) VALUES('{$val}')") OR die(mysql_error());
        }
?>

 

the only indexes in the $_POST['checked'] array are the ones that were checked. it doesn't store all of them, only the checked ones.

[suttercain]

Hi boo_looly

 

Thank for for helping me with this. I was wondering though. Let's say I check a box and run the insert query. What "data" is actually placed in the database where I have them checked? Or what I mean. What data has to be in the column to echo the checkbox as checked?

 

Because when I enter something in the MySQL database under the letter_sent column, it echoes out the word to the right of the unchecked checkbox.

 

 

[pikemsu28]

suttercain,  if you do not give your checkbox a value (value="(some value)") then 'on' will be inserted into your database.  i usually set the value to checkboxes so i know what is being inserted.

 

 

and my code is not wrong.  i've used this code many times to populate checkboxes from databases.

[suttercain]

I inserted "on" into one of my cells and echoed the page. The checkbox is still not checked though.

[pikemsu28]

can you post your code like you have it now?

[boo_lolly]

yes pike, your code is wrong.

 

suttercain, as i mentioned in previous posts, the 'data' that is stored in the database is the value="data". if it is checked, 'data' will be put into your table. if it is not checked, it will not be in the $_POST['checkboxes'] array, so you don't need to worry about it at all.

 

Because when I enter something in the MySQL database under the letter_sent column, it echoes out the word to the right of the unchecked checkbox.

 

i'm not sure i understand what you're saying there.

[pikemsu28]

well boo_lolly, since you think my code is wrong (but i know it works right) and you are a Sr. Helper and i'm only a Forum Regular, knock yourself out with this one!!!  good luck!!

[suttercain]

Hi Boo,

 

i'm not sure i understand what you're saying there.

 

Okay let's say you're looking at the page with the check box. [] = the check box.

 

When I echo out the page I see this:

[]text_in_database

 

I tried hand entering data into the letter_sent column to see if it would echo (I tired true, data, on, 'data') the check box as checked, but instead it echos the text from the database leaving the check box unchecked.'

 

 

Hi Pike,

 

Here is the current code:

<?php
while($row = mysql_fetch_array($result)) {
echo "<tr>
    <td>" . $row['first_name'] . " " . $row['$last_name'] . "</td>
    <td>" . $row['vehicle_year'] . "</td>
    <td>" . $row['vehicle_make'] . "</td>
    <td>" . $row['vehicle_model'] . "</td>
    <td>" . $row['vin'] . "</td>
<td><input name=\"checkboxes[]\" type=\"checkbox\" value=\"{$row['letter_sent']}\"";
                        (($row['letter_sent'] != NULL) ? (" CHECKED") : (""));
                echo ">{$row['letter_sent']}</td>
                        </tr>";
        }

?>

 

 

[boo_lolly]

i'm sorry but i still don't understand what you mean. let me explain what's going on.

 

when you have any amount of checkbox input fields within a form, you should put name="whateveryouwant[]". it is important to put the '[]' at the end of the name. the reason why is when you submit the form, all the checkboxes that were checked will be stored in an array called $_POST['whateveryouwant']. the values that will be stored in this array will be the value="something" for the checkboxes that were checked.

 

what you're trying to do, is pre-check all the checkboxes that have data in your mysql table. that's where this comes in:

(($row['letter_sent'] != NULL) ? (" CHECKED") : (""));

 

but regardless of whether it is pre-checked or not, the only values that will be stored in $_POST['whateveryouwant'] will be the checkboxes that were checked when you submitted the form.

 

does that clear things up a little?

[suttercain]

I think I am getting what you're saying. I have to first enter the data via the checkbox to echo out the checkbox as checked?

[boo_lolly]

well, at further glance, i see there's a little problem with what you want to do. i'll answer your question, but to answer it accurately i need some information from you first. so here's an explaination of what we've been doing...

 

we've been pre-checking checkboxes if there is something already stored in the database. if there isn't anything in the database, we don't pre-check the checkbox.

 

 

however, here's the problem with what we've been doing...

if there's no value in the database, there will be no value in value="{$row['letter_sent']}" because that's where it's being pulled from. so, here's what we should do:

<?php
while($row = mysql_fetch_array($result)) {
	echo "
		<tr>
		<td>" . $row['first_name'] . " " . $row['$last_name'] . "</td>
		<td>" . $row['vehicle_year'] . "</td>
		<td>" . $row['vehicle_make'] . "</td>
		<td>" . $row['vehicle_model'] . "</td>
		<td>" . $row['vin'] . "</td>
		<td><input name=\"checkboxes[]\" type=\"checkbox\" value=\"your_value\"";
                        (($row['letter_sent'] == "your_value") ? (" CHECKED") : (""));
                echo ">your_value_description</td>
                        </tr>
	";
        }
?>

 

so, essentially, what we've been doing is saying if there is something in the database, pre-check the checkbox, but if there isn't, then there's going to be no data in value="{$row['letter_sent']} because that is a NULL value in the database... so, i'd imagine you'd have to hard-code what you want the value to be, and THEN check if there's a match. instead of what we've been doing, by trying to compare something that could be empty with something that could be empty. make sense?

[boo_lolly]

well boo_lolly, since you think my code is wrong (but i know it works right) and you are a Sr. Helper and i'm only a Forum Regular, knock yourself out with this one!!!  good luck!!

 

you'll get there someday =). hahaha. come on man, learn how to take constructive criticism. it's a necessary skill in this game called 'life'.

[suttercain]

Hi Boo,

 

I tried pikemsu28's code and it's doing what I need. Where the letter_sent column has a yes on that row it is echoing the checkbox as checked. If it is null the checkbox is unchecked.

 

I also tried it as a MySQL update and if I check it it enters the value as yes into the column.

 

Seems to be working okay.

 

 

Thanks guys for your time and help.

 

SC

 

<?php
while($row = mysql_fetch_array($result)) {
echo "<tr>
    <td>" . $row['first_name'] . " " . $row['$last_name'] . "</td>
    <td>" . $row['vehicle_year'] . "</td>
    <td>" . $row['vehicle_make'] . "</td>
    <td>" . $row['vehicle_model'] . "</td>
    <td>" . $row['vin'] . "</td>";
?>
<td><input name="letter_sent" type="checkbox" value="yes" <?php if($row['letter_sent'] == "yes"){echo 'checked="checked"';}?>" /></td>
  </tr>
  <?php
  }

?>

[boo_lolly]

try that with more than one checkbox, and you will not be able to retrieve the data correctly. what you have is exactly what i just explained, by hard-coding the value instead of pulling the value from the database.

[suttercain]

Alright I take it back. The code seems to be working as far as when I type in yes into the column.... but when I run an update... it does not work.

 

Here is the code for the first page...

<form action="canada_update.php" method="post"/>
<?php
while($row = mysql_fetch_array($result)) {
echo "<tr>
    <td>" . $row['first_name'] . " " . $row['$last_name'] . "</td>
    <td>" . $row['vehicle_year'] . "</td>
    <td>" . $row['vehicle_make'] . "</td>
    <td>" . $row['vehicle_model'] . "</td>
    <td>" . $row['vin'] . "</td>";
?>
<td><input name="letter_sent" type="checkbox" value="yes" <?php if($row['letter_sent'] == "yes"){echo 'checked="checked"';}?>" /></td>
  </tr>
  <?php
  }
?>
<td><input type="submit" name="submit" value="submit" /></td>
</form>

 

 

Here is the code once the form has been submitted should update:

<?php
$letter_sent = $_POST['letter_sent'];

require ('get_connected.php');
    $sql = "SELECT vin FROM canada WHERE vin ='" . $row['letter_sent'] . "'";
    if ($result = mysql_query($sql)) {
      if (mysql_num_rows($result)) {
        
      }
    }

echo "$letter_sent";
echo "" . $row['vin'] . "";

require ('get_connected.php');
"UPDATE canada SET letter_sent = '$vin' WHERE vin = '$vin'";


?>

 

I know my sql query is somewhat messed up, I just don't know how bad. Is there a way to have this UPDATED depending on if the user checks or unchecks certain code?

 

I think I see what boo is talking about. I just can't figure out how to get this working. pikemsu28's code appears to work, but boo is right, with more than once checkbox it won't work. But when I tried your code Boo, I couldn't get that to work either.

 

SC

[suttercain]

I just tried this:

 

<?php
while($row = mysql_fetch_array($result)) {
	echo "
		<tr>
		<td>" . $row['first_name'] . " " . $row['$last_name'] . "</td>
		<td>" . $row['vehicle_year'] . "</td>
		<td>" . $row['vehicle_make'] . "</td>
		<td>" . $row['vehicle_model'] . "</td>
		<td>" . $row['vin'] . "</td>
		<td><input name=\"checkboxes[]\" type=\"checkbox\" value=\"yes\"";
                        (($row['letter_sent'] == "yes") ? (" CHECKED") : (""));
                echo ">your_value_description</td>
                        </tr>
	";
        }
?>

 

 

I replaced the values with yes.... in MySQL database I entered yes in some of the letter_sent column to see if the checkbox would echo out as checked. They still all appear unchecked

 

[boo_lolly]

ok a few things have changed, so the code won't work. can you explain what $vin is and why you're using 'yes' and what purpose you're using this for?

 

some people may not agree, but for me, it ALWAYS helps to know the scope of the project before i can help code it. what is this being used for. why have you decided to build your SQL tables in the fashion you have. not that it's wrong, it may be correct. but i won't know until i understand what's going on here. if you can explain those things, i can definitely get you going.

[suttercain]

Hi Boo,

 

I'll try to explain it as best I can, and I do appreciate you taking some time to help me on this.

 

I have a database where customers fill out a form and request a letter. That information is entered into the database.

 

The code I have been using in this thread is for the ADMIN side. The admin logs in, selects a date range and the results are echoed (see my first post for that code).

 

In the table that is echoed is the users Name, Address, Car Make, Model and Vehicle Identification Number (VIN) and in the last column I want that check box.

 

The check box is there for the ADMIN to check if the letter has been sent out. If the letter has not been sent, they can check it once they do send it.

 

I was using yes (and maybe not in the right way) to say yes (checked) the letter has been sent

and NULL (unchecked) if it hasn't.

 

Now, if I browse between dates and a record is unchecked and I check it. Once I hit submit, I would like that record to now be (UPDATE) checked when I pull it up in the future.

 

I hope I explained this well. If not let me know.

 

Thanks

 

SC

[boo_lolly]

yeah that explains a lot, thank you for that. so, what i would do, is instead of placing 'yes' in the column 'letter_sent', i'd change the column type to TINY INT. if the letter is sent, i'd just have a value of 1. if not, have a value of 0. this way, you can automate that every time a new row gets made in that table, it will always add 0 in the letter_sent column. obviously this is the case because a person has to send a letter, and THEN confirm that it was sent. in which case the column would be UPDATEd to have a value of 1, instead of 0.

 

remember in my previous post where i said we were comparing something that could be empty with something else that could also be empty? well here's where we fix that problem, and make your CMS altogether much better than it already is... in your table that holds the column 'letter_sent', you're going to need to add another column and call it 'id'. this column needs to be set to AUTO INCREMENT. with this new 'id' column, you can identify any specific row you want to. this is the value that will be stored in the checkbox input field. so, now, it would look something like:

<?php
while($row = mysql_fetch_array($result)) {
	echo "
		<tr>
		<td>" . $row['first_name'] . " " . $row['$last_name'] . "</td>
		<td>" . $row['vehicle_year'] . "</td>
		<td>" . $row['vehicle_make'] . "</td>
		<td>" . $row['vehicle_model'] . "</td>
		<td>" . $row['vin'] . "</td>
		<td><input name=\"checkboxes[]\" type=\"checkbox\" value=\"{$row['id']}\"";
                        (($row['letter_sent'] == "1") ? (" CHECKED") : (""));
                echo "></td>
                        </tr>
	";
        }
?>

 

and your update query would be something like this:

<?php
foreach($_POST['checkboxes'] as $key => $val){
	$sql = "UPDATE canada SET letter_sent = '1' WHERE id = '$val'";
}
?>

 

where $val is the id of that specific row. that small foreach loop will update every checkbox that is checked in your html table.

 

does that make sense? let me know if there's anything that isnt clear, or in need of more explaining.

[suttercain]

Hi Boo,

 

I made the changes to the database as suggested, but instead of naming the column id I named it record_id.

 

Here is the code I ran to generate the table:

 

<?php
while($row = mysql_fetch_array($result)) {
	echo "
		<tr>
		<td>" . $row['first_name'] . " " . $row['$last_name'] . "</td>
		<td>" . $row['vehicle_year'] . "</td>
		<td>" . $row['vehicle_make'] . "</td>
		<td>" . $row['vehicle_model'] . "</td>
		<td>" . $row['vin'] . "</td>
		<td>" . $row['date'] . "</td>
		<td><input name=\"checkboxes[]\" type=\"checkbox\" value=\"{$row['record_id']}\"";
                        (($row['letter_sent'] == "1") ? (" CHECKED") : (""));
                echo "></td>
            </tr>
	";
?>

 

Now I went to the MySQL table and manually changed one of the cells under the letter sent column to "1" (record_id 3) and the rest to zero. When I ran the query record three was echoed as well as the check box but it was unchecked.

 

It should be echoed as checked though right?

 

Thanks again for your time.

[boo_lolly]

yeah it should be checked... it may be the fact that your if statement is checking for a string instead of an intiger... but i'm not sure.

try putting this one in there instead:

(($row['letter_sent'] == 1) ? (" checked") : (""));