PDA

View Full Version : Build a GridPanel from a database



Erwan62
14 May 2009, 5:57 AM
Hi everybody,

I'm currently programing a Web application based on the Ext JS framework, and I need to build a GridPanel with data loaded from a MySQL database.

I looked for tutorials on the internet to understand how to do that, but I didn't find anything that could help me.

I have seven fields in my database : 'civilite', 'nom', 'prenom', 'service', 'tel_societe', 'poste', 'email'.

I just want to build a grid with the data contained into these fields.

Is that possible to catch data in my database in a variable into a PHP script, and use this variable in my JS script to build my grid, or something like that ?

Thanks for your help...

javarine
14 May 2009, 6:25 AM
You can just use the template in the Array Grid Example.
http://www.extjs.com/deploy/dev/examples/grid/array-grid.html

Just bring your mysql data up to the view and build a javascript object array.


var myData = [
['3m Co',71.72,0.02,0.03,'9/1 12:00am'],
['Alcoa Inc',29.01,0.42,1.47,'9/1 12:00am'],
['Altria Group Inc',83.81,0.28,0.34,'9/1 12:00am'],
['American Express Company',52.55,0.01,0.02,'9/1 12:00am'],
['American International Group, Inc.',64.13,0.31,0.49,'9/1 12:00am'],
['AT&T Inc.',31.61,-0.48,-1.54,'9/1 12:00am'],
['Boeing Co.',75.43,0.53,0.71,'9/1 12:00am']
];
Then you can hook up a simple store with your fields as columns



// create the data store
var store = new Ext.data.SimpleStore({
fields: [
{name: 'civilite'},
{name: 'nom'},
{name: 'prenom'},
{name: 'service'},
{name: 'tel_societe'},
{name: poste},
{name: email}
]
});


Then hook it up in your grid like in the Array Grid example and you should be good to go.

Erwan62
14 May 2009, 6:51 AM
Thanks for answering :)

Indeed, I thought the best solution would be to build an array like myData in the example, but I don't really understand how to do it (:|

I suppose I have to do it in a PHP script. So, each rows of the variable myData stands for a row in my database...

But how do we do to build this array ?


$result = array();

$sql = mysql_query("SELECT * FROM salaries");

while($data = mysql_fetch_array($sql))
{
$results = array("civilite" => $data['civilite'], "nom" => $data['nom'], "prenom" => $data['prenom'], "service" => $data['service'], "tel_soc" => $data['tel_soc'], "poste" => $data['poste'], "email" => $data['email']);
}

Something like that ?

And when I built the array in my PHP script, how to send it to my JS script ?

I missed to notice that I had two distinct files : one PHP file and one JS file.

javarine
14 May 2009, 7:00 AM
If you can bring the info to your php file, then build the javascript array in the php file as well. Returning it into a js file is something i haven't done before and I don't even know if it is possible.

SO just run your query and all the Ext stuff in the <script> tags of your php doc.
You can use the js file for stuff that doesn't require dynamic data.

Erwan62
14 May 2009, 7:05 AM
OK, I'll try to do it :)

But according to you, does the array I mentioned above seem to be right or wrong ?

javarine
14 May 2009, 7:33 AM
Hmm:
Well, unfortunately I don't use PHP, yet it seems that one would construct the javascript object array as such.



var myData = [
while($data = mysql_fetch_array($sql))
{

['$data["civilite"]', '$data["nom"]', '$data["prenom"]', '$data["service"]', '$data["tel_soc"]', '$data["poste"]', '$data["email"]'],

}


];


I'm missing some php stuff, yet i hope that helps.
later on,

Erwan62
14 May 2009, 7:46 AM
OK thanks, I'll try it and communicate the results as soon as possible ;)

conorarmstrong
14 May 2009, 7:51 AM
Thanks for answering :)


$result = array();

$sql = mysql_query("SELECT * FROM salaries");

while($data = mysql_fetch_array($sql))
{
$results = array("civilite" => $data['civilite'], "nom" => $data['nom'], "prenom" => $data['prenom'], "service" => $data['service'], "tel_soc" => $data['tel_soc'], "poste" => $data['poste'], "email" => $data['email']);
}




