Is my table right? & advice about adding these extra fields
I have a table for events here is a link to a picture I grabbed from phpmyadmin .
What I need to do is add to the end of the table two fields.
1. 'highlights' and this is going to be like true or false. 0 or 1 like a checkbox
2. 'holiday' this will specify if it is a holiday event will be varchar 50chars
I plan to use this so I can use one table for news and events accross the whole site and just basically specify what gets picked out on what page with it own sql statement.
What I need to know is.
1. Is my table currently set up right? My Primary key is ID. and thats all thats special about it.
2. Should the holiday and highlight field be like: index on foriegn key?
I know nothing about these special attributes to each one of the fields so I am basically needing help understanding what will make my table better and faster. So I can use more powerful mysql statements.
Foreign keys are used for linking two tables together on a common column, so are not applicable at this time. For query efficiency, you may want to create indexes on any non-key, non-unique columns (which are automatically indexed) which are used in where clauses in your select queries.
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
I select most from the date area but it may be equal when I create these two other fields. these fields will help define just what page this stuff should go on.
so maybe make the date an index?
and the bar field a foriegn key. because it will be in used with another table I will create in the near future.
Am I kinda correct? please bear with my personal MySql skills. but how for example, would these work more effieceint in querys (please provide an example for both) then on the application level?
Last edited by Markbad311; 02-20-2006 at 03:35 PM.
MySQL doesn't have a true boolean type. Type BOOLEAN (or BOOL) is actually just a synonym for TINYINT(1). A value of 0 is considered "false" and non-zero values are considered "true".
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
I have a current MySql Query Like this for my events page :
PHP Code:
$sql = "SELECT date, bar, DATE_FORMAT(`updated`,'%M-%d-%Y') updated, details, map_url FROM $table_name WHERE `date` BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) and DATE_ADD(CURDATE(), INTERVAL 30 DAY) ORDER BY highlight DESC LIMIT $start, $show";
It works fine, yet I want to define my order so if it is a highlight it displays first then the rest is ordered by the name of the bar alphabeticaly
these don't provide the results I want.
ORDER BY highlight, bar
ORDER BY bar, highlight
If you click on the link above you will see Fat Tuesday is on top because it is a highlight event. but after the three fast tuesday listings I would like the rest to be ordered by "bar" (the Name of it that is stored in the bar field) Each heading is an example of the "bar" field
Reply With Quote
Bookmarks