PHP Function with MySQL Query output

Question

// 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.

• tadman: WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you will create severe SQL injection bugs. NEVER put $_POST data directly into a query.
• Onur: Notice: use PHP Database Objects PDO
• Death-is-the-real-truth: Also call like echo bgTotSkl("CCA",$conn); connection object is missing there

Answer 1

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';
}

• SubZero: Thank you for the answer, I have done this but it still does not output anything.
• SubZero: Do you think there could be a problem with my while loop in the function?
• Death-is-the-real-truth: I cannot tell because i don't know what is this going to do:- bgTotSkl($bgTotSkl_SchoolName);
• SubZero: I think that line is the problem. I just want to output but I dont know how to output functions that contain mysql queries.
• SubZero: That line bgTotSkl($bgTotSkl_SchoolName); does nothing, I thought it would do something.

Answer 2

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

Hope it helps.