homepage Welcome to WebmasterWorld Guest from 54.211.73.232
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Web-Based Report Generator
NickCoons

10+ Year Member



 
Msg#: 4101664 posted 9:29 pm on Mar 20, 2010 (gmt 0)

I need a little guidance on how to structure this script.

I'm building a report generator for an end-user. Essentially, they'll create the report template in OpenOffice and save the file as an ODT. It might look something like this:

%customer_name% has a balance of %balance%.

Then, they'll create a SQL query attached to the report's record, maybe something like this:

SELECT customers.name AS customers_name, customers.balance FROM customers WHERE customer_id = '$customer_id'

When the script is executed for a given customer, it will read in the ODT file, execute the SQL query, and replace the variables in percentage signs with the values from the query, and generate a new ODT file as the report. All of this works, it's good, no problems here.

Where I run into an issue is how to structure this when there are multiple rows returned in joined tables. So let's say I want a report that shows the customer name from the "customers" table, then a list of payments for the customer from the "payments" table. So I would create the ODT file like this:

%customer_name% has made the following payments:
%payments%


But I don't know how to generate a single SQL query that will return the customer's name followed by a list of all the payments. In fact, I'm pretty sure that embedding a subquery that returns multiple rows in this manner isn't possible.

Normally, if this was a hard-coded report, I'd simply create the PHP script with two SQL queries, one to get the customer's name, then another one that returns the rows of payments and loops through it. But because this report is going to be dynamically generated by user input, that won't work.

So I'm sort of at a loss as to how I'd create this so that the user can generate whatever report they want so long as they provide the ODT template and the SQL query. I do have one idea, but it seems so messy so I'd rather avoid it.

 

Readie

WebmasterWorld Senior Member



 
Msg#: 4101664 posted 9:59 pm on Mar 20, 2010 (gmt 0)

I'm a little confused - is this going to be utilising PHP scripting? Because there are a few fairly simple methods of doing (what I think) you're asking for with PHP.

NickCoons

10+ Year Member



 
Msg#: 4101664 posted 10:04 pm on Mar 20, 2010 (gmt 0)

Yes, the report generator is built in PHP. If you think I've missed something obvious, please share. If you're right, then you'll have helped me out a lot. If you missed something in my original post, then this will give me an opportunity to clear it up. A win-win :-).

Readie

WebmasterWorld Senior Member



 
Msg#: 4101664 posted 9:16 am on Mar 21, 2010 (gmt 0)

Well, you don't get an individual name followed by all their payments with the SQL query, you get lots of rows with the name and payments, and do something like this:

$sql = 'SELECT customers.customer_name, payments.paid, payments.payment_date FROM customers INNER JOIN payments ON customers.customer_id = payments.customer_id WHERE customers.customer_id = "' . $someVariable . '" ORDER BY payments.payment_date DESC';

$result = mysql_query($sql);
$rows = mysql_num_rows($result);

$customer_name = mysql_result($result, 0, "customer_name");

echo '<span style="font-weight: bold;">' . $customer_name . ' made the following payments:</span>';

for($i = 0; $i < $rows; $i++) {
$payment = mysql_result($result, $i, "paid");
$p_date = mysql_result($result, $i, "payment_date");

echo '<br />' . $p_date . ' - &#163;' . $payment; /* &#36; for a dollar sign */
}

NickCoons

10+ Year Member



 
Msg#: 4101664 posted 4:44 pm on Mar 21, 2010 (gmt 0)

The problem, of course, is that the PHP code that you've specified to accompany the SQL query is specific to that query. And since these reports are dynamically generated by the end-user simply by submitting a template and a SQL query, obviously I can't have anything hard-coded like that.

I submitted the customer list with payments as a sample report that a user might want to generate, not as a report that I was attempting to build. Building static reports is quite easy. So let me try to clarify how this will work.

I have a form that looks something like this:

<form action="/save-report.php" method="post" enctype="multipart/form-data">
Report Name<br />
<input type="text" name="report" /><br />
SQL Query<br />
<textarea name="sql_query" rows="5" cols="60"></textarea><br />
ODT Template<br />
<input type="file" name="template" /><br />
<input type="submit" value="Save" /><br />
</form>


So the user submits the SQL query and the report template. My PHP script needs to execute their SQL query, then use the returned data to populate their report. If the SQL query returns one row, then this is simple (and I already have it working nicely), because I can replace "%column%" in the template with "column" from the query. The problem I run into is if the user submits a query that returns multiple rows, such as a list of customer payments, then how would I set up the PHP script to replace the correct fields keeping in mind that I won't know this in advance?

