1 / 47

Website Development

Website Development. Working with Databases. What you will achieve today!. Connecting to mySql Creating tables in mySql Saving data on a server using mySql Getting data from the server using mySql. browser. web server. scripting. database. : Customer. language. request service.

hisa
Download Presentation

Website Development

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. Website Development Working with Databases

  2. What you will achieve today! • Connecting to mySql • Creating tables in mySql • Saving data on a server using mySql • Getting data from the server using mySql

  3. browser web server scripting database : Customer language request service access page interpret set data get data get data return html present html Reminder of the general process today’s emphasis

  4. The database • A relational database is made up of tables (sometimes known as relations) • Tables are made up of rows (records) and columns (fields, attributes) • Table structure is described in terms of its columns (fields, attributes) E.g. cars (make, model, doors, fuel, cost) • Tables are usually linked to other tables via relationships

  5. Outline • MySQL • PHPMyAdmin • Simple database communication using PHP

  6. MySQL is a relational database management system. • A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. The tables are linked by defined relations making it possible to combine data from several tables on request. The SQL part of MySQL stands for "Structured Query Language" - the most common standardized language used to access databases. http://www.mysql.com/information/index.html

  7. MySQL • Lightweight • Easy to administer • Runs on low power machines • Great for development • Not quite there when compared to Oracle, MS SQL Server or IBM DB2 but coming along fast (http://www.devx.com/dbzone/Article/20743 )

  8. Installing on your machine • If you are going to work at home with PHP/MySQL I recommend one of the distributed packages for web development • The one we use at uni is called Wamp and is available from http://www.en.wampserver.com/ • There are other alternatives

  9. GUI • Most common is phpMyAdmin • Hermes link is • http://hermes.hud.ac.uk/phpMyAdmin/ • Normal username and password • Let’s have a look at it…

  10. The Four SQL Commands • SELECT • INSERT • UPDATE • DELETE • These only affect values in the database, not the structure, but once you know these four you are set for life* * not guaranteed

  11. SELECT • The example table is called "Cars" • Each record is made up of data describing ID Make Model Doors Fuel and Cost • The SQL to retrieve all rows and all columns is:-  • SELECT * FROM Cars • The * indicates that all columns from ‘Cars’ are to be included. • This could have been written as:-  • SELECT ID Make Model Doors Fuel and Cost FROM Cars

  12. Query result • The code for all (*) would result in the following being returned

  13. More precise

  14. More precise

  15. Conditional SQL ? • We can use WHERE, along with = <> < > >= <= BETWEEN and LIKE, to narrow down our search:

  16. Conditional query

  17. Wild cards and Like

  18. Conditional logic

  19. Multiple conditions • With AND and OR we are not limited to one condition:

  20. The IN Keyword • This is a neat way of doing multiple ORs. For example, suppose we wanted a listing of all rows where the make is “AUDI” or “ALPHA” • We could obviously do this with an OR but we can also do it with IN. • SELECT * FROM Cars WHERE MAKE IN (“AUDI”, “ALPHA”) • You can have as many alternatives as you like in the brackets above.

  21. The BETWEEN Keyword • We want to list all rows where cost is greater than or equal to 10000 and less than or equal to 20000. We could do this as • SELECT * FROM CarsWHERE cost >= 10000AND cost <= 20000 • An alternative way of writing this is to use the BETWEEN keyword. • SELECT * FROM CarsWHERE cost BETWEEN 10000 AND 20000

  22. INSERT • INSERT INTO table_nameVALUES (value1, value2,....) • Or • INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

  23. UPDATE • UPDATE table_name SET column_name = new_valueWHERE column_name = some_value • Multiple values with a comma seperated • UPDATE table_name SET column_name = new_value, column_name2 = new_value2WHERE column_name = some_value

  24. DELETE • DELETE FROM table_name WHERE column_name = some_value

  25. Tutorial • Go to http://www.w3schools.com/sql/sql_tryit.asp and ‘play’ with SELECT sql • Try the examples on the page and create a few of your own • Use phpMyAdmin at http://hermes.hud.ac.uk/phpMyAdmin to ‘play’ with a mysql database

  26. Accessing MySQL from PHP

  27. Connecting to PHP • We can now write SQL and setup our MySQL database • But we want to be able to talk to the database using PHP • Its easy! • For now simple table editing (update data, delete etc), later database editing (create, dump tables etc)

  28. Creating the connection mysql_connect($hostname, $user, $password);

  29. Then choose a database… mysql_select_db(‘carsDB’);

  30. Now ask it a question… $result = mysql_query(“SELECT * FROM Cars”);

  31. Lets see how many results came back… $num = mysql_num_rows($result);

  32. Now output it to the screen… • Options for dealing with $result • mysql_fetch_row - returns row as enumerated array • mysql_fetch_array – returns row as an associative array • mysql_fetch_row used to be fastest, but mysql_fetch_array is the preferred favourite now

  33. Output to the screen… while($row = mysql_fetch_array($result)) { print($row[‘Make’] . $row[‘Model’] . $row[‘Price’] . “<br/>”); }

  34. Putting data into the database • Example uses cars database

  35. Psuedo code • Take inputs from form • Write sql string including inputted data • Connect to database • Run sql command string • Check to see if it was successful

  36. The form • Example html <form method="post" action="cars_process.php"> Make:<input name="Make" type="text" /><br/> Model:<input name="Model" type="text" /><br/> Price:<input name="Price" type="text" /><br/> <input name="submit" type="submit" value="Add to Database" /> </form>

  37. Get data from the form $Make = $_POST['Make']; $Model = $_POST['Model']; $Price = $_POST['Price'];

  38. Create the sql string $sql = "INSERT INTO cars (Make,Model,Price) VALUES ('$Make','$Model',$Price)";

  39. Connect to mysql and the database mysql_connect('localhost','root') or die("cant talk to mysql"); mysql_select_db(‘cars') or die("cant connect to the database");

  40. Run the mysql command $result = mysql_query($sql);

  41. Check to make sure it was successful if(mysql_affected_rows() == 1) { print("yay, it worked"); } else { print("something went horribly wrong!"); }

  42. Why 2 files? • Sometimes we need everything to be done in one file • E.g. I fill in a form, submit to another page, but something went wrong, now I have to go back and fill in everything again! • To solve this do all the processing in one page where the form submits to itself.

  43. <?php $Make = $_POST['Make']; $Model = $_POST['Model']; $Price = $_POST['Price']; $submit = $_POST['submit']; $success = 0; if ($submit == "Save" && $Make != NULL && $Model != NULL && $Price != NULL) { $sql = "INSERT INTO cars (Make,Model,Price) VALUES ('" . $Make . "','" . $Model . "'," . $Price . ")"; mysql_connect('localhost','root') or die("cant talk to mysql"); mysql_select_db(‘cars') or die("cant connect to the database"); $result = mysql_query($sql); if(mysql_affected_rows() == 1) { print("yay, it worked"); $success = 1; } else { print("something went horribly wrong!"); } } if($success != 1) { ?> <form method="post" action="<?php print($_SERVER['PHP_SELF']); ?>"> Make:<input name="Make" id="Make" type="text" value="<?php print($Make); ?>" /><br/> Model:<input name="Model" id="Model" type="text" value="<?php print($Model); ?>" /><br/> Price:<input name="Price" id="Price" type="text" value="<?php print($Price); ?>" /><br/> <input name="submit" type="submit" value="Save" /> </form> <?php } ?>

  44. Editing data in the database • Pseudo code • Find the data to be changed from the database • Output the data into an editable area (e.g. a form) • Resubmit the data • Update the database

  45. Connect to the database mysql_connect('localhost','root') or die("cant talk to mysql"); mysql_select_db(‘cars') or die("cant connect to the database"); $sql = "SELECT * FROM cars"; $result = mysql_query($sql); while($row = mysql_fetch_array($result)) { print("$row[0] $row[1] - <a href=\"editcar.php?Make=$row[0]&Model=$row[1]&Price=$row[2]\">edit</a><br/>"); }

  46. $Make = $_GET['Make']; $Model = $_GET['Model']; $Price = $_GET['Price']; $success = 0; $submit = $_GET['submit']; if($submit == "Update" && $Make != NULL && $Model != NULL && $Price != NULL) { $sql = "UPDATE cars SET Make='$Make' , Model='$Model' , Price=$Price WHERE Make='$Make' AND Model='$Model'"; mysql_connect('localhost','root') or die("cant talk to mysql"); mysql_select_db(‘cars') or die("cant connect to the database"); $result = mysql_query($sql); if(mysql_affected_rows() == 1) { print("yay, it worked"); $success = 1; } else { print("something went horribly wrong!"); } } if($success != 1) { ?> <form method="get" action="<?php print($_SERVER['PHP_SELF']); ?>"> Make:<input name="Make" id="Make" type="text" value="<?php print($Make); ?>" /><br/> Model:<input name="Model" id="Model" type="text" value="<?php print($Model); ?>" /><br/> Price:<input name="Price" id="Price" type="text" value="<?php print($Price); ?>" /><br/> <input name="submit" type="submit" value="Update" /> </form> <?php }

  47. A whole system… • Admin area would need • A menu • A page for viewing data • A page for adding new data • A page for editing existing data • A page for deleting existing data

More Related