Champions

[Xtremer360]

I'm trying to develop the right kind of query with this. I've tried this before and don't remember how. I just want the best way to do it without rejoining the tables more than once.

 

Basically the desired results are that it should list the titles with the champion's name, contender 1's name, contender 2's name, and contender 3's name for each title.

 

I also am using codeigniter's active record class.

 

I have 3 tables:

 

Table 1: Titles

            Fields: id, title_name

Table 2: Roster

            Fields: id, roster_name

Table 3: Title Champions

            Fields: title_id, champion_id, contender1_id, contender2_id, contender3_id

 

 

[SalientAnimal]

Hi

 

The difficult thing here is that we ahve no idea what your data looks like so can't even identify and common fields. Remember to ensure that you have a successful join you have to have at least one field that has some sort of unique identifier.

 

 

[Barand]

Normalise the champions table. That will simplify your joins.

[Barand]

P.S.

 

Example, if I interpreted your relationships correctly

 

+---------------+               +---------------+               +---------------+
|   Title       |               | Title_champion|               |   Roster      |
+---------------+               +---------------+               +---------------+
|  id           | ------------< |  title_id     |      +------- |  id           |
|  title_name   |               |  contender_no |      |        |  roster_name  |
+---------------+               |  roster_id    | >----+        +---------------+
                                +---------------+               

SELECT t.title_name, 
       CASE WHEN c.contender_no = 0 THEN 'Champion' ELSE 'Contender' END as champion,
       r.contender_no,
       r.roster_name
FROM title t
       INNER JOIN title_champions c ON t.id = c.title_id
       INNER JOIN roster r ON c.roster_id = r.id

[Xtremer360]

How do I normalize my table?

[Xtremer360]

Why do you have contender_no? I'm guessing its supposed to stand for contender number but I have fields with different numbers.

[xyph]

Why do you have contender_no? I'm guessing its supposed to stand for contender number but I have fields with different numbers.

 

For this clause

WHEN c.contender_no = 0 THEN 'Champion' ELSE 'Contender' END as champion

 

If the given roster_id is the champion, it's contender_no should be 0

Otherwise, the contender_no should be 1, 2 or 3, in reference to contender1_id, contender2_id, contender3_id

 

Rather than keep each entry in a single row, you're creating a row for each contender.

[Xtremer360]

So your saying I should change the db column name to contender_no?

[Xtremer360]

Now I need some help coming up with the codeigniter active record query for it.

 

[Barand]

It's not just a case of changing a column name. You need to restructure the table so there are rows for each champion/contender instead of a single row with four id values.

 

I gave you a query for the redesigned table.

[Xtremer360]

I tried doing this:

 

