23 Replies Latest reply: Mar 31, 2013 2:17 AM by osgood_ RSS

    Question about databases in dreamweaver cs6

    IVYY Community Member

      Hi all,

       

      I need to clarify that I am very new to phpmyadmin, mysql, and databases. This is what i am stucked with.

       

      Currently, I have one table (book) which contains these attributes (ISBN(primary key), title, author, publisher, price etc) in my database (book)

       

      I have the other table (users) which contains these attributes (username (primary key), password, name, DOB etc) in my database (user)

       

      Let me explain the series of steps first. An user logs in on the log in page successfully, and he will be directed to the main page, then he will type or select his search terms, then the results page will display the relevant results in the master list, then he will select the specific result to go to the detailed page containing more information.

       

      What I want now is to create a reservation page. If the user likes the specific book in the detailed page, he can click reserve it, and will be directed to the reservation page.

       

      I intend to have these search textfields: Username, Title, Price, Number of orders.

       

      How can I let the username textfield to be automatically filled as the user has logged in at the login page initially. Also, how can I let the title and price textfield to be automatically filled, as the user has clicked reserve it at the specific detailed page? I intend that the user just key in number of orders and press submit button. My 3rd database (reservation) will capture the info of username, title, price, and number of orders. Do i use insert record or update record?

       

      Do i create a relational table in phpmyadmin by linking it with the keys from tables (book and users)? Will this be a many to many relationship? I intend my third table to have these attributes (reservation no (primary key), username, title, price, no, of orders and date of collection?

       

      Moreover, how do I auto generate the date of collection (for e.g. it will be 5 days after the point of time when the user click the submit button) in my third table ?

       

      Thanks!

        • 1. Re: Question about databases in dreamweaver cs6
          osgood_ MVP

          You need to use php sessions which pass information from page to page.

           

          The books details page gets the following information from the database - Title, Price

           

          To use sessions you must have the below at the very top of each page you want to pass the information onto.

           

          <?php

          session_start();

          ?>

           

          Create session variables for each piece of information: (replace rsRecordSetName) with your record set name

           

          <?php

          $_SESSION['Title'] = $row_rsRecordSetName['Title'];

          $_SESSION['Price'] = $row_rsRecordSetName['Price'];

          ?>

           

           

           

          In the form value field for Title insert:

           

          <?php if(isset($_SESSION['Title'])) { echo $_SESSION['Title']; }?>

           

           

          In the form value field for Price insert:

           

          <?php if(isset($_SESSION['Price'])) { echo $_SESSION['Price']; }?>

          • 2. Re: Question about databases in dreamweaver cs6
            osgood_ MVP

            IVYY wrote:

             

             

             

            Moreover, how do I auto generate the date of collection (for e.g. it will be 5 days after the point of time when the user click the submit button) in my third table ?

             

             

             

            Not sure about that one UNLESS there is a ready made php command that can do that. It would have to take into consideration the current date - echo date('d F Y') - and add 5 days to that.

             

            ------------Edit------------

             

            There is indeed a function that can do this:

             

            <?php

            $collectionDate = date('d-m-Y', strtotime("+5 days"));

            ?>

             

            <?php echo $collectionDate; ?>

             

             

            So you would store the $collectionDate in your database.

            • 3. Re: Question about databases in dreamweaver cs6
              IVYY Community Member

              Hi osgood_,

               

               

              To clarify, do i put it at the beginning of the reservation page?

               

              <?php

              session_start();

              ?>

              <?php

              $_SESSION['Title'] = $row_DetailRS1['Title'];

              $_SESSION['Price'] = $row_DetailRS1['Price (S$)'];

              ?>

               

              Do I also need to create a recordset in my reservation page?

               

              Then for the below part, do i insert into the inital value of the form object search textfield?

               

              <?php if(isset($_SESSION['Title'])) { echo $_SESSION['Title']; }?>

               

              <?php if(isset($_SESSION['Price'])) { echo $_SESSION['Price']; }?>

               

              Thanks!

              • 4. Re: Question about databases in dreamweaver cs6
                IVYY Community Member

                Hi osgood,

                 

                I also would like to ask do i need to copy the recordset from the detailed page and paste it on the reservation page to make the session variables work.

                 

                Thanks!

                • 5. Re: Question about databases in dreamweaver cs6
                  osgood_ MVP

                  IVYY wrote:

                   

                  Hi osgood_,

                   

                   

                  To clarify, do i put it at the beginning of the reservation page?

                   

                  <?php

                  session_start();

                  ?>

                  <?php

                  $_SESSION['Title'] = $row_DetailRS1['Title'];

                  $_SESSION['Price'] = $row_DetailRS1['Price (S$)'];

                  ?>

                   

                   

                  Yes, that's correct.

                   

                  The $_SESSION variables should be passed from page to page without any need to create a recordset to get them (obviously you WILL need to create a record set on the Reservations page eventually so you can input the information from the form into your database)

                   

                  What is happening when you get to the reservations page, is the information NOT being written into your form fields?

                  • 6. Re: Question about databases in dreamweaver cs6
                    osgood_ MVP

                    IVYY wrote:

                     

                    Hi osgood,

                     

                    I also would like to ask do i need to copy the recordset from the detailed page and paste it on the reservation page to make the session variables work.

                     

                    Thanks!

                     

                    No, read my previous post.

                    • 7. Re: Question about databases in dreamweaver cs6
                      IVYY Community Member

                      Hi osgood,

                       

                      To clariy,

                       

                      <?php

                      session_start();

                      ?>

                      <?php

                      $_SESSION['Title'] = $row_DetailRS1['Title'];

                      $_SESSION['Price'] = $row_DetailRS1['Price (S$)'];

                      ?>

                       

                      The session variable of title in the Reservation Page is captured from the title column of the Detailed Record Set in the previous Record Set page. Am I correct to say that?

                      • 8. Re: Question about databases in dreamweaver cs6
                        osgood_ MVP

                        IVYY wrote:

                         

                        Hi osgood,

                         

                        To clariy,

                         

                        <?php

                        session_start();

                        ?>

                        <?php

                        $_SESSION['Title'] = $row_DetailRS1['Title'];

                        $_SESSION['Price'] = $row_DetailRS1['Price (S$)'];

                        ?>

                         

                        The session variable of title in the Reservation Page is captured from the title column of the Detailed Record Set in the previous Record Set page. Am I correct to say that?

                         

                        Yes.

                        • 9. Re: Question about databases in dreamweaver cs6
                          IVYY Community Member

                          Hi osgood,

                           

                          I would like to ask again as my reservation displayed nothing. I have created a master detail page set. The detailed page contains a table with results from the database, it does not have any search textfields. Am I still able to use session variables to pass the information to my reservation page? May I paste the source code for u to help me check? Thanks!

                          • 10. Re: Question about databases in dreamweaver cs6
                            osgood_ MVP

                            IVYY wrote:

                             

                            Hi osgood,

                             

                            I would like to ask again as my reservation displayed nothing. I have created a master detail page set. The detailed page contains a table with results from the database, it does not have any search textfields.

                             

                             

                            Thats sounds ok.

                             

                             

                            IVYY wrote:

                             

                             

                             

                            Am I still able to use session variables to pass the information to my reservation page? May I paste the source code for u to help me check? Thanks!

                             

                            Yes, do you have <?php session_start(); ?> at the top of the 'details' page?

                             

                            Make sure the

                             

                            $_SESSION['Title'] = $row_DetailRS1['Title'];

                            $_SESSION['Price'] = $row_DetailRS1['Price (S$)'];

                             

                            come after the mysql statement which requests the book information for your details page. If the variables come before it then the informatiom won't be picked up as the mysql statement will be executed afterwards.

                             

                            It doesnt matter where the S_SESSION variables are on the reservations page.

                            • 11. Re: Question about databases in dreamweaver cs6
                              IVYY Community Member

                              Hi osgood,

                               

                              I finally get what you mean. I did it successfully for passing the info of title and price. Thanks!

                               

                              <?php session_start(); ?> is to be placed at the 1st line of the details page, while

                               

                              <?php

                              $_SESSION['Title'] = $row_DetailRS1['Title'];

                              $_SESSION['Price (S$)'] = $row_DetailRS1['Price (S$)'];

                              ?>

                               

                              is to be placed after the <body> tag of the details page, and then

                              <?php if(isset($_SESSION['Title'])) { echo $_SESSION['Title']; }?>

                               

                              <?php if(isset($_SESSION['Price'])) { echo $_SESSION['Price']; }?>

                              is to be placed in the values of the search textfield of the reservation page.

                               

                              I still have some queries left. Then how can i auto generate the user's email and name in the search text fields of email and name in the reservation page?

                               

                              The user logs into the login page (1st page) and has to go through 4 pages to get to the reservation page.

                               

                              Can I still use session variable? May you advise me on this? Thank you!

                              • 12. Re: Question about databases in dreamweaver cs6
                                osgood_ MVP

                                IVYY wrote:

                                 

                                I still have some queries left. Then how can i auto generate the user's email and name in the search text fields of email and name in the reservation page?

                                 

                                The user logs into the login page (1st page) and has to go through 4 pages to get to the reservation page.

                                 

                                Can I still use session variable? May you advise me on this? Thank you!

                                 

                                Sure you can use $_SESSION variables and pass them to as many pages as you like.

                                 

                                Get the username and email from the database if that where they are stored.

                                 

                                I'm assuming you're probably using the user authentication which ships with Dreamweaver to get the username from the 'user' database table?

                                 

                                I'm not really familar with it but you would do the same thing:

                                 

                                $_SESSION['username'] = $loginUsername (I don't know how Deamweaver handles getting the user so you would have to look in the code to see what is happening).

                                 

                                I think Dreamweaver attributes the username (if its authenticated) to a variable named $loginUsername

                                • 13. Re: Question about databases in dreamweaver cs6
                                  IVYY Community Member

                                  Hi osgood,

                                   

                                  Thanks for your guidance, I also figured out how to set the session variables of emails and users. I found out that the session variable MM_Username can only be used for the page after the login page. So i created a record set and set the email to be equal to the session variable MM_Username. Then I created more session variables Email and Full Name to be used across 4 webpages.

                                   

                                  But I still have a last question.

                                   

                                  Now I have my Title, Price, Full Name, Email textfields all auto filled by session variables, I also have a no. of orders for the users to enter and press submit. So I only have Title, price, full name, email and no. of orders textfield in my reservation page.

                                   

                                  My third table intends to have these attributes: Reservation Numner, Title, Price, Full Name, Email, and Date of Collection. My reservation page doesnt have the reservation and date of collection textfields. I intend to pull out these 2 data after the user press submit.

                                   

                                  May I know what is my primary key for now? Do I use update or insert record? Correct my attributes or textfields if I am wrong. How do I go about doing this now?

                                   

                                  Thanks for all the help!

                                  • 14. Re: Question about databases in dreamweaver cs6
                                    osgood_ MVP

                                    IVYY wrote:

                                     

                                     

                                    But I still have a last question.

                                     

                                    Now I have my Title, Price, Full Name, Email textfields all auto filled by session variables, I also have a no. of orders for the users to enter and press submit. So I only have Title, price, full name, email and no. of orders textfield in my reservation page.

                                     

                                    My third table intends to have these attributes: Reservation Numner, Title, Price, Full Name, Email, and Date of Collection. My reservation page doesnt have the reservation and date of collection textfields. I intend to pull out these 2 data after the user press submit.

                                     

                                    May I know what is my primary key for now? Do I use update or insert record? Correct my attributes or textfields if I am wrong. How do I go about doing this now?

                                     

                                    I'm not quite following;

                                     

                                    So now you have Title, Price, Full Name and Email values which you can get from the Reservations page form. What are you intending to do with it? Insert that into a table in your database?

                                     

                                    Also what are you planning to do with the Reservation Number and Date of Collection?

                                     

                                    As previously mentioned you can add the Collection Date to the reservations page if you want and pass it along with the rest of the data via a hidden from field if you need to.

                                     

                                    <?php

                                    $collectionDate = date('d-m-Y', strtotime("+5 days"));

                                    ?>

                                     

                                    Just add value="<?php echo $collectionDate; ?>" to the hidden form field.

                                    • 15. Re: Question about databases in dreamweaver cs6
                                      bregent MVP

                                      >My third table intends to have these attributes: Reservation Numner,

                                      >Title, Price, Full Name, Email, and Date of Collection.

                                       

                                      That's probably not the correct design. The reservation table is an association table that creates the many-to-many relationship between books and users. Typically, you only want to store the primary keys from those tables (isbn and userid) along with other attributes that make the transaction, like date and quantity. You don't want to store full name, email, etc because that would be redundant as those are already included in the user table, and title, etc in the book table

                                       

                                      >May I know what is my primary key for now?

                                       

                                      Primary key could be a compound key conisiting of the reservation id and the isbn.  Or you could create a surrogate key.

                                       

                                      >Do I use update or insert record?

                                       

                                      If you are creating a new transaction (reservation), then you need to insert a row for each book they order. In a real world situation, the design is typically consists of a transaction table to store transaction headers info (tax, totals, shipping, etc), and transaction details to store the individual line item details. Since this is just a school project, I don't know if you need to get that detailed. Transaction headers info could be calcuated from the details.

                                       

                                      >How do I go about doing this now?

                                       

                                      Not sure what you mean?

                                      • 16. Re: Question about databases in dreamweaver cs6
                                        IVYY Community Member

                                        Hi Bregent,

                                         

                                        Thanks for you explanation.

                                         

                                        I get it now that the reservation table is an intersection table between the books table and the users table  which has a many to many relationship between them too.

                                         

                                        The primary key of the books table is their ISBN while the primary key of the user table is their email address.

                                         

                                        I can pass these primary keys from previous pages as session variables to my reservation page.

                                         

                                        I would like to ask how do I compound a key for the primary key of reservation table in dreamweaver? Am I right to say that the reserveation table's primary key is the combination of ISBN and email address?

                                        <?php if(isset($_SESSION['ISBN'])) { echo $_SESSION['ISBN']; }?>

                                         

                                        <?php if(isset($_SESSION['Email'])) { echo $_SESSION['Email']; }?>

                                         

                                        So the reservation table will have these attributes reservation_ID (a primary key which combines the primary keys of ISBN and emails), no. of orders, reservation number, and date of collection.

                                         

                                        May I ask how do I create a relationship between the reservation table and both the user and book tables in phpmyadmin? (for e.g. how do i combine both primary keys in the user and book tables and link to the primary key of the reservation table in phpmyadmin)

                                         

                                        Thanks!

                                        • 17. Re: Question about databases in dreamweaver cs6
                                          IVYY Community Member

                                          Hi osgood,

                                           

                                          I finally get what you mean.

                                           

                                          My third table has these attributes reservation number (auto incremental primary key), ISBN (primary key of the books table), Email (primary key of the user table), number of orders, and date of collection. This is exactly similar to my form objects in my reservation page.

                                           

                                          I set the reservation number and date of collection as hidden and set the ISBN and email as read only. And I can successfully updated my third table in the database.

                                           

                                          I got one query. When i insert the record from my reservation page and get directed next to my reservation confirmation page, how can i immediately show the reservation number and collection date from my third table onto the reservation confirmation page?

                                           

                                          May you advise me. Thanks!

                                          • 18. Re: Question about databases in dreamweaver cs6
                                            osgood_ MVP

                                            IVYY wrote:

                                             

                                            Hi osgood,

                                             

                                            I got one query. When i insert the record from my reservation page and get directed next to my reservation confirmation page, how can i immediately show the reservation number and collection date from my third table onto the reservation confirmation page?

                                             

                                            May you advise me. Thanks!

                                             

                                            You would still use a $_SESSION variable.

                                             

                                            So on the page where you have the hidden 'collection date' form field value="<?php echo $collectionDate; ?>" all you would do is create a $_SESSION variable on that page:

                                             

                                            $_SESSION['collectionDate'] = $collectionDate;

                                             

                                            Then when you get directed to your confirmation page (so long as it has session_start(); at the top you just echo the $_SESSION variable where you want to show it:

                                             

                                            <?php echo $_SESSION['collectionDate']; ?>

                                            • 19. Re: Question about databases in dreamweaver cs6
                                              bregent MVP

                                              >I would like to ask how do I compound a key for the

                                              >primary key of reservation table in dreamweaver?

                                               

                                              >So the reservation table will have these attributes reservation_ID

                                              >(a primary key which combines the primary keys of ISBN and emails)

                                               

                                              First of all, I would rename 'reservation' to 'reservationLine' or simiar, to make it clear that a single reservation can have more than one line item. And to be clear, the composite key is not a single column combining ISBN and email. A composite key means it uses more than 1 column in the table to ensure uniqueness. Also, the correct composite key for this model would be reservationID and ISBN. Email needs to be included in the table, but is not part of the key. Otherwise a user would not be able to place an order for the same book another time. So you can not use an autoincrement field for the reservation_ID because each reservation can have multiple lines.

                                               

                                              So you need to create a reservationID using a different method - you could use a id generating table. You can also have an autoincrement surrogate key, but still need to have the composite alternate key.

                                              • 20. Re: Question about databases in dreamweaver cs6
                                                bregent MVP

                                                >When i insert the record from my reservation page and get directed

                                                >next to my reservation confirmation page, how can i immediately

                                                >show the reservation number and collection date from my third table

                                                >onto the reservation confirmation page?

                                                 

                                                Use mysql_insert_id() to get the last id created during the current connection.

                                                • 21. Re: Question about databases in dreamweaver cs6
                                                  IVYY Community Member

                                                  Hi bregent,

                                                   

                                                  Let me clarify that i am correct to say that I can store the last id created during the current connection in the reservation page as session variable and retrieve it later at my next page which is the reservation confirmation.

                                                   

                                                  May I know where do I place

                                                  <?php

                                                  $_SESSION['last_insert_id'] = mysql_insert_id();

                                                  ?>

                                                   

                                                  Is it before the <head> tag of the reservation page?

                                                   

                                                  THen how do I retrive the last reservation id in the reservation confiration page?

                                                   

                                                  Is it use <?php if(isset($_SESSION['last_insert_id'])) { echo $_SESSION['last_insert_id']; }?>

                                                   

                                                  Thanks!

                                                  • 22. Re: Question about databases in dreamweaver cs6
                                                    IVYY Community Member

                                                    Hi osgood,

                                                     

                                                    Thanks! I have gotten the part on collection date successfully on reservation confirmation. May I ask how do I do it for reservation number? (reservation number is an auto incremental primary key in my third table, and users can submit without keying it?)

                                                     

                                                    bregent has mentioned using mysql_insert_id(). But I am unsure on how to go about doing that.

                                                     

                                                    Do i place

                                                     

                                                    <?php

                                                    $_SESSION['last_insert_id'] = mysql_insert_id();

                                                    ?>

                                                     

                                                    before the <head> tag of the reservation page?

                                                     

                                                    THen use <?php if(isset($_SESSION['last_insert_id'])) { echo $_SESSION['last_insert_id']; }?> to retrive the last reservation id in the reservation confiration page?

                                                     

                                                    Thanks!

                                                    • 23. Re: Question about databases in dreamweaver cs6
                                                      osgood_ MVP

                                                      Good question.

                                                       

                                                      Let me first say that bregent is far far more knowledgable than me on matters of database design. I just try to think on my feet, not having the extensive knowledge that he does, and come up with a solution that I understand and feel comfortable working with. It's not always the most robust or even the correct way but if it works I'm happy and I'm also happy to take advice from those more knowledgable on such subjects and try to expand my own skills by weaving, if not all, some parts of suggestions made by others, into my solutions.

                                                       

                                                      I'm sure there is way to get the last stored auto Reservations Number from the database table using a mysql statement on the Reservations Page. (I'm not sure how - you may have to Google for that or start another thread asking how to retrieve the last stored record information from a database table).

                                                       

                                                      Once you have that you can assign it to a variable say with the name - $lastStoredResNum - then you could do something like:

                                                       

                                                      $lastStoredResNum = $row_rsRecordSetName['reservationNumber'];

                                                       

                                                      $lastStoredResNum = $lastStoredResNum + 1;

                                                       

                                                      So if the last 'Reservation Number' was 65, the above is saying add 1 to it.

                                                       

                                                      You could then write that number to the table in a 'dedicated' coloum named 'reservationNumber' and use that instead of the primary key to keep track of the Reservation Number. Both numbers in the primary column and the 'reservationsNumber' column should always match, i.e. the next auto created primary will be 66 and the number you write to the new column will also be 66.

                                                       

                                                      That's where I would start anyway.