Clever Geek Handbook

Php PDO query for data from a sum of a column of a derived table

Here is the SQL query I am trying to use in some PDO php code:

SELECT SUM(credit_hours) AS hours FROM(SELECT Course_List.credit_hours FROM Program_Courses,    Course_List 
WHERE program_id= :pid and concentration_id= :conid 
and fulfills_major=1 and Program_Courses.course_id = Course_List.course_id ) AS major_credits

When I run this query against my database in SQL Workbench, I get a view with one column named "hour" and one row with a value of 76 in it.

Here is the php code I'm using to try and get this same data stored in the $ major_hours variable:

$result = $conn->prepare("SELECT * FROM students WHERE username = :un");    
    $result->bindParam(':un',$user);
    $result->execute();
    $data = $result->fetch(PDO::FETCH_ASSOC);   //returns an associated array where the indices are the column names
    $program = $data['program_id'];
    $concentration = $data['concentration_id'];

    //get the total number of credit hours in the student major/concentration
    $result = $conn->prepare("SELECT SUM(credit_hours) AS hours FROM(
                                SELECT Course_List.credit_hours FROM Program_Courses, Course_List 
                                WHERE program_id= :pid and concentration_id= :conid 
                                and fulfills_major=1 and Program_Courses.course_id = Course_List.course_id 
                                ) AS major_credits");
    $result->bindParam(':pid',$program);
    $result->bindParam(':conid',$concentration);
    $result->execute();
    $major_hours = $result->fetchColumn();

I know that the $ user, $ program, and $ concentration variables have legal values ​​because when I iterate over them on the page, I get the correct result. However, repeating $ major_hours does nothing. What am I doing wrong?

+3


source to share


3 answers


Use fetch as you can guess that it will fetch the next row from the result set The fetch_style parameter determines how the PDO

row is returned, which FETCH_ASSOC

is fine in your case , since it returns an array indexed by column name as returned in your result set.



$row = $result->fetch(PDO::FETCH_ASSOC);//row is an associative array
$major_hours = $row['hours'];           //access the column name hour
echo $majors_hours;                     //will print the hour value from db
0


source


I haven't used sql workbench, but you can use isnull () or coalesce () in your SUM (credit_hours) expression. I think this should result in $ major_hours showing 0.



0


source


Use this

$major_hours = $result->fetch(PDO::FETCH_ASSOC);
echo $majors_hours['hours'];

There are two ways to get data back from the database. fetch(PDO::FETCH_ASSOC)

and fetchAll(PDO::FETCH_ASSOC)

. Fetch only returns 1 row from the database .. but fetchAll will return the entire row from the database query you are doing.

And (PDO::FETCH_ASSOC)

means it will return data using an array.

-1


source






More articles:


All Articles