How to Fix Top Five Common PHP Database Problems a Developer Faces

Let suppose that there is only one way to use database correctly… Then you will hire php developer india to create database design, database access and then write PHP business logic code that sits on top of it in any number of ways and finally you often end up getting it wrong.

In this article, we are going to describe five common problems in PHP database design and how to fix these problems when it appears.

Problem 1: Using MySQL directly in PHP code

There is one major common problem is older PHP code, it’s using the mysql functions to access the database directly. Following PHP program shows how to access the database directly.

Program 1. Access/get.php

<?php

functionget_user_id( $name )

{

  $db= mysql_connect( ‘localhost’, ‘root’, ‘password’ );

  mysql_select_db( ‘users’ );

  $res= mysql_query( “SELECT id FROM users WHERE login='”.$name.”‘” );

  while( $row= mysql_fetch_array( $res ) ) { $id = $row[0]; }

  return $id;

}

var_dump(get_user_id( ‘jack’ ) );

?>

Note:

• The mysql_connect function has been used to access the database.
• The query in which we use string concatenation to add the $name parameter to the query.

Above technique has two good alternatives:

1- PEAR DB module:

In it, the other value in using the abstraction layers of the PEAR DB module.

2- PHP Data Objects (PDO) classes:

PHP Data Objects is that you can use the "?" operator in your SQL statements. By doing this, you makes the SQL easier to maintain and secures your application from any type of SQL injection attacks.

Alternative code is given below by using PEAR DB.

Program 2. Access/get_good.php

<?php

require_once(“DB.php”);

functionget_user_id( $name )

{

  $dsn= ‘mysql://root:password@localhost/users’;

  $db =& DB::Connect( $dsn, array() );

  if (PEAR::isError($db)) { die($db->getMessage()); }

  $res = $db->query( ‘SELECT id FROM users WHERE login=?’,

  array( $name ) );

  $id = null;

  while( $res->fetchInto( $row ) ) { $id = $row[0]; }

  return $id;

}

var_dump(get_user_id( ‘jack’ ) );

?>

Problem 2: Not using auto-increment functionality in database

Similar to other modern databases, MYSQL database has ability to create auto-incrementing unique identifiers on a per-record basis. In the following code, we still see the code that first runs a SELECT statement to find the maximum id, after then it adds one to that id, as well as a new record.

Program 3 shows a sample bad schema code.

Program 3. Badid.sql

DROP TABLE IF EXISTS users;

CREATE TABLE users (

  id MEDIUMINT,

  login TEXT,

  password TEXT

);

INSERT INTO users VALUES ( 1, ‘jack’, ‘pass’ );

INSERT INTO users VALUES ( 2, ‘joan’, ‘pass’ );

INSERT INTO users VALUES ( 1, ‘jane’, ‘pass’ );

Here the id field is specified simply as an integer value and it should be unique value. We can add any value as we like as it’s shown in the INSERT statements of that follow the CREATE statement.

Following program shows the PHP code that adds users into this type of schema.

Program 4. Add_user.php

<?php

require_once(“DB.php”);

functionadd_user( $name, $pass )

{

  $rows= array();

  $dsn= ‘mysql://root:password@localhost/bad_badid’;

  $db =& DB::Connect( $dsn, array() );

  if (PEAR::isError($db)) { die($db->getMessage()); }

  $res = $db->query( “SELECT max(id) FROM users” );

  $id = null;

  while( $res->fetchInto( $row ) ) { $id = $row[0]; }

  $id += 1;

  $sth = $db->prepare( “INSERT INTO users VALUES(?,?,?)” );

  $db->execute( $sth, array( $id, $name, $pass ) );

  return $id;

}

$id = add_user( ‘jerry’, ‘pass’ );

var_dump( $id );

?>

Code written in add_user.php file will first performs a query to find the maximum value of the id. Then the file runs an INSERT statement with the id value plus one. But this above code could fail in race conditions.

So what’s the alternative?

 Now use the auto-increment feature in MySQL to create unique IDs for each insertion automatically.

Program 5. Goodid.php

DROP TABLE IF EXISTS users;

CREATE TABLE users (

  id MEDIUMINT NOT NULL AUTO_INCREMENT,

  login TEXT NOT NULL,

  password TEXT NOT NULL,

  PRIMARY KEY( id )

);

INSERT INTO users VALUES ( null, ‘jack’, ‘pass’ );

INSERT INTO users VALUES ( null, ‘joan’, ‘pass’ );

INSERT INTO users VALUES ( null, ‘jane’, ‘pass’ );

Here in above code we have added the NOT NULL flag to indicate that the fields must not be null and also added the AUTO_INCREMENT flag to indicate that the field is auto-incrementing.By doing these changes, we can speed things up a bit.

Following program shows the updated PHP code.

Program 6. Add_user_good.php

<?php

require_once(“DB.php”);

functionadd_user( $name, $pass )

{

  $dsn= ‘mysql://root:password@localhost/good_genid’;

  $db =& DB::Connect( $dsn, array() );

  if (PEAR::isError($db)) { die($db->getMessage()); }

  $sth = $db->prepare( “INSERT INTO users VALUES(null,?,?)” );

  $db->execute( $sth, array( $name, $pass ) );

  $res = $db->query( “SELECT last_insert_id()” );

  $id = null;

  while( $res->fetchInto( $row ) ) { $id = $row[0]; }

  return $id;

}

$id = add_user( ‘jerry’, ‘pass’ );

var_dump( $id );

?>

Now this above code is simpler andmore efficient than the original version (Program 4) and its related schema.

If you are struggling with any such type problem or any other PHP database related problems then hire php developer india. AResourcepool is a reputed PHP development company and we can help you to solve any type of PHP related Problems. We have 24×7 technical support team to assist you by providing quality services.