1 / 13

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.

nora
Download Presentation

Databases with PHP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Databases with PHP A quick introduction

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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”]

  8. 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?

  9. 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:

  10. 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

  11. OK, so back to the chat thing… index.php

  12. And the part that talks to MySQL logic.php

  13. 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

More Related