Newbie confusion about the database docs

[eluser]Doug Lerner[/eluser]
I'm finding the database docs very confusing. I have no real experience in MySQL (I've always used a different, proprietary database) and I'm trying to learn to use the active record pattern syntax because of all the benefits mentioned. But all the examples just say "this active record syntax is equivalent to such-and-such an SQL query", without explaining what either means.

And then there are all these places in the docs like this where it says something like "consider this example":

Code:

$this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20);

$query = $this->db->get();

But it doesn't say what the example is accomplishing, what the the return values are, how the first statement relates to the second statement with the get() in it, what the value of $query is when all done (is it an object? a single value? a field name?)

I can sort of guess from the variable names that this is searching from among the title fields in the table 'mytable" for a certain id and that the results are somehow limited (but by what I don't know).

Anyway, is there any "pre-documentation" that anybody can recommend that can make the CodeIgniter docs more understandable for somebody who doesn't understand SQL query syntax to start with?

And to the doc writers, can I suggest at least including what the return value types are with the examples?

Thanks for any assistance!

doug

[eluser]xwero[/eluser]
The example says: select all the title columns from the table mytable where the id is $id start from row 10 and limit the result to 20 rows. It's almost plain English.

The get method executes the query and returns a object that makes it possible to get the results if the query is returned without errors. By assigning the object to a variable you can use the object to get the result of the query in the way you want.

w3schools sql section is basic but gets you up to speed. after reading the tutorials you probably will notice that the AR select method isn't equal to the SELECT sql statement, as the insert and update methods are, but it's a quirk that is easy to understand.

[eluser]Colin Williams[/eluser]
Sounds like you missed the critical doc page: http://ellislab.com/codeigniter/user-gui...sults.html

[eluser]Doug Lerner[/eluser]
[quote author="xwero" date="1215519981"]The example says: select all the title columns from the table mytable where the id is $id start from row 10 and limit the result to 20 rows. It's almost plain English.

The get method executes the query and returns a object that makes it possible to get the results if the query is returned without errors. By assigning the object to a variable you can use the object to get the result of the query in the way you want.

w3schools sql section is basic but gets you up to speed. after reading the tutorials you probably will notice that the AR select method isn't equal to the SELECT sql statement, as the insert and update methods are, but it's a quirk that is easy to understand.[/quote]

Where is the connection between the first statement and the following statement that uses the get() method?

Does the first statement set a property in $this->db that is remembered for the get?

Thanks,

doug

[eluser]codec_frequency[/eluser]
The first statement prepares the query.
Second statement executes it and (if successful) stores the result in variable named $query.

[eluser]xwero[/eluser]
It's one statement. get, get_where, count_all_results, count_all, insert, update, delete, empty_table and truncate are the action methods. They execute the query, all other methods help to build the sql statement.

Code:

$query = $this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20)->get();

Internally those other methods store their parameters until an appropriate action method reads the data and empties the storage variables.

[eluser]Doug Lerner[/eluser]
[quote author="xwero" date="1215521912"]It's one statement. get, get_where, count_all_results, count_all, insert, update, delete, empty_table and truncate are the action methods. They execute the query, all other methods help to build the sql statement.

Code:

$query = $this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20)->get();

Internally those other methods store their parameters until an appropriate action method reads the data and empties the storage variables.[/quote]

Thanks. That compound statement makes more sense.

When split into two, I didn't see why the results of

$this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20)

didn't have to be stored in a variable to which the get() method was applied. Actually, I still don't see that, though I can see the compound statement working.

Thanks,

doug

[eluser]xwero[/eluser]
In php4 the method chaining doesn't work so you would have to write it as

Code:

$this->db->select('title');
$this->db->from('mytable');
$this->db->where('id', $id);
$this->db->limit(10, 20);
$query = $this->db->get();

Which is the same as the previous two snippets. The methods are not bound to a particular action method. that is the danger of the AR library and at the same time a blessing because with the cache methods you can do something like

Code:

$this->db->start_cache();
$this->db->select('title');
$this->db->from('mytable');
$this->db->where('id', $id);
$this->db->stop_cache();
// normally count_all_results destroys the data from the select, from and where method
$total_rows = $this->db->count_all_results();
// but because of the cache you can continue the statement
$this->db->limit(10, 20);
$query = $this->db->get();
$this->db->flush_cache(); // makes sure the cached data doesn't linger and starts bugging your code.
return $query->result();

Without the cache methods you would have to copy the select, from and where methods for the second query.

[eluser]Doug Lerner[/eluser]
Just one last set of questions for clarification. In the non-chaining examples, like

Code:

$this->db->select('title');
$this->db->from('mytable');
$this->db->where('id', $id);
$this->db->limit(10, 20);
$query = $this->db->get();

(1) Where is the information for the final get() call held? In a property of $this->db?

(2) What does that information look like? Some big string similar to an SQL request? Is it possible to echo it before the get() to see what it looks like?

(3) At what point does the accumulated request get cleared out?

Thanks,

doug

[eluser]Colin Williams[/eluser]

Code:

$query = $this->db->get();

1.) So, it exists in $query.

2.) $query is an object with methods like result(), result_array(), row(), row_array(), num_rows(). These are described at http://ellislab.com/codeigniter/user-gui...sults.html

3.) No need to clear it out after $db->get() method is called. Just set up a new query and assign it to a different variable if needed.

Code:

$bread = $this->db->get('bread');
if ($bread->num_rows() > 0)
{
   $breads = $bread->result();
}

$breads then looks like:

Code:

array(
   object (
      'id' => 1,
      'type' => 'rye'
   ),
   object (
      'id' => 2,
      'type' => 'wheat'
   )
);