130 likes | 280 Views
Databases with PHP. A quick introduction. Y’all know SQL and Databases. You put data in You get data out You can do processing on it very easily LIMIT, ORDER, WHERE, GROUP BY It has a nice Structured Query Language You’ve learned all of this before. You’ve done PostgreSQL.
E N D
Databases with PHP A quick introduction
Y’all know SQL and Databases • You put data in • You get data out • You can do processing on it very easily • LIMIT, ORDER, WHERE, GROUP BY • It has a nice Structured Query Language • You’ve learned all of this before You’ve done PostgreSQL • MySQL doesn’t differ in SQL syntax • Slight differences when creating new users and selecting active databases
What is this lecture then? • How to convert your existing SQL knowledge into PHP • How to use that to make something interesting ten.xd-bx.net
Here’s stuff I prepared earlier • There is some PHP/HTML relating to the visual parts of the page. I will not cover that, unless there is time at the end • There is some CSS, again I’ll leave that to the end • The PHP relating to the logic is in a separate file, I will show you how to create that. Roadmap • Make a database • When people type into the box and hit enter we need to • Connect to the database • Insert the data into the table • When people view the page we need to • Connect to the database • Grab the last 25 comments, ordered by the time they are posted
Delivery in 39 seconds or its free How to create the database root@core:~# date Wed Mar 16 13:58:58 UTC 2011 root@core:~# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 45 Server version: 5.1.41-3ubuntu12 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database gw; Query OK, 1 row affected (0.01 sec) mysql> use gw; Database changed mysql> create table comments (id INT AUTO_INCREMENT PRIMARY KEY, time TIMESTAMP, valTEXT (255)); Query OK, 0 rows affected (0.03 sec) mysql> grant all on gw.* to 'gw'@'localhost' identified by 'somepassword'; Query OK, 0 rows affected (0.12 sec) mysql> quit; Bye root@core:~# date Wed Mar 16 13:59:34 UTC 2011
Connect to the database from PHP • When we execute queries later, PHP needs to know where the database server is and how to log into it • If you only have one database connection (normal) you can forget about the returned connection handle • resource mysql_connect(“servername”,”username”,”password”); • This makes PHP remember that mysql calls must go through that connection • Automatically flushed and closed cleanly Select the correct database • You are now connected to the database server but not yet to your actual database • Need to choose the database (like “use gw;” when creating the database) • bool mysql_select_db(“databasename”); • Returns true on success, false otherwise
Execute arbitrary SQL • You know how to do everything else in SQL, reuse that knowledge! • resource mysql_query(“SQL query string”); • The returned value might be a boolean • If an error then false, if success but not a fetching type of query then true • Or it might be a handle to the result set • Which could be empty • You can then fetch individual rows • array mysql_fetch_assoc($myResultSet); • mysql_fetch_assoc will return false if there are no more rows • while($row = mysql_fetch_assoc($resultSet)){ //Per row code} • The ‘assoc’ part is for ‘associative array’. You access a column by its name like $row[“columnA”]
That’s all we need Any questions on SQL syntax? You should know how to do the following: • SELECT rows from a table WHERE they meet certain criteria • ORDER the returned rows • LIMIT the number of rows returned • GROUP BY is cool, but you probably won’t really need it for the project Are we missing anything?
YES! SECURITY • Barry and other people like him will have his way with your ponies if you are not careful • w3schools puts up a really insecure example in their tutorial, NEVER EVER DO THIS:
Yeah but all that protection stuff kills the mood • Not so, its quick and easy. You have one function which will magically create a locale specific super ultra protection filter and run it on your input: • $input = “Barry says \”;DROP DATABASE ponies;--”;$safe = mysql_real_escape_string($input); • It doesn’t even matter if your database server is CHINESE, it will connect to it and ask it which characters are dangerous • This is why its called _real_ (it doesn’t play around) • Always use it! Keep it in your wallet • Don’t use the old mysql_escape_string(); it has expired and will break when you need it most
OK, so back to the chat thing… index.php
And the part that talks to MySQL logic.php
Tada! • Head over to ten.xd-bx.net and abuse it • Did you know you can embed HTML and JavaScript into your post? You could actually do some pretty cool/nasty things • The annoying refresh every 30 seconds can only be fixed with a splash of AJAX, someone else is lecturing that