you're overwriting your array each time you loop. should be:



while($data = mysql_fetch_array($sql))
{
$results[] = array("civilite" => $data['civilite'], "nom" => $data['nom'], "prenom" => $data['prenom'], "service" => $data['service'], "tel_soc" => $data['tel_soc'], "poste" => $data['poste'], "email" => $data['email']);
}


or better still (if the db is returning only the rows you want)



while($data = mysql_fetch_array($sql))
{
$results[] = $data;
}


finished code in php would probably looks something like:



$output = array();
$output['data'] = array();

$sql = mysql_query("SELECT * FROM salaries");

while($data = mysql_fetch_array($sql))
{
$results['data'][] = $data;
}
$output['results'] = sizeof ( $results['data']);

echo json_encode($output);

Erwan62
15 May 2009, 12:27 AM
OK, I've built with PHP an array based on the template evoced by Javarine above.

Here's the PHP code :


$cpt = 0;
$results = array();

$sql = "SELECT civilite,nom,prenom,service,tel,poste,email FROM salaries";
$req = mysql_query($sql);

while($data = mysql_fetch_array($req))
{
$obj = array($data['civilite'],$data['nom'],$data['prenom'],$data['service'],$data['tel'],$data['poste'],$data['email']);

$results[$cpt] = $obj;

$cpt++;
}I think this code is right because the function print_r shows something like this :


Array ( [0] => Array ( [0] => Mr. [1] => Sanchez [2] => Adrien [3] => Informatique [4] => 06-18-52-63-87 [5] => 18 [6] => [email protected] ) [1] => Array ( [0] => Mr. [1] => Gates [2] => Bill [3] => Direction [4] => 06-59-88-71-32 [5] => 52 [6] => [email protected] ) [2] => Array ( [0] => Mme. [1] => Dupont [2] => Jeanne [3] => Commercial [4] => 06-52-33-93-54 [5] => 13 [6] => [email protected] ) )But now, how can I do to send the $results array in my JS code (in the same page now) ? I tried to send PHP variable to JS variable with var test = <?php echo $t; ?>; for example, but it doesn't work...

Conorarmstrong, what can I do with the json_encode function ? That's to use the JsonReader ? But what values do I have to put int the JsonReader properties ('root', etc.) ?

And what kind of proxy do I have to use in my GoupingStore ? A HttpProxy with the '.' value in the 'url' property (my PHP and my JS are now in the same page) ?

Thanks

conorarmstrong
15 May 2009, 1:46 AM
You should have based the code on what I posted earlier - not on Javarine's version.

At the moment you are creating indexed arrays which are not going to work well when importing into the jsonreader. You should be using associative ones - have a look at: http://uk2.php.net/manual/en/language.types.array.php

The version I posted would output a json string similar to



{"data":[{"civilite":"Mr.","nom":"Sanchez","prenom":"Adrien","service"
:"Informatique","tel":"06-18-52-63-87","poste":"ABCDEFG", "email":"[email protected]"},{"civilite":"","nom":"","prenom":"","service"
:"","tel":"","town":"","poste":"", "email":""}],"results":2}



I've left the data out of the second row because I couldn't be bothered with all the cutting a pasting.


In the case of output like this, the "root" config option should be set to "data" in the jsonreader. the "totalProperty" config option would be set to "results" (you'll need the "totalProperty" to use paging etc, so it's good to have it)

conorarmstrong
15 May 2009, 2:03 AM
And what kind of proxy do I have to use in my GoupingStore ? A HttpProxy with the '.' value in the 'url' property (my PHP and my JS are now in the same page) ?



Depends on your code, but I'd imagine a HttpProxy containg the url of your PHP script. You need to ensure that your PHP script outputs NOTHING other than the json data.

Erwan62
15 May 2009, 2:19 AM
Hum, I think I begin to understand...

So, I based my code on the lines you posted before, but it returns the following string with the json_encode function in my PHP code :


{"data":[],"results":3}

Data don't appear in the json string :S

I tried to modify your code to obtain data in the string :


$cpt = 0;
$output = array();
$output['data'] = array();

