Using the $adb object

[Guido1982]

Okay, so I've spent a couple of hours reading the entire PearDatabase file, and have two conclusions:

- Having learned a lot about PHP the last year, I still need more knowledge.
- The $adb is the system-wide used instance of the PearDatabase class, so all object methods live there through which you can communicate with the database.

Digging further into the method use of $adb, I found this example:

Code:

// Extra function to create unique invoice_number
function getInvoiceNumber() {
global $adb;
$sql="select max(subject) as aantal from vtiger_invoice where
Year(invoicedate) = year(now())";
$result = $adb->query($sql);
$invoice_subject= $adb->query_result($result,0,"aantal");

So basically the two most basic methods are 'query' in which you perform a query, and 'query_results', that'll give you an array with results. If anyone knows more good reading sources about this, please reply with links so I can learn more about his object, since it clearly seems to be one of the most important ones of the system.

[Guido1982]

I ran into the first glitch: I have the query

Code:

SELECT `invoiceid`, `subject` FROM `vtiger_invoice`

But it only selects one row, is that an error in my SQL or should I use the $adb object differently?

[joebordes]

a few pointers:

• pquery is more secure than query, pquery substitutes question marks for values, in general use pquery where you can
  

Code:

$rs = $adb->pquery('select subject from vtiger_invoice where invoiceid=?',array(22));

• query_result returns one row and one column
  

Code:

$val = $adb->query_result($rs,row,column);

• num_rows gives you the amount of rows returned in a result
  

Code:

$nrows = $adb->num_rows($rs);

• to iterate over a set of rows you use a loop. you can do that with a "for" and num_rows, but I prefer a while, like this:
  

Code:

while ($row = $adb->fetch_array($rs)) {
$col1 = $row['column1'];
$col2 = $row['column2'];
....
}

[Guido1982]

Yeah, I saw that. Basically pquery stands for 'prepared query' I presume?

Anyway, I do not understand the array parameter. I saw the $params in the pquery method but I dont understand it. There is a call to a method 'Execute' also in the method, but I couldn't find it anywhere else in the class. Is it a standard PHP method? I found something like this on PHP.net, but lower capped, not 'Execute', but 'execute'. Anyway, the

Code:

where invoiceid=?

will get me all rows then?

I've been using Smarty for a couple of years, so I'm a little familiar with the loop concept. I'll study your 'while' construction and try some different ways just to understand them better and why you would prefer the one you gave me, thanks for the help so far.

[joebordes]

Yes, pquery is "prepared query"

The idea of a prepared query is that you put question marks where you need values and then give it an array of values. The function will take care of doing any magic that is needed to safely setup an SQL command.

For example,

Code:

$adb->pquery('select subject from vtiger_invoice where invoiceid=?',array(22));

will end up with this SQL:

Code:

select subject from vtiger_invoice where invoiceid=22

if there were more than one question mark they would get substituted in order and safely.

If you read the SQL you will see that only ONE invoice will be returned, the one with invoiceid=22, if it exists.
To get them all you would do:

Code:

$adb->pquery('select * from vtiger_invoice',array());

[Guido1982]

Ah, and that is why the array is flattened within the 'pquery' method, to make sure there are no layered arrays going into the query.

---

I truly don't understand what I'm doing wrong then. My query code is:

Code:

<?php

    require "include/database/PearDatabase.php";

    $query = "SELECT `invoiceid`, `subject`, 'subtotal' FROM `vtiger_invoice`";
    $result = $adb->pquery($query,array());

    ?><pre><?php print_r($result); ?></pre><?php
    
?>

But all it returns is an object called 'ADORecordSet_mysql' containing only the first row...

[joebordes]

$result contains a record set, in other words, a collection of many rows, in this case many invoice rows.

Try this:

PHP Code:

<?php
include_once('vtlib/Vtiger/Module.php');
global $current_user,$adb;

    $query = "SELECT `invoiceid`, `subject`, 'subtotal' FROM `vtiger_invoice`";
    $result = $adb->pquery($query,array());
echo "<pre>";
while ($row=$adb->fetch_array($result)) {
 print_r($row);
}
echo "</pre>";    
?>

[Guido1982]

In that case there is some difference between the 'print_r' functionality from Smarty (what I'm used to work with).

I understand I need a loop to actually work with the results, but I thought print_r could give me exact details about the result, including all the rows. How should I visualize this 'record set', as an array, or an instance of a class? I'm asking this so I can solve this on my own from a better understanding next time.

---

In that case there is some difference between the 'print_r' functionality from Smarty (what I'm used to work with).

I understand I need a loop to actually work with the results, but I thought print_r could give me exact details about the result, including all the rows. How should I visualize this 'record set', as an array, or an instance of a class? I'm asking this so I can solve this on my own from a better understanding next time.

I noticed that when I use fetch_array on the result, assign that to a test var and then use "print_r" on that, it'll only give me something if I include the files you included in you example, but I don't understand why. I shall have to read the 'Module.php' file as well. I do understand the bringing in of the $adb instance from the global scope, didn't know I had to bring in the $current_user aswell.


You example is working, but I want to find the error in my thought process. For instance, using this:

PHP Code:

include_once('vtlib/Vtiger/Module.php');
 global $current_user,$adb;

 $query = "SELECT `invoiceid`, `subject`, 'subtotal' FROM `vtiger_invoice`";
 $result = $adb->pquery($query,array());
 
 $test = $adb->fetch_array($result);
 
 print_r($test); 


Gives me:

Code:

Array ( [0] => 89 [invoiceid] => 89 [1] => vtiger_invoice201 [subject] => vtiger_invoice201 [2] => subtotal [subtotal] => subtotal )

But I don't understand why. I'd expect the fetch_array to return an array of all the rows I requested. Why does it only return one, and only makes it possible to visualize all rows when using a while loop? In Smarty using print_r just gives me the entire array or object, including lower levels.

NOTE:

If there is any documentation on the $adb object or generic method descriptions for this: I'm not too lazy to read, I just haven't found it yet.

[joebordes]

That is just the way it works, the record set is just to big, it could contain thousands of records, so you don't get them all, you have explicitly retrieve the row you want. Be that with a direct access (query_result) or by looping them all.

I really don't know if there is more documentation on this object, but reading about PDO should help, all the data base abstraction libraries I have seen take the same approach.

[Guido1982]

Yes I understand the concept that the array could be too large, I just have to wrap my head around the concept that assigning via 'fetch_array' doesn't mean I'm actually getting all the info, only looping over this method will do that. Basically it means the loop is firing multiple SQL queries. 'fetch_array' gets basic data about the rows, but only the loop will get the actual data.

I'm running into some other beginner questions that will probably be easy once you get it, will continue on this tomorrow. In the meantime I'll study PDO.

---

About the including: is the 'Module.php' just the best way to go in most cases? In see it includes 'ModuleBasic.php', which in its turn includes a whole lot of file. Somewhere in those the $adb and $current_user are instantiated I figure.

And is it safe to assume that you always need to bring in $current_user when you want to perform DB query's?