Jump to content
Forums are read-only ×
Larry Ullman's Book Forums

User-Defined Functions To Get Data From Other Tables In A Database?

Natt
 Share

Recommended Posts

Natt Newbie

Natt

Posted
Posted

Hello

 

I have 3 tables in my database:

 

students

toeic_scores

courses_taken

 

These are connected using the student_no field

 

I would like to list students who meet certain search criteria from the students table - using the while loop. However I have a column listing the no of attempts on the test (using a function, cnt_toeic_scores that searches the data from the toeic_scores table) and another column for the no of courses the student has taken (using a function, courses_cnt that searches the data from courses_taken table). However, this has failed miserably. I just dont know why it isnt SELECTING date from the toeic_scores and the courses_taken table. Can someone spot something I havent done correctly? Alternatively what is a better way to go about this? Here is the code:

 

 

<?php # Script 9.4 - view_students.php #4

// This script retrieves all the records from the users table.
// This new version links to edit and delete pages.

$page_title = 'View students';
include('includes/header.inc.html');
echo '<h1>Registered Students</h1>';

require_once ('../../mysqli_connect.php'); // Connect to the db.    
$errors = array(); // Initialize an error array.

// Function to count the no. of TOEIC attempts by a student
function cnt_toeic_attempts($sn)    {
    echo "$sn";

    // Make the query:
    $query = "SELECT * FROM toeic_scores WHERE student_no = $sn ";    
    $r = @mysqli_query ($dbc, $query);

    if ($r)        {
        echo 'It ran OK';    
        // Count the no. of returned rows: 
        $no_of_recs = mysqli_num_rows($r);              
        return $no_of_recs;        
    } else    {    
        Echo 'There is a problem';
    }        
}    // End of the function definition.

// Function to count the no. of English Courses taken by a student
function courses_taken($sn)    {

    // Make the query:
    $query = "SELECT attend_id FROM course_attendance WHERE student_no = $sn ";                
    $r = @mysqli_query ($dbc, $query);

    if ($r)        {
        echo 'It ran OK';    
        // Count the no. of returned rows:
        $no_of_recs = mysqli_num_rows($r);             
        return $no_of_recs;        
    } else    {    
        Echo 'There is a problem';
    }            
}    // End of the function definition.

// Check for a grouping criteria:
if (empty($_REQUEST['group_criteria'])) {
    $errors[] = 'You forgot to select the grouping criteria.';
}    else    {
    $gc = mysqli_real_escape_string($dbc, trim($_REQUEST['group_criteria']));    
}

// Check for a listing criteria:
if (empty($_REQUEST['list_criteria'])) {
    $errors[] = 'You forgot to select the listing criteria.';
}    else    {
    $lc = mysqli_real_escape_string($dbc, trim($_REQUEST['list_criteria']));
    switch ($lc)    {
        case "sn":
            $lc = 'student_no';
            break;
        case "ln":
            $lc = 'last_name';
            break;    
        case "fn":
            $lc = 'first_name';
            break;            
    }
}

// Make the query:

switch ($gc)    {
    case "all":
        $q = "SELECT * FROM students ORDER BY $lc ASC";
        break;
    case "jp":
    $q = "SELECT * FROM students WHERE ccode = 'JP' ORDER BY $lc ASC";
        break;    
    case "njp":
    $q = "SELECT * FROM students WHERE ccode != 'JP' ORDER BY $lc ASC";
        break;                
}
$r = @mysqli_query ($dbc, $q);

// Table header.
echo '<table align="center" cellspacing="3" cellpadding="3" width="75%">
    <tr>
        <td align="left"><b>View Details (V.D.)</b></td>
        <td align="left"><b>Edit</b></td>                
        <td align="left"><b>Student No.</b></td>        
        <td align="left"><b>Name</b></td>    
        <td align="left"><b>Gnd.</b></td>
        <td align="left"><b>Sch.</b></td>                
        <td align="left"><b>Country</b></td>    
        <td align="left"><b>Campus</b></td>        
        <td align="left"><b>TOEIC Attempts</b></td>    
        <td align="left"><b>Courses Taken</b></td>            
    </tr>
';

// Fetch and print all the records. . .

$bg = '#eeeeee';    // Set the initial background color.

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC))    {

    $bg = ($bg =='#eeeeee' ? '#ffffff' : '#eeeeee');    // Switch the initial background color.

    $name = $row['first_name'] .' '. $row['middle_name'] .' ' .$row['last_name'] ;    
            
    // Confirm & write gender in full.
    $gd=$row['gender'];
    $sc = $row['school'];
    $cp = $row['campus'];
    $ctr = $row['ccode'];
    $e = $row['email'];    
    
    // Write Laboratory name in full:
    if ($sc == 'is')    {
        $sc = 'IS';
    } elseif ($sc == 'ks')    {    
        $sc = 'KS';
    } elseif ($sc == 'ms')        {
        $sc = 'MS';                
    } else    {
        $sc = NULL;
    }            

    if ($cp == 'i')    {
        $cp = 'I';    
    } else    {
        $cp = 'T';
    }    

    // Call and assign values returned byt he functions
    $toeic_cnt = cnt_toeic_attempts($row['student_no']);
    $courses_cnt = courses_taken($row['student_no']);

    echo '<tr bgcolor = "' .$bg . '">
        <td align="left"><a href="view_student_details.php?id=' . $row['student_id'] . '">V. D. here</a></td>
        <td align="left"><a href="edit_student.php?id=' . $row['student_id'] . '">Edit here</a></td>                
        <td align="left">' . $sn .'</td>
        <td align="left">' . $name .'</td>                    
        <td align="left">' . $gd .'</td>    
        <td align="left">' . $sc .'</td>                    
        <td align="left">' . $ctr .'</td>
        <td align="left">' . $cp .'</td>        
        <td align="left">' . $toeic_cnt .'</td>
        <td align="left">' . $courses_cnt .'</td>        
        </tr>
    ';
    
}    // End of the WHILE Loop.

