PHP Database Problems and Solution – AResourcePool

1
Common PHP Problems with Solutions
AResourcePool A Block, Sector 63, Noida, Uttar
Pradesh, India, 201307 Email id -
info_at_aresourcepool.com Website -
www.aresourcepool.com
2
(No Transcript)
3
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 its
  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.

4

• 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,

5

• Program 2. Getfiles.php
• lt?php
• require_once("DB.php")
• functionget_user( name )
•   dsn 'mysql//rootpassword_at_localhost/bad_multi
  1'
•   db DBConnect( dsn, array() )
•   if (PEARisError(db)) die(db-gtgetMessage())
  
•   res db-gtquery( "SELECT id FROM users WHERE
  login?",
•   array( name ) )
•   uid null
•   while( res-gtfetchInto( row ) ) uid
  row0
• return uid
• functionget_files( name )

•   rows array()
•   dsn 'mysql//rootpassword_at_localhost/bad_mult
  i2'
•   db DBConnect( dsn, array() )
•   if (PEARisError(db)) die(db-gtgetMessage())
  
•   res db-gtquery( "SELECT FROM files WHERE
  user_id?",
•   array( uid ) )
•   while( res-gtfetchInto( row ) ) rows
  row
•   return rows
• files get_files( 'jack' )
• var_dump( files )
• ?gt

6

• As we can see from the above code, get_user funct
  ion 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.
  Lets see the following updated PHP code

7

• Program 3. Getfiles_good.php
• lt?php
• require_once("DB.php")
• functionget_files( name )
•   rows array()
•   dsn 'mysql//rootpassword_at_localhost/good_mult
  i'
•   db DBConnect( dsn, array() )
•   if (PEARisError(db)) die(db-gtgetMessage())
  
•   res db-gtquery(
•   "SELECT files. FROM users, files WHERE
•   users.login? AND users.idfiles.user_id",
•   array( name ) )
•   while( res-gtfetchInto( row ) ) rows
  row

8
(No Transcript)
9

• 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 manyto-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.

10

• 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' )

11

• In this given example, the programmer chooses to
  create a files field that containing a list of
  file ids 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 state
  ment for each file.
• Just see the following code.

12

• Program 2. Get.php
• lt?php
• require_once("DB.php")
• functionget_files( name )
•   dsn 'mysql//rootpassword_at_localhost/bad_norel
  '
•   db DBConnect( dsn, array() )
•   if (PEARisError(db)) die(db-gtgetMessage())
  
•   res db-gtquery( "SELECT files FROM users
  WHERE login?",
•   array( name ) )
•   files null
•   while( res-gtfetchInto( row ) ) files
  row0
•   rows array()
•   foreach( split( ',',files ) as file )
•   
•     res db-gtquery( "SELECT FROM files WHERE
  id?",

13

• 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

14

• 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' )

15

• Now see the corresponding PHP code.
• Program 4. Get_good.php
• lt?php
• require_once("DB.php")
• functionget_files( name )
•   dsn 'mysql//rootpassword_at_localhost/good_rel'
  
•   db DBConnect( dsn, array() )
•   if (PEARisError(db)) die(db-gtgetMessage())
  
•   rows array()
•   res db-gtquery(
•     "SELECT files. FROM users,files WHERE
  users.login?
•       AND users.idfiles.user_id",
•         array( name ) )
•   while( res-gtfetchInto( row ) ) rows
  row
•   return rows

16
Contact Us

• AResourcePool
• Noida, India (Research Head Office)
• A Block, Sector 63, Noida, Uttar Pradesh,
• India, 201307
• Mobile No 91-9711621550
• Email id- info_at_aresourcepool.com
• Website - www.aresourcepool.com