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,
Technical Lead
interactivetools.com