PHP6 and MySQL Bible


Notes on SQL style: Many SQL queries are one long line of code. Most accomplished programmered recommend you break u every sql statement into more lines for maximum legibility. For ex.

SELECT field2, field2, field3
FROM table
WHERE id < 1000

Recommeded database permissions:

Web visitor: SELECT only

Contributor: SELECT, INSERT, and maybe UPDATE

Editor: SELECT, INSERT, UPDATE and maybe DELETE and maybe GRANT


Put the database variables you use infrequently in a non-Apache-readable directory and change the permissions when on the rare occassion you need to make changes.


mysqldump - u username -p databasename > dumpfilename.sql

If a query was an INSERT, UPDATE, DELETE, CREATE TABLE, or DROP TABLE and it returned TRUE then you can use mysql_affected_rows to see how many rows were changed by the query. 

Basic Plan for a Single-Table Displayer
1. Establish db connection
2. Construct a query to send to the db
3. send the query and hold onto the result identifier that is returned
4. Using the result identifier, found how many columns (fields) there are in each row
5. Start an HTML table
6. Loop through db result rows printing apair to make a corresponding HTML table row.
7. In each row, retrieve the successive fields and display then wrapped in a pair.
8. Close off the HTML table.
9. Close the db connection.
SELECT min(ID) FROM author //efficient
SELECT ID FROM author ORDER BY ID limit 1 //inefficient

Scoping Issues

Names of member variables and member functions are never meaningful to calling code on their own - they must always be reached via the -> construct. This is true both outside the class definition and inside member functions.
The names visible within member functions are exactly the same as the names visible within global functions - that is, member functions can refer freely to other global functions but can't refer to normal global variables unless those variables have been declared global inside the member function definition.
Look into usage of the extract() and compact() functions in php.
Use var_dump to visualize your array.



Bibliographical Information

PHP 5 CMS Framework Development

by Martin Brampton

Packt Publishing, 2010

ISBN 978-18795-11346