1 / 16

PHP Database Problems and Solution – AResourcePool

In this presentation, we are going to solve some common PHP database issues that we face while developing any project. Here we have discussed how you can use multiple databases in your PHP project. We have given here different programs for the solution. These solutions are provided by the expert of AResourcePool. AResourcePool is a well known Web Development Company in Noida. We provide our reliable services in Feel free to contact us anytime and know more about us.<br>AResourcePool <br>Website: https://www.aresourcepool.com/<br>Email-id: info@aresourcepool.com <br>Contact No: 91-9711621550<br>

aresourcepool
Download Presentation

PHP Database Problems and Solution – AResourcePool

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. Common PHP Problems with Solutions AResourcePool A Block, Sector 63, Noida, Uttar Pradesh, India, 201307 Email id - info@aresourcepool.com Website - www.aresourcepool.com

  2. Problem 1: Using multiple databases There may be many scenario when we see an application in which each table is in a separate database. We will do this for extraordinarily large databases, but for an average or small application, we don't needed this level of segmentation. In this process, the syntax became complex and backup and restore of database is not easily manageable. This syntax may or may not work between different database and it’s difficult to maintain the relational structure when the tables are split over multiple databases engines. Following program will shows this data divided into four files.

  3. Program 1. The database files Files.sql: CREATE TABLE files ( id MEDIUMINT, user_id MEDIUMINT, name TEXT, path TEXT ); Load_files.sql: INSERT INTO files VALUES ( 1, 1, 'test1.jpg', 'files/test1.jpg' ); INSERT INTO files VALUES ( 2, 1, 'test2.jpg', 'files/test2.jpg' ); Users.sql: DROP TABLE IF EXISTS users; CREATE TABLE users ( id MEDIUMINT, login TEXT, password TEXT ); Load_users.sql: INSERT INTO users VALUES ( 1, 'jack', 'pass' ); INSERT INTO users VALUES ( 2, 'jon', 'pass' ); Now following is the PHP code to query the database for the files associated with a particular user.

  4. Program 2. Getfiles.php $rows = array(); <?php require_once("DB.php"); $dsn = 'mysql://root:password@localhost/bad_multi2'; $db =& DB::Connect( $dsn, array() ); if (PEAR::isError($db)) { die($db->getMessage()); } functionget_user( $name ) { $dsn= 'mysql://root:password@localhost/bad_multi1'; $db =& DB::Connect( $dsn, array() ); if (PEAR::isError($db)) { die($db->getMessage()); } $res = $db->query( "SELECT * FROM files WHERE user_id=?", array( $uid ) ); while( $res->fetchInto( $row ) ) { $rows[] = $row; } $res = $db->query( "SELECT id FROM users WHERE login=?", array( $name ) ); $uid = null; while( $res->fetchInto( $row ) ) { $uid = $row[0]; } return $rows; return $uid; } } $files = get_files( 'jack' ); functionget_files( $name ) { $uid = get_user( $name ); var_dump( $files ); ?>

  5. As we can see from the above code, get_user function connects to the database which contains the users table and then retrieves the ID for a given user and the get_files function will connect to the files table and then retrieves the file rows associated with the given user. But the better way to do all this is to load the data into one database, then perform a query. Let’s see the following updated PHP code

  6. Program 3. Getfiles_good.php <?php require_once("DB.php"); functionget_files( $name ) { $rows= array(); $dsn= 'mysql://root:password@localhost/good_multi'; $db =& DB::Connect( $dsn, array() ); if (PEAR::isError($db)) { die($db->getMessage()); } $res = $db->query( "SELECT files.* FROM users, files WHERE users.login=? AND users.id=files.user_id", array( $name ) ); while( $res->fetchInto( $row ) ) { $rows[] = $row; } return $rows; } $files = get_files( 'jack' ); var_dump( $files ); ?> As we can see, the above code is not only shorter but it's also easier to understand and it’s more efficient than previous one. Instead of performing two queries, here we are performing one query.

  7. Problem 2: Not using relations As we know that relational databases aren't like programming languages, they don't have array types, Instead of it, they use relationship among tables to create a one-to-one, one-to-many, many-to-one or many=to- many structure between objects, which has the same effect as an array. When engineers attempts to use a database as though it were a programming language just creating arrays by using text strings with comma-separated identifiers then you will see some problem with applications. Just look at the schema given below.

  8. Program 1. Bad.sql DROP TABLE IF EXISTS files; CREATE TABLE files ( id MEDIUMINT, name TEXT, path TEXT ); DROP TABLE IF EXISTS users; CREATE TABLE users ( id MEDIUMINT, login TEXT, password TEXT, files TEXT ); INSERT INTO files VALUES ( 1, 'test1.jpg', 'media/test1.jpg' ); INSERT INTO files VALUES ( 2, 'test1.jpg', 'media/test1.jpg' ); INSERT INTO users VALUES ( 1, 'jack', 'pass', '1,2' );

  9. In this given example, the programmer chooses to create a files field that containing a list of file id’s and that is separated by commas. If you want to get a list of all the files for a particular user then programmer must first read the row from the users table, after then parse the file's text and run an individual SELECT statement for each file. Just see the following code.

  10. Program 2. Get.php <?php require_once("DB.php"); functionget_files( $name ) { $dsn= 'mysql://root:password@localhost/bad_norel'; $db =& DB::Connect( $dsn, array() ); if (PEAR::isError($db)) { die($db->getMessage()); } $res = $db->query( "SELECT files FROM users WHERE login=?", array( $name ) ); $files = null; while( $res->fetchInto( $row ) ) { $files = $row[0]; } $rows = array(); foreach( split( ',',$files ) as $file ) { $res = $db->query( "SELECT * FROM files WHERE id=?", array( $file ) ); while( $res->fetchInto( $row ) ) { $rows[] = $row; } } return $rows; } $files = get_files( 'jack' ); var_dump( $files ); ?>

  11. Note: the above technique is slow, difficult to maintain and doesn't make good use of the database. Now the only solution is to re-architect the schema to turn it back into a traditional relational form. If you are unable to solve it yourself the hire php developer India. Just see the following PHP code

  12. Program 3. Good.sql DROP TABLE IF EXISTS files; CREATE TABLE files ( id MEDIUMINT, user_id MEDIUMINT, name TEXT, path TEXT ); DROP TABLE IF EXISTS users; CREATE TABLE users ( id MEDIUMINT, login TEXT, password TEXT ); INSERT INTO users VALUES ( 1, 'jack', 'pass' ); INSERT INTO files VALUES ( 1, 1, 'test1.jpg', 'media/test1.jpg' ); INSERT INTO files VALUES ( 2, 1, 'test1.jpg', 'media/test1.jpg' ); Here in the above code, each file is related to the user through the user_id function in the file table.

  13. Now see the corresponding PHP code. Program 4. Get_good.php <?php require_once("DB.php"); functionget_files( $name ) { $dsn= 'mysql://root:password@localhost/good_rel'; $db =& DB::Connect( $dsn, array() ); if (PEAR::isError($db)) { die($db->getMessage()); } $rows = array(); $res = $db->query( "SELECT files.* FROM users,files WHERE users.login=? AND users.id=files.user_id", array( $name ) ); while( $res->fetchInto( $row ) ) { $rows[] = $row; } return $rows; } $files = get_files( 'jack' ); var_dump( $files ); ?> Now in the above code, it’s not complex and we make one query to the database to get all rows.

  14. AResourcePool Noida, India (Research & Head Office) A Block, Sector 63, Noida, Uttar Pradesh, India, 201307 Mobile No: +91-9711621550 Email id- info@aresourcepool.com Website - www.aresourcepool.com

More Related