Re: Freehand Query form. Help needed.
I finally worked out a solution.
I placed a new variable underneath my require_once for my connection info.
$query= $_POST['query'];
I then placed all of my previous code in the IF section of an IF...ELSE statement and used
if (!$query){
$stmt = $pdo->query("select * from tbltest2");
continue with the remaining code as earlier.
In the ELSE section I placed a second copy of my previous code and changed the $stmt variable as follows.
else {
$stmt = $pdo->query("$query");
continue with the remaining code as earlier.
Can't believe how simple it turned out to be.
Below is the full code for anyone that is interested.
======================================================
<html>
<head>
<link rel="stylesheet" type="text/css" href="stripe.css">
</head>
<body>
<?php
require_once "conn.inc.php";
$query= $_POST['query'];
if (!$query){
$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="query" 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%'"
}
else {
$stmt = $pdo->query("$query");
$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="query" 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 know it could be a lot prettier, but functionality was the important thing for me.