SQL | PHP Tutorial

1. SQL | PHPTutorial at 8am. god, it’s early.

2. SQL intro • There are many different versions of SQL available for usage. • Oracle • MySQL • SQLite • DB2 • Mimer • The popular ones are Oracle and MySQL with MySQL quickly gaining ground. I’ll be showing you MySQL. The syntax between SQL domains varies little and with google skills you can adjust this knowledge for SQLite, which I’ve never used.

3. Databases _ creation CREATE TABLE tableName ( name VARCHAR(55), sex CHAR(1), age INT(3), birthdate DATE, salary DECIMAL(10,2), primary key(name) ); Types of attributes: char, varchar, int, smallint, decimal, date, float, etc. *varchar is a string with varying # of characters. In our example, 55 is the characters longest possible string allowed. *decimal(10,2) indicated 2 places after the decimal point and 10 total digits (including the decimal numbers)

4. Databases _ creation 2 CREATE TABLE tableName ( name VARCHAR(55), sex CHAR(1) NOT NULL, age INT(3), birthdate DATE, salary DECIMAL(10,2) DEFAULT ‘0.00’, primary key(name) ); Primary key: primary key is a UNIQUE value. For every entry in your database this must be unique and not null and every DB must have one. NOT NULL: column must have a value DEFAULT: you can set a default value if no other value is inputted for that column.

5. Databases _ indexed primary keys Instead of specifying a column as a primary key you can have the database create a column of numbers that will automatically increment with each entry inserted into the DB. Example: CREATE TABLE tableName ( id INT AUTO_INCREMENT, name VARCHAR(55), sex CHAR(1), age INT(3), birthdate DATE, salary DECIMAL(10,2), primary key(id) ); Entry 1 will have 1 as a key. Entry 2 will have 2 and so forth.

6. Databases _ deletion DROP TABLE tableName;

7. Databases _ insertion Inserting data in the database: INSERT INTO tableName(name,sex,age) VALUES(‘Mr. Freeze’,’M’,42); Also valid: INSERT INTO tableName(sex,name,age) VALUES(‘F’,’Mr. Freeze’,42); Order doesn’t matter.

8. Databases _ the meat Always in the form of: SELECT …. FROM …. WHERE …. So select a column from your database. From a database Where x meets y condition. * Except in the case of modification

9. Databases _ updating Suppose we want to change Mr. Freeze’s age to 52. UPDATE tableName SET age = ’52’ WHERE name LIKE ‘Mr. Freeze’ And so forth.

10. Databases _ aggregates This is the actual meat of using SQL. These are where you set your conditions, narrow down your table into a usable set. Here are the usable functions, I’ll show a quick example with each. The only way to really know this stuff is practice. Group by Count Sum Avg Min/Max Order by

11. Databases _ group by This is the actual meat of using SQL. These are where you set your conditions, narrow down your table into a usable set. Here are the usable functions, I’ll show a quick example with each. The only way to really know this stuff is practice. Group by lumps all the common attributes into one row. SELECT employee_id, MAX(salary) FROM Works_In GROUP BY dept_id; * MAX selects the maximum value in its () likewise for MIN

12. Databases _ count Count counts the number of columns with the specified attribute. SELECT term, COUNT(course_id) FROM teaches GROUP BY term; We counted the number of courses taught during x term. AVG & SUM function pretty much the same way.

14. PHP _ connecting to the db This is the basic connect script for accessing your db: <?php mysql_connect(“localhost”,”username”,”password”) or die(mysql_error()); ?> Localhost indicates the current machine. So you’re asking the machine to connect to itself. The die(mysql_error) part says if there’s an error halt everything and display this error. If it errors on this part, it means either your host, username, or password are wrong.

15. PHP _ error checking w/ echo Consider the connection script again with this modification: <?php mysql_connect(“localhost”,”username”,”password”) or die(mysql_error()); echo “Connected to database.” ?> Later on you may be unable to differentiate where the error occurred. So while developing your code throw in some echo statements, they just print stuff to the screen. When PHP is done connecting to our database it tell us.