Readie

WebmasterWorld Senior Member



 
Msg#: 4101664 posted 5:04 pm on Mar 21, 2010 (gmt 0)

Ahh, I see. This is doable :)

$sql = $_POST['sql_query'];
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

if($rows > 0) {
$customer_name = mysql_result($result, 0, "customer_name");
if($rows == 1) {
// Single line report
} else {
echo '<span style="font-weight: bold;">' . $customer_name . ' made the following payments:</span>';

for($i = 0; $i < $rows; $i++) {
$payment = mysql_result($result, $i, "paid");
$p_date = mysql_result($result, $i, "payment_date");

echo '<br />' . $p_date . ' - &#163;' . $payment; /* &#36; for a dollar sign */
}
}
} else {
// Message saying no results for your query
}

My only concern here is the user is capable of entering any SQL command here - they could quite easily enter "DROP DATABASE database_name" or something.

My suggestion:
Have the form giving the user the ability to enter the customer name rather than the whole query, and do this:

$name_request = mysql_real_escape_string($_POST['name_request']);

$sql = 'SELECT customers.customer_name, payments.paid, payments.payment_date FROM customers INNER JOIN payments ON customers.customer_id = payments.customer_id WHERE customers.customer_id = "' . $name_request . '" ORDER BY payments.payment_date DESC';

And remove the textarea for a SQL query, and replace it with a text input named "name_request". The user would need to enter the customer ID with the code above, but you can quite easily modify the SQL to suit your purposes.

NickCoons

10+ Year Member



 
Msg#: 4101664 posted 12:08 am on Mar 22, 2010 (gmt 0)

Ahh, I see. This is doable :)


I appreciate your example, but the inherent problem here is that it still hard-codes things like "customer_name", which it cannot do because the report may have nothing to do with this. The report may pull up an aggregate listing of all customer purchases over a given time period, or a valuation of on-hand inventory. It really could be anything.

The idea here is that the end-user could generate any report, and I have no idea in advance what that report is going to be, so I can't hard-code anything into the script related to the report contents.

If I knew that the end-user wanted 50 specific reports, and this was one of them, then I could just build this one along with the other 49 by hand. But I don't know what they'll want to do.

My only concern here is the user is capable of entering any SQL command here - they could quite easily enter "DROP DATABASE database_name" or something.


I'm not worried about that. It's their database, so if they want to drop it... :-).

But seriously, the authenticated MySQL user running the query doesn't have access to do things like drop the database, and it's backed up automatically every four hours. So this isn't really a concern of mine.


My suggestion:
Have the form giving the user the ability to enter the customer name rather than the whole query, and do this:


But again, this only works if I knew in advance that this is the specific report they wanted to generate, which I don't.

I feel like I'm not explaining this very well, so I apologize for any confusion, and I appreciate your willingness to help. If you have any questions you could ask that might help clarify, I'd be happy to provide whatever information I can.

Readie

WebmasterWorld Senior Member



 
Msg#: 4101664 posted 9:27 am on Mar 22, 2010 (gmt 0)

Right, so we need a form with various drop-down menus for the "SELECT", the "WHERE", the "ORDER BY" and the "LIMIT" - and even "INNER JOIN"?

This get's a little more complex.

