[SQL] How to get 2x value (from different storage) and divide it?

[ligus]

Hello, I want to divide value from storage 8544 by value from storage 8541

You can check this: http://vpx.pl/i/2014/07/18/storage.jpg
For example player Ackerman - in result I want to have 94/3 = 31

I tried

SELECT `name`,`key`,`value` FROM `players`,`player_storage` WHERE (`key` = 8541 AND players.id = player_storage.player_id) OR (`key` = 8544 AND players.id = player_storage.player_id)

But I do not have idea how I can divide it.

I tried also

SELECT ((SELECT `value` FROM `players`,`player_storage` WHERE `key` = 8541 AND players.id = player_storage.player_id)/(SELECT `value` FROM `players`,`player_storage` WHERE `key` = 8544 AND players.id = player_storage.player_id)) as diff

but:
#1242 - Subquery returns more than 1 row

Does anyone have an idea?

 

[Chris]

SELECT p.name, ps.key, ps.value FROM players AS p LEFT JOIN player_storage AS ps ON ps.player_id = p.id WHERE ps.key IN(8541, 8544);

Edit: Sorry, I did not completely read your thread, was just looking at the queries themselves. Just noticed you wanted to divide your values.

 

[ligus]

Yea, I want to divide it ^^

 

[Chris]

For a specific character:

SELECT p.name, (ps1.value / ps2.value) AS value FROM players AS p JOIN player_storage AS ps1 ON (ps1.key = 8544 AND ps1.player_id = p.id) JOIN player_storage AS ps2 ON (ps2.key = 8541 AND ps2.player_id = p.id) WHERE p.name = 'CHARACTER NAME HERE' LIMIT 1;

For all of them:

SELECT p.name, (ps1.value / ps2.value) AS value FROM players AS p JOIN player_storage AS ps1 ON (ps1.key = 8544 AND ps1.player_id = p.id) JOIN player_storage AS ps2 ON (ps2.key = 8541 AND ps2.player_id = p.id);

 

[ligus]

In database it works perfect!
I don't understand but there is something wrong when I want to use this in PHP.

<?PHP
if(file_exists($f) && filemtime($f) > (time() - $cache_sec))
    $main_content = file_get_contents($f);
else
{
    mysql_pconnect($config['server']['sqlHost'], $config['server']['sqlUser'], $config['server']['sqlPass']) or die('MySQL connection error.');
    mysql_select_db($config['server']['sqlDatabase']);

    $main_content .= '
    <table border="0" cellpadding="4" cellspacing="1" width="100%">
        <tr align="center" style="height: 22px; background-image: url(\'layouts/elemental/images/news/newsheadline_background.gif\');">
        <td height="22" width=10% class="white" style="text-align: center; font-weight: bold;">Rank</td>
        <td height="22" class="white" style="text-align: center; font-weight: bold;">Name</td>
        <td height="22" width =22% class="white" style="text-align: center; font-weight: bold;">Result</td>
        </tr>';
  
    $i = 1;
    $pd = mysql_query('SELECT p.name, ps1.value / ps2.value AS value FROM players AS p JOIN player_storage AS ps1 ON ( ps1.key =8544 AND ps1.player_id = p.id ) JOIN player_storage AS ps2 ON ( ps2.key =8541 AND ps2.player_id = p.id ) ORDER BY  `value` DESC LIMIT 0,30');

    if(mysql_num_rows($pd) != 0)
    {
        while($pld = mysql_fetch_array($pd))
        {
            $a = mysql_query('SELECT name FROM players WHERE id='.$pld['player_id'].' AND group_id < 4');
            if(mysql_num_rows($a) != 0)
            {
                $a = mysql_fetch_array($a);
                                    if(is_int($nr_gracza / 2))
                        $bgcolor = $config['site']['lightborder'];
                    else
                        $bgcolor = $config['site']['darkborder'];
                    $nr_gracza++;
                    $main_content .= '<TR BGCOLOR="'.$bgcolor.'">

                    <td align="center">'.$i.'.</td>
                    <td height ="22"><b><A style="text-decoration: none;" href="?subtopic=characters&name='.htmlspecialchars($a['name']).'"><center>'.$a['name'].'</center></a></b></td>
                    <td><center>'.$pld['value'].'</center>                  
                    </td>          
                </tr>';
                $i++;
            }  
        }
    }
    else
        $main_content .= '<tr BGCOLOR="'.$config['site']['darkborder'].'" class="Even"><td colspan ="3" height="24">&nbsp;No one has died yet.</td></tr>';
    $main_content .= '</table><br><br>';          
}
?>

