SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Zealot Nu1's Avatar
    Join Date
    Jul 2000
    Location
    California
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help setting up template system...

    Hi all,

    I am building a web site where a user arrives at a splash page.
    The splash page has a "Choose city" pull down button which at
    choosing takes the user to their corresponding city page.

    I want to accomplish a template system where depending what
    city the user picks, the content is populated dynamically.

    I have created MySQL tables for the content that contain the
    content buy my problem is I am not sure how this is done.
    I understand this could be done with variables but I am still
    unsure. If anyone could clear understanding on this, I would
    greatly appreciate it.

    Thanks!
    "Don't sweat the small stuff"

  2. #2
    SitePoint Guru
    Join Date
    Feb 2002
    Posts
    625
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, there are so many different ways of doing this. Where to begin.

    But since you say you have your content for the different cities stored in the database already...
    Just how does your DB Design look?

    Can you maybe post the table structure?

    Usually what you would do is having a form which allows to select the city, store the value in a session (or DB whatever), and using this value you select the appropiate content from the database.

    of course in order for this to work your DB Design will have to be correct.

    For example, let's say i pick the city Vienna, you would retrieve the content from the database using this cities name.

    SELECT * FROM table WHERE flag='$city'

    Something like this.

    I hope this helps you get you started, if not post the table structure and i will get back tommorow (although im pretty sure someone else might help out as well)

  3. #3
    SitePoint Zealot Nu1's Avatar
    Join Date
    Jul 2000
    Location
    California
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey thanks for your input. Well my table layout looks something like this:

    State_tbl:
    Key field - state_id
    Fields - state_name, state_initial

    City_table:
    Key field - city_id
    Fields - city_name, state_id(from state table)

    Nav Menu table:
    Key field - menu_list, city_name (from city_table)

    Events_table:
    Key field - event_id
    Fields - event_name, event_date, event_time, event_description, city_name (from city_table)

    Top_5spots:
    Key field - topspot_id
    Fields - spot_name, spot_comments, spot_rating, city_name (from city_table)

    Thanks!
    "Don't sweat the small stuff"

  4. #4
    SitePoint Guru
    Join Date
    Feb 2002
    Posts
    625
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now i am not sure, did you get it to work or do you still need help ?

    From what i've seen you can realize the above concept

  5. #5
    SitePoint Zealot Nu1's Avatar
    Join Date
    Jul 2000
    Location
    California
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey datune!

    Good to hear from you! I have not succeeded yet.
    My problem is that I am not sure on how to pull all these tables once the user has picked a city.l

    Would I need to use variables? I believe I have to created small php scripts to make this work but I am not sure how to go about this. I do have a book that I am browsing through but nothing specific to what I am doing. Any pointers as to what is a good book or good tutorial to put the templates to work together with the scripts and tables?

    Thanks! Much thanks!
    "Don't sweat the small stuff"

  6. #6
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, this is more or less an example I guess and yes you definitely need to use variables.

    Lets say you have a simple page where the user would choose a city...
    Code:
     
    <html>
    <head>
    </head>
    <body>
    Please choose your city:<br>
    <form method="post" action="city.php">
    <select name="city">
    <option value="New York">New York</option>
    <option value="Seattle">Seattle</option>
    <option value="Detroit">Detroit</option>
    </select>
    <input type="submit" value="Submit">
    </form>
    </body>
    </html>
    Then city.php would generate the dynamic content depending on the city that was chosen. So on city.php you could do something like...
    PHP Code:
    <?php
    $city 
    $_POST['city'];
    // display events for this city
    $sql "SELECT * FROM Events_table WHERE city_name = '$city'";
    $result mysql_query($sql);
    if (!
    $result) { 
    echo(
    "The database might be down. Please try again" ); 
    exit; 
    }
    ?>
    <html>
    <head>
    </head>
    <body>
    Events for <?=$city?>:<br>
    <table>
    <tr>
    <td>Name of Event</td><td>Date</td><td>Time</td><td>Description</td>
    </tr>
    <?php

    while ($data mysql_fetch_array($result)) {
    $event_name $data["event_name"];
    $event_date $data["event_date"]; 
    $event_time $data["event_time"]; 
    $event_desc $data["event_description"]; 
    echo(
    "<tr>" );
    echo(
    "<td>$event_name</td><td>$event_date</td><td>$event_time</td><td>$event_desc</td>" );
    echo(
    "</tr>" );
    }
    ?>
    </table>
    </body>
    </html>
    You can add the content of the other tables by doing something like above.
    Last edited by Toly; May 21, 2003 at 08:19.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  7. #7
    SitePoint Zealot Nu1's Avatar
    Join Date
    Jul 2000
    Location
    California
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dude! You rock!

    That definitely works for me. Here is something I am also interested in.. Where do I include the usual script that logs on to the database? does it go into the city.php file?

    Thanks!
    "Don't sweat the small stuff"

  8. #8
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That definitely works for me. Here is something I am also interested in.. Where do I include the usual script that logs on to the database? does it go into the city.php file?
    Sorry, I forgot about that. If you know don't this yet, you can create a file say db_login.php and put your database login details there. Then upload this file to your server outside the public_html folder and include it in your city.php for instance...
    PHP Code:
    <?php
    // db login details
    include(db_login.php);
    $city $_POST['city'];
    // display events for this city
    $sql "SELECT * FROM Events_table WHERE city_name = '$city'";
    $result mysql_query($sql);
    if (!
    $result) { 
    echo(
    "The database might be down. Please try again" ); 
    exit; 
    }
    ?>
    <html>
    <head>
    </head>
    <body>
    Events for <?=$city?>:<br>
    <table>
    <tr>
    <td>Name of Event</td><td>Date</td><td>Time</td><td>Description</td>
    </tr>
    <?php
    while ($data mysql_fetch_array($result)) {
    $event_name $data["event_name"];
    $event_date $data["event_date"]; 
    $event_time $data["event_time"]; 
    $event_desc $data["event_description"]; 
    echo(
    "<tr>" );
    echo(
    "<td>$event_name</td><td>$event_date</td><td>$event_time</td><td>$event_desc</td>" );
    echo(
    "</tr>" );
    }
    ?>
    </table>
    </body>
    </html>
    That should do it.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  9. #9
    SitePoint Zealot Nu1's Avatar
    Join Date
    Jul 2000
    Location
    California
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay so here is the deal...

    in the events_table
    I want to also include a field from the city_table called:

    city_name but do not know how to set it up in MySQL
    I am trying this:

    city_name varchar(30) unsigned notnull default auto_increment unique

    what do you think I am doing wrong?

    Thanks!
    "Don't sweat the small stuff"

  10. #10
    SitePoint Zealot Nu1's Avatar
    Join Date
    Jul 2000
    Location
    California
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dude! I keep getting a:
    "The database might be down Please try again" on this one.
    Am I doing this wrong?

    ===================================
    Here is the db_login.php file:

    <?php // database connect
    $dbhost = "localhost";
    $dbuser = "user_name";
    $dbpass = "PASSWORD";
    function dbConnect($db="database_name") {
    global $dbhost, $dbuser, $dbpass;

    $dbcnx = @mysql_connect($dbhost, $dbuser, $dbpass)
    or die("The site database appears to be down.");
    if ($db!="" and !!@mysql_select_db($db))
    die("The site database is unavailable.");

    return $dbcnx;
    }
    ?>
    ====================================

    Also my city.php file looks like this:

    <?php
    // db login details
    include(db_login.php);
    $city = $_POST['city'];
    // display events for this city
    $sql = "SELECT * FROM Events_name WHERE city_name = '$city'";
    $result = mysql_query($sql);
    if (!$result) {
    echo("The database might be down. Please try again" );
    exit;
    }
    ?>
    <html>
    <head>
    </head>
    <body>
    Fun spots for <?=$city?>br>
    <table>
    <tr>
    <td>Name of Event</td><td>Date</td><td>Time</td><td>Description</td>
    </tr>
    <?php
    while ($data = mysql_fetch_array($result)) {
    $event_name = $data["event_name"];
    $event_date = $data["event_date"];
    $event_time = $data["event_time"];
    $event_desc = $data["event_description"];
    echo("<tr>" );
    echo("<td>$event_name</td><td>$event_date</td><td>$event_time</td><td>$event_desc</td>" );
    echo("</tr>" );
    }
    ?>
    </table>
    </body>
    </html>

    What do you think?
    Thanks!!!!
    Last edited by Nu1; May 21, 2003 at 16:01.
    "Don't sweat the small stuff"

  11. #11
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see an error when selecting the info. It seems that you are selecting the info from the colum Events_name rather than the table.

    PHP Code:
    <?php
    // db login details
    include(db_login.php);
    $city $_POST['city'];
    // display events for this city
    $sql "SELECT * FROM Events_name WHERE city_name = '$city'"// it should be SELECT * FROM Events_table
    $result mysql_query($sql);
    if (!
    $result) { 
    echo(
    "The database might be down. Please try again" ); 
    exit; 
    }
    ?>
    So the correct way would be...

    PHP Code:
    <?php
    // db login details
    include(db_login.php);
    $city $_POST['city'];
    // display events for this city
    $sql "SELECT * FROM Events_table WHERE city_name = '$city'"
    $result mysql_query($sql);
    if (!
    $result) { 
    echo(
    "The database might be down. Please try again" ); 
    exit; 
    }
    ?>
    This should work. Let me know.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  12. #12
    SitePoint Zealot Nu1's Avatar
    Join Date
    Jul 2000
    Location
    California
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did see exactly what you saw and made some changes but still no "box of cigars".

    If you notice I change the table name to "Events_tbl" and where event_description used to be down the page more is now "event_details".

    I also had to add double quotes to the include file as I would get an error. Now all I get is "Database might be down" Here is the code.

    <?php
    // db login details
    include("db_login.php"
    );
    $city = $_POST['city'
    ];
    // display events for this city
    $sql = "SELECT * FROM Events_tbl WHERE city_name = '$city'"
    ;
    $result = mysql_query($sql
    );
    if (!
    $result
    ) {
    echo(
    "The database might be down. Please try again"
    );
    exit;
    }
    ?>
    <html>
    <head>
    </head>
    <body>
    Events for <?=$city?>br>
    <table>
    <tr>
    <td>Name of Event</td><td>Date</td><td>Time</td><td>Description</td>
    </tr>
    <?php
    while ($data = mysql_fetch_array($result
    )) {
    $event_name = $data["event_name"
    ];
    $event_date = $data["event_date"
    ];
    $event_time = $data["event_time"
    ];
    $event_desc = $data["event_details"
    ];
    echo(
    "<tr>"
    );
    echo(
    "<td>$event_name</td><td>$event_date</td><td>$event_time</td><td>$event_desc</td>"
    );
    echo(
    "</tr>"
    );
    }
    ?>
    </table>
    </body>
    </html>


    THANKS!!!!!
    "Don't sweat the small stuff"

  13. #13
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry about the quotes. I've been missing a lot of stuff lately.

    The code is fine the way it is. Since you are connecting successfully to the database something is wrong while selecting the info from the table. Could you post here the layout table for the Events_table.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  14. #14
    SitePoint Zealot Nu1's Avatar
    Join Date
    Jul 2000
    Location
    California
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No worries man!

    Here is the Events_tbl layout:

    Field Type Attributes Null Default Extra Action
    event_id int(10) UNSIGNED Not null auto_increment Primary
    event_name varchar(40) Not null
    event_address varchar(40) Not null
    city_name varchar(20) Not null
    event_date date Not null 2000-00-00
    event_time time Not null 00:00:00
    event_details varchar(64) Not null

    Here is the city_table:

    Field Type Attributes Null Default Extra Action
    city_id int(10) UNSIGNED No auto_increment Primary
    city_name varchar(30) No
    state_id int(2) UNSIGNED No 0

    State_tbl:

    Field Type Attributes Null Default Extra Action
    state_id int(10) UNSIGNED No auto_increment Primary
    state_name varchar(30) No
    state_initial char(2) No
    Last edited by Nu1; May 22, 2003 at 08:47.
    "Don't sweat the small stuff"

  15. #15
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I tried the script on my server just to make sure and everything worked fine. I really don't know what to say other than check that name of your table on the script is exactly as the one you have created through your phpmyadmin or SSH or whatever. I'm sure you have already done that but still.

    If you want you could create a sub folder in your server so I could ftp into it and test the script.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine


Bookmarks

Posting Permissions

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