1 / 36

USING PHP AND MySQL - UPDATE / SEARCH -

USING PHP AND MySQL - UPDATE / SEARCH -. WEEK 11 (November 19). REVIEW OF LAST WEEK. A Content Management System is .

nascha
Download Presentation

USING PHP AND MySQL - UPDATE / SEARCH -

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. USING PHP AND MySQL - UPDATE / SEARCH - WEEK 11 (November 19)

  2. REVIEW OF LAST WEEK • A Content Management System is • a bundled application to create, manage, store and deploy content on Web pages. Web content includes text and embedded graphics, photos, video, audio, and code that displays content or interacts with the user.

  3. REVIEW OF LAST WEEK

  4. REVIEW OF LAST WEEK • Job Opportunities

  5. REVIEW OF LAST WEEK The White House >> White House

  6. REVIEW OF LAST WEEK • Free and Open Source software • You don’t have to pay for a software license • You can modify the software • Large and vibrant community of users and developers • Many people testing it, finding security issues, etc. • Many modules freely available from developers • Many people donating their time to writing documentation, helping new users, etc. • Flexible architecture • You can create your own modules for custom features • You can create your own themes for custom design • Based on standards: • Core software is PHP/MySQL, giving many hosting options • Output uses XHTML, CSS, JavaScript, so compatible with most browsers

  7. REVIEW OF WEEK 9 ADD DELETE

  8. REVIEW OF WEEK 9 ADD

  9. REVIEW OF WEEK 9 DELETE

  10. TODAY’S TOPIC • USE PHP AND MySQL UPDATE SEARCH

  11. USING PHP AND MySQL Step 1: Establish connection Step 2: Select DB Step 3 (SQL): Get, Insert, Update, Delete Data Step 4: Close connection

  12. UPDATE PAGE

  13. SQL UPDATE • The UPDATE statement is used to update existing records in a table. UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

  14. SQL UPDATE • Example We have the following “students" table:

  15. SQL UPDATE UPDATE students SET age=36, major=‘Economics' WHERE f_name=‘Walt' AND l_name=‘Disney' Updated 

  16. PROCESS OF UPDATE PAGES updateform.php

  17. UPDATE PAGE • Use mysql_query() function to update a record • Formulate a UPDATE statement • Execute the query using mysql_query() Formulate an UPDATE SQL statement Execute the query using mysql_query()

  18. TODAY’S LAB - UPDATE  updateform.php • Sticky forms • A user can update information • using the forms • Updated information will be sent to • update.php. • update.php  • Execute the query

  19. updateform.php HTML form (sticky form)

  20. update.php Receive data from the HTML forms Execute the query

  21. SEARCH PAGE

  22. DEVELOP SEARCH FORM At the heart of a search form there is a SELECT statement with a WHERE clause that takes users’ input as conditions. Another frequently used function is to search a database. So far we have learned how to use PHP to add a record, delete a record and update a record in MySQL.

  23. SQL SELECT STATEMENT SELECT • The most commonly used SQL command is SELECT statement. • The SQL SELECT statement is used to query or retrieve data from a table in the database.

  24. SQL SELECT STATEMENT SQL WHERE Clause • The WHERE Clause is used when you want to retrieve specific information from a table excluding other irrelevant data. • SQL offers a feature called WHERE clause, which we can use to restrict the data that is retrieved.

  25. SQL WHERE CLAUSE SQL WHERE Clause - Syntax SELECT column_list FROM table-name WHERE {column} comparison-operator value • column - is the column of a table. • comparison-operator - operators like = < > etc. • value - Any value for comparison

  26. SQL WHERE CLAUSE Example student Q: Find the name of students whose hobby is golf. SELECT f_name, l_nameFROM student WHERE hobby = "Golf";

  27. LIKE CLAUSE SQL LIKE Operator The LIKE operator is used to search for a specified pattern in a column. SELECT column_name(s) FROM table_name WHERE column_nameLIKE pattern

  28. LIKE CLAUSE SELECT * FROM users WHERE last_name LIKE ‘Joh%’; The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern. This would return rows from the table that start with Joh. For example: Johnson John John-Jules Johnstone

  29. LIKE CLAUSE SELECT * FROM bookmark WHERE comment LIKE ‘%mail%’; Now, we can select records that contains the pattern “mail" from the comment column in the bookmark table. For example: Gmail Yahoo mail service Mailing list Oh!myemailaddress

  30. PHP CODE FOR A SEARCH PAGE

  31. TODAY’S LAB – SEARCH PAGE  searchform.php • HTML form • Send search terms to SQL • search.php  • Execute the query • Show the search results

  32. TODAY’S LAB uwm  Send “uwm” to SQL statement in search.php file

  33. TODAY’S LAB • The query, uwm, is transferred • through the URL.

  34. FINAL PROJECT • Final Project Instructions will be handed out Next Week! • Individual project. • A template will be given. • You will need to modify the template to your theme.

  35. SCHEDULE • WEEK 12 (Nov. 26) : PHP Cookies and Sessions • Login page, User registration • WEEK 13 (Dec. 3): Common Programing Skills • Pagination, Uploading files • WEEK 14 (Dec. 10): Common Programing Skills • Photo album • Quiz 2

  36. HOMEWORK THIS WEEK! • Create updateform.php • Create update.php • Create searchform.php • Create search.php • Upload to FTP bookmark folder. If everything works upload your pages to D2L Dropboxweek 11. • Deadline: November 25th 11:59 pm.

More Related