Problems with Checkboxes and displaying results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jviney
Forum Newbie
Posts: 7
Joined: Sun Jul 06, 2008 7:24 am

Problems with Checkboxes and displaying results

Post by jviney »

I have a site where the user uses checkboxes to select options. They are stored in a MySQL database as "Y/N" answers.

How do I write a query to display only the results that were checked by the user ("Y") and display only those column headings of the "Y"???

Any help would be appreciated! :D
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Problems with Checkboxes and displaying results

Post by califdon »

jviney wrote:I have a site where the user uses checkboxes to select options. They are stored in a MySQL database as "Y/N" answers.

How do I write a query to display only the results that were checked by the user ("Y") and display only those column headings of the "Y"???

Any help would be appreciated! :D
That all depends on what data type the field is. It could be Bool, or TinyInt, or Enum, or Char, or possibly something else. You need to find out what data type the field is, in the table.
jviney
Forum Newbie
Posts: 7
Joined: Sun Jul 06, 2008 7:24 am

Re: Problems with Checkboxes and displaying results

Post by jviney »

they are enum('N', 'Y')
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Problems with Checkboxes and displaying results

Post by califdon »

jviney wrote:they are enum('N', 'Y')
It will make a difference whether you allow a Null or not. If you don't allow a Null, you must set a Default of either 'N' or 'Y'.

To display a checkbox for such a field, it would be like:

Code: Select all

...
$sql = "SELECT id, myYNfield, ... FROM ....";
...
    echo "<input type='checkbox' name='myCheckbox' ";
    if($myYNfield=='Y') echo "checked";
    echo ">";
...
I don't understand what you mean by "display only those column headings of the 'Y'".
jviney
Forum Newbie
Posts: 7
Joined: Sun Jul 06, 2008 7:24 am

Re: Problems with Checkboxes and displaying results

Post by jviney »

the default is "N"...what I am trying to display in the output is...
If they checked the checkmark, display the row name.

An example I have is multiple checkmarks to select, "alcohol" and "food" and "dancing". If the venue has alcohol served, the checkmark for alcohol is selected and so on. In my output I want the table to say "alcohol" if it was checked.
:o
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Problems with Checkboxes and displaying results

Post by califdon »

jviney wrote:the default is "N"...what I am trying to display in the output is...
If they checked the checkmark, display the row name.

An example I have is multiple checkmarks to select, "alcohol" and "food" and "dancing". If the venue has alcohol served, the checkmark for alcohol is selected and so on. In my output I want the table to say "alcohol" if it was checked.
:o
Oh! OK, sorry I didn't understand what you wanted. Then you would want to do something like:

Code: Select all

...
while($row=mysql_fetch_array($result)) {
   ... // probably echo other fields, such as venue name, or whatever
   if($row['alcohol']=='Y') echo "Alcohol<br />";
   if($row['food']=='Y') echo "Food<br />";
   if($row['dancing']=='Y') echo "Dancing<br />";
}
 
jviney
Forum Newbie
Posts: 7
Joined: Sun Jul 06, 2008 7:24 am

Re: Problems with Checkboxes and displaying results

Post by jviney »

:lol: FANTASTIC It works! Thank you, thank you, thank you!

Now, I have another question, I'm having problems inserting those lines into a table format, any ideas? It would be much appreciated!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Problems with Checkboxes and displaying results

Post by califdon »

jviney wrote::lol: FANTASTIC It works! Thank you, thank you, thank you!

Now, I have another question, I'm having problems inserting those lines into a table format, any ideas? It would be much appreciated!
It's pretty hard to tell you how to code it when I don't know what you want it to look like. I presume you know basic HTML syntax (if you don't, I can't conduct a class in HTML here). Start with writing the HTML code for your table. Wherever it needs a value from the database, you just use <?php echo "...."; ?> whatever values you need. If it's different depending on something else, as in my example, you do all the conditional code (if, or while, or whatever) inside the php tags. For long stretches of HTML that doesn't need any values from PHP, you can just write plain HTML (outside any php tags), or for short stretches of HTML, you can use echo, from inside php tags. You'll find all the references and a lot of tutorials at http://w3schools.com

[Added: The thing to remember is that PHP is a preprocessor, meaning that Apache web server reads the script file and wherever there's a stretch of <?php ... ?> code, it processes that before it sends the file to the browser. So you can mix HTML, Javascript, and PHP, but the only part of the PHP code that is included is what is "echoed" or "printed".]
jviney
Forum Newbie
Posts: 7
Joined: Sun Jul 06, 2008 7:24 am

Re: Problems with Checkboxes and displaying results

Post by jviney »

Thanks much, I am very familiar with HTML...so I guess I'll start playing! :lol:
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Problems with Checkboxes and displaying results

Post by Ollie Saunders »

Note: Probably simpler to use a boolean field for such things in future.

Compare

Code: Select all

if ($wantsIceCream == 'y') // with enum
and

Code: Select all

if ($wantsIceCream) // with boolean
Also might be slightly better performance for the DB.
jviney
Forum Newbie
Posts: 7
Joined: Sun Jul 06, 2008 7:24 am

Re: Problems with Checkboxes and displaying results

Post by jviney »

Thank you Ollie...
Can you tell me how I put an if statement inside a table?
Thank you :D
jviney
Forum Newbie
Posts: 7
Joined: Sun Jul 06, 2008 7:24 am

Re: Problems with Checkboxes and displaying results

Post by jviney »

Can anyone tell me why this doesn't work????

<table border=3>
<tr><td><?php if($row['alcohol']=='Y') echo "Alcohol"; ?> </td>
</tr></table>
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Problems with Checkboxes and displaying results

Post by Ollie Saunders »

Please use php tags to surround your code.
There doesn't appear to be anything wrong with the code you've posted. Put this above it:

Code: Select all

<?php print_r($row); ?>
That will tell you what $row contains.
cool75
Forum Newbie
Posts: 2
Joined: Sun Jul 13, 2008 10:17 am

Re: Problems with Checkboxes and displaying results

Post by cool75 »

Great information posted by califdon.
Thanks for this valuable information here in this thread.
Post Reply