Database join question...

[frullix]

I have this table:

Result

id-----Name----------Competition----------Result-----Competition nr
1------Alex(id.2)----Lubjana Cup(id.4)------20-------------1 (id.1)
2------Alex(id.2)----Lubjana Cup(id.4)------26-------------2 (id.2)

Now I'd like to view in another list

if Competition nr == 1 (or 2)

return

Name, Competition, Result_1, Result_2
Alex----Lubjana Cup----20---------26

how could I solve?
Thank you in advance.

 

[juuser]

Hi, I think you need to explain a bit more your setup and what exactly do you want to achieve, e.g screenshots from table setups drawn in Excel etc.

 

[frullix]

Yes sorry, it is really difficult to explain. I have made an outline with excel, I hope it is easier to understand

 

[juuser]

OK, seems clear now. It's a bit complicated.

One option would be to make a players table and create calc elements for competition result 1, 2, 3 and 4. In the calc element you can query and sum the results.

Or you could check out list "pivot" plugin (haven't used myself) or mysql views which you can link to Fabrik list.

 

[frullix]

Hi juuser, I tried with pivot plugin, but i think that is not right way.
Maybe "If else" function...

if ('{table_player___competition}' == 1 OR 2 OR 3)
return '{table_player___name_player}';
else
return ' ' ;
---(Not work!)---

for every elemets

and for the final rank I used:

$a = (float) '{finale___competition_1}';
$b = (float) '{finale___competition_2}';
$c = (float) '{finale___competition_3}';

$rank = max($a,$b,$c);

$result = number_format($rank, 2);

return $result;
(It work!)

 

[juuser]

Yup, calc elements are probably your best bet to keep it within Fabrik core functions.

As I understand you have already created a "players" table. And you have joined a "competition" and "results " table to that "players" table?
If that's done you can create the calc element contents with "if" and "then" like you suggested.

I don't know if the first bit of code is just a concept / example, but it has some issues. You have to do something like:

if ('{table_player___competition_raw}' == 1 || '{table_player___competition_raw}' == 2 || '{table_player___competition_raw}' == 3) {
return '{table_player___name_player}';
} else {
return ' ' ;
}

"_raw" is for getting the value not label in case of dropdown and databasejoin elements.

 

[frullix]

Yes, I've a players table with a Join "competition" and "results " table.
The first bit of code is just a concept / example.
This is the online view

and this is the db result table

I've tried your code, but not work, I think because must be group by "categoria"

if ('{table_player___competition_raw}' == 1 || '{table_player___competition_raw}' == 2 || '{table_player___competition_raw}' == 3) {
return '{table_player___fs_score}';
) {
return '{table_player___name_player}';
} else {
return 'x ' ;
}

It's most complicated, because for every "atleta" there are three different competition
and it must always be connected to the athlete, the category and the competition.

exemple for "Gara 1"

if ('{table_player___competition_raw}' == 1) {
return '{table_player___fs_score}';
} else {
return '0' ;
}

 

[juuser]

Sorry, misinterpreted your needs a bit wrong, that tends happens when you have time to concentrate just for a few moments and several topics are on the table at the same time.

There seems no need to join the other tables to players table in the list settings. You will just get multiple rows / records for one player which you probably don't need.

Just fetch the data to players table from competitions and results table using queries in your calc elements, something like:

$mydb =& JFactory::getDBO();
$mydb->setQuery("SELECT result, competition_id FROM results_table
WHERE player_id = '{table_player___name_player_raw}' ");
$myrow = $mydb->loadRow();

$myresult = $myrow['0'];
$mycompetition = $myrow['1'];

And then do the if-else thing with the query results like:

if($mycompetition == something) {
return $result;
} else {
return "";
}

 

[frullix]

Hi juuser, no problem! Thank you so much for your help!!

I tried editing your code:
For the player's name

$mydb =& JFactory::getDBO();
$mydb->setQuery("SELECT at.*, scheda_atleta.* FROM at, scheda_atleta
WHERE scheda_atleta.atleta = at.id AND qualificazione = 1 order by categoria");
$myrow = $mydb->loadRow();

$myresult = $myrow['1'];

return $myresult;

and for the results:

$mydb =& JFactory::getDBO();
$mydb->setQuery("SELECT fs_score, qualificazione, at.*, scheda_atleta.* FROM at, scheda_atleta
WHERE scheda_atleta.atleta = at.id AND qualificazione = 1 order by categoria");
$myrow = $mydb->loadRow();

$myresult = $myrow['0'];
$mycompetition = $myrow['1'];

if($mycompetition == 1) {  // or ==2  or ==3
return $myresult;
} else {
return '0';
}

but I've three problems:
1°) populates the first line only -
I would like it to populate with every player with "qualificazione == 1 OR 2 OR 3"
maybe use $myrow = $mydb->loadRowList();
instead of
$myrow = $mydb->loadRow();

2°) Gara 2, Gara 3 - takes random data (i'd like thats take fs_score of the player)
I think because it is not connectedto the players ID

3° )
Debug: Eval exception : gara_4 (id 160):reFormatFormJoins() : $mydb =& JFactory::getDBO(); $mydb->setQuery("SELECT fs_score, partecipazione_competizioni, qualificazione FROM scheda_atleta WHERE qualificazione = 4 order by atleta"); $myrow = $mydb->loadRow(); $myresult = $myrow['0']; $mycompetition = $myrow['2']; if($mycompetition == 4) { return $myresult; } else { return "0"; } : Only variables should be assigned by reference

 

[frullix]

oook, I tried a new way for resolve this problem.
With Sourcer plugin and this code:

{source}<?php
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $query->select($db->quoteName(array('fs_score','atleta','qualificazione',)))
          ->from($db->quoteName('scheda_atleta'))
          ->where('categoria= 1')
          ->order('fs_score DESC')
          ->setLimit('24');
$db->setQuery($query);
$options = $db->loadAssocList();
?>

<table border="1">
    <thead>
<tr>
            <th>nr</th>
            <th>Atleta</th>
            <th>Gara 1</th>
            <th>Gara 2</th>
            <th>Gara 3</th>
            <th>Gara 4</th>
            <th>Rank</th>
</tr>
    </thead>
    <tbody>
        <?php
            foreach( $options as $option )
            {
        ?>
<tr>
    <td>php number of rows from 1 to 24</td>

    <td><?php print_r($option['atleta']); ?></td>
    <td><?php if($option['qualificazione'] == 1 ) {
                 print_r($option['fs_score']);
                       } else {
                 print_r (["--"]);
                              } ?>
</td>
<td><?php if($option['qualificazione'] == 2 ) {
          print_r($option['fs_score']);
              } else {
                print_r (['--']);
} ?></td>
<td><?php if($option['qualificazione'] == 3 ) {
          print_r($option['fs_score']);
              } else {
                print_r (['--']);
} ?></td>
<td>
<?php if($option['qualificazione'] == 4 ) {
          print_r($option['fs_score']);
              } else {
                print_r (['--']);
} ?>
</td>
<td> ***MAX php function***ORDER BY the MAX result fs_score</td>
</tr>
        <?php
            }
        ?>

{/source}

with this code I've this result:

and I want this result:

NR|Atleta|Gara 1|Gara 2|Gara 3|Gara 4|Rank
1 |Eva | 10,02| 12,32 | 0 | 0 | 12,32
2 | John| 11,23 | 10,25 | 0 | 0 | 11,23

this is the db table

please help me!!

 

[juuser]

What happens when you change print_r($option['fs_score']); to echo $option['fs_score'];

 

[frullix]

Hi Juuser, not change anything!

If I use only


<?php
foreach( $options as $option )
{
?>
<tr>
<td>1</td>
<td><?php print_r($option['atleta']); ?></td>
<td><?php print_r($option['fs_score']); ?> </td>


It work, but I've need all result for every competitions (gara 2, gara 3 etc.)

 

[frullix]

I've tried to change
<?php if($option['qualificazione'] == 1 ) {
print_r($option['fs_score']);
} else {
print_r (["0"]);
} ?>
with (without ==)

<?php if($option['qualificazione'] = 1 ) {
print_r($option['fs_score']);
} else {
print_r (["0"]);
} ?>
and this is the result:

But not work!!!

 

[juuser]

OK, checked your code a bit in more detail. As I understand you need a distinct (one row per one athlete) in your table.

So you could first select the athletes and then loop them through and get the results, something like:

$mydb =& JFactory::getDBO();
$mydb->setQuery("SELECT DISTINCT athlete_id FROM your_athlete_table");
$myresults = $mydb->loadObjectList();

foreach ($myresults as $myresult) {

$mydb->setQuery("SELECT comp1_result,  comp2_result, comp3_result, comp4_result FROM your_results_table WHERE athtlete_id = " . $mydb->Quote($myresult->athlete_id));
$myscore = $mydb->loadObjectList();

$comp1_score = $myscore->comp1_result;
$comp2_score = $myscore->comp2_result;
$comp3_score = $myscore->comp3_result;
$comp4_score = $myscore->comp4_result;

if($comp1_score != "") {
echo $comp1_score;
} else {
echo "-";
}

if($comp2_score != "") {
echo $comp2_score;
} else {
echo "-";
}
//add your tables and formatting as needed
}

It might be possible to do it with nested queries and arrays, but this is just a small example (that might work with some modifications) without seeing and testing in real environment.

 

[frullix]

comp1_result I've to get it with -> SELECT atleta, fs_score FROM scheda_atleta WHERE qualificazione = 1 AND athtlete_id = " . $mydb->Quote($myresult->athlete_id));
comp2_result --------------------->SELECT atleta, fs_score FROM scheda_atleta WHERE qualificazione = 2 AND athtlete_id = " . $mydb->Quote($myresult->athlete_id));
etc.

 

[juuser]

Maybe, hard to tell without seeing your whole database table setup related to this issue.

 

[frullix]

scheda_atleta

 

[juuser]

Quickly looking at it, your suggestion in your previous post should work. Be sure to check the athlete fields. I'm not sure where you are storing the "id" and where the name of the athlete (is it separate "athletes " table etc.).

 

[frullix]

Athletes table - AT


would you advise me to proceed with:

$mydb->setQuery ("SELECT fs_score FROM scheda_atleta WHERE qualificazione = 1 AND athtlete_id = " . $mydb->Quote($myresult->athlete_id))
union
("SELECT fs_score FROM scheda_atleta WHERE qualificazione = 2 AND athtlete_id = " . $mydb->Quote($myresult->athlete_id))
union
("SELECT fs_score FROM scheda_atleta WHERE qualificazione = 3 AND athtlete_id = " . $mydb->Quote($myresult->athlete_id))
union
("SELECT fs_score FROM scheda_atleta WHERE qualificazione = 4 AND athtlete_id = " . $mydb->Quote($myresult->athlete_id));

$myscore = $mydb->loadObjectList();

$comp1_score = $myscore->comp1_result;
$comp2_score = $myscore->comp2_result;
$comp3_score = $myscore->comp3_result;
$comp4_score = $myscore->comp4_result;


he's making me crazy

 

[juuser]

OK, then your first query to get the athletes could be:

SELECT id, nome_e_cognome FROM AT
$myresults = $mydb->loadObjectList();

and inside loop something like:

SELECT fs_score FROM scheda_atleta AS a WHERE qualificazione = 1 AND atleta = " . $mydb->Quote($myresult->id)
$myscore = $mydb->loadObjectList();

and then
$athlete_name = $myresult->nome_e_cognome;
$comp1_score = $myscore->fs_score;