$sql = "SELECT civilite,nom,prenom,service,tel,poste,email FROM salaries";
$req = mysql_query($sql);

while($data = mysql_fetch_array($req))
{
$obj = array($data['civilite'],$data['nom'],$data['prenom'],$data['service'],$data['tel'],$data['poste'],$data['email']);

$output['data'][$cpt] = $obj;

$cpt++;
}

$output['results'] = sizeof($output['data']);

echo json_encode($output);

It returns the following string :


{"data":[["Mr.","Sanchez","Adrien","Informatique","06-16-81-55-31","18","[email protected]"],["Mr.","Gates","Bill","Direction","06-59-88-71-32","52","[email protected]"],["Mme.","Dupont","Jeanne","Commercial","06-52-33-93-54","13","[email protected]"]],"results":3}

This time, the keys don't appear in the data :S

There should be a little modification to proceed in your code to build the right json string, but I don't know what...

Erwan62
15 May 2009, 2:41 AM
Sorry for double posting, but I have some news :$

I managed to generate the json string with the following code :


$cpt = 0;
$output = array();
$output['data'] = array();

$sql = "SELECT civilite,nom,prenom,service,tel,poste,email FROM salaries";
$req = mysql_query($sql);

while($data = mysql_fetch_array($req))
{
$obj = array("civilite" => $data['civilite'],"nom" => $data['nom'],"prenom" => $data['prenom'],"service" => $data['service'],"tel" => $data['tel'],"poste" => $data['poste'],"email" => $data['email']);

$output['data'][$cpt] = $obj;

$cpt++;
}

$output['results'] = sizeof($output['data']);

echo json_encode($output);

That returns this string :


{"data":[{"civilite":"Mr.","nom":"Sanchez","prenom":"Adrien","service":"Informatique","tel":"06-16-81-55-31","poste":"18","email":"[email protected]"},{"civilite":"Mr.","nom":"Gates","prenom":"Bill","service":"Direction","tel":"06-59-88-71-32","poste":"52","email":"[email protected]"},{"civilite":"Mme.","nom":"Dupont","prenom":"Jeanne","service":"Commercial","tel":"06-52-33-93-54","poste":"13","email":"[email protected]"}],"results":3}

I think that is the json string we were looking for...

But when I include it in my JsonReader, the data don't appear in the list -_-"

On test.js, here's my JsonReader :


var reader = new Ext.data.JsonReader({
root : 'data',
totalProperty : 'results'
},[
{name : 'civilite'},
{name : 'nom'},
{name : 'prenom'},
{name : 'service'},
{name : 'tel'},
{name : 'poste'},
{name : 'email'}
]);

Here's my datastore :


var ds = new Ext.data.GroupingStore({
proxy : new Ext.data.HttpProxy({
url : './test2.php'
}),
reader : reader, // Reader associé
sortInfo : {field: 'nom', direction: "ASC"}, // Propriétés de tri des élements à l'affichage de la grille
groupField : 'service' // Champ sur lequel les données sont groupées à l'affichage de la grille
});

In test2.php, here's my code :


$cpt = 0;
$output = array();
$output['data'] = array();

$sql = "SELECT civilite,nom,prenom,service,tel,poste,email FROM salaries";
$req = mysql_query($sql);

while($data = mysql_fetch_array($req))
{
$obj = array("civilite" => $data['civilite'],"nom" => $data['nom'],"prenom" => $data['prenom'],"service" => $data['service'],"tel" => $data['tel'],"poste" => $data['poste'],"email" => $data['email']);

$output['data'][$cpt] = $obj;

$cpt++;
}

$output['results'] = sizeof($output['data']);

echo json_encode($output);

test.js and test2.php are in the same directory, I don't understand why it doesn't work with the correct Json data...

conorarmstrong
15 May 2009, 3:15 AM
You're not loading the store.

Erwan62
15 May 2009, 3:45 AM
I load it after I render the grid ;)

Here's the full code of test.php, if it could help you to identify the problem :


<!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>
<title>ALTERNATIVE Formation</title>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<!-- Scripts relatifs à la librairie Extjs -->
<script type="text/javascript" src="../../extjs/adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="../../extjs/ext-all.js"></script>
<script type="text/javascript" src="../../extjs/ext-all-debug.js"></script>

