About YAHOO.ext.grid.Grid + PHP + XML

[wolverine4277]

I have been trying work with YAHOO.ext.grid.Grid + PHP + XML and finally i got an example working.
All works fine but the performance maybe improved...
I don't know if some of the functions that i use can be improved somehow... for example the function colum_name() or maybe another way using less queries, i use 3 query, one for the total count of records, another for obtain the column name, and the last for the data that must be showed in the grid...

<test.php>

<?php
function total_count() {

if ($resultado_mysql = mysql_query('SELECT COUNT(*) FROM items')) {
if ($fila = mysql_fetch_row($resultado_mysql)) {
return $fila[0];
} else {
// Something usefull for the xml that contains the error
}
} else {
// Something usefull for the xml that contains the error
}
}


function column_name($column_index) {

$result = @mysql_query('SHOW COLUMNS FROM items');
if ($result) {
if ($row = @mysql_fetch_row($result)) {
if (@mysql_data_seek($result, $column_index)) {
return $row[$column_index];
} else {
// Something usefull for the xml that contains the error
}
} else {
// Something usefull for the xml that contains the error
}
} else {
// Something usefull for the xml that contains the error
}
}


function to_xml($sql_query) {

if ($resul = @mysql_query($sql_query)) {
$xml_doc = "<?xml version='1.0' encoding='iso-8859-1' ?>";
$xml_doc.= "\n<Result>\n";
$xml_doc.= "\n<Items>\n";
while($row = @mysql_fetch_assoc($result)) {
$xml_doc.= "\t<Item>\n";
while(list($key, $value) = each($row)) {
$xml_doc.= "\t\t<" . $key . ">" . $value . "</" . $key . ">\n";
}
$xml_doc.= "\t</Item>\n";
}
$xml_doc.= "</Items>";
$xml_doc.= "\n<TotalCount>" . total_count() . "</TotalCount>";
$xml_doc.= "\n</Resultado>";
} else {
// Something usefull for the xml that contains the error
$xml_doc = ...
}
return $xml_doc;
}

$xml_doc = '';
$offset = $_POST['pageSize'];
$from = $offset*($_POST['page'] - 1);
if ($conn = @mysql_connect('server', 'user', 'password')) {
if (@mysql_select_db('test')) {
$sql_query = 'SELECT *' . "\n";
$sql_query.= 'FROM items' . "\n";
if (isset($_POST['sortDir']) && !empty($_POST['sortDir'])) {
$sql_query.= 'ORDER BY ' . column_name($_POST['sortColumn']) . ' ' . $_POST['sortDir']. "\n";
}
$sql_query.= 'LIMIT ' . $from . ', ' . $offset;
$xml_doc = to_xml($sql_query);
} else {
// Something usefull for the xml that contains the error
$xml_doc = ...
}
} else {
// Something usefull for the xml that contains the error
$xml_doc = ...
}
header('Content-type: text/xml');
echo $xml_doc;
?>

[rodiniz]

I use 2 querys . One for the count and one for the results..the query that gets the results has the columns I want ..so there is no need for a thrid query.

[BernardChhun]

I use 2 querys . One for the count and one for the results..the query that gets the results has the columns I want ..so there is no need for a thrid query.

rodiniz is right on that one. Why do you need the column names?

[wolverine4277]

I know that, but the post parameter sortColumn that is needed for remote sorting is a number (the index of column in the grid), is for this that i use a third query, maybe that i can do is send the column name as another post parameter, i will search for this option.

[BernardChhun]

I know that, but the post parameter sortColumn that is needed for remote sorting is a number (the index of column in the grid), is for this that i use a third query, maybe that i can do is send the column name as another post parameter, i will search for this option.

ohh right. My solution was to make a php array containing the same data as the schema for my grid.

Correct me if I'm wrong but I think I've read somewhere that the new grid will send the column name to the php script.

[wolverine4277]

I don't find the name in the list of parameters passed with the request, but the list of parameters can be changed and i do that when i can :D

/** Maps named params to url parameters - Override to specify your own param names */
this.paramMap = {'page':'page', 'pageSize':'pageSize', 'sortColumn':'sortColumn', 'sortDir':'sortDir'};

[Animal]

No metadata in the result of PHP's SQL query then? 8)

[wolverine4277]

Animal, i don't understand your post, i'm from Argentina and my English isn't the best, maybe is that :(
But with the query "SHOW COLUMNS FROM items" i'm getting certain metadata from the table items. The data that i need is the column name associated with a column index, and i get this from query.
I had been looking for some functions that makes the trick but i don't find them, maybe you know some.

[Animal]

Not in PHP.

With Java, the Object that is returned from an SQL query contains the data, and metadata - that is data about the data: column names, data types etc.

So you only need 2 queries: "SELECT COUNT(*)" and "SELECT *"

[wolverine4277]

I know that, but with PHP i don't find anything similar to java. Then i have to do the trick by myself :wink:

[pomata]

you can use SQL_CALC_FOUND_ROWS in mysql....

P

[alien3d]

I sucessly deploy php mysql and yui-ext around something 20 table and 10 module.
Suggestion

1)Beware of certain type of charater like ',$ change to a proper way like space into +
2)addcslashes for certain qoute strip up
3)No need for third party xml.You just code directly into the page
4)Auto generate table is totally bad idea.


** Try to use sortby.int and sortby date but not working.Do no why.Is there any format date can be parse up?
** mysql_num_rows for total query
**Sample can be gave but the code quite long