Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Website Development PowerPoint Presentation
Download Presentation
Website Development

Website Development

120 Views Download Presentation
Download Presentation

Website Development

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Website Development Database Issues

  2. browser web server scripting database : Customer language request service access page interpret set data get data get data return html present html Reminder of the general process

  3. The Guestbook Database is a Bit Weedy

  4. CREATE TABLE forum_topics ( topic_id int NOT NULL AUTO_INCREMENT, PRIMARY KEY(topic_id), topic_title varchar(150), topic_create_time datetime, topic_owner varchar (150) ); CREATE TABLE forum_posts ( post_id int NOT NULL AUTO_INCREMENT, PRIMARY KEY(post_id), topic_id int not null, post_text text, post_create_time datetime, post_owner varchar (150) ); Two Tables

  5. Remember this?

  6. The Northwind Database • Would be a very good database for an example e:commerce application • Better if it were implemented in MySQL • We can “reverse engineer” an entity model from the database using Visio.

  7. Forward engineering the model into MySQL

  8. Let’s get some data in those tables and so on…..

  9. How can we get from that to… • A working online ordering system • An administrative system for the online ordering system

  10. “Skeleton” Functionality Registration of users Logging in Admin and normal rights Adding products Navigation Adding to shopping basket Ordering Viewing users Viewing orders Viewing baskets

  11. Registering register.htm <FORM METHOD="POST" ACTION="saveUser.php"> <P><b>Registration</b></P> <TABLE> <TR> <TD ALIGN="right"><em>User Name</em></TD> <TD><INPUT TYPE=TEXT NAME="UserName" SIZE=16 MAXLENGTH=16></TD> </TR> <TR> <TD ALIGN="right"><EM>Password</EM></TD> <TD><INPUT TYPE=PASSWORD NAME="Password" SIZE=16 MAXLENGTH=16></TD> </TR> </TABLE> <INPUT TYPE=SUBMIT VALUE="Submit Form"> <INPUT TYPE=RESET VALUE="Reset Form"> </FORM>

  12. What do we need to do now? • Check to see if anyone has used that password already. • If not add the new user to our table of registered users. • This is dealt with in saveUser.php

  13. Adding user record saveUser.php $query = "SELECT PASSWORD FROM $table_users WHERE USERNAME = \"$UserName\""; $result=mysql_query($query); if (mysql_num_rows($result) > 1) { echo("Someone has already used that username<br>"); include('register.htm'); exit(); }; $query = "INSERT INTO $table_users SET USERNAME = \"$UserName\", PASSWORD = \"$Password\""; $result=mysql_query($query); if ($result) { include('loginForm.htm'); };

  14. What next? • Once a user has registered we get them to log in using the standard login form. • This is loginForm.htm

  15. Logging in loginForm.htm <FORM METHOD="POST" ACTION="login.php"> <P><b>Log in</b></P> <TABLE> <TR> <TD ALIGN="right"><em>User Name</em></TD> <TD><INPUT TYPE=TEXT NAME="UserName" SIZE=16 MAXLENGTH=16></TD> </TR> <TR> <TD ALIGN="right"><EM>Password</EM></TD> <TD><INPUT TYPE=PASSWORD NAME="Password" SIZE=16 MAXLENGTH=16></TD> </TR> </TABLE> <INPUT TYPE=SUBMIT VALUE="Submit Form"> <INPUT TYPE=RESET VALUE="Reset Form"> </FORM>

  16. What next? • This is the standard login form for registered users • We now need to find the username in our database and check that the password matches the one we have on record. • This is done in loginCheck.php • Save the username and password in a cookie so we can remember it for future checks that the user has logged in

  17. login.php Logging in <?php setcookie("User", $UserName); ?> <?php setcookie("Pword", $Password); ?> <html> <head></head> <body> <p> <?php $User=$UserName; $Pword = $Password; include('loginCheck.php'); include('welcome.php'); ?> </body> </html>

  18. Checking the log in loginCheck.php $query = "SELECT PASSWORD FROM $table_users WHERE USERNAME = \"$User\""; $result=mysql_query($query); if (mysql_num_rows($result) == 0) { echo("Not registered<br>"); include('register.htm'); exit(); }; if (mysql_num_rows($result) > 1) { echo("Multiple registration - contact the web master<br>"); exit(); }; $row = mysql_fetch_row($result); if ($row[0] <> $Pword) { echo("Wrong Password"); include('loginForm.htm'); exit(); };

  19. What next? • Now the user can do stuff • Add comments to your Guestbook • Buy things from Northwind etc.

  20. Page Structures Most of the php pages we have created have had the following structure. • Connect to the database • Create some kind of query • Execute the query • Display the result.

  21. General format of pages for an e:commerce application: Check that the user is logged in and valid Provide the page, pushing code into hidden functions where possible Display navigation

  22. Choosing products chooseProduct.php <b>Order form</b> <p> <?php include('loginCheck.php'); ?> <?php chooser(); include('navigation.php'); ?></p>

  23. showBasket.php Showing the basket <b>Shopping Basket</b> <?php include('loginCheck.php'); showBasket(); include('navigation.php'); ?>

  24. submitOrder.php Submitting an order - example of detailed code <?php include('loginCheck.php'); $dbcnx = myConnect(); $query = "SELECT $table_basket.PROD_ID, $table_basket.CUST_ID, QUANTITY FROM $table_basket, $table_users WHERE $table_basket.CUST_ID = $table_users.CUST_ID AND $table_users.USERNAME = \"$User\""; $result=mysql_query($query); if (! $result) printf ("Error: %s<br>", mysql_error ()); for ($i = 0; $i < mysql_num_rows ($result); $i++) { $row = mysql_fetch_row($result); $cust_id = $row[1]; $query = "INSERT INTO $table_order SET PROD_ID =".$row[0]. ", QUANTITY = ".$row[2].", CUST_ID = ".$row[1].", DAY = ".date("Ymd"); $result2=mysql_query($query); if (! $result2) printf ("Error: %s<br>", mysql_error ()); }; if (mysql_num_rows ($result)>0) { $query = "DELETE FROM $table_basket WHERE $table_basket.CUST_ID = $cust_id"; $result=mysql_query($query); if (! $result) printf ("Error: %s<br>", mysql_error ()); }; echo("ORDER SUBMITTED"); include('navigation.php'); ?>

  25. prodEdit.php Editing the product table <?php include('loginCheck.php'); ?> <?php viewTable($table_products, ""); addToTable($table_products,"prodEdit.php"); include('navigation.php'); ?>

  26. Navigation and selective functionality navigation.php <table border=1> <td><a href="chooseProduct.php">Choose Product </a> <td><a href="showBasket.php">Show Basket </a> <td><a href="submitOrder.php">Submit Order</a> <td><?php if ($User == "ADMIN") { ?> <td><a href="prodEdit.php">Add Product </a> <td><a href="allBaskets.php">All Baskets </a> <td><a href="allUsers.php”>All Users </a> <td><a href="allOrders.php">All Orders </a> <td><a href="dbCREATE.php">Zap Database</a><?php }; ?> <td><a href="showOrders.php">Show Orders</a> <td><a href="loginForm.htm">login</a> </table>

  27. What’s Going On? • We are beginning to develop a generic framework of php code for an e:commerce application • You could “flesh out” this framework to work with a specific database • Provided you are not frightened of php code!