Membership

Main Menu

Forum Boards

Stats

18 tutorials
72,298 members
695,968 forum posts
11 blog posts

Tutorials

Data Joins Unions

by Barry Andrew on Jun 30, 2008 5:59:59 PM

Sample Application

Beetsem-Daley wants an input screen, similar to his original spreadsheet, where he can view and edit the subjects chosen by each pupil. Pupils will be listed in alphabetical order within their houses. Checkboxes will be used to indicate pupils' choices.

+------------+----------------+--------------------------------------------------------------+
|   House    |      Pupil     |       Biol Chem Comp Econ Engl Geog Germ Hist Math Phys      |
|  Jardine   |                |                                                              |
|            | Anne Bailey    |       [X]  [ ]  [ ]  [ ]  [ ]  [ ]  [X]  [X]  [ ]  [X]       |
|            | John Patterson |       [ ]  [ ]  [ ]  [ ]  [ ]  [X]  [X]  [ ]  [ ]  [ ]       |
|            | John Tully     |       [X]  [ ]  [ ]  [ ]  [X]  [ ]  [ ]  [X]  [X]  [ ]       |
|            | Mary Sheldon   |       [ ]  [X]  [ ]  [X]  [X]  [ ]  [ ]  [X]  [ ]  [ ]       |
|            | Michael Grove  |       [ ]  [ ]  [ ]  [X]  [X]  [ ]  [ ]  [ ]  [ ]  [X]       |
|            | Peter Appleby  |       [X]  [ ]  [ ]  [X]  [ ]  [ ]  [X]  [ ]  [X]  [ ]       |
+------------+----------------+--------------------------------------------------------------+

The sample code

<?php
$cnx = mysql_connect('localhost');     //  substute
mysql_select_db('jointute', $cnx);           //  your connection code

/**
* get the subjects and store in array for headings
*/
$subjects = array();
$res = mysql_query ("SELECT SUBSTRING(subject,1,4) FROM subject ORDER BY subject");
$subjectCount = mysql_num_rows($res);
while ($row = mysql_fetch_row($res)) $subjects[] = $row[0];
mysql_free_result($res);

/**
* get the pupil subject choices
* NOTE the cartesian join pupil-subject to get a row for each subject for each pupil
*   then a LEFT JOIN to choice to see if each pupil/subject combination was chosen
*/

$sql = "SELECT h.house_name, p.pupil_name, p.pupilID, s.subjectID, c.subjectID as chosen
		FROM pupil p
			INNER JOIN subject s
			INNER JOIN house h USING (houseID)
			LEFT JOIN choice c USING (pupilID, subjectID)
		ORDER BY h.house_name, p.pupil_name, s.subject";
$choice_res = mysql_query($sql);
?>
<html>
<head>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta name="generator" content="PhpED Version 4.5 (Build 4513)">
<title>Sample application</title>
<meta name="author" content="Barry Andrew">
<meta name="creation-date" content="06/29/2008">
<style type='text/css'>
       body, p, td {
       		font-family: verdana, sans-serif;
       		font-size : 10pt;
       		padding: 5px;
       }
       td {
       	    background-color: #FFF;
       }
       td.house {
       		background-color: #999;
       		color: white;
       		font-size : 12pt;
       		font-weight : 600;
       		border: 1pt solid gray;
       }
       td.cb {
       		text-align: center;
       }
       th {
       		background-color: #EEE;
       		color: black;
       		font-size : 10pt;
       		font-weight : 600;
       		padding: 5px;       
       }
       table {
       		background-color: #666;
       }
</style>
</head>
<body>
<form method='POST' action='updateChoices.php'>

<?php 
	$prevHouse = '';
	$prevPupil = '';
	while (list($house, $pupil, $pid, $sid, $chosen) = mysql_fetch_row($choice_res))
	{
	    if ($prevHouse != $house)
	    {
	    	$span = $subjectCount+1;
	    	if ($prevPupil != '')
	    	{
	    		echo '</tr>'."\n";           // end previous row
	    	}
	    	echo "<tr><td class='house' colspan='$span'>House : $house</td></tr>\n";
			echo '<tr><th rowspan="2">Pupil</th><th colspan="'.$subjectCount.'">Subject</th></tr>'."\n";
			echo '<tr><th width="6%">' . join ('</th><th width="6%">', $subjects ) . '</th></tr>'."\n";
	    	$prevHouse = $house;
	    	$prevPupil = '';
	    }
	    if ($prevPupil != $pupil)
	    {
	    	if ($prevPupil != '')
	    	{
	    		echo '</tr>'."\n";           // end previous row
	    	}
	    	echo "<tr><td>$pupil</td>\n";
	    	$prevPupil = $pupil;
	    }
	    $checked = $chosen ? 'checked=checked' : '';
	    echo "<td class='cb'><input type='checkbox' name='choice[$pid][$sid]' value='1' $checked></td>\n";
	    
	}
	echo '</tr>'."\n";
	echo "<tr><th  colspan='$span'><input type='submit' name='btnSubmit' value='Save'></th></tr>\n";
?>
</table>
</form>
</body>
</html>

Hopefully I'll be back with a look at some more advanced queries

« Previous
1
2
3
4
5
6
7

Comments

Very useful!
My php for dummies was useless for joins - many thanks!

1. Tom Butterworth on Jul 1, 2008 6:20:06 PM

Barand, could you extend the tutorial to include how to loop the results of the query. I find that when I use a Join , the query will return the first result and then stop. It will not run thru both tables and list all the results. When the 1st match is found, the query then doesnt look thru for other matches which seems to mean that the if stmt following the result is not running correctly .

Ex: $sql11 = "SELECT * FROM `TabA`
INNER JOIN
`TabB` USING (Sid)
WHERE TabA.Eid = $group AND TabA.Month = $month AND TabA.Year = $year ORDER BY TabB.Name asc" ;

$result11 = mysql_query($sql11);
if($myrow11 = mysql_fetch_array($result11))
{ do whatever}
while($myrow11 = mysql_fetch_array($result11);

Thanks !

2. swatisonee on Jul 3, 2008 4:07:11 AM

swatisonee: You'd do it like with all other query results:

$result = mysql_query('SELECT * FROM table1 INNER JOIN table2 ON table1.field1 = table2.field1'); if (mysql_num_rows($result)) { while($row = mysql_fetch_array()) { // do something } } else { echo 'The query returned no results'; }

3. Daniel Egeberg on Jul 3, 2008 5:45:05 AM

Thanks Daniel but I'm afraid I still fail to run thru the loop. I will post on the help forum the code as it runs without using Join and then the one I am working on to replace it using Joins as it is too long to post as a tute. comment.

4. swatisonee on Jul 4, 2008 4:38:28 AM

PHP Freaks