Repeated Query Results

[obrienkev]

Hi all,

Why are the below form fields displayed twice?? I run the queries in MSSQL Server Enterprise Manager and it works fine but somehow they are doubled below.

Why is this? Thanks.

// perform query - get class_ID
$query = "SELECT class_ID ".
                  "FROM class ".
                 "WHERE course_ID = '$course' ".
                 "AND class_start = '$class_start' ".
                 "AND class_end = '$class_end' ".
                 "AND location_ID = '$location'";
//echo $query.'<br />';
$result = mssql_query($query) or die('Select Query Error');

while($row = mssql_fetch_array($result))
{
   // declare class_ID as variable
      $class = $row['class_ID'];

 // get matching data from training table
  $query2 = "SELECT DISTINCT training_ID, t.class_ID, t.ffnumber, firstname, surname, attended, ".
                       "passed, result1, result2, result3, result4, attempts ".
                      "FROM training t ".
                     "INNER JOIN results r ".
                     "ON t.ffnumber = r.ffnumber ".
                      "WHERE t.class_ID = '$class'";
 echo '<p>'.$query2.'</p>';
 $result2 = mssql_query($query2) or die('Select Error');
 $num_results2 = mssql_num_rows($result2);
   echo '<br />RESULTS:'.$num_results2.'<br />';

   // display ffnumbers from results
     while($row2 = mssql_fetch_array($result2))
     {
            $value = $row2['ffnumber'].'-'.$row2['class_ID'];
             echo '<tr><td>'.$row2['ffnumber'].'</td> '.
                  '<td>'.$row2['firstname'].'</td> '.
                         '<td>'.$row2['surname'].'</td> '.
                         '<td>'.$row2['attended'].'</td> '.
                        '<td><input type="text" class="results" name="passed['.$value.']" id="passed" value="'.$row2['passed'].'" /></td> '.
                        '<td><input type="text" class="results" name="attempt['.$value.']" id="attempt" value="'.$row2['attempts'].'" /></td> '.
                        '<td><input type="text" class="results" name="result1['.$value.']" id="result1" value="'.$row2['result1'].'" /></td> '.
                        '<td><input type="text" class="results" name="result2['.$value.']" id="result2" value="'.$row2['result2'].'" /></td> '.
                        '<td><input type="text" class="results" name="result3['.$value.']" id="result3" value="'.$row2['result3'].'" /></td> '.
                        '<td><input type="text" class="results" name="result4['.$value.']" id="result4" value="'.$row2['result4'].'" /></td> '.
                          '<td> '.
                        '</td></tr>';
     }
}// end first while
     echo '</table>';

[Bob_PHP_Builder]

While loop is running twice

[obrienkev]

This query works...

Checked that class_ID on results table AND the training table. Works fine now...

 	 $query2 = "SELECT training_ID, t.class_ID, t.ffnumber, t.firstname, t.surname, t.attended, ".
	 				 	 "t.passed, r.result1, r.result2, r.result3, r.result4, r.attempts ".
	 					 "FROM training t ".
						 "INNER JOIN results r ".
						 "ON t.ffnumber = r.ffnumber ".
	 					 "WHERE r.class_ID = '$class' ".
						 "AND t.class_ID = '$class'";

[jdorsch]

Stop what you are doing. Don't do a query then loop on it and do a query for every result, this is a very bad habit to get into even if the number of queries is always fixed. I see it a lot in code the I have to fix and its a convoluted sloppy mess.

If you have 100 class ID's, that is going to be 100 queries, as your database grows your pages grow slower no matter how well indexed your db is because you are just doing more queries.

You should merge those two queries into one using a join then loop over the single result set.

[atmorell]

obrienkev you should try mysql manager:
http://www.sqlmanager.net/en/products/mysql/manager
I allways use it when I create querys. If you want to join 2 tables or more you just drag and drop the IDs to each other and select the data you need. You can allse very easy add conditions: http://www.sqlmanager.net/en/products/mysql/manager/screenshots/170 When you are done you click edit and copy past the sql code. 🙂