Number total from Report Builder Query

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. 

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.

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!