<!-- Feuilles de style -->
<link rel="stylesheet" type="text/css" href="../../extjs/resources/css/ext-all.css"/>
<link rel="stylesheet" type="text/css" href="../../style2.css"/>

<!-- Script JavaScript -->
<script language="javascript">

/* Fonction Extjs appellée au chargement de la page */

Ext.onReady(function(){

/* Initialisation des info-bulles */

Ext.QuickTips.init();

/* Création d'un layout englobant notre application */

var viewport = new Ext.Viewport({
title : 'Application',
layout : 'border',
items : [
{
title : 'ALTERNATIVE Formation',
region : 'north',
margins : '5 10 2.5 10'
},
{
title : 'Aide',
region : 'south',
height : 150,
margins : '2.5 10 5 10',
collapsible : true,
contentEl : 'aide_apprenants'
},
{
title : 'Navigation',
region : 'west',
margins :'2.5 2.5 2.5 10',
cmargins : '5 5 0 0',
collapsible : true,
width : 250,
contentEl : 'menu'
},
{
title : 'Apprenants',
region : 'center',
autoScroll : true,
collapsible : false,
margins : '2.5 10 2.5 2.5',
paddings : '5 5 5 5',
contentEl : 'grille_apprenants'
}
]
});

/* Menu */

// Panel de gestion des apprenants

var p1 = new Ext.Panel({
title : 'Gestion des apprenants', // Titre du panel
collapsible : true, // Repliable
renderTo : 'menu', // Div contenant le panel
width : 200, // Largeur du panel
contentEl : 'panel_groupes' // Contenu du panel
});

// Panel de gestion des groupes

var p2 = new Ext.Panel({
title : 'Gestion des groupes',
collapsible : true,
collapsed : true, // Replié à l'ouverture de la page
renderTo : 'menu',
width : 200,
contentEl : 'panel_apprenants'
});

/* Création de la grille contenant les informations sur les apprenants */

// Données de la grille

/*var data = [
['Mr.','Sanchez','Adrien','Informatique','03-16-81-55-31',18,'[email protected]'],
['Mme.','Dupond','Jeanne','Commercial','03-52-96-41-18',52,'[email protected]'],
['Mr.','Durant','Pierre','Informatique','03-14-89-96-93',24,'[email protected]'],
['Mr.','Couture','Julien','Communication','03-52-96-41-18',89,'[email protected]'],
['Mme.','Zboinski','Romuald','Commercial','03-52-96-41-18',9,'[email protected]'],
['Mme.','Ricart','Alexandre','Marketing','03-52-96-41-18',11,'[email protected]'],
['Mlle.','Dubuc','Kevin','Informatique','03-52-96-41-18',62,'[email protected]']
];*/

// Création d'un reader caractérisant les champs de notre tableau de données

var reader = new Ext.data.JsonReader({
root : 'data',
totalProperty : 'results'
},[
{name : 'civilite'},
{name : 'nom'},
{name : 'prenom'},
{name : 'service'},
{name : 'tel'},
{name : 'poste'},
{name : 'email'}
]);

// Création d'un datastore stockant les données

var ds = new Ext.data.GroupingStore({
proxy : new Ext.data.HttpProxy({
url : './test2.php'
}),
reader : reader, // Reader associé
sortInfo : {field: 'nom', direction: "ASC"}, // Propriétés de tri des élements à l'affichage de la grille
groupField : 'service' // Champ sur lequel les données sont groupées à l'affichage de la grille
});

// Création d'un objet nous permettant d'utiliser les checkbox

var sm = new Ext.grid.CheckboxSelectionModel();

// Création d'un modèle de colonne désigant les colonnes de la grille

var colModel = new Ext.grid.ColumnModel([
sm,
{header : "Civilit&eacute;", width: 50, sortable: true, dataIndex: 'civilite'},
{header : "Nom", width: 120, sortable: true, dataIndex: 'nom'},
{header : "Pr&eacute;nom", width: 120, sortable: true, dataIndex: 'prenom'},
{header : "Service", width: 120, sortable: true, dataIndex: 'service'},
{header : "T&eacute;l&eacute;phone", width: 120, sortable: true, dataIndex: 'tel'},
{header : "Poste", width: 50, dataIndex: 'poste'},
{header : "Email", width: 140, dataIndex: 'email'}
]);

// Création de la grille

var grid = new Ext.grid.GridPanel({
title : 'Liste des apprenants', // Titre de la grille
ds : ds, // Datastore associé
cm : colModel, // Modèle de colonnes associé
sm : sm, // Utilisation des checkbox
view : new Ext.grid.GroupingView({ // Propriétés de groupement
forceFit : true,
groupTextTpl : '{text} ({[values.rs.length]} {[values.rs.length > 1 ? "membres" : "membre"]})'
}),
tbar : [
{
text : 'Ajouter',
tooltip : 'Ajouter un apprenant',
iconCls : 'add'
},
'-',
{
text : 'Modifier',
tooltip : 'Modifier un apprenant',
iconCls : 'update'
},
'-',
{
text : 'Supprimer',
tooltip : 'Supprimer un apprenant',
iconCls : 'remove',
handler : fct_supp
}
],
autoHeight : true,
width : 751,
frame : true,
stripeRows: true // Style appliqué sur les lignes
});

// Afichage de la grille

grid.render(document.getElementById('grille_apprenants'));

ds.load(); // Chargement du datastore

function fct_supp()
{
Ext.MessageBox.show({
title : 'Suppression d\'apprenants',
msg : 'Etes-vous sur de vouloir faire cela ?',
buttons : Ext.Msg.buttonText = {
yes : "Oui",
no : "Non"
},
icon : Ext.Msg.WARNING // Icône en forme de triangle de signalisation apparaissant dans l'alert
});
}

});

