MySQL Forums
Forum List  »  PHP

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 "&pound;{$row['DebitAmount']} </td><td align=right>";
echo "&pound;{$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> &pound;" . $tCredF ."</td></tr>";
echo "<tr><td>Total Debit&nbsp;</td>"."<td align=right> &pound;" . $tDebF ."</td></tr>";
echo "<tr><td>Profit/Loss&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>"."<td align=right> &pound;" . ($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.

Options: ReplyQuote


Subject
Written By
Posted
Freehand Query form. Help needed.
September 25, 2013 06:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.