displaying a mysql table using an array in PHP - getting an "Trying to get property of non-object error

Question

Good day, I'm still new to PHP so bare with me,

my sql works in php myadmin, but when I try to display the table in php, the array doesn't get any of the values from the object

lines generating the error are all 5 $row[] = $record->column_name;

$sql = "Select 
  DATE(FROM_UNIXTIME(timecreated)) AS 'date', 
  COUNT(DISTINCT userid) AS 'unique_visitor', 
  SUM(IF (action = 'viewed', 1,0)) AS 'viewed',
  SUM(IF (action = 'created', 1,0)) AS 'created',
  SUM(IF (action = 'restored', 1,0)) AS 'restored'
  FROM `table_name`
  WHERE `courseid` = $courseId AND (`timecreated` Between $startDateU AND $endDateU)
  GROUP BY DATE(FROM_UNIXTIME(timecreated))";

PHP code

$records = $DB->get_record_sql($sql, null);

//create the table
$table->define_columns(array('date', 'unique_visitor', 'viewed', 'created', 'restored'));
$table->define_headers(array('Date', 'Unique Visitor', 'Viewed', 'Created', 'Restored'));

$table->set_attribute('class', 'generaltable generalbox boxaligncenter boxwidthwide table-bordered');
$table->show_download_buttons_at(array(TABLE_P_BOTTOM));


$table->setup();

print_r($records) . "<br><br>";

if ($records) {
    foreach ($records as $record) {

        $row = array();

        $row[] = $record->date;
        $row[] = $record->unique_visitor;
        $row[] = $record->viewed;
        $row[] = $record->created;
        $row[] = $record->restored;
        
        $table->add_data($row);
    }
}

$table->finish_output();

this is the result where I see the object before the table, but cant build the array, thus the table is blank

What line generates _Trying to get property of non-object error_? — AbraCadaver, Aug 09 '22 at 15:43
`$startDateU` and `$endDateU` need to be in quotes. But you shouldn't substitute variables directly into the query, you should use a prepared statement with parameters. — Barmar, Aug 09 '22 at 15:54
lines generating the error are all 5 times I try to assign a value to $row[] = $record->column_name; — Zealotwraith, Aug 09 '22 at 17:20
@barmar, i just looked up the prepare statement, how would I apply it here? would ireplace my "where" with WHERE `courseid` =? AND (`timecreated` Between `stardate`=? AND `endate`=?) and then use $stmt->bind_param("sss", $courseId, $startDateU, $endDateU); — Zealotwraith, Aug 09 '22 at 17:37
See https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Barmar, Aug 09 '22 at 17:38

score 0 · Answer 1 · answered Aug 09 '22 at 17:39

Fellow Moodler? :)

$sql = "
  SELECT DATE(FROM_UNIXTIME(timecreated)) date, COUNT(DISTINCT userid) unique_visitor, SUM(IF (action = 'viewed', 1,0)) viewed, SUM(IF (action = 'created', 1,0)) created, SUM(IF (action = 'restored', 1,0)) restored
    FROM `table_name`
   WHERE `courseid` = :courseId AND (`timecreated` BETWEEN :startDateU AND :endDateU)
GROUP BY DATE(FROM_UNIXTIME(timecreated))";

$records = $DB->get_record_sql($sql, ['courseId' => $courseId, 'startDateU' => $startDateU, 'endDateU' => $endDateU]);

score 0 · Answer 2 · answered Aug 10 '22 at 15:10

I feel dumb for this ... but I changed the "get_record_sql to "get_records_sql" and everything started working ... thanks everyone for their feedback and the help with the SQL as well

as was $records = $DB->get_record_sql($sql, null);

as is (solution) $records = $DB->get_records_sql($sql, null);

displaying a mysql table using an array in PHP - getting an "Trying to get property of non-object error

2 Answers2