about DB sql query

about DB sql query

by hui chi -
Number of replies: 2

hello,

I  make a plugin by myself. 

I hope I can connect DB and show some data on web.

but it alway show fail sql connect fail.

I don't know what's wrong?

can anyone help me?

this is my reference:

https://docs.moodle.org/dev/Data_manipulation_API#DB_object


this is my code:


<?php
 
require_once(__DIR__ . '/../../config.php');
defined('MOODLE_INTERNAL') || die();
global $CFG, $DB, $OUTPUT,$USER;
 
$sql="SELECT * FROM mdl_course ";
    $Re = $DB->get_records_sql($sql);
   
    $rs = $DB->get_recordset($sql) ;
   
      foreach ($rs as $record) {
        echo $row['id']; //I try to show id data but fail.....
      }
?>
thanks.

Average of ratings: -
In reply to hui chi

Re: about DB sql query

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I think there are a number of issues you should look at in your code.

Firstly, there is no point in checking for "defined('MOODLE_INTERNAL') || die();" immediately after including the main 'config.php' file (the MOODLE_INTERNAL check is for files that do not directly include config.php themselves, to avoid library files being accessed directly and causing unwanted side effects).

Next, if you want to do a simple query like you have done, use $DB->get_records('course'); instead of constructing an SQL query (and if you really need to construct an SQL query, make sure you write "{course}" not "mdl_course", or your code will break on any site that uses a non-standard prefix, including PHPUnit / Behat automated testing sites).

I'm not clear why you are calling $DB->get_records_sql($sql) and then ignore the result of that and immediately call $DB->get_recordset($sql). The first should work (although it would be better to use $DB->get_records(), as I mentioned above), but the second won't work, as it would expect the form: $DB->get_recordset('course', []); (you could use $DB->get_recordset_sql($sql) if you _really_ wanted to, but it's not the best approach).

Finally, you loop through $rs, returning each entry in the results as a variable called $record, before completely ignoring $record and trying to access a brand-new variable called $row (that you've not mentioned until then). $record will also be an object, so $record->id would be the correct way to access the id.

Please make sure you have turned Debugging on to developer level and that will mean you get helpful error messages to show your mistakes.
Average of ratings: Useful (1)
In reply to Davo Smith

回應: Re: about DB sql query

by hui chi -
hello ,Davo Smith
thank for your help, I hava slove the problem
this is my code:

 <?php
require_once(__DIR__ . '/../../config.php');
defined('MOODLE_INTERNAL') || die();
global $CFG, $DB;

$sql="SELECT * FROM {course} ";
$rs = $DB->get_records_sql($sql);
foreach ($rs as $record) {
echo $record->fullname;
}
?>


 share for anyone who's need.
thanks~
Average of ratings: Useful (1)