Day 26 - 30 : Accessing MySQL db from the Web with PHP, Authentication

Day 26: [txtbk Chapter 11] connect  and process database access: select, insert

1. $db = new mysqli(...);
2. check bd connection error? mysqli_connect_errno()
3. connection ok, $db->real_escape_string()
4. $db->query(); ex: $result = $db->query()
5. if the query "SELECT", $result->num_rows; 
• if "UPDATE / INSERT", $db->affected_rows;
6. make a loop to retrieve data from db [for / while] 0 to num_rows-1
• $row = $result->fetch_assoc();
• get the db data ex: $row['id'], $row['name']
7. $result->free();
8. $db->close();

Day 27: 

• update
• encryption, date & time Function in MySQL
• prevent SQL injection : preparedStatement 
• bind_param variables -i, d, s, b 
1. $db = new mysqli(...);
2. check bd connection error? mysqli_connect_errno()
3. ex: $query = "select name from books where isbn=?"; //value replace to ? in query
4. $db->prepare(); ex: $stmt = $db->prepare($query);
5. $stmt->bind_param('s', $isbn); //if 1 ? then, parameter type 's' refer to its type, and the $isbn refer to the value of the ?
6. if the query "SELECT",  $stmt->bind_result($name);
7. $stmt->execute();
8. if the query "SELECT", $stmt->store_result();
9. if the query "SELECT", $stmt->num_rows; 
• if "UPDATE / INSERT", $stmt->affected_rows;
10. make a loop to retrieve data from db using while : $stmt->fetch()
• get the db data ex: $name // as what the parameter we keep for bind_result
11. $stmt->free_result();
12. $stmt->close();
13. $db->close();

Day 28: 

• [txtbk Chapter 17] Implement Authentication with PHP & MySQL
• [txtbk Chapter 23] Using Session Control

Day 29:

• [txtbk Chapter 27] Building User Authentication and Personalization
• ispell dictionary ->select English -> download  collection of word lists for spell-checking

  download Ispell English Word Lists .zip

• [txtbk Chapter 34] Web 2.0 Applications with Ajax
• step to change chapter 27 codes to be ajax  :--
• insert new_ss.css and new_ajax.js to project chapter27
• amend function display_add_bm_form() in output_fns.php
• amend do_html_header() in  output_fns.php
• link external js and css
• modify add_bms.php
• remove --- do_html_header('Adding bookmarks');
• remove ---  display_user_menu();
• remove ---  do_html_footer();
•  remove --- check_valid_user();
• remove --- try block and exception handling to something make sense in ajax environment 
• add else(s)
• modify url_fns.php
• in function add_bm() : throw change to echo(s); add else; remove --- return true;

Day 30:

• [txtbk Chapter 28] Building a Shopping Cart