Forum Moderators: coopster
I have a bunch of select menus that are designed in the following way:
[fixed]
<select>
<option value="1">Apples</option>
<option value="2">Bananas</option>
</select>
[/fixed]The number '1' is what is being input into my MySQL database instead of the word 'Apples'. The difficulty arises in extracting the number value from the database and displaying it's fruit name. For example, a user is viewing another user's profile which lists favorite fruits. How do I get PHP to display 'Apples' instead of '1'? The only way I can think of would be a very long arduous process:
[fixed]
$selectedData = query used to extract from database
if ($selectedData == 1) {
echo 'Apples';
}
elseif ($selectedData == 2) {
echo 'Bananas';
} else {
echo '';
}
[/fixed]For an example as simple as the one above, this would be fine; but I have option values ranging any where from 100 to just about 1200. 1200 if, elseif statements is quite the undertaking; there must be a better, more server friendly (and developer friendly) way of tackling this issue!
Also, I am currently setting the instances in the database tables (instances that store data similar to the above example) as INT rather than varchar since the input is only a number; is this wise?
Thank you.
$array = array(1 => "Apples", 2 => "Bananas", 3 => "etc");
if(in_array($selectedData, $array)){
echo $array[$selectedData];
} else {
echo "Error msg";
}
id, Fruit
1, Apple
2, Banana
then your select would join on id and get the fruit.
A quicker way would be to just store the fruit instead of the number in the database. It is a bit wasteful for database size but it would certainly speed up processing and coding time.
Also, is it wise to store these number values in the database as INT or something else like varchar(4) for example?
I like to use INT and then just use autoincrement for the ID.
[edited by: mooger35 at 7:00 pm (utc) on June 5, 2009]
category, id, label
fruit, 1, Apple
fruit, 2, Banana
veggie, 1, Carrot
veggie, 2, Cucumber
Make sure you define a unique index on category+id to make your queries efficient.
As far as INT vs VARCHAR, if the values are always numbers, it's definitely better to declare the field as INT, for one thing it's self-documenting. Also if you run queries like "SELECT * FROM table WHERE favorite_fruit = 1", an INT field with an index on it would be best for query optimization.
Then again, if you allow the user to specify several favorite fruits, a lazy way to implement that is to store the values as an imploded array, e.g. '1,3,4', so then you'd want to declare the field as VARCHAR and try to not include it in a WHERE clause, espcially as the only the condition (that would result in a full table scan).
idfer, is there a non-lazy way of storing the values of multiple favorite fruits? I'm actually working on a multiple check box form right now and was planning on using the implode() function.
eg 1 = apple and nowhere in your database do you have that 1 is an apple?
...what's the point of the DB if it's merely storing keys without that associated value?
If I can make sense of the data in my database without bogging it down then isn't that all that should matter?
It seems like a lot more work than necessary.
Like your predicament here, when you want to display information on another page, that 1 is meaningless without the associated value 'apple'
You're mixing your presentation layer with your data...storing data in your presentation layer just creates headaches.
idfer, is there a non-lazy way of storing the values of multiple favorite fruits?
You'd need to create a separate table for favorite_fruits, with a foreign key to your users table (there's a technical term for this relationship but i skipped that class in our DBMS course), so something like:
user_id, fruit_id
1, 1
1, 2
2, 1
3, 3
user_id is the foreign_key, you want to create a non-unique index on it, and maybe another index on fruit_id if you frequently need to lookup all users who like apples.
If I can make sense of the data in my database without bogging it down then isn't that all that should matter?
Having more data in your DB, especially in separate tables, doesn't bog it down at all. Keep in mind one of the top rules of software engineering is to implement with maintainability as a priority, and only optimize afterwards, if you find performance problems. Well, up to a certain point. Hope this helps.
I don't know how to populate select menus from the database.
Same principle as in mooger35's post, except you populate the array by selecting from the lookup table:
function getFruits() {
$fruits = array();
$qh = mysql_query('select * from fruits order by id');
if($qh === false) die mysql_error();
while($row = mysql_fetch_array($qh)) {
$fruits[$row['id']] = $fruits[$row['fruit']];
}
return $fruits;
}
Bear with me as this is uncharted territory. Currently my database is composed of a simple hierarchy of tables which reference other tables. So, we have a category table whose primary key ID is a number and the adjacent column is the name of that number.
categoryID ¦ category_name
Then we have a post table which references the category table.
postID ¦ columns... ¦ categoryID
And finally we have the individual category posts.
ID ¦ columns... ¦ postID
If I were to create a look-up table; then my post table and individual category tables would be composed primarily of foreign keys which all link back to the look-up table as well as to each other and back to the main category table. Am I understanding this correctly? Also, how can I keep track of segments in the look-up table? Like this segment (ID 1-4) is for fruits and that segment (ID 5-8) is for vegetables? Keep in mind we are looking at thousands of rows which will be inserted into this table.
$qh = mysql_query("select * from lookupTable where optionType = 'fruit'");
To really normalize the DB, you could have a separate table (optionTypeID, optionType) for all optionTypes and use the optionTypeID in your lookup table, but that would be a bit of overkill. ;-)
The first is have one table called fruits, and another called vegetables, and while it's descriptive, it can be difficult to maintain if you're going to have to keep adding new tables. Depending on the type, amount of data and the type of system this is sometimes the right decision, other times not.
In this case though, since your table definitions would be identical for both fruits and vegetables, with only their type being the difference, putting them in one table is the right decision. And since thousands of rows for a database to handle is literally nothing there's no need to split the table up into segments (I'm currently working with tables with over a billion records and they are still performing well).
One thing that will improve performance of your webpages though, is don't use "select * from...". Be specific in your select clause and only return the columns you use. The database doesn't much care, but the network certainly does, it'll speed up pages, and depending on the amount of traffic, it can significantly reduce bandwidth consumption.
To really normalize the DB, you could have a separate table (optionTypeID, optionType) for all optionTypes and use the optionTypeID in your lookup table, but that would be a bit of overkill. ;-)
As a 'trick' you can name the column optionTypeCode, and use 'fruit' or 'vegetable' as the code, and later if you need to store more information about the optionTypes in their own table, you would use it as a foreign key. So your new table table would contain optionTypeId, optionTypeCode, optionTypeDescription, etc, etc.
I went with idfer's 'overkill' method since my database was already set up in a similar way. I have the look-up table properly indexed for quick and easy queries and for my while loops I simply add WHERE id = x;
Again, thanks a lot guys - I really appreciate you taking the time to comment and showing me that this kind of functionality exists in PHP.
I have one more question on this topic. So I created a while loop that looks like this:
[fixed]
$name_results = mysql_query("SELECT column, column FROM `table` WHERE column = 1");
if (statement) {
while($name_row = mysql_fetch_object($name_results)) {
Do stuff
}
} else {
No results
}
[/fixed]Is it possible to have the while loop stop after let's say, the first 6 results so I can insert a bit of CSS deco? Basically something like this:
[fixed]
$name_results = mysql_query("SELECT column, column FROM `table` WHERE column = 1");
if (statement) {
while($name_row = mysql_fetch_object($name_results)) {
Do stuff but stop after 6 results
}
echo CSS deco
while($name_row = mysql_fetch_object($name_results)) {
Do stuff but start after last stop; stop after next 6
}
echo CSS deco
while($name_row = mysql_fetch_object($name_results)) {
Do stuff but start after last stop; show remaining options
}
} else {
No results
}
[/fixed]
You want to use the modulus operator for that problem:
$name_results = mysql_query("...");
if (statement) {
$nrows = 0;
while($name_row = mysql_fetch_object($name_results)) {
Do stuff
$nrows++;
if($nrows % 6 == 0) {
echo CSS deco
}
}
} else {
No results
}
The modulus operator checks for a remainder; so your code is saying every time we divide by 6 and a remainder of 0 is displayed then do the following. So every 6 rows, the same CSS design was being displayed. My goal was to display a different CSS design at the 12th row so I used the following instead:
[fixed]
if($nrows == 6)
if($nrows == 12)
[/fixed]I really appreciate you siting the name of the operator rather than just showing code as it guided me in the right direction. Once again, I thank you for your input in this matter.
if($nrows % 12 == 0) {
// This should match 12, 24, 36, etc
} else if($nrows % 6 == 0) {
// This should match 6, 18, 30, etc
} else {
// All other rows
}
[edited by: Gibble at 8:01 pm (utc) on June 10, 2009]
As a general rule in programing, if you ever find yourself repeating anything, you're doing it wrong.
