Access checkbox translates to tinyint(3) field in Mysql ...

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
os2al
Forum Newbie
Posts: 14
Joined: Tue Jun 25, 2002 3:26 pm

Access checkbox translates to tinyint(3) field in Mysql ...

Post by os2al »

Hello,

I have an Access-to-Mysql translator, which works very well, except I'm stuck on one thing. I think the problem with my query not working is that I'm feeding checkbox data into the field, tinyint(3), which is the field type the translator created from an Access database checkbox.

I ended up doing this: my html side code changed to:

<input type="checkbox" value="Age" name="Age">Age<br>

Of course, this inputs the text Age into the field. At first I changed field type in the database to text and made it big enough to take the text, Age. That works okay. But I'm not sure what the value= should be for my Mysql database that has the tinyint(3) field. I thought that it would take a "1" or a "0" for on and off, something like that, but I don't know and I'm hoping someone might have run across the problem and can help me.

So, to get data from my html checkbox into the Mysql database that has a tinyint(3) field, I would change or do what?

Al :(
f1nutter
Forum Contributor
Posts: 125
Joined: Wed Jun 05, 2002 12:08 pm
Location: London

Post by f1nutter »

A check box allows multiple selections to be made, with the same "reference". So, for example, "please tick all that apply.. "

Code: Select all

<html>
<body>

<form method=get>
<input type="checkbox" value="red" name="colour">Red<br>
<input type="checkbox" value="blue" name="colour">Blue<br>
<input type="checkbox" value="green" name="colour">Green<br>
<input type="submit">
</form>

</body>
</html>
Then, if you select two colours and hit submit, the address line will look something like http://www.mysite.com/form.htm?colour=red&colour=green

The "value" field is the value you want to send when the checkbox is selected. Which is why you are sending the value "Age". You could select a range of ages:

Code: Select all

<html>
<body>

<form method=get>
<input type="checkbox" value="0-25" name="age">0-25<br>
<input type="checkbox" value="25-50" name="age">25-50<br>
<input type="checkbox" value="50+" name="age">50+<br>
<input type="submit">
</form>

</body>
</html>
and the database field would need to be text. If you want a true/false value, and only select one age range at a time, change type to "radio" and change the MySQL field to tinyint(1) and you can select BOOL from attributes. With this set-up you would need three columns, one named 0-25, one 25-50 and one 50+. One column would be true, the other two false.

This might not be the correct answer, but maybe this will send you in the right direction :)
os2al
Forum Newbie
Posts: 14
Joined: Tue Jun 25, 2002 3:26 pm

Thanks for the checkbox info

Post by os2al »

It's kind of what I thought; that is, that the tinyint(3) field didn't make a lot of sense. At least, that is what I figure you're saying, as well as providing the rest of the checkbox info.

I'm thinking of what to do with what I have, and thanks for your help.

Al
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

if you want to allow multiple selections but also want to store it in one field you may concatenate the 'real' values to one big string.
Or you can assign numbers as values to input-elements, i.e.

Code: Select all

<input type="checkbox" name="colour&#1111;]" value="1">Red<br> 
<input type="checkbox" name="colour&#1111;]" value="2">Blue<br> 
<input type="checkbox" name="colour&#1111;]" value="4">Green<br>
<input type="checkbox" name="colour&#1111;]" value="8>Yellow
when submitted the values will be in an array.

take a look at i.e. http://www.northwestern.edu/musicschool ... stdbh.html or http://www.enged.com/students/infor/infor46.html (or anything else that explains binary numbers) to see why assigning 1,2,4,8,16... as values

If you use array_sum on that array you will receive a number that is unique for each combination.
i.e. 'red' and 'green' selected --> array contains the values 1 and 4 --> combination-value = 5.
There is only one possibility how 5 can be represented in a binary system, as 2^0 + 2^2 = 1 + 4 = 5

you will lose the 'original' information which colours have been chosen, so you have to store it somewhere else (1=>'red', 2=>'blue'....).

To check wheter a colour has been selected you check if its number is in the combined-value, i.e. if ( ($value & 4) != 0) --> 'green' has been selected
Post Reply