/**

* Get all title champions and contenders

*

* @return object

*/

    function get_title_champions()

    {

        $query = $this->db->query("SELECT ". $this->titles_table.".title_name,

      CASE WHEN ".$this->title_champions_table.".contender_no = 0 THEN 'Champion' ELSE 'Contender' END as champion,

      ". $this->roster_table.".contender_no,

      ". $this->roster_table.".roster_name

FROM". $this->titles_table."

      INNER JOIN ". $this->title_champions_table." ON".$this->titles_table.".id =".$this->title_champions_table.".title_id

      INNER JOIN ". $this->roster_table." ON ".$this->title_champions_table.".roster_id =".$this->roster_table.".id");

       

        if ($query->num_rows() > 0) return $query->result();

return array();

    }

 

And it gives me this error:

 

Error Number: 1064

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN title_champions ONtitles.id =title_champions.title_id INNER JO' at line 6

 

SELECT titles.title_name, CASE WHEN title_champions.contender_no = 0 THEN 'Champion' ELSE 'Contender' END as champion, roster.contender_no, roster.roster_name FROMtitles INNER JOIN title_champions ONtitles.id =title_champions.title_id INNER JOIN roster ON title_champions.roster_id =roster.id

 

Filename: /home/xtremer/public_html/kowmanager/modules/titles/models/titles_model.php

 

Line Number: 73

[Barand]

you missing a couple of spaces between words eg FROMtitle

[Xtremer360]

Okay so something isn't right then because in my view file I'm getting this:

 

http://jsfiddle.net/5FcT4/

 

And it should ONLY be showing 3 rows since I have 3 titles and then the champions name, and each contenders's name on one row .

 

here's the current query:

 

/**
 * Get all title champions and contenders
 *
 * @return	object
 */
    function get_title_champions()
    {
        $query = $this->db->query("SELECT ". $this->titles_table.".title_name, 
       CASE WHEN ".$this->title_champions_table.".contender_no = 0 THEN 'Champion' ELSE 'Contender' END as champion,
       ". $this->title_champions_table.".contender_no,
       ". $this->roster_table.".roster_name
FROM ". $this->titles_table."
       INNER JOIN ". $this->title_champions_table." ON ".$this->titles_table.".id =".$this->title_champions_table.".title_id
       INNER JOIN ". $this->roster_table." ON ".$this->title_champions_table.".roster_id = ".$this->roster_table.".id");
        
        if ($query->num_rows() > 0) return $query->result();
	return array();
    }

[Mahngiel]

Now I need some help coming up with the codeigniter active record query for it.

 

Your controller should pass the values to your model, which interacts with the database.

 

controller

<?php
$this->load->model('model_name', 'model');

$data = 'CASE WHEN ".$this->title_champions_table.".contender_no = 0 THEN 'Champion' ELSE 'Contender' END as champion ';

$title_champs = $this->model->get_title_champions( $data );

model

<?php
function get_title_champions( $data = '' ) {

if( $data = '' ) return FALSE; 

$this->db
          ->select('title_name')
          ->where($data)
          ->join('champ_table', 'titles_table.id = champ_table.title_id' 'inner')
          ->join('roster_table', 'champ_table.roster_id = roster_table.roster_id', 'inner')
          ->get();

// Check if query result exists
if($query->result()) {

// Query row exists, return query row
return $query->result();
} else {
// Query row doesn't exist, return FALSE
return FALSE;
}

 

Untested, but i have several handshakes that follow this methodology.

[Xtremer360]

I do have it doing that however the query itself is not right.

[Barand]

And it should ONLY be showing 3 rows since I have 3 titles and then the champions name, and each contenders's name on one row .

 

With the structure and joins I suggested it will give a row for each matched title_champions row.

 

If you want a single row for each title you can

 

SELECT t.title_name,
GROUP_CONCAT(r.roster_name, CASE WHEN c.contender_no = 0 THEN ' (Champion)' ELSE '' END
    ORDER BY c.contender_no SEPARATOR ', ' ) as Contenders
FROM title t
       INNER JOIN title_champions c ON t.id = c.title_id
       INNER JOIN roster r ON c.roster_id = r.id
GROUP BY t.title_name

Result example-->

+-------------+----------------------------------------+
| title_name  | Contenders                             |
+-------------+----------------------------------------+
| Heavyweight | fred (Champion), joe, alan, ben        |
| Junior      | harry (Champion), ken, lou, mike       |
| Lightweight | charlie (Champion), dave, eric, george |
+-------------+----------------------------------------+

[Xtremer360]

If you look at how my view file is I don't know if that rendered table will work because of how I want it to look. It should be:

 

title_name  contender1 contender2 contender3

test title      jeff                      mike        kevin

test title 2  will                      steven    dwayne

test title 3  josh                    tom          bob

[Barand]

Then

 

<?php

$res = mysql_query("SELECT t.title_name,
    GROUP_CONCAT(r.roster_name ORDER BY c.contender_no SEPARATOR '</td><td>' ) as Contenders
FROM title t
       INNER JOIN title_champions c ON t.id = c.title_id
       INNER JOIN roster r ON c.roster_id = r.id
GROUP BY t.title_name");

echo "<table border='1' cellpadding='4'>\n";
echo"<tr><th>Title</th><th>Champion</th><th>Contender 1</th><th>Contender 2</th><th>Contender 3</th></tr>";
while (list($title, $cons) = mysql_fetch_row($res))
{
    echo "<tr><td>$title</td><td>$cons</td></tr>\n";
}
echo "</table>\n";
?>

 

[Xtremer360]

Any other better ideas? I'd rather not have a query like that?

[xyph]

I'd suggest doing it your way, then?

[Barand]

it's a simple trade-off. Simple SQL but more complex PHP output.

 

This gives the same results as my other post

 

<?php
  
$res = mysql_query("SELECT t.title_name, c.contender_no, r.roster_name
FROM title t
       INNER JOIN title_champions c ON t.id = c.title_id
       INNER JOIN roster r ON c.roster_id = r.id
ORDER BY t.title");

echo "<table border='1' cellpadding='4'>\n";
echo"<tr><th>Title</th><th>Champion</th><th>Contender 1</th><th>Contender 2</th><th>Contender 3</th></tr>\n";

$newArr = array('','','','');
$lastTitle = '';

while (list($title, $conno, $name) = mysql_fetch_row($res))
{
    
    if ($title != $lastTitle) {                                        // change of title value?
        if ($lastTitle) {                                              // don't output first, it is empty
            $constr = join('</td><td>', $cons);                        
            echo "<tr><td>$lastTitle</td><td>$constr</td></tr>\n";
        }
        $cons = $newArr;                                               // reset
        $lastTitle = $title;
    }
    $cons[$conno] = $name;                                             // store name in array
}
$constr = join('</td><td>', $cons);
echo "<tr><td>$lastTitle</td><td>$constr</td></tr>\n";                 // don't forget the last one

echo "</table>\n";
?>

[Xtremer360]

I just don't understand how a query can't be made so that the array looks like this:

 

[0] Title One name

    [champion] Jeff

    [contender1] kevin

    [contentder2] brian

    [contender3] will

 

[1] Title Two name

    [champion] Jeff

    [contender1] kevin

    [contentder2] brian

    [contender3] will

 

[2] Title Three name

    [champion] Jeff

    [contender1] kevin

    [contentder2] brian

    [contender3] will

 

And then do a foreach title display it and then each the champion and contenders.

[Barand]

You can do that using that last query of mine. The emphasis is on the "you" in that last sentence.

[Xtremer360]

I really wish there was a simpler way to write this

[Barand]

there is, if that's how you want them displayed.

 

<?php

$res = mysql_query("SELECT t.title_name, c.contender_no, r.roster_name
FROM title t
       INNER JOIN title_champions c ON t.id = c.title_id
       INNER JOIN roster r ON c.roster_id = r.id
ORDER BY t.title_name, c.contender_no") or die(mysql_error());

$lastTitle = '';
echo "<table>\n";
while (list($title, $conno, $name) = mysql_fetch_row($res))
{
    
    if ($title != $lastTitle) {                                        // change of title value?
        echo "<tr><th colspan='2'><br />$title</th></tr>\n";
        $lastTitle = $title;
    }
    $cstr = $conno==0 ? "Champion" : "Contender $conno";
    echo "<tr><td>$cstr</td><td> $name</td></tr>\n";
}
echo "</table>\n";
?>

 

Or have you changed your mind again about how you want to output the results?