If you could give a list of tables that need to be searched and their column names (use pseudo names if you'd like to) then I have an idea or 2 here.

Please also include the relationships between the tables - i.e.

"customers => payments. one to many as customer_id => customer_id"

CyBerAliEn

5+ Year Member



 
Msg#: 4101664 posted 4:56 pm on Mar 22, 2010 (gmt 0)

My only concern here is the user is capable of entering any SQL command here - they could quite easily enter "DROP DATABASE database_name" or something.


Agree. You note that the user is incapable of doing a drop, etc... and if they do --- who cares, lol. I'd recommend giving the mySQL user that this script uses only SELECT privileges (and whatever else is needed) so that no damaging query can be run. Just a thought.


But there seems to be a fundamental flaw here... All you get from the user to do the report is:
(a) report name [what's this for?]
(b) a query command in SQL
(c) a template file

This essentially means your end-user must be capable of supplying an SQL query to run whatever report they want.

I'm not aware of any query off the top of my head to do exactly what you want. So would a user know it either? All they can do is enter a query, right?

This seems a fundamental issue here to me. "Limiting" a user to entering a query means you're limited in what you can do to some degree. No?

You may have to do some more complex work here. In this example...
%customer_name% has made the following payments:
%payments%


It is really easy to see what 'customer_name' is going to be and how you're going to replace it. But what about 'payments'? I don't see how you can 'dynamically' account for this with just a single query.

Maybe you could allow your "query" textbox to take in multiple queries? Suppose your user could enter:
{SELECT customers.name AS customers_name FROM customers WHERE customer_id = '$customer_id'}
{payments:SELECT date,amount FROM payments WHERE customer_id='$customer_id'}


Then you can setup your PHP code to process the textbox string into queries? Basically, enclose each query in braces (and probably code it to run a single query that does not include braces too). Have it run each query in the normal way. But when it gets to "{payments:[etc]}", your code should go "oh, this is a result table!". Run the query, build out a table, and smack that where the template has "%payments%". Just a thought.

But to do what you want to do, you're going to have to delve into some manipulation of some type.

NickCoons

10+ Year Member



 
Msg#: 4101664 posted 11:54 pm on Mar 22, 2010 (gmt 0)

But there seems to be a fundamental flaw here... All you get from the user to do the report is:
(a) report name [what's this for?]
(b) a query command in SQL
(c) a template file


Right, this is all the provide. The name of the report is provided because what they are doing is defining a report at this time, which will allow them to choose the report from a list to generate it in the future for the given situation. So they might create a report called "Customer Statement". Then, they'd go into a customer's record, go to the reports tab and click on "Customer Statement" in order to generate a report for that customer given the predefined information above.

This essentially means your end-user must be capable of supplying an SQL query to run whatever report they want.

I'm not aware of any query off the top of my head to do exactly what you want. So would a user know it either? All they can do is enter a query, right?


Yes, they will have to know the SQL query, which will create somewhat of a trial-and-error situation. But for this particular case, I'm not concerned about that. The end-user will be able to create the queries without any issues.

This seems a fundamental issue here to me. "Limiting" a user to entering a query means you're limited in what you can do to some degree. No?


I can't imagine anything less limiting than allowing the end-user to free-form their own query, which is why I planned on going in this direction rather than choose options from a drop-down list (which does seem limiting, because then you can only do what the drop-downs are allowing you to do).

You may have to do some more complex work here. In this example...
%customer_name% has made the following payments:
%payments%

It is really easy to see what 'customer_name' is going to be and how you're going to replace it. But what about 'payments'? I don't see how you can 'dynamically' account for this with just a single query.


And that's where I'm stuck :-).

Maybe you could allow your "query" textbox to take in multiple queries? Suppose your user could enter:
{SELECT customers.name AS customers_name FROM customers WHERE customer_id = '$customer_id'}
{payments:SELECT date,amount FROM payments WHERE customer_id='$customer_id'}

Then you can setup your PHP code to process the textbox string into queries? Basically, enclose each query in braces (and probably code it to run a single query that does not include braces too). Have it run each query in the normal way. But when it gets to "{payments:[etc]}", your code should go "oh, this is a result table!". Run the query, build out a table, and smack that where the template has "%payments%". Just a thought.


Aha, now we're getting somewhere. In my first message, I mentioned that I had a thought on this, but it seemed kind of messy. It went something like this:

SELECT customer_name, "SELECT * FROM payments where customer_id = '$customer_id'" AS subquery1 FROM customers WHERE customer_id = '$customer_id'

Then I'd look for instances of subqueryX in the returned parent query and execute those. But as I said, that just seems like a mess. Your idea is essentially the same, but so much cleaner.

But to do what you want to do, you're going to have to delve into some manipulation of some type.


What do you mean?

CyBerAliEn

5+ Year Member



 
Msg#: 4101664 posted 4:56 pm on Mar 23, 2010 (gmt 0)

### Clarifications ###
I can't imagine anything less limiting than allowing the end-user to free-form their own query, which is why I planned on going in this direction rather than choose options from a drop-down list (which does seem limiting, because then you can only do what the drop-downs are allowing you to do).

To clarify: It's unlimited in the sense that your user has full control to build a query of any type or shape --- this is ultimate control. But it is limiting in the sense that user's are then limited to running reports based off their own individual skills and knowledge of SQL. I see this as an issue (personally) because most people (speaking generally) do not know how to code, how to do SQL, or even how to do advanced SQL. But if your users DO know SQL, then it isn't really an issue. Just something I felt worth needed being noted.

But to do what you want to do, you're going to have to delve into some manipulation of some type.
What do you mean?

I'm just saying that no matter what "solution" you derive, it will involve some type of manipulating... in the sense that you will likely need to add some "customizations" to the query input and special programming in the script(s) to catch these things (such as my original suggestion).


### EXPLORING THE IDEA ###
What I would do is allow the query input textbox be able to accept "specially formatted" queries. Your programming should be setup so that if a single query is entered, such as this:
SELECT * FROM customers
Will still work. Or that this will work:
{SELECT * FROM customers}
And then you can allow users to make the queries as advanced as they want or are capable.

The idea is to *expand* this by allowing special formatting. Essentially, your script WILL NOT just take the query textbox and try to run the query. Your script will instead have to PARSE the query textbox and then perform actions (queries) depending on how you "set it up" (your rules/formatting/etc). The basic idea is that multiple, individual queries can be run by wrapping them in braces, such as this:
QUERY TEXT BOX:
{SELECT name AS 'customerName',address AS 'customerAddress' FROM customers WHERE id=$customer_id}
{SELECT name AS 'supplierName',phone AS 'supplierPhone' FROM suppliers,customerAssignment WHERE ((customerAssignment.supplier=suppliers.ID) AND (customerAssignment.customer=$customer_id))}

TEMPLATE FILE:
%customerAddress%
Dear %customerName%,
Your supplier has recently been changed to %supplierName% (%supplierPhone%). We apologize for any inconvenience.

Though you can do a lot of advanced stuff doing queries and sub-queries, it is often a lot easier to write multiple queries. The above example will allow you to run a query to get the customer info; another query to get the supplier info; and then all those values can be stuffed into the template.

For a more advanced situation (such as 'payments').... suppose your user wanted to generate a report showing the customer name and contact information (etc) followed by a table of their 30 most recent payments. You could enable the user to enter a query such as this:
{SELECT name AS 'customerName',accountnumber AS 'customerAcctNum' FROM customers WHERE ID=$customer_id}
{table:payments:SELECT date AS 'Date',status AS 'Status',description AS 'Description',amount AS 'Amount' FROM payments WHERE customer=$customer_id}

With a template like:
RECENT CUSTOMER PAYMENTS
Customer: %customerName%
Account #: %customerAcctNum%

PAYMENTS
%payments%

So how does this work out? You need to program your script to grab each "query" (each group of wrapped braces). The first query will get your customer information. Easy. Now the second one. Notice it is in a FORMAT of: {type:identifier:query} --- You will use a format/rule such as this to know how to program. Your program should split the "brace" by the colons and get the type of command, the identifier, and then the query. Having a "type" will allow future expansion. In the meantime, let's just assume the only "type" is "table". OK, so your script knows that you want to create a "table" with the identifier "payments", and it will build this table using the "query" specified. The identifier will simply relate the table/results to the "variable" (%payments%) entered in the template. In the future, you could expand the reporting by including other types; for example, "list" might result in the results being returned in an "inline" listed fashion; etc. Now, a user only needs to create a template knowing they need/want a table, putting a "name" for it in the template where they want, then building the query in the right fashion. It is important to note that your script should abort and return a user to the prompt (HTML form) if a given query textbox cannot be processed/parsed.

So how do you do this? Below is a prototype code to get you started (assumes that form vars have already been grabbed and cleaned; and that mySQL connection is already established/connected)...
<?php
//Form Variables: isnew, name, descrip, query, template

//###Get Values to Run Queries###
if ($isnew=='false')
{
//Grab report values from "report table" and set them to vars so they can be processed!
//so query table and setup the vars 'name', 'descrip', etc!
}
//###Catch: Simplest Query###
//add code here that will check if the query is in the form "SELECT * FROM customers" (etc; ie - no braces, just a straight-up query) and wrap it in braces for below processing.
//###Setup Queries###
$pattern = '?'; //I'm not good enough with regex to split the string by groups of "{query}{query}{query}". someone else prob can help you here.
$queries = preg_split($pattern,$query,NULL,PREG_SPLIT_NO_EMPTY);
if (count($queries)>0)
{
foreach ($queries as $query)
{
$parts = explode(':',$query);
if (count($parts)==3)
{
//###Query is Advanced/Formatted###
list($type,$identifier,$queryStr) = $parts;
if ($type=='table')
{
//###Run Query; Get Associative Array of Results###
$output = '';
$rows = array();
$results = mysql_query($queryStr);
while ($row = mysql_fetch_array($results,MYSQL_ASSOC)) { $rows[] = $row; }
//###Setup HTML Output Table###
if (count($rows)>0)
{
$output = "<table>";
//###Table Header (column names)###
$output.="<tr>";
if (count($rows[0])>0) { foreach ($rows[0] as $colName=>$value) { $output.="<td>{$colName}</td>"; } }
$output.="</tr>";
//###Result Rows###
foreach ($rows as $row)
{
$output.="<tr>";
foreach ($row as $name=>$value) { $output.="<td>{$value}</td>"; }
$output.="</tr>";
}
$output.="</table>"
}
else { $output = 'No results.'; }
//###Cast Table to Identifier Variable###
$$identifier = $output;
}
else
{
//Error: unknown type provided; return to HTML form
}
}
else
{
//###Simple/Single Query###
//run query string to mySQL which is var 'query' and then manipulate the results as you do now
}
}
}
else
{
//Error: Return to HTML form - no queries specified/returned.
}
?>

(the above looks a lot nicer if this forum didn't remove tabs)

Minor note: In your example queries, variables are referenced like "$customer_id". How are these variables being set/defined?


### HTML FORM CONSIDERATIONS ###
You say that entering a report name will save the query for later running? This is great! What I would consider you do is to setup your HTML form field with the fields: (1) report name; (2) report description; (3) query; (4) template file. The report name should be simple/succint ("Customer Balance"). The description could describe in more detail what the report is ("Displays a specific customer's balance on their accounts."). The rest is same as usual. When a report is first run (created), you should have a separate SQL table to "store" the report's name, description, query, and template. This will allow you to "recall" the report later.

When a user goes to run a report... the "report name" should be a drop down showing choices of reports to run (names and descriptions). A user then only needs to click "Customer Balance" and then click a button ("Run") and voila! Results! But, the drop down should have a choice like "*Create New Report*", that when selected... will "pop up" (via Javascript) the additional form elements, allowing a user to then create a new report. You should also include a hidden field such as "isnew" that will return true/false depending on whether the report is using an existing report or doing a new one (makes it easier to script).

I would see the above as a HUGE user improvement issue. Though I'm sure you are doing something similar, I wanted to emphasize this.

CyBerAliEn

5+ Year Member



 
Msg#: 4101664 posted 5:05 pm on Mar 23, 2010 (gmt 0)

To further emphasize, the above is a prototype.

You would still need to add coding to accomplish such things as: connecting to database; grabbing form variables; getting/checking uploaded template file; casting mySQL results to variables and then to template file content; etc.

NickCoons

10+ Year Member



 
Msg#: 4101664 posted 5:16 pm on Mar 23, 2010 (gmt 0)

CyBerAliEn,

I've come up with a solution which is essentially based off of your first post in this thread, with some modifications. Instead of creating a single query text box and entering in multiple queries segregated by braces, I'm storing the queries in a separate linked table so that each report can have an unlimited number of queries associated with it. Each query will have a name associated with it, which will correlate with the same name in the template. This provides a cleaner way for me to store the queries and doesn't require that I parse through them looking for opening and closing braces.

Using the customer payments example, it might look something like this:

Query "main" - SELECT customer_name FROM customers WHERE customer_id = '$customer_id'

Query "payments" - SELECT date, amount FROM payments WHERE customer_id = '$customer_id' ORDER BY date


Then the template might look like this:

Dear %customer_name%,

Here are the payments that have been applied to your account:

[!-- BEGIN payments --]
%date - %amount%
[!-- END payments --]


The BEGIN and END markers indicate an iteration through the rows returned in the query, and the label "payments" indicates that it applies to the query called "payments".

So far, I don't see any problems with this method, and will hopefully have something tested and working within a few hours, so I'll post back then with results.

Regarding your other suggestions, I think they may be good ideas in different contexts, such as the quick-add of a new report. In my situation, the user that creates the report will not be the user that runs the report, and the user that runs the report will not be allowed to add new reports.

NickCoons

10+ Year Member



 
Msg#: 4101664 posted 5:54 pm on Mar 23, 2010 (gmt 0)

Everything seems to be working well with my test report, which is only about half a page. The real test will be when the end-user tries to create their much more complex 24-page report, which will show if there are any glitches due to things I may have overlooked.

CyBerAliEn

5+ Year Member



 
Msg#: 4101664 posted 3:59 pm on Mar 24, 2010 (gmt 0)

A slight difference in actual approach, but it is the same idea! :) Kudos and congratulations on arriving to a solution.



The fact that one set of users makes the reports and another set runs the reports is a big difference from what I gathered out of your first posts lol... I was under the impression all of these actions were being performed by the same user set.

NickCoons

10+ Year Member



 
Msg#: 4101664 posted 4:11 pm on Mar 24, 2010 (gmt 0)

The fact that one set of users makes the reports and another set runs the reports is a big difference from what I gathered out of your first posts lol... I was under the impression all of these actions were being performed by the same user set.


Since my question didn't really revolve around that, I didn't think it necessary to provide that information. But I do thank you for your assistance.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved