foscode

Connecting ExtJS MySql PHP

// April 23rd, 2010 // Code

In this ExtJs tutorial I’ll show you how to connect ExtJs to MySql using php. Most ExtJs examples you’ll find on their site usually pertain to static datastores, which in my opinion it isn’t where this framework is best used. I’m planing on this being the first of a series of tutorials that will help you put together a buffered datagrid (your data will load super fast, since it will be loaded on demand — more on this to come) where you’ll be able to search/filter, edit, group and display detailed information you’ll be obtaining from your MySql database (you can see the my previous tutorials here and here).Here are a couple of screenshots of what your datagrid might look, the first one displaying all our MySql data, the second grid with the filter applied (I’ve added 2 datagrids to my panel with independent datastores, you could do the same, or something completely different):

Alight, so now you saw it can actually be done, I’ll show you how to build your mysql connection and “bind” your MySql data to you your datagrid. The first thing we need to do is create the file we’ll connect and upload our data with, we’ll call these connect.php and data.php, just make sure to keep your connect.php someplace safe, so it won’t be in your root folder and end up being displayed on the net for everyone to see your credentials.

connect.php

<?php
# FileName="connect.php"
$hostname = "xx.xx.xx.xx";
$database = "dbname";
$username = "loginname";
$password = "yourpassword";
?>

Ok, now that we have our file to do the connection for us, we need to create the file that will run the query and bring the data so our datagrid can be populated.

data.php

<?php
#First we include our credentials file
include('../Connections/connect.php');
#And now we connect to the database
//connection String
$connect = mysql_connect($hostname, $username, $password)
or die('Could not connect: ' . mysql_error());
//select database
mysql_select_db($database, $connect);
//Select The database
$bool = mysql_select_db($database, $connect);
if ($bool === False){
	print "can't find $database";
}

Our files above look pretty straight forward, and now that we managed to make the connection we’ll bring in the data and store it into a json array:

#First we assign the variables to be used with our search box
$search = $_REQUEST["pattern"]; // This is our search box name
$searchbox = " (PMNumber LIKE '%".$search."%'
                OR Description LIKE '%".$search."%'
		OR DATE_FORMAT((Completed_Date + 0) ,'%m/%d/%y') LIKE '%".$search."%'
                OR ControlNumber LIKE '%".$search."%' '') " ;

# Now we create the first query, the one that gets loaded
# initially, before any searches are submitted

// If this isn't a search just count our records and show our data
if(!isset($search) || $search == '') {
// count the Data
$result = mysql_query("SELECT COUNT(Completion_ID) AS count FROM PM_Completion")
or die ('Could not do count on table: ' . mysql_error());
$row = mysql_fetch_assoc($result);
$count = $row['count'];

// get data and store in a json array
$query = "SELECT Completed_Date, PMNumber, Description, ControlNumber
FROM  PM_Completion" ;
if ($sort != "") {
	$query .= " ORDER BY PMNumber ";
}
$query .= " LIMIT ".$start.",".$limit;
$result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$myInventory[] = array(
        'Completed_Date' => $row['Completed_Date'],
        'PMNumber' => $row['PMNumber'],
	'ControlNumber' => $row['ControlNumber'],
	'Description' => $row['Description']
		);
}

$myData = array('myInventory' => $myInventory, 'totalCount' => $count);
echo json_encode($myData);
mysql_free_result($result);

} else { // We're doing a search, so we'll run this query instead
// Do the count again

$result = mysql_query("ID, Completed_Date, PMNumber, Description, ControlNumber,COUNT(ID) AS count
FROM  PM_Completion
FROM
  PM_Completion
WHERE {$searchbox}
     GROUP BY ID") or die ('Could not do count on table: ' . mysql_error());
$row = mysql_fetch_assoc($result);
$count = $row['count'];

//now we load the grid with only the items searched
$query = "SELECT ID, PMNumber, ControlNumber, Description,
        DATE_FORMAT((Completed_Date + 0) ,'%m/%d/%y')as cd
FROM
  PM_Completion
  HAVING". $searchbox ;

$query .= " ORDER BY PMNumber";

$result = mysql_query($query) or die("SQL Error: " . mysql_error());

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$myInventory[] = array(
        'ID' => $row['ID'],
        'PMNumber' => $row['PMNumber'],
	'ControlNumber' => $row['ControlNumber'],
	'Completed_Date' => $row['Completed_Date'],
	'Description' => $row['Description']
		);
}

$myData = array('myInventory' => $myInventory, 'totalCount' => $count);
echo json_encode($myData);
mysql_free_result($result);
        }
//And we're done
?>

This is it for the connection and data gathering. I hope this step by step tutorial has helped you some. Please stay tuned for my next tutorial, where we’ll see how to add the data we just gathered into our ExtJS datagrid.

Thank you for stopping by and please share with others, after all, code should be free.