Number total from Report Builder Query
5 posts by 2 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: November 7, 2013 (RSS)
Hi,
I am wondering if there is a way to have a column number for each result from a MySQL query in Report Builder. For example:
1 | Bob
2| Jane
3| David
Here is an example of my current Query:
<?php $firstTable = true;?>
<?php foreach (getSchemaTables() as $tableName) :?>
<?php if(strpos($tableName, "_main")): ?>
<?php $schema = loadSchema($tableName); ?>
<?php if(@$schema['name']): ?>
<?php if($firstTable) {
$firstTable = false;
}
else {
echo "UNION";
}
?>
SELECT `name` as "Name", FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`
<?php endif; ?>
<?php endif ?>
<?php endforeach ?>
Thanks!
By Dave - November 4, 2013
Hi 8bitpixel,
I google'd for "MySQL Row Counter" and found this helpful article: http://stackoverflow.com/questions/3126972/mysql-row-number
Basically if you put this BEFORE your query:
SELECT @i:=@i+1 AS "Column Number", myquery.* FROM (
And this AFTER your query:
) myquery, (SELECT @i:=0) myrowcounter
Then you'll get a column number column added:
SELECT @i:=@i+1 AS "Column Number", myquery.* FROM (
SELECT * FROM <?php echo $TABLE_PREFIX ?>accounts
) myquery, (SELECT @i:=0) myrowcounter
Let me know if that works for you.
interactivetools.com
By gadefgaertgqe - November 5, 2013 - edited: November 5, 2013
Hi Dave,
Yep, I also googled for a number of days, and used my experts exchange account to try and get to the bottom of this. I tried the exact same method as you kindly posted below.
It works on very simple queries, but seems to start failing when union etc, comes into it.
So far the code has advanced to this stage but still not working. I might just have to give up on this one. Anyway this is what it looks like at the moment after discussing elsewhere and working on it:
<?php
$firstTable = true;
foreach (getSchemaTables() as $tableName):
if(strpos($tableName, "_main")):
$schema = loadSchema($tableName);
if(isset($schema['name'])):
if($firstTable) {
$firstTable = false;
} else {
echo " UNION";
}
printf("SELECT @ROW := @ROW + 1 AS rowNum, name as `Name` FROM `%s`, (SELECT @ROW := 0) r;", $TABLE_PREFIX . $tableName);
endif;
endif;
endforeach;
?>
Anyway your help is appreciated as I know that this is not really a CMSB problem, but I was just hoping that someone else might have found themselves in a similar situation and worked it out.
Well, after having one last ditch attempt, I now have it working! I revisited the earlier solution (the same as the one you posted Dave), as I was starting to wonder if I had gone to far down the rabbit hole!
So, this is the OLD BROKEN code:
<?php $firstTable = true;?>
<?php foreach (getSchemaTables() as $tableName) :?>
<?php if(strpos($tableName, "_main")): ?>
<?php $schema = loadSchema($tableName); ?>
<?php if(@$schema['bike_stock']): ?>
<?php if($firstTable) {
$firstTable = false;
}
else {
echo "UNION";
}
?>
SELECT @i:=@i+1 AS "Column Number", myquery.* FROM (
SELECT `subdomain_name` as "Subdomains List", `dealer_title` as "Dealer Title" FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`
WHERE bike_stock = '1'
) myquery, (SELECT @i:=0) myrowcounter
<?php endif; ?>
<?php endif ?>
<?php endforeach ?>
and here is the CURRENT FIXED Code:
<?php $firstTable = true;?>
<?php foreach (getSchemaTables() as $tableName) :?>
<?php if(strpos($tableName, "_main")): ?>
<?php $schema = loadSchema($tableName); ?>
<?php if(@$schema['bike_stock']): ?>
<?php if($firstTable) {
$firstTable = false;
}
else {
echo "UNION";
}
?>
SELECT @i:=@i+1 AS "Column Number", myquery.* FROM (
SELECT `subdomain_name` as "Subdomains List", `dealer_title` as "Dealer Title" FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`
WHERE bike_stock = '1'
) myquery, (SELECT @i:=0) myrowcounter
<?php endif; ?>
<?php endif ?>
<?php endforeach ?>
So, the only difference is the new empty line before the first SELECT. That's it. Can someone explain this please? So much time wasted because of a missing empty line. I kind of feel robbed but glad it's fixed.
Thought I would share just in case someone else falls foul of this.
By Dave - November 7, 2013
Hi 8bitpixel,
So, the only difference is the new empty line before the first SELECT. That's it. Can someone explain this please? So much time wasted because of a missing empty line. I kind of feel robbed but glad it's fixed.
PHP sometimes removes space around the beginning and end of <?php ?> tags. My guess is PHP wasn't outputting any space before your "SELECT". You could test it by copying and pasting your code into a test.php scren and seeing what MySQL it outputs.
Here's an example:
1
<?php print "2"; ?>
3
Outputs:
1
23
Anyways, glad it's working and thanks for posting back!
interactivetools.com