Delicatessens Data Driven Website Project

User Generated

oesrfa

Programming

Description

Unformatted Attachment Preview

158 PHP & MySQL: Novice to Ninja SELECT joketext FROM joke INNER JOIN jokecategory ON joke.id = jokeid INNER JOIN category ON categoryid = category.id WHERE name = "Knock-knock" As you can see, this query uses two joins. First, it takes the joke table and joins it to the jokecategory table; then it takes that joined data and joins it to the category table. As your database structure becomes more complex, multijoin queries like this one become common. The following query lists the categories that contain jokes beginning with “How many lawyers …”: SELECT name FROM joke INNER JOIN jokecategory ON joke.id = jokeid INNER JOIN category ON categoryid = category.id WHERE joketext LIKE "How many lawyers%" And this query—which also makes use of our author table to join together the contents of four tables—lists the names of all authors who have written knock-knock jokes: SELECT author.name FROM joke INNER JOIN author ON authorid = author.id INNER JOIN jokecategory ON joke.id = jokeid INNER JOIN category ON categoryid = category.id WHERE category.name = "Knock-knock" Categories and Joke Search. Matt Prichard – comp 1321 Overview • • • • Confirm delete The category system The joke system… first part Search functionality Confirm delete ? • To prevent accidental deletion. • Give the user a sense of control. • Not essential for the Coursework, but good practice. The theory • When the user clicks delete we load a newtemplate that fetches the row of that author only. • Displays it with a ‘yes’ and ‘no’ button. • User clicks ‘yes’, run the delete block from last week. • User clicks ‘no’, reload the ‘list all authors’ template Changes to controller - user clicks delete This block selects the row of the selected author and passes the data to our ‘confirm delete’ template. This is the code of my confirm_delete.html.php page Display the name of the author, assign the author id to a hidden field. Create 2 buttons named ‘action’ with values ‘yes’ and ‘no’. Form action=“” sends form data back to the controller. The controller – yes or no User click yes – run our delete block from last week User click no – Previous if statement not valid so run delete code Category system • This is essentially the same as the authorsystem. • We are simply providing an interface to add, edit and delete categories. • The files needed and the bulk of the code are identicalto the author system. • The logic is the same. • See if you can work it out? • Duplicate the authors folder and rename it categories Category system cont… • You should work through the files updating variable names, database table names, fields etc… • If you take care and have understood the code from last week this should be a fairly straight forward task. (it took me 10 mins) • Make sure you have set the foreign keys in the database as described last week so that when you delete a category the reference in the jokecategory table goes too. • We don’t want to delete jokes though, as they may belong to more than one category. • Pages 205 - 212 for the category system code. The file structure The 4 new files. Copies of the author files but with the required changes. Not using confirm delete this time The full category system Managing Jokes • This is more complex than authors and categories. • We need to be able to assign categories and authors to our jokes. • We will have many more jokes than categories and authors. • We need a better way of looking through our jokesto avoid a huge long list. • We may know the author or the category or sometext? • We will build a very simple ‘search engine’ to help. What we are making The jokes file structure New folder ’jokes’. Create 3 new files; index.php jokes.html.php searchform.html.php Use our previous error.html.php template Admin/jokes/index.php – our new controller Start a new controller Admin/jokes/index.php Look through this code. We are running 2 select queries and making 2 arrays. One for authors one for categories New concept – dynamic dropdown menus • We will use the arrays from the previous page to dynamically populate some drop down lists. • This is a common procedure in data driven sites. • It means we don’t have to hard code our dropdown menus. • It prevents user input errors as the data is from the database. searchform.html.php Full code. Breakdown in next few slides Page 213/214 searchform.html.php detail Include our helper file for the output Creating an ‘add’ variable to Method ”get” allows user to detect when user intends to bookmark their search result. add new joke. Always use get for searches searchform.html.php detail cont Create an html drop down menu Line 17: loop through the Option value = primary key array from the controller Dropdown text = name from database Repeat this technique for the category drop down. Lines 24-29 searchform.html.php detail cont Create a text box named ‘text’ Assign the value ‘search’ to a submit button named ‘action’ for use later on. The finished form Notice the data in the URL as we are using ‘get’ for our search Warning – tricky concepts ahead Build the search query • This is the last section for this week. • We need to build a dynamic search querythat responds to the users input and selection. • This is quite complex. • We will do it in small stages. The theory • To create our search functionality we need to be able to run various select queries depending on whether the user selects an author, a category, joke text or any combination of the above. • We could hard code the various queries and tryand select between them, or we can use variables to create a dynamic query. Queries we may need If the user selects no options we want to return all the jokes. SELECT * would do this, but we want to add FROM and WHERE clauses as we go. We need a WHERE clause that initially has no effect. WHERE TRUE is always true so will do the job • With nothing selected: • SELECT * FROM joke • SELECT id, joketext FROM joke WHERE ?? • SELECT id, joketext FROM joke WHERE TRUE Queries we may need – author selected If the user selects an author we need to add this to the WHERE clause SELECT id, joketext FROM joke WHERE TRUE AND authorid = :authorid The ‘:authorid’ will come from the selected option of the author dropdown menu named ‘author’. Queries we may need – category selected If the user selects a category we need to add this to the query. SELECT id, joketext FROM joke INNER JOIN jokecategory ON id = jokeid WHERE TRUE AND categoryid = :categoryid The ‘:categoryid’ will come from the selected option of the category dropdown menu named ‘category’. Queries we may need – joke text entered If the user enters text into the text box we need to deal with that too. SELECT id, joketext FROM joke WHERE TRUE AND joketext LIKE '%:joketext%’ The ‘:joketext’ will come from the text box named ‘text’ Queries we may need – author and text If the user enters text into the text box and selects an author we need the following SELECT id, joketext FROM joke WHERE TRUE AND authorid = 2 AND joketext LIKE '%knock%’ • We need to cover all the other permutations too. Begin the search query NB. this code goes before the other code in the index so far. Check if ‘action’ isset and contains ‘search’, include our DB Connection. Begin the search query We are assigning some strings to variables that when joined together form our basic query that we have seenin the previous slides. $select . $from . $where would be; SELECT id, joketext FROM joke WHERE TRUE Create a variable $placeholder and assign an array toit. Next stage • Now we need to check each of the possible constraints (author, category and search text). • If they have been submitted with the formwe must append them to our SQL query. • First the author check. An author is selected If the author value in the $_GET array is not empty an author has been selected so we must adjust our query The append operator .= is used to add a new string to an existing string. We are adding to the WHERE clause. Also we are storing our placeholder for the query in the $placeholder array with the placeholder name as the array index A category is selected If the category value in the $_GET array is not empty a category has been selected so we must adjust our query The append operator .= is used to add a new string to the FROM and WHERE clauses (note the spaces at the start of the strings). Again we are storing our placeholder for the query in the $placeholder array. Some search text is entered. If the text value in the $_GET array is not empty some text has been entered so we must adjust our query. The append operator .= is used to add a new string to the WHERE clause (note the spaces at the start of the string). Again we are storing our placeholder for the query in the $placeholder array. We are also storing the % wildcard delineators – be very careful of the syntax here. Because we have the values of all our placeholders stored in a PHP array variable ($placeholders) the execute method lets us supply an array containing the values that we want to assign to the placeholders in our prepared statement, rather than having to use bindValue separately for each one. The full code for the search query block. This cover slides 29-35 and pages 215-218 in the book. This comes BEFORE The search form code from earlier although it is in the same index.php file Lastly we need the jokes.html.php template on line 51, this is slightly different to our previous joke template so start a new one Joke.html.php – study this codeyourself Questions ? Test Plan and Test Log A computer system is tested to make sure that it performs every task that it was intended to, correctly and completely. A list of aims for the system is drawn up at the specification stage. These aims detail what your system is supposed to do and how. During the testing phase, you run the system with test data to check that it: • • • • • • Meets each aim accurately Rejects erroneous data that could produce incorrect or silly results Displays adequate prompts to guide the user Displays all output fully and clearly All data capture forms include all the information required in a clear manner and correspond to screen input All outputs display all information in a concise and clear way with suitable headings, etc. Each test on your test plan should be numbered. When you run your system with test data to see the results of that test you must obtain either output or screenshots that show the results of testing. Number these to correspond to the test question. Annotate each one to state whether any changes to this part of the system are necessary, what you plan to do and why. Your Test Plan (first 5 columns) and Test Log (columns 6-8) could look something like this: What is Test being Number tested 1 2 3 4 How Test data Expected used Results Enter data from Order of data input on capture Set 1, data sheet into Page 39 entry the form screen on the data entry screen Data entry order is the same as that on the data capture sheet Date Actual Results Action taken You would complete the Test Plan, having invented some suitable test data for the purpose. After the implementation you would run the system to see what actually happens, complete your test log, and correct any errors. In practice, systems tend be tested to a degree as they are developed. Sometimes you just have to do this to be able to continue. Just make a note of the testing you do, keep a record of the outcomes and any printouts or screen shots. These can be added to your Test Log later. Don't throw away any examples of where things went wrong. These are your evidence of testing. Store them safely in date order (having written a date and perhaps time on them)! Add edit delete – from book chapter7 Matt Prichard – comp 1321 Overview • Recap • Add, edit, delete • Confirmation Reminder of what we are making We will need 5 new files – we will create these now These will be, 1, an authors template, 2, a confirmdelete template, 3, our usual error template, 4, our add authors form and of course 5, our index controller. Now the PHP Warning lots of code…take your time!!!! Index.php (authors folder) list all authors part Select block (list all authors)…same as last week really. Include our new template authors.html.php. Note: this will be at the end of the controller authors.html.php (template to output our authors list) This should start to look familiar. Line 1 include our output helper. Line 10 sends query string (?add) to detect user wants to add new author. Line 13 action is empty ,the user decides whether to edit or delete later. L line 17/18. 2 submit buttons, with the same name but different values Browser view Delete author (using foreign keys) Very similar delete block to last week Delete author (using foreign keys) cont… New code: line 127. User clicks the Delete button next to the author namein the template. Since the button’s name attribute is set to action, we can detect this button click by checkingif $_POST['action'] is set, and if so, check if its valueis 'Delete'. Test it out • If you add authors, jokes and jokecatergory to yourtables in phpMyAdmin, you should now be able to delete an author from your web page. • Check your database and any jokes and jokecategory references should be gone too. • Note – in my system there is a ‘confirm delete’ page. • This is part of the the user experience / control. • We will look at this later as extra material. Insert author • We could implement the same system from last week where we added jokes. • This would work fine. • But… We also would like to edit authors. Both features require the user to fill out a similar form. • It is possible to do both tasks with the same form. The add/edit form Note the six PHP variables inserted into the content of this page: $pageTitle: sets the title and top-level heading () for this page $action: sets the value passed in the query string when the form is submitted $name: sets the initial value of the form field for the author’s name The add/edit form cont… $email: sets the initial value of the form field for the author’s email address $id: sets the value of the hidden form field for the author’s database ID $button: sets the label of the form’s submit button These variables enable us to use the form for two purposes: creating new authors and editing existing ones. This table shows the values we’d like to assign to every variable in each instance. Controller code to load form when ‘add new author’ link is clicked ( slide 25 line 10) . We are assigning values to our list of variables, which are then inserted into the form page. The form and view source See how the h1tag, the ‘form action’ and button have been populated by the variables from the previous slide. When the user submits the form in this mode we can watch for $_GET[‘addform’], then include our db connection as before. Complete insert author block Edit author • This is done in 3 distinct stages. • What are they? 1. Select the data we need to edit fromthe database 2. Include and populate the form. 3. Run an update query on the database Edit author block ( first part) Same as the delete block, except we check if action isset and it conations ‘Edit’ Run a select query where author_id = $_POST[‘id’], our hidden form value. New concept !!! The fetch( ) method allows us to fetch just one row from the database. We only want the row where it matches our author_id, so no need for a foreach loop Again no need to loop into an associative array, just define the columns names in the $row array Complete edit author block, selecting and populating the form Browser view and view source See how the form is populated with the variables we assigned and the data from our table. Last bit – run the update query Check ‘if isset editform’. Then run our update query binding values posted from the form. Simples Round up • There is alot of code here, but if you work through it slowly and refer to the book you’ll get there. • The next few slides contain the code for this part of the system. You need to read through the slides to understand it, don’t just blindly copy. Form.html.php Authors.html.php Error.html.php Index.php lines 1 -50 Index.php lines 50-95 Index.php lines 95-136 End Additional work for next time • If you notice my system has a confirm delete page. As a challenge, try adding a confirmation prompt to this process. • Modify your controller to respond to the Delete button by simply displaying another template, this one prompting the user to confirm the action. • When the user submits the form in this page, it should trigger the code in the controller that actually deletes the data. • This second form will also have to submit in a hidden field the ID of the author to be deleted. • This is the code of my confirm_delete.html.php page • You will need to add code to the controller to handle it. • Try yourself and I will show my code next week. Next week • The code for the category management system is virtually identical to the author management system. • When we delete a category, we must avoid simultaneously deleting any jokes that belong to that category, because those jokes may also belong to other categories. • let’s allow for the possibility of including jokes in our database with no assigned category. • Thus, to delete a category, we also need to delete any entries in the jokecategory table that refer to that category. • Again we can do this with our foreignkeys. Questions ? PHP Email – on the IDrive Matt Prichard – comp 1321 E mail • PHP mail( ) function can be used to generate e-mails. • The mail function takes the following arguments. • to_address: the destination address i.e. jo@gre.ac.uk • subject: specify the subject of the e-mail • message: specify the body content of the message • extra_headers: optional arguments like (From: or Cc: etc.) • Format for mail function: • mail (to_address, subject, message, extra_headers); E mail – the form http://stuiis.cms.gre.ac.uk/pm76/phpEx/Email.php E mail – the php Requesting the data from the form and assigning it to the message variable Just to show the message was sent Required on the Uni servers Use your email address!! The mail function including the $message variable Mail warning • Do not use PHP to send emails pretending to be someone else • Do not send spam • Do not use my email address for testing ! COMP1321 (2020/21) Web Technologies Contribution: 50% of course Course Leader: Mr Matthew Prichard Securing a Data Driven Site Deadline Date: Tuesday 30/03/2021 This coursework should take an average student who is up-to-date with tutorial work approximately 50 hours Feedback and grades are normally made available within 15 working days of the coursework deadline Learning Outcomes: A. Discuss the current and emerging internet and web technologies. B. Develop a web application with database connectivity using a variety of technologies specially PHP, MYSQL and JavaScript. E. Discuss issues surrounding internet and intranet technologies, such as browser dependency, accessibility, legal and security. F. Develop secure web applications. Plagiarism is presenting somebody else's work as your own. It includes: copying information directly from the Web or books without referencing the material; submitting joint coursework as an individual effort; copying another student's coursework; stealing coursework from another student and submitting it as your own work. Suspected plagiarism will be investigated and if found to have occurred will be dealt with according to the procedures set down by the University. Please see your student handbook for further details of what is / isn't plagiarism. All material copied or amended from any source (e.g. internet, books) must be referenced correctly according to the reference style you are using. Your work will be submitted for plagiarism checking. Any attempt to bypass our plagiarism detection systems will be treated as a severe Assessment Offence. Coursework Submission Requirements • • An electronic copy of your work for this coursework must be fully uploaded on the Deadline Date of Tuesday 30/03/2021 using the link on the coursework Moodle page for COMP1321. For this coursework you must submit a single PDF document. In general, any text in the document must not be an image (i.e. must not be scanned) and would normally be generated from other documents (e.g. MS Office using "Save As .. PDF"). • • • • • For this coursework you must also upload a single ZIP file containing supporting evidence. There are limits on the file size (see the relevant course Moodle page). Make sure that any files you upload are virus-free and not protected by a password or corrupted otherwise they will be treated as null submissions. You must NOT submit a paper copy of this coursework. All courseworks must be submitted as above. Under no circumstances can they be accepted by academic staff The University website has details of the current Coursework Regulations, including details of penalties for late submission, procedures for Extenuating Circumstances, and penalties for Assessment Offences. See http://www2.gre.ac.uk/current-students/regs Securing a data driven website This is an individual piece of coursework. It is to be implemented in PHP and MySQL on the CMS student web server (i.e. on your I drive) or on your localhost. You must use PHP PDO programming and show an understanding of relational database design including referential integrity. General Overview This coursework is to be built onto Coursework 1, the recipe finder site. If you did not complete CW1 speak to me immediately as you may need to use a pre-made system. Your task is to secure the site you made in Coursework 1, providing role-based access control. Below is a list of the initial requirements you need to provide in order of importance. • A secure password protected role-based access control system to the site. • Make the self-registered users sign up with a password and give them rights to edit and delete their own recipes. • Find a way to deal with lost or forgotten passwords. • To allow registered users to format their own content • To implement suitable client side and server-side validation. • To secure the customer sign up page with a CAPTCHA • To implement suitable front-end technologies including JQuery and Google APIs. Detailed specification A A secure role-based access control system to the site. • The admin part of your system needs role-based access control for three levels of “user” I. II. III. • • Content Editor (add, remove and edit recipes) Account Admin (add remove and edit authors plus content editor rights) Site Admin (add, edit and remove categories plus content editor and account editor rights) You need to make use of password encryption. This will all be covered in detail in the labs and lectures. B Customer sign up/ sign in system • The front end of your site will need a sign up/sign in form. This can be on the home page or a separate page. • Sign up. The customer will need to create an account to add recipes to the system. At least a user name/email address and (encrypted) password will need to be stored in the database. • User should receive a welcome email when they sign up. • Sign in. Once an account is created the user can enter new recipes into the system. They can also edit and delete their own recipes only. • How this is displayed and managed is up to you. C Find a way to deal with lost or forgotten passwords( you may need to work on the I drive for this part) • • • D To allow registered users to format their own content • • • E You need, with the aid of your own research, to find a way to manage lost or forgotten passwords. As they are to be encrypted the system admin cannot tell the user what their password is. You can either allow the system to reset it for them or allow the user to reset it themselves. This is entirely up to you. When registered users add their own recipes they may want to format the text, eg add paragraphs or bold and italics. This has implications for what can be stored in the database. We will look at some options in the lectures and labs Client side and server-side validation • Using JavaScript, JQuery or HTML 5 as appropriate, you need to provide client-side form validation to mitigate against the GIGO effect by controlling user input into your database. • As JavaScript can be disabled in the browser you need to provide server-side PHP data input validation as a 2nd line of defence. F Add a CAPTCHA to the sign-up process https://en.wikipedia.org/wiki/CAPTCHA G Implement front-end technologies including JQuery and Google APIs. • • We will cover some front-end technologies including HTML 5 and CSS3, CSS Less/SASS, jQuery and basic Google APIs. As part of the design of your customer facing front end implement these technologies where you see fit. For example, an interactive Google map, jQuery image slider, CSS Less/SASS mixins, Twitter or other social media feeds. Deliverables 1. A formal report (no word limit or restriction) in PDF format containing: • • • • • • • • 2. date. Title page, including the URL of the web site and the admin area and all user names and passwords needed to access the site. Table of contents. Executive summary outlining the key issues and findings of the report. Detailed consideration of any legal issues around personal data storage and an understanding of GDPR. How you developed your system including design of the pages, navigation structure, data diagrams if notably different form CW1. Overview of your system with screen shots showing sample pages to prove the functional requirements have been met. Evidence of a structured and thorough testing schedule including input validation / control. Evaluation and conclusions. A ZIP file of your entire site. The report and ZIP file must be uploaded to Moodle by 23:30 on the due Assessment Criteria Prototype web solution 60% Report including evidence of testing and evaluation 40% ________________________________________________________________ Total 100% ________________________________________________________________ Grading Criteria – 70% + (1st class) A functional professional solution has been developed in PHP and MySQL A high quality and complete implementation of the required functionality is present. Critical evaluation and specific conclusions are presented. 60-69% (2:1) A functional Web solution has been developed in PHP and MySQL Most of the required functionality is present. Good evaluation and conclusions are presented. 50-59% (2:2) A Web solution has been developed in PHP and MySQL which includes some of the requirements Some of the required functionality is present. Some evaluation and conclusions are presented. 40-49% (3rd) A Web solution has been developed in PHP and MySQL with large gaps in the technical requirements. An attempt at the required functionality has been made but shows limited technical competence or understanding. Very little, if any evaluation or conclusions are presented. fetch() as we want one row Assign values to the variables to populate the edit version of the form . Build list of authors as we have been doing before . Get list of categories containing this joke. Select categoryid from jokecategoty where joke id = $id variable from the variable list in slide 15 . Loop through $s (our pdo object) assigning categoryid to the $selectedCategories array NEW CODE line 200. While building the list of all categories for the form to display as checkboxes, we check each category’s ID to see if it’s listed in our $selectedCategories array. The built-in function in_array does this for us automatically. We store the return value (either TRUE or FALSE) in the 'selected' item of the array that represents each category. This value will then be used by the form template (as we’ve already seen) to select the appropriate checkboxes. Update the joke • Done in 3 parts • Update the joke table • Delete obsolete rows from the jokecategory table • Insert the newly selected checkbox selections back into the jokecategoty table Update the joke. This code should be familiar. 213-218 is a simple validation block to check an author has been selected. Delete records from jokecategory where jokeid = id sent from form Insert into jokecategory where jokeid = id sent from form Loop through assigning the category ids of the selected categories. Page 232. Try and work out what this is doing. Delete Joke • You should be able to do this part yourselves by now Additional improvements • Confirm delete jokes? • Client side form validation? • Decide about adding jokes with no categories? All done - almost • You now have a fully functional CMS system allowing add edit delete, searching and using dynamic drop down menus and check boxes. • There is enough here to complete the coursework • You now need to apply what you have learned to your own system. Questions ? Add edit delete – from book chapter7 Matt Prichard – comp 1321 Overview • Recap • Add, edit, delete • Confirmation Reminder of what we are making We will need 5 new files – we will create these now These will be, 1, an authors template, 2, a confirmdelete template, 3, our usual error template, 4, our add authors form and of course 5, our index controller. Now the PHP Warning lots of code…take your time!!!! Index.php (authors folder) list all authors part Select block (list all authors)…same as last week really. Include our new template authors.html.php. Note: this will be at the end of the controller authors.html.php (template to output our authors list) This should start to look familiar. Line 1 include our output helper. Line 10 sends query string (?add) to detect user wants to add new author. Line 13 action is empty ,the user decides whether to edit or delete later. L line 17/18. 2 submit buttons, with the same name but different values Browser view Delete author (using foreign keys) Very similar delete block to last week Delete author (using foreign keys) cont… New code: line 127. User clicks the Delete button next to the author namein the template. Since the button’s name attribute is set to action, we can detect this button click by checkingif $_POST['action'] is set, and if so, check if its valueis 'Delete'. Test it out • If you add authors, jokes and jokecatergory to yourtables in phpMyAdmin, you should now be able to delete an author from your web page. • Check your database and any jokes and jokecategory references should be gone too. • Note – in my system there is a ‘confirm delete’ page. • This is part of the the user experience / control. • We will look at this later as extra material. Insert author • We could implement the same system from last week where we added jokes. • This would work fine. • But… We also would like to edit authors. Both features require the user to fill out a similar form. • It is possible to do both tasks with the same form. The add/edit form Note the six PHP variables inserted into the content of this page: $pageTitle: sets the title and top-level heading () for this page $action: sets the value passed in the query string when the form is submitted $name: sets the initial value of the form field for the author’s name The add/edit form cont… $email: sets the initial value of the form field for the author’s email address $id: sets the value of the hidden form field for the author’s database ID $button: sets the label of the form’s submit button These variables enable us to use the form for two purposes: creating new authors and editing existing ones. This table shows the values we’d like to assign to every variable in each instance. Controller code to load form when ‘add new author’ link is clicked ( slide 25 line 10) . We are assigning values to our list of variables, which are then inserted into the form page. The form and view source See how the h1tag, the ‘form action’ and button have been populated by the variables from the previous slide. When the user submits the form in this mode we can watch for $_GET[‘addform’], then include our db connection as before. Complete insert author block Edit author • This is done in 3 distinct stages. • What are they? 1. Select the data we need to edit fromthe database 2. Include and populate the form. 3. Run an update query on the database Edit author block ( first part) Same as the delete block, except we check if action isset and it conations ‘Edit’ Run a select query where author_id = $_POST[‘id’], our hidden form value. New concept !!! The fetch( ) method allows us to fetch just one row from the database. We only want the row where it matches our author_id, so no need for a foreach loop Again no need to loop into an associative array, just define the columns names in the $row array Complete edit author block, selecting and populating the form Browser view and view source See how the form is populated with the variables we assigned and the data from our table. Last bit – run the update query Check ‘if isset editform’. Then run our update query binding values posted from the form. Simples Round up • There is alot of code here, but if you work through it slowly and refer to the book you’ll get there. • The next few slides contain the code for this part of the system. You need to read through the slides to understand it, don’t just blindly copy. Form.html.php Authors.html.php Error.html.php Index.php lines 1 -50 Index.php lines 50-95 Index.php lines 95-136 End Additional work for next time • If you notice my system has a confirm delete page. As a challenge, try adding a confirmation prompt to this process. • Modify your controller to respond to the Delete button by simply displaying another template, this one prompting the user to confirm the action. • When the user submits the form in this page, it should trigger the code in the controller that actually deletes the data. • This second form will also have to submit in a hidden field the ID of the author to be deleted. • This is the code of my confirm_delete.html.php page • You will need to add code to the controller to handle it. • Try yourself and I will show my code next week. Next week • The code for the category management system is virtually identical to the author management system. • When we delete a category, we must avoid simultaneously deleting any jokes that belong to that category, because those jokes may also belong to other categories. • let’s allow for the possibility of including jokes in our database with no assigned category. • Thus, to delete a category, we also need to delete any entries in the jokecategory table that refer to that category. • Again we can do this with our foreignkeys. Questions ? Categories and Joke Search. Matt Prichard – comp 1321 Overview • • • • Confirm delete The category system The joke system… first part Search functionality Confirm delete ? • To prevent accidental deletion. • Give the user a sense of control. • Not essential for the Coursework, but good practice. The theory • When the user clicks delete we load a newtemplate that fetches the row of that author only. • Displays it with a ‘yes’ and ‘no’ button. • User clicks ‘yes’, run the delete block from last week. • User clicks ‘no’, reload the ‘list all authors’ template Changes to controller - user clicks delete This block selects the row of the selected author and passes the data to our ‘confirm delete’ template. This is the code of my confirm_delete.html.php page Display the name of the author, assign the author id to a hidden field. Create 2 buttons named ‘action’ with values ‘yes’ and ‘no’. Form action=“” sends form data back to the controller. The controller – yes or no User click yes – run our delete block from last week User click no – Previous if statement not valid so run delete code Category system • This is essentially the same as the authorsystem. • We are simply providing an interface to add, edit and delete categories. • The files needed and the bulk of the code are identicalto the author system. • The logic is the same. • See if you can work it out? • Duplicate the authors folder and rename it categories Category system cont… • You should work through the files updating variable names, database table names, fields etc… • If you take care and have understood the code from last week this should be a fairly straight forward task. (it took me 10 mins) • Make sure you have set the foreign keys in the database as described last week so that when you delete a category the reference in the jokecategory table goes too. • We don’t want to delete jokes though, as they may belong to more than one category. • Pages 205 - 212 for the category system code. The file structure The 4 new files. Copies of the author files but with the required changes. Not using confirm delete this time The full category system Managing Jokes • This is more complex than authors and categories. • We need to be able to assign categories and authors to our jokes. • We will have many more jokes than categories and authors. • We need a better way of looking through our jokesto avoid a huge long list. • We may know the author or the category or sometext? • We will build a very simple ‘search engine’ to help. What we are making The jokes file structure New folder ’jokes’. Create 3 new files; index.php jokes.html.php searchform.html.php Use our previous error.html.php template Admin/jokes/index.php – our new controller Start a new controller Admin/jokes/index.php Look through this code. We are running 2 select queries and making 2 arrays. One for authors one for categories New concept – dynamic dropdown menus • We will use the arrays from the previous page to dynamically populate some drop down lists. • This is a common procedure in data driven sites. • It means we don’t have to hard code our dropdown menus. • It prevents user input errors as the data is from the database. searchform.html.php Full code. Breakdown in next few slides Page 213/214 searchform.html.php detail Include our helper file for the output Creating an ‘add’ variable to Method ”get” allows user to detect when user intends to bookmark their search result. add new joke. Always use get for searches searchform.html.php detail cont Create an html drop down menu Line 17: loop through the Option value = primary key array from the controller Dropdown text = name from database Repeat this technique for the category drop down. Lines 24-29 searchform.html.php detail cont Create a text box named ‘text’ Assign the value ‘search’ to a submit button named ‘action’ for use later on. The finished form Notice the data in the URL as we are using ‘get’ for our search Warning – tricky concepts ahead Build the search query • This is the last section for this week. • We need to build a dynamic search querythat responds to the users input and selection. • This is quite complex. • We will do it in small stages. The theory • To create our search functionality we need to be able to run various select queries depending on whether the user selects an author, a category, joke text or any combination of the above. • We could hard code the various queries and tryand select between them, or we can use variables to create a dynamic query. Queries we may need If the user selects no options we want to return all the jokes. SELECT * would do this, but we want to add FROM and WHERE clauses as we go. We need a WHERE clause that initially has no effect. WHERE TRUE is always true so will do the job • With nothing selected: • SELECT * FROM joke • SELECT id, joketext FROM joke WHERE ?? • SELECT id, joketext FROM joke WHERE TRUE Queries we may need – author selected If the user selects an author we need to add this to the WHERE clause SELECT id, joketext FROM joke WHERE TRUE AND authorid = :authorid The ‘:authorid’ will come from the selected option of the author dropdown menu named ‘author’. Queries we may need – category selected If the user selects a category we need to add this to the query. SELECT id, joketext FROM joke INNER JOIN jokecategory ON id = jokeid WHERE TRUE AND categoryid = :categoryid The ‘:categoryid’ will come from the selected option of the category dropdown menu named ‘category’. Queries we may need – joke text entered If the user enters text into the text box we need to deal with that too. SELECT id, joketext FROM joke WHERE TRUE AND joketext LIKE '%:joketext%’ The ‘:joketext’ will come from the text box named ‘text’ Queries we may need – author and text If the user enters text into the text box and selects an author we need the following SELECT id, joketext FROM joke WHERE TRUE AND authorid = 2 AND joketext LIKE '%knock%’ • We need to cover all the other permutations too. Begin the search query NB. this code goes before the other code in the index so far. Check if ‘action’ isset and contains ‘search’, include our DB Connection. Begin the search query We are assigning some strings to variables that when joined together form our basic query that we have seenin the previous slides. $select . $from . $where would be; SELECT id, joketext FROM joke WHERE TRUE Create a variable $placeholder and assign an array toit. Next stage • Now we need to check each of the possible constraints (author, category and search text). • If they have been submitted with the formwe must append them to our SQL query. • First the author check. An author is selected If the author value in the $_GET array is not empty an author has been selected so we must adjust our query The append operator .= is used to add a new string to an existing string. We are adding to the WHERE clause. Also we are storing our placeholder for the query in the $placeholder array with the placeholder name as the array index A category is selected If the category value in the $_GET array is not empty a category has been selected so we must adjust our query The append operator .= is used to add a new string to the FROM and WHERE clauses (note the spaces at the start of the strings). Again we are storing our placeholder for the query in the $placeholder array. Some search text is entered. If the text value in the $_GET array is not empty some text has been entered so we must adjust our query. The append operator .= is used to add a new string to the WHERE clause (note the spaces at the start of the string). Again we are storing our placeholder for the query in the $placeholder array. We are also storing the % wildcard delineators – be very careful of the syntax here. Because we have the values of all our placeholders stored in a PHP array variable ($placeholders) the execute method lets us supply an array containing the values that we want to assign to the placeholders in our prepared statement, rather than having to use bindValue separately for each one. The full code for the search query block. This cover slides 29-35 and pages 215-218 in the book. This comes BEFORE The search form code from earlier although it is in the same index.php file Lastly we need the jokes.html.php template on line 51, this is slightly different to our previous joke template so start a new one Joke.html.php – study this codeyourself Questions ?
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Done. The updated copy of the website
Here i...


Anonymous
I use Studypool every time I need help studying, and it never disappoints.

Studypool
4.7
Indeed
4.5
Sitejabber
4.4

Related Tags