Creating reports

Radon3k

I have a simple system in which you can enter invoices into a database. It contains dates, amounts, items, etc. I want to be able to create custom reports based on just about whatever I want and I want to be able to sort them just about any way I want to. For example I want to be able to create a report based on how much spent in descending order based on person, date or what the item was. I'm not looking for code, but rather a way to go about accomplishing this. So far an idea I have would be to generate a list of all of the invoices in the database and have little icons at the top so when you click on it it sorts it based on that. However, as mentioned before I'd like to sort it even further than that, if possible. Any ideas? Thanks. 🙂

bastien

Start with a search form...listing out all the criteria that you could wish to search from.

From there its then a matter of building the sql on the fly when the form is processed like this

// get the list of items in the inventory
$sql = "select * from vehicle_inventory where record_deleted != 1";
$sql_where = "";

// from linked page to list all items from a particular manufacturer
if (!empty($prod_cat)){
	$sql_where = " and make like '$prod_cat%' ";
}

// from this page to search based on criteria
if (!empty($make)){
	$sql_where .= " and make like '$make%' ";
}
if (!empty($model)){
	$sql_where .= " and model like '$model%' ";
}
if (!empty($category)){
	$sql_where .= " and category like '$category%' ";
}
if (!empty($condition)){
	$sql_where .= " and condition like '$condition%' ";
}
if ($_POST['submit'] == "Show All"){ $sql_where = ""; }

$sql .= $sql_where . " order by stock desc limit $NextBlock, $display";

Radon3k

I'm sorry but I don't think I really follow what it is that you're saying. Building the SQL query on the fly is kind of what I'm looking for I guess, but I just want to make sure that I can narrow down the search to specific details and in a certain order such as first by item, then by date, followed by price, etc or any other order that I want.

bastien

if you look at the code, that is what it does...i don't order here because i don't need to, but you can just as easy either the order by clause fixed as above or build it in on when there is that element in the search