CODEWITHSUNDEEP
phpmysqlfunctionoutput
SubZero
SubZero

Reputation: 87

PHP Function with MySQL Query output

// Sum of boys and girls for each school 

function bgTotSkl($bgTotSkl_SchoolName, $conn) {
    $bgTotSkl_Query = "SELECT SUM(result_studpoints) AS totalbg, stud_gender
    FROM result
    JOIN students ON result.stud_id WHERE result.stud_id = students.stud_id
    AND stud_school = '$bgTotSkl_SchoolName' 
    GROUP BY stud_gender";
    $mainQuery = mysqli_query($conn, $bgTotSkl_Query);
while ($data = mysqli_fetch_assoc($mainQuery)) {
echo bgTotSkl($bgTotSkl_SchoolName);
    }
}

echo bgTotSkl("CCA");

The code above is a function that contains a mysql query. The query is supposed to calculate the sum of the points for boys and girls gained by a specific school in my database. The query works fine, I have tested it.

Also the variable in my query represents school name so when I want to output the totals for a specific school e.g:

echo bgTotSkl("CCA");

The school CCA will be placed in the function and the query will grab the total points for boys and girls from the database for the specific school CCA.

The problem is I cannot get the function to output the results of the query and I do not know if my syntax is wrong (have I implemented the variable in the query correctly?) or the whole logic is wrong (Is the while loop completely wrong?) or both.

Thanks in advance.

Upvotes: 1

Views: 88

Answers (2)

Death-is-the-real-truth
Death-is-the-real-truth

Reputation: 72299

Two tings:-

1.Inside functionadd this two line first:-

if(isset($bgTotSkl_SchoolName)){
 $bgTotSkl_SchoolName = mysqli_real_escape_string($conn,$bgTotSkl_SchoolName);//It will prevent `SQL Injection`
}

2.Call function like:-echo bgTotSkl("CCA",$conn); connection object(second parameter) is missing there

Now try like below:-

function bgTotSkl($bgTotSkl_SchoolName, $conn) {
    $bgTotSkl_Query = "SELECT SUM(result_studpoints) AS totalbg, stud_gender
    FROM result
    JOIN students ON result.stud_id WHERE result.stud_id = students.stud_id
    AND stud_school = '$bgTotSkl_SchoolName' 
    GROUP BY stud_gender";
    $mainQuery = mysqli_query($conn, $bgTotSkl_Query);
    while ($data = mysqli_fetch_assoc($mainQuery)) {
        echo $data['stud_gender'].' has gained overall '.$data['totalbg'].'points';
    }
}

bgTotSkl("CCA",$conn);

Or:-

function bgTotSkl($bgTotSkl_SchoolName, $conn) {
$result = array();
    $bgTotSkl_Query = "SELECT SUM(result_studpoints) AS totalbg, stud_gender
    FROM result
    JOIN students ON result.stud_id WHERE result.stud_id = students.stud_id
    AND stud_school = '$bgTotSkl_SchoolName' 
    GROUP BY stud_gender";
    $mainQuery = mysqli_query($conn, $bgTotSkl_Query);
    while ($data = mysqli_fetch_assoc($mainQuery)) {
        $result['stud_gender'] = $data['totalbg'];
    }
}
$college_name = 'CCA';
$data = bgTotSkl($college_name,$conn);

foreach($data as $key=>$value){
   echo $key.' of college '.$college_name.' has gained overall '.$value.'points';
}

Upvotes: 1

dmeehan
dmeehan

Reputation: 2357

Here is a simple tutorial for mysqli prepared statements: http://markonphp.com/mysqli-select-prepared-statements/

Hope it helps.

Upvotes: 0

Related Questions