</script>
</head>

<body>

<div id="menu">

<div id="panel_apprenants">

<ul>
<li><a href="">- Lien 1</a></li>
<li><a href="">- Lien 2</a></li>
<li><a href="">- Lien 3</a></li>
</ul>

</div>

<div id="panel_groupes">

<ul>
<li><a href="">- Lien 1</a></li>
<li><a href="">- Lien 2</a></li>
<li><a href="">- Lien 3</a></li>
</ul>

</div>

</div>

<div id="grille_apprenants"></div>

<div id="aide_apprenants">

<p>
Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Sed metus nibh, sodales a, porta at, vulputate eget, dui. Pellentesque ut nisl. Maecenas tortor turpis, interdum non, sodales non, iaculis ac, lacus.
</p>

<p>
Vestibulum auctor, tortor quis iaculis malesuada, libero lectus bibendum purus, sit amet tincidunt quam turpis vel lacus. In pellentesque nisl non sem. Suspendisse nunc sem, pretium eget, cursus a, fringilla vel, urna.
</p>

</div>

</body>

</html>In FireBug, I can see that when I check the network :

POST test2.php

<!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>
<title>ALTERNATIVE Formation</title>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>

<body>

{"data":[{"civilite":"Mr.","nom":"Sanchez","prenom":"Adrien","service":"Informatique","tel":"06-16-81-55-31"
,"poste":"18","email":"[email protected]"},{"civilite":"Mr.","nom":"Gates","prenom":"Bill","service"
:"Direction","tel":"06-59-88-71-32","poste":"52","email":"[email protected]"},{"civilite":"Mme.","nom"
:"Dupont","prenom":"Jeanne","service":"Commercial","tel":"06-52-33-93-54","poste":"13","email":"d.jeanne
@continental.com"}],"results":3}
</body>

</html>

conorarmstrong
15 May 2009, 5:01 AM
Arrrggghhh!

What's all this about:

<!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>
<title>ALTERNATIVE Formation</title>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>

<body>



and this



</body>

</html>


your PHP shoud output the JSON only. So no HTML whatsoever.

Erwan62
15 May 2009, 5:16 AM
That's it !

Thank you very much Conorarmstrong, I work on this grid since 2 days for a stupid error like this -_-"

Now, thanks to you, it works like a charm =D

Thank you again and again, bye :)

conorarmstrong
15 May 2009, 5:18 AM
a pleasure