Popular in ADV FRONT END WEB DEVELOPMNT
verified elite notetaker
Popular in ComputerScienence
This 0 page Class Notes was uploaded by Petey Martin on Tuesday December 8, 2015. The Class Notes belongs to CSC 209 at University of Rochester taught by KOSTIN R in Summer 2015. Since its upload, it has received 22 views. For similar materials see ADV FRONT END WEB DEVELOPMNT in ComputerScienence at University of Rochester.
Reviews for Week 13
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 12/08/15
Petey Martin Notes 113015 0 PHP Database Connectivity 0 Using MySQL from PHP Getting information out of a database 0 easy Databases are smart Databases like MySQL take care of a lot of the details foryou 0 File locking Permissions 0 Etc PHP is smart PHP takes care of some of the details too 0 Put data into an array 0 phpMyAdmin httpwwwphpmyadminnet Good idea to install in a nondescript directory and change your default MySQL password if you didn t during installation 0 Here is the command substitute your password for 39newpassword39 SET PASSWORDPASSWORD39newpassword39 As a second level of defense let39s tell Apache not to let anyone access the phpMyAdmin directory without authenticating themselves This is done by placing a le called quothtaccessquot in this directory 0 The recipe The steps to basic database access are 1 Create a Database Connection 2 Perform a Database Query 3 Use Returned Data if any 4 Release returned data if any 5 Close the Connection to the Database Note that in SQLspeak a query can both fetch data from a database or put data into a database 0 Create a Database Connection The rst step is to open a connection to a database server We do this with the mysqliconnect function 0 This function returns a link to the database We can optionally specify what host the database server is running on need not be the same as the web server the port socket user and password dbhost quotlocalhostquot dbuser quotwidgetcmsquot dbpass quotcoffeequot dbname quotwidgetcorpquot connection mysqliconnectdbhost dbuser dbpass dbname o Preform a Database Query In SQL create a database query In PHP use the mysqliquery function to send the query to the open connection query quotSELECT quot query quotFROM subjects quot query quotWHERE visible 1 quot query quotORDER BY position ASCquot result mysqliqueryConnection query 0 Use Returned Data if any There are several functions available for retrieving the results of the query or information aboutthe results of the query Three of the most commonly used are mysqlinumrows or mysqliaffectedrows mysqlifetcha rray mysqlifetchassoc The result will end up in PHP as an array which can be looped and turned into HTML ltugt ltphp whiesubject mysqlifetchassocresult gt ltigt ltphp echo subjectquotmenunamequot quot quot subjectquotidquot quotquot gt ltligt ltphp gt ltugt o mysqlinumrows mysqlinumrows returns the number of rows in the results of a query speci cally a SELECT query The number of rows affected by an INSERT UPDATE or DELETE query are obtained with mysqliaffectedrows This function is useful not only for relaying to the user how many records have been found but also in loops through the results and in determining whether to display all of the results or just some portion thereof vNumberOfRows mysqlinumrowsresult mysqlifetcha rray mysqlifetcharray stores the requested data in an array In addition to the numeric indices it also stores the data in associative indices using the database eld names as the keys vResultsArray mysqlifetcharrayresult Note This function gets the next row in the array of results not the entire array mysqlifetchassoc mysqlifetcharray stores the requested data in an associative array using the database eld names as the keys vResultsArray mysqlifetchassocresult Note This function gets the next row in the array of results not the entire array Release returned data if any Use mysqlifreeresut Closing the connection Although the connection to the database will be closed automatically when the script ends it is a good idea to close it explicitly so that the connection is not tied up any longer than needed The function for this is mysqliclose Petey Martin Notes 12215 0 Database Inserting o The Database Saga Continues Putting Data Into a Database Database Error Checking 0 Remember Forms Many times HTML forms go handinhand with databases for what I hope are obvious reasons We will start out with a very simplistic approach to HTML forms and MySQL databases 0 Recipe Review Recall the basic recipe for fetching data from last class 1 Open a Connection to MySQL 2 Select The Database to Use 3 Send a Query to the Selected Database 4 Retrieve The Results of the Query 5 Close the Connection to the Database 0 Inserting Data into a Database Remember A query can both fetch data from a database and put data into a database The process of inserting or modifying data in a database is virtually identical The most signi cant thing we have to do is change the SQL we send in the query 0 An Insert Query The only thing we have to change in the code we used before is the string we pass as the query Oh and we don39t have to build an array of the results TheQuery quotinsert into people values quot39LastName3939FirstName3939NickName39quot Note that since PersonID is set to autoincrement in the database de nition we should not provide a value 0 NEVER trust a user What if your data comes from a form and a user types a quote into a data eld Can you see why this would be a problem 0 Data lnteg rity Who knows what kind of data the user will supply o If you are asking for an int as an age and they give you quotfortyquot instead of 40 what should you do In reality you should check the user input on BOTH the client and server sides For the age problem above in PHP we would check if isnumericGET39age39 0 or if isnumericPOST39age39 To make sure we are getting an int BEFORE we put into the database 0 SQL Injection We also can have the problem of users trying to do something like SQL injection defeating the purpose of our query To alleviate this problem you should always have This stops SQL Injection in GET vars foreach GET as key gt value GETkey mysqlrealescapestringvalue Andor This stops SQL Injection in POST vars foreach POST as key gt value POSTkey mysqlrealescapestringvalue 0 Did it work Instead of the mysqlnumrows function we used with our SELECT query with an INSERT or UPDATE or DELETE query we use mysqlaffectedrows In the case of our simple insert the results ought to be 1 NumberOfRowslnserted mysqlaffectedrowsdbLink 0 Error Checking Actually the quotdid it workquot question is one we really want to ask ourselves at each step along the way of interacting with a database This does make the code more complex but if we encapsulate the added complexity inside functions we only need to worry about that once 0 Trapping mysqlconnect Errors Since mysqlconnect is the rst step in the database process it39s a logical place to begin thinking about trapping errors If you can39t get a link to the database you can39t do anything else So once we do this dbLink mysqlconnectDBHostDBUser DBPass We should also do this if dbLink lldeal with the error else lwe have a link select our database 0 Trapping mysqlselectdb Errors The next step we want to check for success is mysqlselectdb So adding onto the previous code if dbLink lldeal with the error elseif mysqlselectdbDatabaseName lldeal with the error 0 Errors What Errors How do we know what the problem was mysqlerrno Gives us an error number handy for switch statements or zero for success for the last MySQL function executed mysqlerror Gives us a quothuman readablequot error message or an empty string for success for the last MySQL function executed Note These two functions quotdon39t countquot as quotlast MySQL function executedquot o Deleting Records The PHP for deleting records is very similar to that for inserting records The SQL is closer to that for selecting records TheQuery quotdelete from people where LastName 39Smith39 0 Be Afraid Be very Afraid TheQuery quotdelete from peoplequot39 Will delete ALL of the records in the table Without con rmation Note Don39t forget the WHERE to specify a criteria
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'