Connect Multiple MySQL Database in PHP

Monday, August 15, 2011

If your website move on to a traffic worthy website. you need to work with multiple database for load balancing and overhead distribution. So we need to work with multiple database on single project. MySQL and PHP provide some efficient logic to handle multiple database on single project. Let us see how to work with Multiple database in efficient way.



Example Project working with Multiple Database.

include/connectdb.php

<?php
// Define Connection Details
define(DB_SERVER,"localhost");
define(DB_USER,"root");
define(DB_PASSWORD,"");

// Define Database Names and Table Names
define(DB_CUSTOMER,"w3lessons_customer_db");
define(DB_SERVICE,"w3lessons_service_db");

// Define Table Name prefix with database name
// For Customer Table
define(TBL_CUSTOMER,DB_CUSTOMER.".tbl_customers");
// For Service Table
define(TBL_SERVICE,DB_SERVICE.".tbl_services");

// Connect MySQL Server
$conn=mysql_connect(DB_SERVER,DB_USER,DB_PASSWORD);
?>

index.php

<?php
include("include/connectdb.php");

if(isset($_POST['customer_id']) and $_POST['customer_id']!="")
{
	$sql="select * from ".TBL_CUSTOMER." where customer_id='{$_POST['customer_id']}'";
	$result=mysql_query($sql);
	$row=mysql_fetch_array($result);

//	$customer_id=$row['customer_id'];
//	$email_id=$row['email_id'];
//	$service_id=$row['service_id'];
// Instead of above three lines we can use	
	extract($row);
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Working with Multiple Database :Service Update</title>
<style type="text/css">

<!--
body,td,th {
	font-family: Verdana, Geneva, sans-serif;
	font-size: 11px;
	color: #000;
	font-weight: bold;
}
body {
	margin-left: 10px;
	margin-top: 10px;
}
-->
</style></head>

<body>
<form action="<?=$_SERVER['PHP_SELF']?>" method="post" id="thisForm">

<table border="0" cellspacing="0" cellpadding="7" style="border:1px solid #999;">
  <tr>
    <td colspan="2" bgcolor="#D6D6D6">Customer Service Update Sheet</td>

    </tr>
  <tr>
    <td>Customer Name</td>
    <td><select name="customer_id" id="customer_id" onchange="javascript:document.getElementById('thisForm').submit()">

      <option value="">Select Customer</option>
      <?php
	// Load Service Names from Customer DB
	$sql="select * from ".TBL_CUSTOMER;
	$result=mysql_query($sql);
	while($row=mysql_fetch_array($result))
	{
		if($row['customer_id']==$customer_id)
		echo '<option value="'.$row['customer_id'].'" selected="selected">'.$row['customer_name'].'</option>';
		else		
		echo '<option value="'.$row['customer_id'].'">'.$row['customer_name'].'</option>';
	}							  
	?>

    </select></td>
    </tr>
  <tr>
    <td>Customer Email ID</td>
    <td><input name="email_id" type="text" id="email_id" value="<?=@$email_id?>" /></td>

    </tr>
  <tr>
    <td>Service Requirement</td>
    <td>
      <select name="select" id="select">

        <?php
	// Load Service Names from Service DB
	$sql="select * from ".TBL_SERVICE;
	$result=mysql_query($sql);
	while($row=mysql_fetch_array($result))
	{
		if($row['service_id']==$service_id)
		echo '<option value="'.$row['service_id'].'" selected="selected">'.$row['service_name'].'</option>';
		else
		echo '<option value="'.$row['service_id'].'">'.$row['service_name'].'</option>';
	}							  
	?>

        </select>
    </td>
    </tr>
  </table>
<br />
<br />
<a href="http://www.w3lessons.com/">View Article for Working with Multiple Database</a>

</form>
</body>
</html>

Output:






Download This Script     Download Script

No comments:

Post a Comment