![<?phpmysql_query("SET character_set_client = utf8");mysql_query("SET character_set_connection = utf8");mysql_query("SET character_set_results = utf8"); ?>MySQL UTF-8Working with UTF-8 (Hebrew).<?php$sql= "SELECT * FROM students WHERE ID > 5 order by id";$result = mysql_query($sql) or die ("Unable to SELECT FROM DB: ".$sql);while($line = mysql_fetch_array($result, MYSQL_ASSOC)){ echo $line["id"]."<br/>";}?>MySQL QueryPreparing, sending and receiving the query.MYSQL_ASSOC – Associative Array MYSQL_NUM – Numeric Array](https://image.slidesharecdn.com/workingwithphpanddbs-taltamir-110404034940-phpapp01/75/working-with-PHP-DB-s-33-2048.jpg)
Uploaded byHi-Tech College
working with PHP & DB's
This document discusses different approaches to connecting PHP with databases. It begins with an introduction to using PHP with databases. It then describes three major strategies: the native interface, where PHP connects directly to the database server; the ODBC interface, which uses a driver; and the ORM interface, which maps database elements to objects. It provides examples of code for each approach and discusses how frameworks often implement ORM.
working with PHP & DB's
- 1. Working With PHPAnd DBs
- 2. Working With PHPAnd DBsIntroduction to PHP & DB
- 3.
- 4.
- 5. The ORM Model& Interface
- 6. Q & AIntroductionTo PHP & DBFor many people, the main reason for learning PHP is because of the interaction with databases it can offer.
- 7. It is surprisinghow useful a database can be when used with a website.
- 8. There is ahuge variety of things you can do when you interact the two, from displaying simple lists to running a complete website from a database. Why Would We Want A Database?Some examples of PHP and DBs being used together are:
- 9. Banner Rotation -Each banner is generated by PHP script. This opens the DB and picks a random banner from it to show the visitor. It also counts the number of times the banner has been viewed.
- 10. Forums- All thepages and messages in the forum are stored in the DB and are presented and updated by PHP scripts.Why Would We Want A Database?Some examples of PHP and DBs being used together cont’d:
- 11. Databases – Storinguser login and personal information, images, articles, item stock, sales data etc. All of these task can be done easily using PHP scripts.
- 12. Reports – Themost popular product on site, the best article, the biggest image. Visitor counters and trails, from which countries our visitors come from? All of these question can be answered easily using PHP scripts.PHP & SQL ServersSame Machine Same Network (LAN) Over The Internet (WAN) Internet(WAN)
- 13. Supported DatabaseTypesSome of the DBs that PHP supports:How Does PHP Use A Database?There are three major strategies for using a DB:
- 14. The Native Interface– PHP connects directly to the SQL server using its native protocol, e.g. MySQL protocol version 10.
- 15. The ODBC Interface– The ODBC driver is used as a mediator between the PHP and the SQL server. PHP uses the ODBC API which translates the SQL dialect.
- 16. The ORM Interface– PHP uses objects which are mapped to DB tables and elements.Famous Sites That Use PHP & DB
- 17. Working With PHPAnd DBsIntroduction to PHP & DB
- 18.
- 19.
- 20. The ORM Model& Interface
- 21.
- 22. The Native InterfacePHPconnects directly to the SQL server using its native protocol, e.g. MySQL protocol version 10.
- 23. Pros: Best performance.No drivers or mediators are used, hence the connection and queries are performed in optimal speed.
- 24. Cons: Compatibility. EachDB vendor has his own SQL dialect. Native MySQL code won’t run on ORACLE and vice versa.PHP MethodologySimple steps to follow:
- 25. Create a connectionto the database.
- 26.
- 27. Compose an SQLstatement (query, creation etc).
- 28. Submit the statementto the database.
- 29. Receive results fromthe database.
- 30. Parse results anduse them.
- 31. Close database connection.Repeat these steps for multiplestatements
- 32. <?php$link = mysql_connect('localhost','root', '') or die('Could not connect: ' . mysql_error());mysql_select_db(dbname') or die('Could not select database');?>MySQL ConnectionPHP connects to the MySQL server:UsernameComputerPassword
- 33. <?phpmysql_query("SET character_set_client =utf8");mysql_query("SET character_set_connection = utf8");mysql_query("SET character_set_results = utf8"); ?>MySQL UTF-8Working with UTF-8 (Hebrew).<?php$sql= "SELECT * FROM students WHERE ID > 5 order by id";$result = mysql_query($sql) or die ("Unable to SELECT FROM DB: ".$sql);while($line = mysql_fetch_array($result, MYSQL_ASSOC)){ echo $line["id"]."<br/>";}?>MySQL QueryPreparing, sending and receiving the query.MYSQL_ASSOC – Associative Array MYSQL_NUM – Numeric Array
- 34. <?phpmysql_close($link);?>MySQL CloseClosing theconnection (Very important).Working With PHP And DBsIntroduction to PHP & DB
- 35.
- 36.
- 37. The ORM Model& Interface
- 38. Q & AODBCConnectivity Diagram
- 39. PHP & ODBCODBC- Open Database Connectivity provides a standard software API method for using database management systems (DBMS).
- 40. In Windows, programming interfaces allowany application to talk to any printer or database by way of a “centralized manager”.
- 41. The API thatapplications use on Windows to access the database "manager" is titled ODBC.ODBC InstallationODBC can be obtained for free at: http://dev.mysql.com/downloads/connector/odbc/5.1.html
- 42. After a quickstandard (next…next…) installation, make the following adjustments:
- 43. Open the Administrative Tools iconin your Control Panel.
- 44. Double-click on the DataSources (ODBC) icon inside.
- 45. Choose the System DSN taband click on Add.
- 46. Select the Microsoft AccessDriver. Click Finish.
- 47. In the nextscreen, click Select to locate the database.
- 48. Give the databasea Data Source Name (DSN).<?php$conn = odbc_connect('MySQL','user','password');....odbc_close($conn);?>PHP SQL ConnectThe odbc_connect() function is used for connecting to the database we configured in the ODBC driver.
- 49. This function receivesa database name (DSN), a user name and a password. It returns a database handle.Closing the connection
- 50. <?php$conn=odbc_connect('MySQL','root','');if (!$conn) { exit("Connection Failed: " . $conn);}$sql="SELECT * FROM customer";?>PHP SQL QueryAfter the connection we create a String variable that holds the SQL Query:Error Handling
- 51. <?php$rs= odbc_exec($conn,$sql);if (!$rs){exit("Error in SQL");}while (odbc_fetch_row($rs)){echo odbc_result($rs,1);echo odbc_result($rs,2)."<br/>";}?>PHP SQL ResultsFirst we execute the query using odbc_exec().
- 52. Second we useodbc_fetch_row() and odbc_result() to print the result to the user.Error Handling
- 53. <?php$conn = odbc_connect('MySQL','root','');if(!$conn) {exit("Connection Failed: " . $conn);}$sql="SELECT * FROM customer";$rs= odbc_exec($conn,$sql);if (!$rs) {exit("Error in SQL");}while (odbc_fetch_row($rs)){echo odbc_result($rs,1);echo odbc_result($rs,2)."<br/>";}odbc_close($conn);?>Bringing it all together
- 54. Working With PHPAnd DBsIntroduction to PHP & DB
- 55.
- 56.
- 57. The ORM Model& Interface
- 58. Q & AIntroductionTo ORMObject Relational Mapping, is a database design approach that simplifies managing complex databases for programmers.
- 59. The ORM layerin a PHP can make “objects” stored in a database behave like actual objects from a programming perspective.
- 60. This way creatinga new user would become very simple such as $user->new().MVC RecapThe user interacts with the user interface in some way. The controller handles the input event from the user interface, and converts the event into an appropriate user action, understandable for the model.The controller notifies the model of the user action, possibly resulting in a change in the model's state. A view queries the model in order to generate an appropriate user interface. The view gets its own data from the modelThe user interface waits for further user interactions, which restarts the control flow cycle.
- 61. ORM ImplementationsBy abstractingactual database access, web development can be more productive and result in more reliable applications.
- 62. ORM is verycommon among PHP frameworks, such as:<?phpclass Student extends DataMapper{public $has_many= array('course');public function __construct() { parent::__construct(); }}class Course extends DataMapper{public $has_many= array('student');public function __construct() { parent::__construct(); }}?>ORM Student ExampleModel classes for the student DB application.The classes represent entities which were defined in the DB.The classes could be given any relevant functionality.
- 63. class Students extendsController {public function __construct() {parent::__construct();$this->load->model('Student');$this->load->helper('url');$this->load->library('pagination'); }function index($offset=0) { $student_list= new Student();$total_rows= $student_list->count();$student_list->order_by('name'); $data['student_list'] = $student_list->get(5, $offset)->all; $config['base_url'] = site_url("students");$config['total_rows'] = $total_rows;$config['per_page'] = '5';$this->pagination->initialize($config); $this->load->view('student/index', $data); } }ORM Student ExampleController class for the students DB application.The class represents the functionality if the DB in queries.The classes could be given any relevant functionality.
- 64. <table><tr><th>Name</th><th>Course</th></tr><?php$ctr= 0; ?><?phpforeach($student_listas$student): ?><?php$student->course->get()->order_by('name'); ?><?php$ctr++ ?><?phpif ($ctr% 2): ?><tr><td><?= $student->name ?></td><td><?foreach($student->course->all as $course): ?><?= $course->name ?><br/><?endforeach?></td></tr><?phpelse: ?><trclass="odd"><td> <?= $student->name ?></td><td> <?foreach($student->course->all as $course): ?><?= $course->name ?><br/><?endforeach?></td></tr><?phpendif; ?><?phpendforeach?></table>ORM Student ExampleViewer class for the students DB application.The class represents the UI that is presented to the user.The classes could be given any relevant design.
- 65.