PHP PDO datagrid conversion

[newpie]

Hello, I am trying to pull data from a MySQL database with PHP PDO code and trying to convert and put in datagrid format.

I am using the format as below (see site: http://php.net/manual/en/pdostatement.fetchall.php)

Code: Select all

<?php
$sth = $dbh->prepare("SELECT * FROM employees");
$sth->execute();

$result = $sth->fetchAll();
print_r($result);
?>

It returns the following list of tuples:

Array
(
[0] => Array
(
[idAI] => 1
[0] => 1
[EmpId] => 987
[1] => 987
[Name] => James Smith
[2] => James Smith
)

[1] => Array
(
[idAI] => 2
[0] => 2
[EmpId] => 123
[1] => 123
[Name] => Jane Robert
[2] => Jane Robert
)

[2] => Array
(
[idAI] => 3
[0] => 3
[EmpId] => 456
[1] => 456
[Name] => Tom Jones
[2] => Tom Jones
)

)

I would like to load it into my datagrid, but I believe the format is not appropriate. What is the appropriate format for datagrid? I don't mind changing my PDO prepared statement either if it makes it easier for Livecode in the end / or increases performance. Most likely I will have to use a repeat loop of some kind to help me convert it to a variable.

Any help would be appreciated, thanks.


This is what I used previously, but I would like to use PHP for security reasons.

Code: Select all

   global gConnectionID

on mouseUp
   connectToDB
   ## Connect to the database
   ## Query the database for contact details to be displayed in the field
   put "SELECT * from employees" into tSQL
   put revDataFromQuery(tab,return,gConnectionID,tSQL) into tRecords
   set the dgText of group "DataGrid 1" to tRecords
   closeDB gConnectionID 
end mouseUp

[MaxV]

You could use this:

Code: Select all

<?php
$pdo = new PDO(...);
$results = $pdo->query("SELECT * FROM myTable INTO OUTFILE 'data.txt'   FIELDS TERMINATED BY '\t'   LINES TERMINATED BY '\n'  ");
$dummy = $result->fetchAll();

The data.txt file will be written on the MySQL server. The directory must be writable by the uid of the mysqld process. It will not overwrite any existing file, and requires that you have the FILE SQL privilege.

This way you have all data in the datagrid format. Just use

Code: Select all

put URL "http://where.is.myfile/data.txt" into temp
set the dgText of group "mydtagrid" to temp

Please read this: http://livecode.wikia.com/wiki/Datagrid_API#dgText
Please not that your data should not contains any TAB or return char; otherwise, obviously, you'll split your data across the datagrid.

If your data contains the special chars TAB and return, please change that chars with special chars like § and # (or else), and then create the data.txt file.
After importing in the database, revert data to the original chars.

[newpie]

Thank you MaxV, I appreciate the quick reply, I am concerned about storing the sensitive data outside of the MySQL database on the server. I wonder if I can do the same type of thing but echo it instead at the end of the PHP PDO coding. Possible?

[MaxV]

You don't need to store data, you just export data.
However you can use livecode to talk directly with MySQL:

########CODE#######
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into connID
set the connDB of this stack to connID #so you don't need to use revOpenDataabse again
put "SELECT * FROM myTable ; " into tSQL
put revDataFromQuery(tab,return,connID,tSQL) into tRecords
set the dgText of group "mydtagrid" to tRecords
#####END OF CODE#####

See: http://lessons.livecode.com/m/4071/l/70 ... l-database

[MaxV]

Sorry,
propbably I misunderstood yuor question.
Yes, you can echo the result with (this is a table with column name and age):

Code: Select all

echo "<table>";
echo "<tr><th>Name</th><th>Age</th></tr>";
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	echo "<tr><td>"; 
	echo $row['name'];
	echo "</td><td>"; 
	echo $row['age'];
	echo "</td></tr>"; 
} 
echo "</table>";

[newpie]

Thanks MaxV, I appreciate you taking the time in analyzing this for me. Today while I was researching I found this as well. I have to mod it with Livecode before it works, but another option at least.

Code: Select all

$colcount = $result->columnCount();

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
		echo nl2br("\n\r");
        for ($i = 0; $i < $colcount; $i++){
            $meta = $result->getColumnMeta($i)["name"];
            echo($row[$meta] . ',');	
        }

Thanks again