Opensource.Platon.SK: phpMyEdit: Creating a &quot;totals&quot; column

Index » Projects » phpMyEdit » Forum » Creating a "totals" column

phpMyEdit Configuration Creating a "totals" column

Post new topic Reply to topic Goto page 1, 2, 3, 4, 5, 6, 7, 8, 9 Next

tricomp Joined: 20 Apr 2012 Posts: 6

Posted: 2012-04-20 18:46
Back to top Reply with quote

Hello everyone,
I need some help here.

I have 2 tables: customers and invoices. Each customer in the "customers" table has one or more invoice records in the "invoices" table.Each invoice in the "invoice" table has an "amount" field.

I want to have the customers displayed in PME and to include a virtual column that shows the total amount from the invoices table for each customer.

This will be like executing the following for each row containing customer information:

Code:

SELECT SUM(amount) FROM invoices WHERE customer_id = {id_from_customers_table}

I hope I was able to make it clear.

I'd appreciate any help I get on this.

Thanks in advance.

Feyisayo

doug Joined: 10 Feb 2003 Posts: 1013 Location: Denver, Colorado (USA)

Posted: 2012-04-21 13:17
Back to top Reply with quote

No provision exists for totaling (summing) columns:
http://opensource.platon.sk/forum/projects/viewtopic.php?t=994127&highlight=column+total

If you can use phpMyAdmin's SQL window to produce the desired report, then you can save that report as a non-editable VIEW, and phpMyEdit will be able to read the VIEW. You might want to use the Search feature to look for alternative solutions or work-around.

tricomp Joined: 20 Apr 2012 Posts: 6

Posted: 2012-04-21 19:58
Back to top Reply with quote

Thanks for the reply Doug.

I did not know PME could load saved views from PHPMyAdmin.

I would love to give that a try but I don't know how. Can you give me quick information on how to. Or do the know where I can find the documentation to do so?

Feyisayo

doug Joined: 10 Feb 2003 Posts: 1013 Location: Denver, Colorado (USA)

Posted: 2012-04-21 23:33
Back to top Reply with quote

You web host usually provides phpMyAdmin in the website control panel. If not, then your web host is second rate. At the demo http://demo.phpmyadmin.net/master-config/ click on the SQL window atop the screen. Enter a query and hit Go.

I recommend Hostgator as a web host and have 100+ client domains hosted there. The cPanel has a lot of good tools.
http://secure.hostgator.com/~affiliat/cgi-bin/affiliates/clickthru.cgi?id=denver27

tricomp Joined: 20 Apr 2012 Posts: 6

Posted: 2012-04-22 20:35
Back to top Reply with quote

Thanks for that response.

I actually meant that how does one use the exported view in PME?

Feyisayo

doug Joined: 10 Feb 2003 Posts: 1013 Location: Denver, Colorado (USA)

Posted: 2012-04-23 01:19
Back to top Reply with quote

The same way you'd use it with any table. Create a form. Disable Add, Change, Copy, and Delete since those actions won't work with a view and might damage something.

tricomp Joined: 20 Apr 2012 Posts: 6

Posted: 2012-04-23 11:23
Back to top Reply with quote

Sorry Doug if this seems "duh" but I didn't understand your last reply.

I figured out the SQL statement that does displays the data I want. Here it is:

Code:

SELECT customers.*, total
FROM customers
LEFT JOIN (
SELECT customer_id, sum( amount_payable ) as total FROM invoices GROUP BY customer_id
) AS tb ON tb.customer_id = customers.customer_id

The above code works in PHPMyAdmin.

However, the challenge is to get PME to display the same data as this SQL statement displays.

tricomp Joined: 20 Apr 2012 Posts: 6

Posted: 2012-04-23 13:16
Back to top Reply with quote

Hey Doug,
I figured it out!!!
There were 2 tricks involved:
1) Instead of using a normal table as the join table I used a SELECT statement that calculates the sub-totals for distinct customer IDs.

2) I removed the "`" character that PME uses to delimit all field names. The "`" character makes everything in the SELECT statement a literal thus preventing the SUM function from working and causing PME to return a "1033 incorrect table" error.

Below are 2 code snippets illustrating the points above:
Point 1:

Code:

$main_table = 'customers';
$join_table = '(SELECT customer_id, SUM(amount_payable) as total FROM invoices GROUP BY customer_id)';
$opts['fdd']['total'] = array(
'name' => 'Total Receipts',
'input' => 'VR',
'sql' => 'null',
'select' => 'T',
'options' => 'VACPDL',
'maxlen' => 81,
'sort' => true,
'values' => array( // API for currently making a join in PME.
'table' => $join_table, // the table to which we're joining
'column' => 'customer_id', // the key field in the joined table
'join' => '$main_table.customer_id = $join_table.customer_id',
'description' => 'total' // the desired column in the joined table
),
'number_format' => array(2, '.', ',')
);
// Now important call to phpMyEdit
require_once 'phpMyEdit.class.php';
new phpMyEdit($opts);

Point 2:

Code:

// This edit is in the function get_SQL_join_clause() of the
// phpMyEdit.class.php file. Line 637
$join_clause .= " LEFT OUTER JOIN ". str_replace('`', '' , $dbp.$table) ." AS $join_table ON (";

I hope someone else finds this useful.

Thanks for your help Doug.

Feyisayo

doug Joined: 10 Feb 2003 Posts: 1013 Location: Denver, Colorado (USA)

Posted: 2012-04-23 17:39
Back to top Reply with quote

Nice work! Having removed the backtick character, make sure none of the columns are named using Reserved Words, which would produce unexpected results.
http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

tricomp Joined: 20 Apr 2012 Posts: 6

Posted: 2012-04-24 07:19
Back to top Reply with quote

So far none of the fields are reserved words. I'll take of it though if I have to add more fields.

Thanks again Doug.

adsvoo Joined: 14 Feb 2020 Posts: 2

Posted: 2020-02-14 09:41
Back to top Reply with quote

campbelllinda11 Joined: 24 Nov 2020 Posts: 17

Posted: 2020-12-15 20:45
Back to top Reply with quote

campbelllinda11 Joined: 24 Nov 2020 Posts: 17

Posted: 2020-12-15 22:24
Back to top Reply with quote

apurvabajaj Joined: 19 Oct 2020 Posts: 15

Posted: 2021-04-23 05:32
Back to top Reply with quote

I really like this brilliant and informative article all the details are awesome and good in this blog thanks for sharing this article.
http://www.bangalorehotescorts.co.in/
http://www.bangaloremodelescort.co.in/
http://www.kalpanachawla.in/
http://www.bangaloreescortindia.co.in

candymika Joined: 16 Apr 2021 Posts: 180

Posted: 2021-07-13 08:45
Back to top Reply with quote

Thanks for sharing this valuable content. In my view, if all webmasters and bloggers were creating content as good as you do, the website would be more useful than ever. minesweeper

Post new topic Reply to topic Goto page 1, 2, 3, 4, 5, 6, 7, 8, 9 Next



	Leaders
	Activities
	Commerce
	Support
	History
	Contact



	ep
	fastcgi-gallery
	libcfg+
	Metafox
	mylist
	phpMyEdit
	phpWebFileManager
	PLaTos
	thinkpad-status
	Tučniak
	sendxmpp
	vimconfig



	Webhosting
	Domain Backorder
	Nginx Support
	MyFax
	Password.sk


Copyright © 2002-2006 Platon Group Site powered by Metafox CMS	Go to Top · Feedback form · Application form Report bug on PLATON.SK website · Terms of use · Privacy policy