List:General Discussion« Previous MessageNext Message »
From:Stephen Moretti Date:November 29 2005 2:28pm
Subject:Re: Data type in MySQL / ASP.net
View as plain text  
Dewald Troskie wrote:
> I'm relatively new to the MySQL / Web development  - database
> environmeent, having moved from Microsoft SQL. Just two questions ?
>  
> 1. I have an ASP.Net page that commits data to a MySQL database. I have
> several check boxes on the page. What data type do I define my fields in
> the database as (similar to a boolean value) to write a checked value.
>   
There are a few ways you can solve this.  If you only have a couple of 
set values that will never change, then you could use an ENUM data type 
with the options as entries, but probably more appropriately you would 
be best to have a look up table that contains an ID field and a 
description for each of the values used by the check boxes.  Dynamically 
generate the checkboxes on your page from a query of this look up table, 
setting the value of each to be the unique ID assigned to the 
description.  Store the selected IDs in your database rather than the 
description alongside some form of "response" identifier. If users can 
select more than one checkbox, then you will probably need some form of 
link table between your "response" table and your lookup table, so that 
you don't end up with a many to many relationship in your database 
structure.  Its really a question of database design rather than 
datatype and this shouldn't be any different whatever database engine 
you use.
> 2. I have a foreign key field in the database. On the web page I have a
> drop down list populated by the text value from table in the database
> that the foreign key refers to. How can I convert this to a integer
> reference in the page so that I can write the foreign key.
This actually an HTML question rather than MySQL, but each OPTION tag in 
a select has a VALUE attribute.  Place your foriegn key in this 
attribute and the text bewteen open and close OPTION tags. eg.

<option value="{FOREIGNKEYID}">{TEXTDESCRIPTION}</option>

I don't know ASP.NET especially well, so the words in {} are intended to 
show where ASP.NET variables returned by your query would go. In CFML 
this would be :

<cfoutput query="qryLookUp"><option 
value="#qryLookUp.ID#">#qryLookUp.Description#</option></cfoutput>

Where ID and Description are the column names in the look up table.

Hope this helps

Regards

Stephen
Thread
Data type in MySQL / ASP.netDewald Troskie29 Nov
  • Re: Data type in MySQL / ASP.netStephen Moretti29 Nov
  • query problemross8 Mar
    • Re: query problemPeter Brawley8 Mar
    • Re: query problemDon Read12 Mar