SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Apr 2007
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Display logged in users data and not every other users data

    hi
    i have a booking form online for bands to book a studio for rehearsing or recording. (php and mysql)
    Once they submit the form, they receive a summary email with an activation link.
    When they click on the activation link, it goes to a login area where they can login to see if their booking has been activated. no probs.
    They can log in at a later date and check to see if management has confirmed the booking.

    The database table "bookings" contains the usual columns "names, phone, email , dates, time wanted etc.," and an activation column and a confirmation column.
    The confirmation column is boolean, so when I confirm the booking it will display the number 1 in a display table (and an array of other stuff associated with the booking that I want to display to them).

    My problem is that when the user is logged in, the persons booking data is displayed with every other users data.

    For example if I log into my bank website i dont see everyone elses savings accounts.

    How do i code the mysql command to display the current logged in user and NOT the other users data????



    the activation page with most of the coding:

    PHP Code:
    <?php 
    include 'includes/connect.inc.php';

    $id $_GET['id'];

    $query "SELECT * FROM bookings WHERE activated='1' AND confirmation='0' ";
    $result mysql_query($query) or die("Query failed robbo:".mysql_error());

    $qProfile "SELECT * FROM bookings WHERE id='$id' ";
    $rsProfile mysql_query($qProfile);
    $row mysql_fetch_array($rsProfile);
    extract($row);
    $username stripslashes($username);
    $day_contact stripslashes($day_contact);
    $date_contact stripslashes($date_contact);
    $month_contact stripslashes($month_contact);
    $year_contact stripslashes($year_contact);
    $start_contact stripslashes($start_contact);
    $hour_contact stripslashes($hour_contact);


    echo 
    "<form method=\"post\" action=\"layout\" >\r\n"
    echo 
    "<fieldset>\r\n";
    echo 
    "<legend>Activated Bookings List</legend>\r\n";
    echo 
    "<br />\r\n";
    echo 
    "<p>Welcome "$_SESSION['first_name'] ." of "$_SESSION['band_name'] ."!<br />\r\n";
    echo 
    "This page Only displays Activated Bookings. <br />\r\n";
    echo 
    "Username: "$_SESSION['username'] ."<br />Day: "$_SESSION['day_contact'] ."<br />Date: "$_SESSION['date_contact'] ." "$_SESSION['month_contact'] ." "$_SESSION['year_contact'] ."<br />Starting at: "$_SESSION['start_contact'] ." for "$_SESSION['hour_contact'] ." <br /><br />Please check information above with table below and verify if necessary with management.</p>\r\n";

    echo 
    "<table class=\"rates\" summary=\"layout\" >\r\n";
    echo 
    "<caption>Activated Bookings Database Table</caption>\r\n";
    echo 
    "<thead>\r\n";
    echo 
    "<tr>\r\n";
    echo 
    "<td scope=\"col\" colspan=\"6\"  class=\"result\" >Database List of Activated Only Bookings</td>\r\n";
    echo 
    "</tr>\r\n";
    echo 
    "</thead>\r\n";
    echo 
    "<tbody>\r\n";
    echo 
    "<tr>\r\n";
    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >Day</td>\r\n";
    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >Date / Month / Year </td>\r\n";
    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >Start Time</td>\r\n";
    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >Username</td>\r\n";
    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >Booking Activated <br />[ Yes = 1 ] [ No = 0 ]</td>\r\n";
    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >Confirmed by Management [ Yes = 1 ] [ No = 0 ]</td>\r\n";
    echo 
    "</tr>\r\n";
    while(
    $line mysql_fetch_array($result,MYSQL_ASSOC)){
    echo 
    "<tr>\r\n";

    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >".$line['day_contact']."</td>\r\n";
    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >".$line['date_contact']." / ".$line['month_contact']." / ".$line['year_contact']." </td>\r\n";
    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >".$line['start_contact']." for ".$line['hour_contact']."</td>\r\n";
    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >".$line['username']."</td>\r\n";
    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >".$line['activated']."</td>\r\n";
    echo 
    "<td scope=\"row\" class=\"wbordgr9l\" >".$line['confirmation']."</td>\r\n";

    echo 
    "</tr>\r\n";
    }
    echo 
    "</tbody>\r\n";
    echo 
    "</table>\r\n";
    echo 
    "</fieldset>\r\n";
    echo 
    "</form>\r\n";

    mysql_free_result($result);
    mysql_close();?>

    The above script works great if ya want to show everyones data!
    Any help greatly appreciated!

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    You've forgotten the third part of the Database Creation Trinity.

    1: Identify Entities (User, Picture, Comment, etc) [These will be your main tables]
    2: Identify Properties of the Entities (User's Name, Email, etc) [These will be fields in your tables]
    3: Identify Relationships Between Entities (A Comment is posted by a User) [These will be Foreign Keys and/or Join Tables]

    How is your bookings table related to your user table?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Member
    Join Date
    Apr 2007
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, just letting you know that i'm not qualified or have studied Mysql..... so i don't know how to identify relationships etc.,

    (with the following info i think i should have not called or named my database "users"...lol)
    In my ISP's control panel...i click the database folder....its shows 1 database which is called users..after clicking users...its shows "Users for database users on domain example.com"....1 database user called "robbo".
    I then click the Webadmin button....which displays my bookings table ( /localhost/users/bookings |phpMyAdmin 2.11.9.6 ).

    When clients book online, all text inputs (required fields), the username, email, dates, times etc., are altogether in the one form.... so when the form is submitted, all these details are listed in the table bookings.
    I dont have a separate user table so to speak, so therefore I cant relate it to the bookings table.

    Q. Do i need to create a user table?... (and then relate it somehow??..(which i dont know how to do)).

    I done some researching last night and came up with the following command, which still needs implementing and tesing in my webpage.

    in the activation webpage.....
    SELECT user_name, day_contact, date_contact, month_contact, year_contact, start_contact, hour_contact FROM `bookings` WHERE id='$id' AND activated='1' AND confirmation='0'

    in the confirmation webpage.....
    SELECT user_name, day_contact, date_contact, month_contact, year_contact, start_contact, hour_contact FROM `bookings` WHERE id='$id' AND activated='1' AND confirmation='1'

    Ok ... so i'm going to try this now and get back to this thread later....
    cheers

  4. #4
    SitePoint Member
    Join Date
    Apr 2007
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, after seeing a sitepoint thread with this link in it http://www.databaseanswers.org/data_...gigs/index.htm
    and this one http://www.databaseanswers.org/tutor...al_slide_5.htm explains heaps.

    My new data model wont be as comphrensive as the one displayed in the link, but i see what ya are saying now.
    Primary keys and foreign keys and how they relate to each other.

    After i posted this thread, i ran into a friend the other day who is a database guru and he is going to help us out.
    Will be doing a complete overhaul of me old database n tables.

    Thanks to everyone that replied to my thread and were willing to help me out. cheers!!

    Once my new data model etc., is complete, will post the new code up to help newbie database designers like myself and hopefully get the answers that they seek.
    cheers
    have a great day!

    P.S. tried the select statements above in my 2nd post and obviously they didn't work...also user_name was meant to be username.

  5. #5
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    As pointed out by StarLion for a database to be effective (and therefore efficient) and to just plain work for the modifications that your client will throw at you over the coming months years it has to be optimised. Data should NEVER be repeated in a database, enter the data once and then piece that info together by joining tables and using condition statements to limit the results to what you need.


    Your problem with putting everything in one table is that you cant use the database to retrieve parts of information or to easily link data together and several pieces of data (ie the band details) are being repeated. In your scenario you should have at least 3 tables


    Table_1 Band_t
    Band_ID (this will be a numeric field that auto increments so every band has a different ID)
    Name
    Address
    Contact
    ..... ie all the info about the band

    Table_2 bookings_t
    This table wil be a diary of your bookings
    Booking_ID (Auto incremented same as for the bands ID, each booking has a unique ID)
    Date
    Start_time
    Duration
    Price
    Confirmed
    ..... ie all details specific to the booking

    Table_3 Band_Booking_t
    Band_ID
    Booking_ID


    You'll appreciate that this is done without a lot of thought on my part, and that you may require even more tables to accomodate other features of your site, but at least your getting the drift (hopefully).

    So a band only has to enter their details once and these are stored in the Band_t

    When they come to make a booking the details of the booking are entered in the booking_t and a reference entry linking the booking to the band made in the cross reference table. Now a band can make multiple bookings without reentering all the band info each time and you can retrieve all bookings for one band only. and of course you can do useful things like find all the bands contact details that are booked for the coming week/month/day whatever.

    Hope that helps a bit

  6. #6
    SitePoint Member
    Join Date
    Apr 2007
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks heaps Mandes, i appreciate your time and explaining things clearly....and yes, i'm slowly getting the drift...lol....cheers......i'll be back next week sometime with latest updates etc.,..have a great day!


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
  •