Divide by zero warning

[Cheops]

I have a PHP function that queries a

database and then uses the results of the

different queries to give me a result but I

am getting a divide by zero warning,

however when I run the queries on the

database I get the result I need but on

calling the PHP function I get the

warning. What could be the problem?

Here is the function code

function predictor ($home_team, $away_

team){

global $con;

$result=[0,0];

// For the home team

$home_team_strength=get_query_value($

con,"SELECT SUM(HTgoals)/Count(*)

FROM `results` WHERE Home_team = '$

home_team' ");

$league_strength=get_query_value($

con,"SELECT SUM(HTgoals)/Count(*)

FROM `results`");

$home_team_attack_strength=$home_

team_strength/$league_strength;

$away_team_strength = get_query_value($

con, "SELECT SUM(HTgoals)/Count(*)

FROM `results` WHERE Away_team='$

away_team'");

$away_team_defence_strength=$away_

team_strength/$league_strength;

$result[0]=$home_team_attack_strength*$

away_team_defence_strength*$league_

strength;

// For the away team

$away_team_strength_2 = get_query_

value($con, "SELECT SUM(ATgoals)/Count

(*) FROM `results` WHERE Away_team='$

away_team'");

$league_away_strength=get_query_value($

con, "SELECT SUM(ATgoals)/Count(*)

FROM `results`");

$away_team_attack_strength = $away_

team_strength_2/$league_away_strength;

$home_team_defence =get_query_value($

con, "SELECT SUM(ATgoals)/Count(*)

FROM `results` WHERE Home_team='$

home_team'");

$home_team_defence_strength =$home_

team_defence/$league_away_strength;

$result[1] = $away_team_attack_strength*

$home_team_defence_strength*$league_

away_strength;

return $result;

}

function get_query_value ($con,$query){

$return_value=-1;

$con->real_query($query);

$result=$con->use_result();

$row=$result->fetch_row();

$return_value=$row[0];

$result->close();

}

[Barand]

Use code tags and put it in a legible format, and then someone may look at the problem for you

[Cheops]

Here is the code in a more presentable manner, hopefully it looks better

 

function predictor ($home_team, $away_team){
global $con;
$result=[0,0];

// For the home team

$home_team_strength=get_query_value($con,"SELECT SUM(HTgoals)/Count(*)FROM `results` WHERE Home_team = '$home_team' ");

$league_strength=get_query_value($con,"SELECT SUM(HTgoals)/Count(*)FROM `results`");

$home_team_attack_strength=$home_team_strength/$league_strength;

 

$away_team_strength = get_query_value($con, "SELECT SUM(HTgoals)/Count(*)FROM `results` WHERE Away_team='$away_team'");

$away_team_defence_strength=$away_team_strength/$league_strength;

$result[0]=$home_team_attack_strength*$away_team_defence_strength*$league_strength;

// For the away team

$away_team_strength_2 = get_query_value($con, "SELECT SUM(ATgoals)/Count(*) FROM `results` WHERE Away_team='$away_team'");

$league_away_strength=get_query_value($con, "SELECT SUM(ATgoals)/Count(*)FROM `results`");

$away_team_attack_strength = $away_team_strength_2/$league_away_strength;

$home_team_defence =get_query_value($con, "SELECT SUM(ATgoals)/Count(*)FROM `results` WHERE Home_team='$home_team'");

$home_team_defence_strength =$home_team_defence/$league_away_strength;

$result[1] = $away_team_attack_strength*$home_team_defence_strength*$league_away_strength;

 

return $result;
}
function get_query_value ($con,$query){
$return_value=-1;
$con->real_query($query);
$result=$con->use_result();
$row=$result->fetch_row();
$return_value=$row[0];
$result->close();
}

[maxxd]

Are you sure $home_team and $away_team contain the values you expect? Try printing them both before running the bank of queries.

[ginerjm]

Why do you close your connection at the end of the get query value function every time? Wasteful!

[Barand]

Your get_query_value() function does not return anything

[Barand]

A couple of other things.

• Avoid the use of "global", pass the connection to the function.
• You are wasting resources by using several queries, each fetching only a single column. You can do everything in a single query.

function predictor ($con, $home_team, $away_team)
{
    $sql = "SELECT
                IFNULL(home_team_strength * away_team_strength / home_strength, 0) as result0
              , IFNULL(away_team_strength_2 * home_team_defence / away_strength, 0) as result1
            FROM
            (
                SELECT
                    AVG(IF(home_team = ?, HTgoals, null)) as home_team_strength
                  , AVG(IF(home_team = ?, ATgoals, null)) as home_team_defence
                  , AVG(IF(away_team = ?, HTgoals, null)) as away_team_strength
                  , AVG(IF(away_team = ?, ATgoals, null)) as away_team_strength_2
                FROM results
            ) team
            CROSS JOIN
            (
                SELECT
                    AVG(HTgoals) as home_strength
                  , AVG(ATgoals) as away_strength
                FROM results
            ) league;";
    $stmt = $con->prepare($sql);
    $stmt->execute( [ $home_team,$home_team,$away_team,$away_team ] );
    return $stmt->fetch(PDO::FETCH_NUM);
}

[maxxd]

Your get_query_value() function does not return anything

 

Well, that would certainly do it...

[Cheops]

Well, that would certainly do it...

How can I rectify it?

[ginerjm]

return the value? Although it is really bad code, doing queries for single values and closing the connection after each query. Let alone that your function is not written as a proper self-contained "thing", what with doing a close when the open wasn't part of it. A wiser approach would be to do complete queries for all the values you need and only putting it into a function if you need to do that 'better' code multiple times.

[Barand]

Setting a variable $return_value does not return the value.

function get_query_value ($con,$query){
  $con->real_query($query);
  $result=$con->use_result();
  $row=$result->fetch_row();
  $result->close();
  return $row[0];                            //<---- return the value
}

@ginerjm - the function is closing the result set, NOT the connection.

[Cheops]

Thanks guys it now works