echo '</table>'; // Close the table.
mysqli_free_result($r);
mysqli_close($dbc); // Close the database connection.
          
        
// Include the footer and close the script:
include ('includes/footer.inc.html');


?>

Link to comment
Share on other sites
Antonio Conte Newbie

Antonio Conte

Posted
Posted

A better way to do this would be to write a more advanced query. That way, you can get more information from a single query.

// Build query
$query = "SELECT student.*,
UPPER(student.school) AS uschool,
UPPER(student.campus) AS ucampus,
COUNT(course.student_no) AS attempts,
COUNT(scores.student_no) AS attendance

FROM student AS student
LEFT OUTER JOIN course_attendance AS course ON (student.student_no = course.student_no)
LEFT OUTER JOIN toeic_scores AS scores ON (student.student_no = scores.student_no)
	
WHERE student.student_no = $id

ORDER BY $order ASC";

// Get result
$result = mysqli_query($dbc, $query);

while ( $row = mysqli_fetch_array($result))
{
    echo $row['uschool'];    // Uppercased shool name
    echo $row['ucampus'];    // Uppercase campus name
    echo $row['attemps'];    // Number of attemps
    echo $row['attendance']; // Number of attendances
}

I don't really have the time to explain this to much, but try it out an play with it. I bet you can use it for something clever. I'll reply later on if you have any questions.

 

Btw. I guess the reason why your code don't work is because of variable scope. You can't use a variable inside a function without:

1. Passing it as a parameter

2. Declaring it a global variable

$dbc = ''; // DBC variable;

mysqliSomething1( ); // Declared global
mysqliSomething2($dbc); // Pass as variable
mysqliSomething3( ); // Unavailable 

function mysqliSomething1()
{
    global $dbc; // Declare it global

    // We can use $dbc here now.
}

function mysqliSomething2( $dbc )
{
    // $dbc as a paramter. We can use it now
}

function mysqliSomething3()
{
// $dbc unavailable here 
}
  • Upvote 2
Link to comment
Share on other sites
Natt Newbie

Natt

Posted
  • Author
Posted

Hi Antonio;

 

Thank you so much for taking your time to look at my code. I was so frustrated with my lack of progress until yesterday when there was a breakthrough.

 

I used the idea you gave in the second part of your advice about the declaring parameters within functions. And voila, I got the results I needed. That was just such a nice feeling after 3 days of frustrations. However, when I looked at the advice you gave in the first section about writing advanced code, now I am dissatisfied again. At the moment the code looks like jargon to me because of my level but from the robustness, I think that it is the better approach. I will take more of my time and look carefully through it as well as try to implement using that code structure.

 

Thank you once again.

 

Natt

  • Upvote 1
Link to comment
Share on other sites
Antonio Conte Newbie

Antonio Conte

Posted
Posted

Nice you got it working.

 

Also, don't sweat it too much. There's always "some better way" to do things, but we must all start out somewhere. Gradually improve your code as you learn more, and make sure to enjoy the learning processes.

 

I'll take some time to explain the query now. Some part of the key is the aliasing. When you use several tables in a query, adding the AS alias is often required. When a column has the same name in several tables (as your student_no) MySQL will need to know which table the column is in. The same is true for aggregation functions (UPPER(column)) as echo $row['UPPER(column)'] looks a bit clumsy.

 

The next part is the FROM part. A join will basically gather data from two tables that share an identifier. In this case that is student_no. It's the ON (student.student_no = course.student_no) part that illustrates that shared identifier. All rows from the student table will be linked to rows from the corresponding course table it shares that identifier with. The exact same thing happens with the student and score tables at the next line.

 

The where part limits the search to ONE specific student_no. If you remove the WHERE clause, you'll see ALL students linked with data from the two other tables.

 

However, you need to specify what columns to display from the results we have. Because of our aliases, (you are free to choose the alias yourself) prefixing anything with student.column will look in the student table, courses.column in the courses alias (course_attendance) and scores.column in the scores alias (toeic_scores). SELECT student.student_no, course.student_no and scores.student_no will therefor display the student_no column from the respective tables.

 

Lastly, the LEFT OUTER JOIN specifies HOW the joining is done. This join type will use the LEFT table as a base. You can think of the table used in FROM table as the left most, and the last JOIN line as the right. In this example, the student table is the base for the joining of the tables. The OUTER part will use the base inclusive and use rows from the student table even if no match is found in the "right" table. The normal way is exclusive, where student rows without matching course or score rows would be ignored. JOIN is just the basic keyword. In other words, this is valid. This would however make an exclusive match.

FROM student AS s
JOIN course_attendance AS c ON (s.student_no = c.student_no)

You won't likely ever need to write a more advance query than this. I very seldom do. Hope that made some parts of it less scary. If you have phpMyAdmin, I would recommend you to run the query there. It'll make it more clear to have it "visualized"

  • Upvote 1
Link to comment
Share on other sites
 Share
Go to topic listing
×
×
  • Create New...