www.webdeveloper.com
Results 1 to 6 of 6

Thread: Is my table right? & advice about adding these extra fields

  1. #1
    Join Date
    Oct 2005
    Location
    Erie, PA
    Posts
    401

    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.


    MySql ver#:4.1.14-standard
    PHP ver#:4.4.1

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,633
    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

    eBookworm.us

  3. #3
    Join Date
    Oct 2005
    Location
    Erie, PA
    Posts
    401

    ok ok that helped alot.

    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 04:35 PM.

  4. #4
    Join Date
    Oct 2005
    Location
    Erie, PA
    Posts
    401
    Woa follow up question isn't my true or false field for 'highlights' suppose to be boolean? and how do I use that is says to use it like (bool)??

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,633
    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

    eBookworm.us

  6. #6
    Join Date
    Oct 2005
    Location
    Erie, PA
    Posts
    401
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles