Connecting ExtJS MySql PHP

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.foscode.com | becasuse code should be free

16 thoughts on “Connecting ExtJS MySql PHP

  1. Pingback: ExtJS Dynamic Grid Search | foscode

  2. Pingback: Tweets that mention Connect extjs with mysql using php | foscode -- Topsy.com

  3. Pingback: Populate ExtJs Datastore with MySql Data | foscode

  4. Kindly tutor me about cascading combo box having continent , country and state with extjs,php,mysql and json store.

    thanks in advance

  5. @samir, thanks for stopping by, I’ll post an article within the next few weeks with a step by step tutorial on this, since I think others would also benefit from it.

  6. Pingback: Extjs Cascading (Dependent) Combo Boxes | foscode

  7. @samir: You’re welcome, glad you have come back. Hopefully this tutorial and others I have will be able to help you out and get you on your way to continuing developing your app.

  8. can you have share about tutorial in compress,.. (.zip) where can found link download full tutorial.zip

    thanks…

  9. @panega Thanks for stopping by, but what compressed file are you looking for? I don’t have any attached to the tutorial, but I can try to explain a little more if you have questions about how to go connect extjs with mysql, in case the explanations on this tutorial didn’t make sense. Please let me know.

  10. I want to create a CRUD with PHP and ExtJs added user login permissions, and each user can only view the data on entry Just do it, if you can give a little tutorial about it … ?
    thanks

  11. Hi,
    I am new learning here, can you please put a zip file of this example with sql file to create the table, please your help is appreciated.
    thanks
    Mjaid

  12. Hey Dan,

    Could you please upload your source code please ?
    It would be very helpful !

    Thx in advance

Leave a Reply

Your email address will not be published. Required fields are marked *

*


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>