Custom SQL Select

4 posts by 2 authors in: Forums > CMS Builder
Last Post: November 14, 2018   (RSS)

By Ryan - November 8, 2018

Hi, I'm looking at creating some reports based on data i hold in a cmsb table and was wondering what the best approach to go about this is. 

I can run the following code in the MySQL console and get the results i want between my selected date range. 

I could use the report builder add on but i want to display the results on the website, not within the admin section.

SELECT 
mydate, (SUM(field1)+SUM(field2)+SUM(field3)+ AS CombinedTotal
FROM cmsb_tablename
WHERE mydate >= 'yyyy-mm-dd' AND production_date <= 'yyyy-mm-dd'
GROUP BY mydate

Not sure on how to go about using the native functions to select this data and display it out using a foreach loop.

Can anyone point me in the right direction?

Thanks,
Ryan

By Ryan - November 8, 2018

After another bit of hacking about i managed to get the following working. 

While it does work I'm not sure if it's the correct or most efficient approach.

$CombinedTotals = mysqli()->query("SELECT 
				mydate, (SUM(field1)+SUM(field2)+SUM(field3)+SUM(field4)) AS Total
				FROM cmsb_tablename 
				WHERE mydate>= '$_SESSION[startDate]' AND production_date <= '$_SESSION[endDate]'
				GROUP BY mydate");
							
while ( $rows = $CombinedTotals ->fetch_assoc() ) {
    echo $rows['mydate'] . " | " . $rows['Total'];
    echo "<br>";
}

Ryan

By daniel - November 13, 2018

Hi Ryan,

For a custom query like this, you've found a generally correct approach. One small security issue is passing outside values into the query - such as from the $_SESSION variable. This can be addressed with an escape function, which could look something like this: 

"SELECT mydate, (SUM(field1)+SUM(field2)+SUM(field3)+SUM(field4)) AS Total
 FROM cmsb_tablename 
 WHERE mydate >= '" . mysql_escape($_SESSION['startDate']) . "' AND production_date <= '" . mysql_escape( $_SESSION['endDate'] ) . "'
 GROUP BY mydate"

Additionally, there's a shortcut function in CMSB that can simplify the direct querying process a bit, mysql_select_query():

$CombinedTotals = mysql_select_query("SELECT...");
							
foreach ( $CombinedTotals as $row ) {
    echo $row['mydate'] . " | " . $row['Total'];
    echo "<br>";
}

Let me know if you have any questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

By Ryan - November 14, 2018

Good point Daniel, I've escaped those variables now.