There is 30x error

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\pages\top.php on line 25

25 line: if(mysql_num_rows($a) != 0)

 

[Cornex]

In database it works perfect!
I don't understand but there is something wrong when I want to use this in PHP.


There is 30x error

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\pages\top.php on line 25

It means that the mysql_num_rows() function is need to count from an result, now $a just return true/false.

 

[Chris]

You don't need that secondary SQL statement, you can fetch all the required information from the one I provided you with.

<?PHP
if(file_exists($f) && filemtime($f) > (time() - $cache_sec))
    $main_content = file_get_contents($f);
else
{
    mysql_pconnect($config['server']['sqlHost'], $config['server']['sqlUser'], $config['server']['sqlPass']) or die('MySQL connection error.');
    mysql_select_db($config['server']['sqlDatabase']);

    $main_content .= '
    <table border="0" cellpadding="4" cellspacing="1" width="100%">
        <tr align="center" style="height: 22px; background-image: url(\'layouts/elemental/images/news/newsheadline_background.gif\');">
        <td height="22" width=10% class="white" style="text-align: center; font-weight: bold;">Rank</td>
        <td height="22" class="white" style="text-align: center; font-weight: bold;">Name</td>
        <td height="22" width =22% class="white" style="text-align: center; font-weight: bold;">Result</td>
        </tr>';
    $i = 1;
    $pd = mysql_query('SELECT p.name, ps1.value / ps2.value AS value FROM players AS p JOIN player_storage AS ps1 ON ( ps1.key =8544 AND ps1.player_id = p.id ) JOIN player_storage AS ps2 ON ( ps2.key =8541 AND ps2.player_id = p.id ) WHERE p.group_id < 4 ORDER BY  `value` DESC LIMIT 0,30');

    if(mysql_num_rows($pd) != 0)
    {
        while($pld = mysql_fetch_array($pd))
        {
            //$a = mysql_query('SELECT name FROM players WHERE id='.$pld['player_id'].' AND group_id < 4');
            //if(mysql_num_rows($a) != 0)
            //{
                //$a = mysql_fetch_array($a);
                                    if(is_int($nr_gracza / 2))
                        $bgcolor = $config['site']['lightborder'];
                    else
                        $bgcolor = $config['site']['darkborder'];
                    $nr_gracza++;
                    $main_content .= '<TR BGCOLOR="'.$bgcolor.'">

                    <td align="center">'.$i.'.</td>
                    <td height ="22"><b><A style="text-decoration: none;" href="?subtopic=characters&name='.urlencode($pld['name']).'"><center>'.$pld['name'].'</center></a></b></td>
                    <td><center>'.$pld['value'].'</center>                 
                    </td>         
                </tr>';
                $i++;
            //} 
        }
    }
    else
        $main_content .= '<tr BGCOLOR="'.$config['site']['darkborder'].'" class="Even"><td colspan ="3" height="24">&nbsp;No one has died yet.</td></tr>';
    $main_content .= '</table><br><br>';         
}
?>

Also, use urlencode() rather than htmlspecialchars() when you're creating part of a URL (and then decode it with urldecode()).

 

[ligus]

Awesome!
I have one more little thing - when we divide: (value from storage 8544) / (value from storage 8541) and value from storage 8541 is 0... then is it possible to return only value from 8544 or divide by 1?

 

[Chris]

Alter your query, and check if the value is 0 in php, set it to either key1value or key2value.

SELECT p.name, ps1.value AS key1value, ps2.value AS key2value, ps1.value / ps2.value AS value FROM players AS p JOIN player_storage AS ps1 ON ( ps1.key =8544 AND ps1.player_id = p.id ) JOIN player_storage AS ps2 ON ( ps2.key =8541 AND ps2.player_id = p.id ) WHERE p.group_id < 4 ORDER BY `value` DESC LIMIT 0, 30;

 

[ligus]

I edited query and I did:

if (($pld['ps2.value']) == 0)
    $xxx = $pld['ps1.value'];
else
    $xxx = round($pld['value'],2);

And now there is nothing everywhere. What am I doing wrong?

 

[Chris]

$xxx = (empty($pld['key2value']) ? $pld['key1value'] : round($pld['value'], 2));

 

[ligus]

It does not work, I have empty results :/

 

[Chris]

TeamViewer?