Active Record (a bit of hand-holding needed).

[eluser]atw[/eluser]
Hi guys,

I have two questions:

1. why doesn't my query/function/model (below) work?
2. Is it possible to print the current SQL Query in a page (for debugging) without making part of the query ambiguous (as I have done below)?

This is the method I am working on in my model (the top comments are the query I am trying to recreate--which does work against this schema):

Code:

//  SELECT *, ($table.won - $table.lost) AS average, ($table.won + $table.lost) AS played
//  FROM players, $table, teams
//  WHERE ((players.id = avg_singles.player_id) AND (players.team = teams.team_id))
//  ORDER BY average DESC
  function get_players( $team = NULL, $order = 'id' )
  {
    $this->db->select('*');
    $this->db->select('(avg_singles.won - avg_singles.lost) AS average');
    $this->db->select('(avg_singles.won + avg_singles.lost) AS played');
    $this->db->from('players, avg_singles, teams');
    $this->db->where('players.id', 'avg_singles.player_id');
    $this->db->where('players.team', 'teams.team_id');
    $this->db->where('players.active', 'y');
    
    // optional parameters
    if(isset($team)) {
        $this->db->where('team', $team);
    }
    $this->db->order_by($order);
    
    // run query, then create nice array
    $query = $this->db->get();
    foreach ($query->result_array() as $row)
    {
      $result[] = $row;
    }
    echo '<PRE>'; print_r($result); echo '</PRE>';
    return $result;
  }

If I edit the final where clause segment to be ambiguous:

Code:

$this->db->where('active', 'y');

I can see that the query generated is pretty well correct:

Code:

SELECT *, (avg_singles.won - avg_singles.lost) AS average,
(avg_singles.won + avg_singles.lost) AS played
FROM (`players`, `avg_singles`, `teams`)
WHERE `players`.`id` = 'avg_singles.player_id' AND `players`.`team` = 'teams.team_id'
AND `active` = 'y'

However, I get the error (line 37 is "return $result" in the function above:

Code:

A PHP Error was encountered
Severity: Notice
Message: Undefined variable: result
Filename: models/dbmodel.php
Line Number: 37

This is the relevant part of my database schema:

Code:

--
-- Table structure for table `avg_singles`
--

CREATE TABLE `avg_singles` (
  `player_id` int(3) NOT NULL,
  `won` int(2) NOT NULL default '0',
  `lost` int(2) NOT NULL default '0',
  `last_updated` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`player_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `players`
--

CREATE TABLE `players` (
  `id` int(3) NOT NULL auto_increment,
  `fname` varchar(100) NOT NULL,
  `surname` varchar(100) NOT NULL,
  `team` int(3) NOT NULL,
  `phone` varchar(11) default NULL,
  `email` varchar(255) default NULL,
  `active` enum('y','n') NOT NULL default 'y',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `teams`
--

CREATE TABLE `teams` (
  `team_id` int(3) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `address1` varchar(255) NOT NULL,
  `address2` varchar(255) default NULL,
  `address3` varchar(255) default NULL,
  `city` varchar(255) NOT NULL,
  `county` varchar(255) NOT NULL,
  `postcode` varchar(8) NOT NULL,
  `captain_id` int(3) NOT NULL,
  `secretary_id` int(3) NOT NULL,
  `division` int(1) NOT NULL,
  `pub_photo` varchar(255) default NULL,
  `lng` float(10,6) default NULL,
  `lat` float(10,6) default NULL,
  `active` enum('y','n') NOT NULL default 'y',
  PRIMARY KEY  (`team_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Many thanks,


atw

[eluser]NateL[/eluser]
Enable Profiling in your constructor within your controller:

Code:

<?php
class Blog extends Controller {

       function __construct()
       {
            parent::Controller();
            $this->output->enable_profiler(TRUE);

       }
}
?>

Also - read your error. I think the problem is you're trying to echo the results out in the Model. If you enable profiling, you won't have to echo the results in the model. the Controller will handle all that

[eluser]atw[/eluser]
Profiling == AWESOME!
I haven't got as far as that in my learning process

I don't however, think you are correct regarding 'you’re trying to echo the results out in the Model'. That worked perfectly earlier, when I was trying a much simpler get-everything-from-one-table-test [lol]

I have now commented out ALL echo's and print_r's from anywhere outside my view and still it doesn't work.

Profiling shows this is my SQL query:

Code:

SELECT *, (avg_singles.won - avg_singles.lost) AS average, (avg_singles.won + avg_singles.lost) AS played
FROM (`players`, `avg_singles`, `teams`)
WHERE `players`.`id` = 'avg_singles.player_id'
AND `players`.`team` = 'teams.team_id'
AND `players`.`active` = 'y'
ORDER BY `id`

Q1. Does this SQL look syntactically correct (if that's a real word)?

Q2. Is there a better way to do my (very basic) JOINs?

I am using:

Code:

* MySQL 5.0.32
* PHP 5.2.0-8+etch15
* Apache 2.2.3
* CodeIgniter 1.7.1

Many thanks,

atw

[eluser]NateL[/eluser]
I could be wrong

pop open phpMyAdmin and run that query. Do you get results?

[eluser]atw[/eluser]
INTERESTING!

I just followed a hunch (the back ticks looked wrong). I went into phpMyAdmin and ran the query (copied verbatim from the profiler) and it returned zero rows.

Followed my hunch and removed the back ticks and it worked.

So, I just edited my model, like so:

Code:

$this->db->select('*');
$this->db->select('(avg_singles.won - avg_singles.lost) AS average');
$this->db->select('(avg_singles.won + avg_singles.lost) AS played');
$this->db->from('players, avg_singles, teams');
$this->db->where('players.id', 'avg_singles.player_id', FALSE);
$this->db->where('players.team', 'teams.team_id', FALSE);
$this->db->where('players.active', 'y');

Note the addition of a third FALSE argument to the WHEREs.

Will this be okay?

Why has it caused a problem (is it my version of MySQL)?

[eluser]atw[/eluser]
@NateL: Sorry, you posted too quickly

[eluser]David Johansson[/eluser]
[quote author="atw" date="1248657939"]INTERESTING!

I just followed a hunch (the back ticks looked wrong). I went into phpMyAdmin and ran the query (copied verbatim from the profiler) and it returned zero rows.

Followed my hunch and removed the back ticks and it worked.

So, I just edited my model, like so:

Code:

$this->db->select('*');
$this->db->select('(avg_singles.won - avg_singles.lost) AS average');
$this->db->select('(avg_singles.won + avg_singles.lost) AS played');
$this->db->from('players, avg_singles, teams');
$this->db->where('players.id', 'avg_singles.player_id', FALSE);
$this->db->where('players.team', 'teams.team_id', FALSE);
$this->db->where('players.active', 'y');

Note the addition of a third FALSE argument to the WHEREs.

Will this be okay?

Why has it caused a problem (is it my version of MySQL)?[/quote]

Your change is correct! The problem was that you were searching for a row where the field player.id had the exact value 'avg_singles.player_id' but you rather wanted the field player.id to have the same value as the field avg_singles.player_id, by adding FALSE you get you intended result.

[eluser]atw[/eluser]
That's awesome. Thank you very much for the help!

I still have a lot to learn regarding SQL.

In fact I have so much to learn period and new stuff keeps coming along to learn. How am I ever going to learn it all before I die? Nightmare!

atw