Forum Moderators: coopster
%customer_name% has a balance of %balance%.SELECT customers.name AS customers_name, customers.balance FROM customers WHERE customer_id = '$customer_id'%customer_name% has made the following payments:
%payments%
$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 . ' - £' . $payment; /* $ for a dollar sign */
}
<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>
$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 . ' - £' . $payment; /* $ for a dollar sign */
}
}
} else {
// Message saying no results for your query
}
$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';
Ahh, I see. This is doable :)
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:
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.
%customer_name% has made the following payments:
%payments% {SELECT customers.name AS customers_name FROM customers WHERE customer_id = '$customer_id'}
{payments:SELECT date,amount FROM payments WHERE customer_id='$customer_id'}
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.
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).
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?
SELECT * FROM customers{SELECT * FROM customers}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.{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}RECENT CUSTOMER PAYMENTS
Customer: %customerName%
Account #: %customerAcctNum%
PAYMENTS
%payments%<?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.
}
?>
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 dateDear %customer_name%,
Here are the payments that have been applied to your account:
[!-- BEGIN payments --]
%date - %amount%
[!-- END payments --]
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.
