Displaying a table of results

[eluser]Thorpe Obazee[/eluser]
errr..... that's good then...

[eluser]kgill[/eluser]
[quote author="mdcode" date="1239097323"]

The only thing about this is that the first column on the table echoes the project id number, however if I just had id in the foreach loop (with the id column in the projects table being named id), then it echoed the id of the customer, not the project. I only got around this by calling the id column in the projects table pid, not really ideal to me but it works -- any ideas how I can change it back to id and still have this work?

Many thanks for your help and time.[/quote]

You have two columns both with the name, "id", so when that gets turned into an object the one id is overwriting the other. If you want to have the tables both use the column name id, you'll need to alias them in your query 'projects.id proj_id, customers.id cust_id...' so that they get different names. I know you prefer the idea of short column names but the best solution is to use more descriptive column names in your tables to start with. E.g. cust_id, cust_name, cust_age, etc.

[eluser]mdcode[/eluser]
[quote author="kgill" date="1239102879"]You have two columns both with the name, "id", so when that gets turned into an object the one id is overwriting the other. If you want to have the tables both use the column name id, you'll need to alias them in your query 'projects.id proj_id, customers.id cust_id...' so that they get different names. I know you prefer the idea of short column names but the best solution is to use more descriptive column names in your tables to start with. E.g. cust_id, cust_name, cust_age, etc.[/quote]

Thanks for the input kgill, I summised that the second reference to "id" was overwriting the first in the set of results. Unfortunately, when I started pasting code here, and people saw that I had id columns and the like set as 'projectid' and 'customerid', I was advised early on to change them to simplify matters... so Idid and spent almost 2 hours reworking my entire db schema. I'm not sure how far to go changing it again...

[eluser]jedd[/eluser]
[quote author="mdcode" date="1239103573"][quote author="kgill" date="1239102879"]I know you prefer the idea of short column names but the best solution is to use more descriptive column names in your tables to start with. E.g. cust_id, cust_name, cust_age, etc.[/quote]

...
Thanks for the input kgill, I summised that the second reference to "id" was overwriting the first in the set of results. Unfortunately, when I started pasting code here, and people saw that I had id columns and the like set as 'projectid' and 'customerid', I was advised early on to change them to simplify matters... so Idid and spent almost 2 hours reworking my entire db schema. I'm not sure how far to go changing it again...[/quote]

Don't go changing, to coin a phrase.

"A", as distinct from "the best", solution is to change all your columns to reflect your table name, and then just in case you ever access two databases, you can further prefix the column names with the database name too ... so you will end up with:

Code:

customerdb_customer_id
customerdb_customer_age
customerdb_customer_address
customerdb_project_id
customerdb_project_name

Far more descriptive, of course, and will save you having to type 'AS cust_id' ever again .. and that's a HUGE saving. Sure, you get to type the word 'customer' 8 times as often, and the meaning of your column names is now obscured amongst a lot of redundant noise, and if you ever make a subtle change to your schema you're up for a huge number of changes to all your column names ...

</sarcasm>

Context. A wonderful thing. You, and everyone else, should use it. In the same we way do not need to have the string 'controller' in all our controllers' filenames - because they live in the controller sub-directory - we can also live with uniquely identifying a column by the table that it is in.

That's precisely what the dot is for - to provide context. customer.id is very readable and meaningful, and if you're only working in the customer table then the it's equally obvious and readable to rely on the context, and refer to just id and name and so on.

[eluser]mdcode[/eluser]
[quote author="jedd" date="1239120434"]Context. A wonderful thing. You, and everyone else, should use it. In the same we way do not need to have the string 'controller' in all our controller's filenames - because they live in the controller sub-directory - we can also live with uniquely identifying a column by the table that it is in.

That's precisely what the dot is for - to provide context. customer.id is very readable and meaningful, and if you're only working in the customer table then the it's equally obvious and readable to rely on the context, and refer to just id and name and so on.[/quote]

I believe that your comments from another thread of mine are the reason I changed my entire schema in the first place jedd, and I thank you for it. For instance my customers table now looks like this:

Code:

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(8)       | NO   | PRI | NULL    | auto_increment |
| name  | varchar(150) | NO   |     |         |                |
+-------+--------------+------+-----+---------+----------------+

I already am using 'context' as can be seen from my final select query:

Code:

$this->db->select('projects.id, projects.customer, projects.date_required, projects.job_status, customers.id, customers.name');

However in this instance it's not working for me, as in the foreach loop you cannot use periods to seperate the table and the column as you get a PHP error like this:

Code:

A PHP Error was encountered
Severity: Notice
Message: Undefined property: stdClass::$project
Filename: pages/projects_reports.php
Line Number: 36

and

A PHP Error was encountered
Severity: Notice
Message: Use of undefined constant id - assumed 'id'
Filename: pages/projects_reports.php
Line Number: 36

Is there a way to use "AS" statements in an AR query or should I just revert back to using straight MySQL queries (that admittedly I am more comfortable with), even though I thought that AR queries were the prefered method of writing queries when working with CodeIgniter?

[eluser]jedd[/eluser]

I believe that your comments from another thread of mine are the reason I changed my entire schema in the first place jedd, and I thank you for it.

I'm glad you're glad .. and it's the reason I got a bit tetchy at the contra-suggestion that lengthy column names are the preferred solution to an unstated problem.

Anyhoo.

I don't use the AR stuff, but I did just spend the requisite three minutes to run this script against one of my tables:

Code:

$this->db->select('login_name AS bob');
$query = $this->db->get('member');

echo "Number of results = ". $query->num_rows() . "<br />";
foreach ($query->result() as $row)
    echo $row->bob . "<br />";

Seems to work just fine. What was the problem you were having?

[eluser]mdcode[/eluser]
Searching and looking, looking and searching, a little experimentation, with a little help and prompting of course, I have got this working on one of those "Eureka!" moments, and once again, a little more understanding as to how things work which can never be a bad thing.

Final Model code:

Code:

function get_last_ten_entries()
{
$this->db->select('projects.id, projects.customer, projects.date_required,
                   projects.job_status, customers.id AS cust_id, customers.name');
$this->db->from('projects');
$this->db->join('customers', 'projects.customer = customers.id', 'left');
$this->db->order_by('projects.id', 'DESC');
$this->db->limit(10);

return $this->db->get();
}

Thanks once again jedd!

[eluser]kgill[/eluser]
[quote author="jedd" date="1239168678"]
I'm glad you're glad .. and it's the reason I got a bit tetchy at the contra-suggestion that lengthy column names are the preferred solution to an unstated problem.

Anyhoo.

[/quote]

Tetchy? little bit of an understatement lol Perhaps I should have prefaced things with a "in my opinion" but that should have been implied. Either way we'll just have to disagree on this one.