Overview: Write a program to query a database, and generate a list of student in

Question

Overview: Write a program to query a database, and generate a list of student information from that database. The data is in the "university" database, and the connect string for it is: Sdbc - nysqli,connect ("130.18.87.8,"student, "password", "university") The database is described on page 3.6 of the course packet. The output table should include a TACK HUOHE list of all student names (first and last) department name for their major. Department Cearses Taken OSHUA LE ERNEST WRIGHT The department name is held in the departmentBETTY MORRIS table ANTHONY ADAMS Accovering OSHUA LEE Finally, count the number of courses the studentJACK HEGHES has taken. (You'll have to use a second query, ED from the courses_taken table to do this) Business Indormation Syunms VICTOR YOUNG SANDRA HARRIS BRENDA JONES ANTHONY ADAMS Business Indormation Syweus Systems6 Business Information Systes CSS: Give the table some borders Busmess Information Systm Hints: I strongly recommend working on this one piece at a time. You can get the student names working first with a single table query. .Then expand it to a two-table query to add in the department name. Then finally a second query on courses_taken for the student counts. You can use a SQL function in this part, or do a manual count of results using PHP

Explanation / Answer

Before answering this question , i am assuming ,it is mandatory to define primary keys and foriegn keys are required on tables in order to quering results .

Assume Student table have structure = { studentid,firstname,lastname,departmentid }

Department table have structure ={dept_id,dept_name}

Courses table have structure={course_id , course_name , course_dept , studentid_registered}

Programme.php :

<?php

////////////////////////////////////////////////////////DB PART////////////////////////////////////////////////////

DEFINE ('DB_USER', 'student');

DEFINE ('DB_PASSWORD', 'password');

DEFINE ('DB_HOST', '130.18.87.8');

DEFINE ('DB_NAME', 'university');

$mysqli = mysqli_connect(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);

// Check connection

if (mysqli_connect_errno())

{

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

////////////////////////////////////////////////////////DB PART CONNECTION CLOSE////////////////////////////////////////

///////////////////////////////////////////////Result Part BEGIN///////////////////////////////////////////////////////////

echo "<table border=1>";

echo "<tr><th>Name</th><th>Department Name </th><th>Course count </th></tr>" ;

$types1=mysqli_query($con,"SELECT S.studentid,S.firstname,S.lastname,D.dept_name FROM `Student` S, Department D where S.departmentid =D.dept_id ");

while($row1=mysqli_fetch_array($types1))

{

$sid=$row1['studentid'] ;

$name=$row1['firstname']." " .$row1['lastname'];

$dept=$row1['dept_name'];

$types2=mysqli_query($con,"SELECT COUNT(course_id) FROM `Courses ` C, Department D where C. course_dept =D.dept_name and C.studentid_registered='$sid'");

while($row2=mysqli_fetch_array($types2))

{

$couse_count=$row2[0];

echo "<tr> <td> ".$name."</td><td>".$dept."</td><td>".$couse_count."</td></tr>";

}

}

echo "</table>";

//////////////////////////////////////////////// CLOSE /////////////////////////////////////////////////////////////////////

?>

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.