16. PHP _ select the database. <?php mysql_connect(“localhost”,”username”,”password”) or die(mysql_error()); echo “Connected MySQL!”; mysql_select_db(“ljlayou_comp353” or die(mysql_error()); echo “Connected to database 353”; ?>

17. PHP _ create/drop table <?php mysql_connect(“localhost”,”username”,”pw”) or die(mysql_error()); mysql_select_db(“ljlayou_comp353” or die(mysql_error()); mysql_query(“CREATE TABLE Works_In(…)“) or die(mysql_error()); ?> We’re querying PHP to tell MySQL to do something, in this case create the table. The same applies for dropping a table. As you can see our code is being reused over and over. It gets pretty repetitive like this. Again we tell php to stop everything if an error occurs.

18. PHP _ insertion <?php mysql_connect(“localhost”,”username”,”pw”) or die(mysql_error()); mysql_select_db(“ljlayou_comp353” or die(mysql_error()); mysql_query(“INSERT INTO Works_In(company,position) VALUES(‘McDonalds’,’fry cook’)”); ?> We’re querying PHP to tell MySQL to do something, in this case create the table. The same applies for dropping a table. As you can see our code is being reused over and over. It gets pretty repetitive like this.

19. PHP _ selecting a table In order to manipulate, fetch, etc data from your database you must have PHP remember the result. So we store it in an array (?) to preserve “columns”. PHP variables unlike Java do not need a type declaration. From now on I’ll be omitting the connect stuff. <?php […] $result = mysql_query(“SELECT * FROM Works_In”) or die(mysql_error()); $row = mysql_fetch_array($result); echo “company: “ .$row[‘company’]; echo “position:” .$row[‘position’]; ?> From these lines we see that each cell in the area is labeled under the column name. Using this method we can output or even compare data.

20. PHP _ selecting a table In order to manipulate, fetch, etc data from your database you must have PHP remember the result. So we store it in an array (?) to preserve “columns”. PHP variables unlike Java do not need a type declaration. From now on I’ll be omitting the connect stuff. <?php […] $result = mysql_query(“SELECT * FROM Works_In”) or die(mysql_error()); $row = mysql_fetch_array($result); echo “company: “ .$row[‘company’]; echo “position:” .$row[‘position’]; ?> From these lines we see that each cell in the area is labeled under the column name. Using this method we can output or even compare data. The ‘*’ symbol in the SELECT statement just means that we select all the columns in the table. The above statement however results in the first row only being shown.

21. PHP _ selecting a table 2 To solve this problem, we loop continuously until there are no more rows to choose from. <?php […] while ($row = mysql_fetch_array($result)) { echo “company: “ .$row[‘company’]. “ | “position:” .$row[‘position’]; echo “<br/>”;} ?> If you have noticed the ‘.’ symbol signifies a concatenation.

22. PHP _ the formula We looked over it all. Here’s the general formula: <?php mysql_connect(“localhost”,”username”,”pw”) or die(mysql_error()); mysql_select_db(“databaseName” or die(mysql_error()); $result = mysql_query(yourQuery) or die(mysql_error()); $row = mysql_fetch_array($result); while ($row = mysql_fetch_array($result)) { … }; ?> (Show 255 final)

24. PHP _ form processing Topic job but it’s all good. I’m assuming you know how to create forms in HTML. Else, well, google it. It’s pretty straight forward. So let’s take this form as our example, this is a snippet of the form code: <form name=“animas” action=“processform.php" method="post”> […] <b>FUN</b>: <input type="radio" name="extra" value="horns">Horns <input type="radio" name="extra" value="wings">Wings <input type="radio" name="extra" value="mane">Mane <input type="radio" name="extra" value="giraffe">Giraffe Neck <input type="submit" name="submit" value="imagine it" class="submit" /> </form>

25. PHP _ form processing 2 Our form action tells the form what to do with the data. POST is a method that sends an array of variables, our data. Only when the submit button is pressed is the data sent. <form name=“animals” action=“processform.php" method="post”> […] <input type="submit" name="submit" value="imagine it" class="submit" /> </form> It is common practice to create a separate file for form processing.

26. PHP _ form processing 3 Our data is now winding through the PHP tubes. Let’s look how it’s processed. $_POST[‘submit’] if( $dbc = @mysql_connect(‘localhost’,’username’,’pw’)) { if(!@mysql_select_db(‘database’)) { die(mysql_error());} } else { die(mysql_error());} $query = “INSERT INTO animals(id,data,appendages, tail, sound,extra) VALUES(0,’{$_POST[‘body’]}’,’{$_POST[‘appendages’]}’, […] )” if(@mysql_query($query) { print “success”;} else { print “you lose”;} mysql_close();

27. PHP _ form processing 4 • Some notes on the previous slide: • ‘@’ symbol means success or halt script. • mysql_close(); it’s very important to close your connection when you’re done