Transcript Databases with PHP

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
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
How to create the database
Delivery in 39 seconds or its free
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, val TEXT (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