Php and mysql combine

[fjafferi]

Hi there, I just recently found this library and i must say, its pretty impressive. However, I can't seem to find any documentation on how to create a report that i can use both php and mysql in. For instance, i want to run a query through mysql with some filters and then manipulate the results before it gets outputted. (Manipulation i am referring to cannot be done via SQL statement). Please advice.

Thanks.

[jdorn]

That's definitely possible, just lacking documentation and examples.

You can do everything with the PHP report type. It's not as clean as a simple SQL report, but it's great when you need the extra flexibility. In the example below, I'm using PHP's mysql_ functions, but you could just as easily use PDO or load an ORM or other helper library.

<?php
// Example Report

// Connect to the mysql database using connection info from config.php
$conn = mysql_connect($environment['mysql']['host'],$environment['mysql']['user'],$environment['mysql']['pass']);
mysql_select_db($environment['mysql']['database']);

// Do your queries and PHP manipulation
$sql = "SELECT * FROM MyTable";
$result = mysql_query($sql);
if(!$result) throw new Exception("Query failed: ".mysql_error());

$report_rows = array();
while($row = mysql_fetch_assoc($result)) {
  $row['extra'] = 'added with php'; // PHP manipulation!
  $report_rows[] = $row;
}

// Output the report rows
echo json_encode($report_rows);

// Close the database connection
mysql_close($conn);

[fjafferi]

thank you. That seems to work. Now i ran into another problem, i am trying to create a variable to load values from the databas. However, it doesn't seems to be working in php. I am using this code below:

// VARIABLE: { // name: "filter_node", // display: "Node", // type: "select", // database_options: { // table: "nodes", // column: "node_name", // all: "true" // }

I am not getting any errors nor is it populating the dropdown.

Thanks.

[adamjakab]

Hi,

yes same here - dropdown does not get populated - however even if it was, the 'database_options' is not flexible enough to be able to present a dropdown with different key/value pairs (such as userid for value and concat-ed firsname & lastname for label).

To overcome this I have loaded this from the db into an array but I cannot seem to add it to the report (\Report) class which should be available inside my report.

Is there a way to do this?

[lolaslade]

Same question here. I even tried including a SQL format report with it's own variables to see if they would get populated. They appear but they do not get populated.