SQL problem

Scriptcase 8 Discussion
, ,
#1

I can run this SQL statement in MySQL workbench and it works great but when I run it in scriptcase all I get is a blank page with the tile.
$sSQL = “set @last = ( select max(date) from all_data);
SELECT
type_entry,
COUNT(CASE WHEN DATEDIFF(@last, date) < 31 THEN value END) as val_count1,
FLOOR(SUM(CASE WHEN DATEDIFF(@last, date) < 31 THEN value END)) as sum_glu1,
COUNT(CASE WHEN DATEDIFF(@last, date) < 14 THEN value END) as val_count2,
FLOOR(SUM(CASE WHEN DATEDIFF(@last, date) < 14 THEN value END)) as sum_glu2,
COUNT(CASE WHEN DATEDIFF(@last, date) < 7 THEN value END) as val_count3,
FLOOR(SUM(CASE WHEN DATEDIFF(@last, date) < 7 THEN value END)) as sum_glu3
FROM all_data
WHERE DATEDIFF(@last, date) < 31 AND type_entry = ‘Glucose’
GROUP BY type_entry”;
sc_lookup(dataset,$sSQL);

?>
<html>
<head>

&lt;link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" &gt;
&lt;/head&gt;

&lt;body&gt;
    &lt;div "container" style = "text-align: center"&gt;
            &lt;h2&gt;Snapshot&lt;/h2&gt;
    &lt;/div&gt;
    &lt;?php
        foreach({dataset} as $avalue){
    ?&gt;
    &lt;div class="container"&gt;
         &lt;table class = "table" style="text-align: center""width: 10%"&gt;
             &lt;tr&gt;
                &lt;td&gt;&lt;?php echo 'Number of Test for 7 Days: &lt;/td&gt;&lt;td&gt;'. $avalue[5]; ?&gt;&lt;/td&gt;
            &lt;/tr&gt;        
            &lt;tr&gt;
                &lt;td&gt;&lt;?php echo 'Average for 7 Days: &lt;/td&gt;&lt;td&gt;'. ROUND($avalue[6]/$avalue[5]); ?&gt;&lt;/td&gt;
            &lt;/tr&gt;
             &lt;tr&gt;
                &lt;td&gt;&lt;?php echo 'Number of Test for 15 Days: &lt;/td&gt;&lt;td&gt;'. $avalue[3]; ?&gt;&lt;/td&gt;
            &lt;/tr&gt;    
            &lt;tr&gt;
                &lt;td&gt;&lt;?php echo 'Average for 14 Days: &lt;/td&gt;&lt;td&gt;'. ROUND($avalue[4]/$avalue[3]); ?&gt;&lt;/td&gt;
            &lt;/tr&gt;
             &lt;tr&gt;
                &lt;td&gt;&lt;?php echo 'Number of Test for 30 Days: &lt;/td&gt;&lt;td&gt;'. $avalue[1]; ?&gt;&lt;/td&gt;
            &lt;/tr&gt;    
            &lt;tr&gt;
                &lt;td&gt;&lt;?php echo 'Average for 30 Days: &lt;/td&gt;&lt;td&gt;'. ROUND($avalue[2]/$avalue[1]); ?&gt;&lt;/td&gt;
            &lt;/tr&gt;
            
        &lt;/table&gt;        
    &lt;/div&gt;    
    
    &lt;?php
        }
    ?&gt;
&lt;/body&gt;

</html>
<?php
Anyone know what is happing?

#2

Hi, Gehrenfeld,

Have you tried to check for errors after calling sc_lookup?

From the macros documentation I took this sample:

sc_lookup(my_data, “select customer_id, customer_name, credit_limit from customers”);
if ({my_data} === false)
{
echo “Access error. Message=”. {my_data_erro} ;
}
elseif (empty({my_data}))
{
echo “Select command didn’t return data”;
}
else
{
{customer_id} = {my_data[0][0]};
{customer_name} = {my_data[0][1]};
{credit_limit} = {my_data[0][2]};
}

Regards,

#3

No errors but still get a blank page.

#4

I can only think of a PHP syntax error in your code… could you use the developer tools from your navigator (something like Firebug for Firefox or the F12 key in Chrome) and try to find where the execution stops?

Because you’re saying you get a blank page, I’m assuming you don’t see any output, not even the literal strings. Am I right?

Good luck,

#5

The code works and runs just find on my website as a php file but when I try it in scriptcase it runs without errors but shows a blank page with the title of the page.