PHP-MySql coding

PHP-MySql coding

by Robert Lefebvre -
Number of replies: 7

 the way I was taught to connect to a
 db was with this type of command

>: $sql1 = "SELECT * FROM mdl_university
> where university_num = '$university_num'"; $result1 =
> @mysql_query($sql1, $conn) or die("Couldn't execute 'Continue addition'
> query1 ".$sql1);
 and then retrieve some vals


> while ($row = mysql_fetch_array($result1))
> {     //while in
>
> $university_num = $row['university_num'];

 The $conn variable in @mysql_query($sql1, $conn)is an array of the db
 host, username, password This method has always worked fine for me.

I need help to understand the syntax you use in Moodle. Where are the username, password, table name values stored? And where and how is the sql query stored?

 

 

Thanks

 

Average of ratings: -
In reply to Robert Lefebvre

Re: PHP-MySql coding

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
All the connections are done for you (in lib/setup.php).

All you need to do is use the functions in lib/datalib.php to get records etc. Most of these return nice sorted arrays of data objects, no need for all this low-level stuff. smile
Average of ratings: -
In reply to Martin Dougiamas

Re: PHP-MySql coding

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
Robert: Moodle uses the ADOdb php data independence layer. One of the mooted benefits of this is that the syntax is a bit like the MS ado stuff, but don't let that put you off smile. It is a nicer syntax than the native code (as you gave in your example) and on top of that it allows you to switch to alternative databases.

As Martin says though, you should have all the connectivity done for you by Martins code, with the added benefit that a whole swag of other people will have tested it for you.

Either way, check out the benefits of adodb, as it would be a nice choice even if it didn't bring database independence with it.

Marcus
Average of ratings: -
In reply to Marcus Green

Re: PHP-MySql coding

by Robert Lefebvre -

Thanks for the help Marcus.

I've looked at the files that Martin mentioned and I sort of understand what they are doing. What I can't seem to grasp is the path of the connection (please excuse my poor choice of words). What I mean is in my native code, the variable $conn carries the values of the username, password, etc.

I would guess then that I just need to do an "include lib/setup.php" statement?

Then I don't understand where the code in datalib.php picks up the values for the variables in it. For example, the code there in a "get records" type function read like this:

return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort $limit");

how do I get a value into $fields?

Perhaps a simple sample code would help the nickel to drop but so far I haven't caught on. I didn't want to press it either with the launch of the new version, but I appreciate you took the time. 

Average of ratings: -
In reply to Robert Lefebvre

Re: PHP-MySql coding

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
For a very simple example, see the script moodle/userpix/index.php within the current distributions. You pass parameters to functions like get_records to specify what you want to get.

It's designed to make things easy. All the connection details, table prefixes etc are all handled.

eg:

get_records("user"); // gets all records from the user table

get_records("user", "lang", "EN"); // gets all the English speakers

get_record("user", "id", 1); // Gets the single user record with id = 1
Average of ratings: -
In reply to Martin Dougiamas

Re: PHP-MySql coding

by Robert Lefebvre -

Ok, that broke away a big part of the log jam. I think I need to digest what all the 50+ functions in datalib.php do. Right now they are still sort of a black box to me.

Question: After you get_records (I've now gotten that far) how do I get the values out??wideeyes.gif

In my old coding method I used > while ($row = mysql_fetch_array($result1))
> {     //while in
>
> $university_num = $row['university_num'];

Once I get the value into a varible I'll be ok. If the value comes out as an array I can explode it, or I can handle them one at a time, whatever., but the next log jam is :

Using the functions, the function uses "return get_record"

What would I use to see inside "return get_record"?blackeye.gif I've tried a few but some sort of "echo return get_record" would help me, I think.

Somewhere in the return get_record is that variable university_num that I need.

By the way, Martin, I realize this is pretty basic stuff to you all and I appreciate the help. Has any of your studies looked at the effectiveness of "looking over the shoulder" of more experienced people as a teaching  technique?  approve.gif  That is a big part of what Moodle enables the users to participate in.

Average of ratings: -
In reply to Robert Lefebvre

Re: PHP-MySql coding

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
Yes, you're talking about "mentoring" which is a big part of social constructionism and my preferred approach.

get_record() returns the data as an object.  So if you do:

      $user = get_record("user", "id", 1);

then $user is assigned as the object (or NULL if the record wasn't found) and you can access the fields of that object record as $user->firstname, $user->lastname  etc.

To "see" the object for debugging purposes insert a line like this in your code:

      print_object($user);


get_records() returns data as an *array* of the above objects. eg 

      $users = get_records("user");

To loop through the array in order just:

      foreach ($users as $user) {
          echo "$user->firstname $user->lastname";
      }

I'd recommend you copy a script like moodle/userpix/index.php and start playing with it a bit.
Average of ratings: -
In reply to Martin Dougiamas

Re: PHP-MySql coding

by Robert Lefebvre -

Hey, that got it!biggrin.gif

Thanks, Martin

I don't know how useful this will be, but I took the datalib.php page and made a table of contents of all the functions (there are over 50) and indexed each of them to the section of the page where the appropriate code is. To take a look: http://advertisite.us/missions_training/library/?l_lid=93#get%5frecords%5flist

A slightly different version is at http://advertisite.us/missions_training/library/?l_lid=92

I used the "Library" module that someone submitted to moodle a while ago to build it. Library doesn't seem to get much discussion but I'm finding it pretty useful.

Copy and paste them if this is something you want for moodle.org.

Average of ratings: -