Freehand Query form. Help needed.
Posted by:
Scott Reid
Date: September 25, 2013 06:11AM
Hi all. I am working on a little personal project, but since I am not a coder myself I am limited to finding examples and hacking them a bit until they work the way I was hoping for. However I've now reached a point where I would be grateful for assistance from someone to help me finish it off.
Basically, I work as a self employed courier and each month I download my financial records and import them into my database. I have created a web based front end which will display all the records from this database using the bog standard "Select * From tbltest2" query which is hard coded into my page and also display totals for income, expenses and total profit/loss.
Currently, if I wish to only view records for a certain month, or view only income or expenses I have to manually edit the hard coded query.
What I would like is as follows.
a: Upon opening my page in a web browser, all records are displayed automatically.
b: A simple textbox at the base of the page where a new mysql search can be input.
c: The page should update and display the records according to the mysql query entered into the textbox. I would prefer to not have any part of the query hardcoded.
Here is the code I have currently worked out. I know it's not pretty (bear in mind I am not a coder so I'm not too worried about that, or about sql injection as this will only ever run on my own computer.
<html>
<head>
<link rel="stylesheet" type="text/css" href="stripe.css">
</head>
<body>
<?php
require_once "conn.inc.php";
$stmt = $pdo->query("SELECT * FROM tbltest2");
$results = $stmt->fetchAll(PDO::FETCH_ASSOC); #Get all rows of the database
#Variables
$tCred=0; #Total income
$tDeb=0; #Total expenses
$tpro=0; #Total Profit/Loss
$tCredF=0; #Total income, formatted to decimal
$tDebF=0; #Total expenses, formatted to decimal
foreach ($results as $row) {
$tCred = ($tCred + $row['CreditAmount']);
$tCredF = number_format($tCred, 2, ".",",");
$tpro += $tCredF; #From the results set, sum the income, format to decimal and add the total profit/loss variable
}
foreach ($results as $row) {
$tDeb = ($tDeb + $row['DebitAmount']);
$tDebF = number_format($tDeb, 2, ".",",");
$tpro -= $tDebF; #From the results set, sum the expenses, format to decimal and subtract from total profit/loss variable
}
echo ("<table border=1 width=100%><tr><th>Ref</th><th>Date</th><th>Type</th><th>Description</th><th>Debit</th><th>Credit</th></tr>");
foreach($results as $row){
echo "<tr><td>";
echo "{$row['Ref']} </td><td>";
echo "{$row['TransDate']} </td><td>";
echo "{$row['TransType']} </td><td>";
echo "{$row['TransDescription']} </td><td align=right>";
echo "£{$row['DebitAmount']} </td><td align=right>";
echo "£{$row['CreditAmount']} </td>";
echo "</tr>"; # Output the results to HTML table
}
$tproF = number_format($tpro, 2,".",","); #Format total profit/loss variable to decimal.
echo "</table>";
echo "<hr>";
echo "<div align=right>";
echo "<table width=22%><tr><td>Total Credit</td>"."<td align=right> £" . $tCredF ."</td></tr>";
echo "<tr><td>Total Debit </td>"."<td align=right> £" . $tDebF ."</td></tr>";
echo "<tr><td>Profit/Loss </td>"."<td align=right> £" . ($tCredF-$tDebF) ."</td></tr></table>";
echo "</div><hr>"; # Output income, expenses, total profit/loss to seperate HTML table
printf ('<form action="index2.php" method="post">
Search: <input type="text" name="term" size="150"/><br />
<input type="submit" name="submit" value="Submit" />
</form>'); #Form to input new query ie "Select * from tbltest WHERE Transdescription LIKE '%M6 Toll%'"
?>
</body>
</html>
I'm sure this will be simple for someone, but it is definitely beyond my capabilities.
I hope one of you will be able to assist. Thanks in advance, Scott.