Display data from MySQL table

[encho]

As mentioned in the title, I have table in my database (same as PW) with 366 rows and want to display the fields on my site. I hate starting from scratch otherwise I would use repeater field to make it lot easier, but still struggling with basic concepts, so with more time I will eventually import csv into repeater fields through the api.

Table has columns: month, day, event, time, location. I have the php script working independently, using separate file for credentials to do mysql query, but I would like to use PWs already established connection to query my database. I have searched but found nothing on the topic. Please advise.

[Soma]

in templates you can do

$results = $db->query("SELECT * FROM yourtable");

or in modules

$results = $this->db->query("SELECT * FROM yourtable");

[Soma]

It would be a matter of couple php lines to import those data to a repeater or pages.

Also everything about repeater you should know is well described here:

http://processwire.com/api/fieldtypes/repeaters/

[encho]

Thanks, will try this code. And as for your suggestion for the repeater, I am still in process of learning. I need quick solution now and later I will spend more time investigating it. In mean time if someone has done something similar, please feel free to post it here

[encho]

Tried the code above and I get generic error 'Unable to complete this request due to an error. Error has been logged.' This is my code from standalone file, just replaced query part (btw I am not MySQL expert, just borrowed the code):

$day = date('d');
$month = date('m');
$table = "timetable";

$rows= array();
$results= $db->query("SELECT event,time,location FROM $table WHERE day = '$day' AND month = '$month' ORDER BY month ASC");
if ($results) {
// loop through the result set and inspect one row at a time
while ($row = $results->fetch(PDO_FETCH_ASSOC)) {
 array_push($rows, $row);
}
}

$event = $rows[0][event];
...

What am I doing wrong?

[teppo]

First of all, you'll get more descriptive error messages by turning debug mode on in /site/config.php. It's very useful while developing, but you shouldn't keep it on after a site has been made public -- in error situations like this it'll output way more information than you really want visitors to see.

Problem here seems to be that you're using PDO commands while what you should be using are mysqli ones with $db->query(), since it actually utilizes mysqli under the hood. More information about this can be found here: http://dev.mysql.com....choosing.html. Another (minor) problem is that you're using event as a constant at that last row (that $rows[0][event] should be $rows[0]['event'].)

With following changes your code should work properly. Note that I've also added extra check to the end of the script to make sure that some events are actually found. Originally you were only checking if a mysqli_result object was returned.. which doesn't necessarily mean that any data was found

$day = date('d');
$month = date('m');
$table = "timetable";

$rows= array();
$results= $db->query("SELECT event,time,location FROM $table WHERE day = '$day' AND month = '$month' ORDER BY month ASC");
if ($results) {
   // loop through the result set and inspect one row at a time
   while ($row = $results->fetch_array()) {
       array_push($rows, $row);
   }
}
if (count($rows)) {
   $event = $rows[0]['event'];
} else {
   // no data found. you might want to add some kind of error message here.
}
...

[Soma]

To check if there's really results you can check num_rows

if ($results->num_rows) {
// outpute the results
} else {
 // no results
}

[encho]

Thanks! That worked. I was not aware of the debugging option, really useful. Using diferrent cms' over the years I have neglected the php/sql, and instead I have learned different cms-specific tags and scripts. This is great opportunity to renew my skills.