You have an error in your SQL syntax

[genzedu777]

Hi all,

 

I have received an error while trying to perform INNER JOIN. Do you guys have any idea what went wrong? Thanks

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.level_id) INNER JOIN tutor_subject AS ts USING (sl.subject_id) WHERE ols.tutor_' at line 1

 

<?php
$query3 = "SELECT sl.level_id, sl.subject_id, tl.level_name AS level_name, ts.subject_name AS subject_name " .
        "FROM tutor_overall_level_subject AS ols " .
	"INNER JOIN tutor_subject_level AS sl USING (subject_level_id) " .
        "INNER JOIN tutor_level AS tl USING (sl.level_id) " .
        "INNER JOIN tutor_subject AS ts USING (sl.subject_id) " .
	"WHERE ols.tutor_id = '" . $_GET['tutor_id'] . "'";


  $data3 = mysqli_query($dbc, $query3)
  or die(mysqli_error($dbc));

  if (mysqli_num_rows($data3) == 1) {

echo '<div id="panel4">';

	echo'<table><tr>'; // Start your table outside the loop... and your first row
	$count = 0; // Start your counter
	while($row3 = mysqli_fetch_array($data3)) {
		/* 	Check to see whether or not this is a *new* row
			If it is, then end the previous and start the next and restart the counter.
		*/
		if ($count % 5 == 0) { 
			echo "</tr><tr>"; $count = 0;
		}
		echo '<td class="label">' . $row3['level_name'] . '</td><td>' . $row3['subject_name'] . '</td>';
		$count++; //Increment the count
	} 
	echo '</tr></table><br/>'; //Close your last row and your table, outside the loop



echo '</div>'; //End of panel 4

} //End of IF for $query and $data (Teaching Subjects)

else {
	echo '<p class="error">There was a problem accessing your profile.</p>';
}
  
?>

[mikosiko]

        "INNER JOIN tutor_level AS tl USING (sl.level_id) " .
        "INNER JOIN tutor_subject AS ts USING (sl.subject_id)

 

use only the column name inside USING()  don't use aliases.

From the Manual:

The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:

 

and last... too many " and . in your sql... you can write it like this:

$query3 = "SELECT sl.level_id, 
                  sl.subject_id, 
                  tl.level_name AS level_name, 
                  ts.subject_name AS subject_name 
             FROM tutor_overall_level_subject AS ols
	  JOIN tutor_subject_level AS sl USING (subject_level_id)
                  JOIN tutor_level AS tl USING (level_id)
                  JOIN tutor_subject AS ts USING (subject_id)
            WHERE ols.tutor_id = '" . $_GET['tutor_id'] . "'";

[genzedu777]

Hi mikosiko,

 

Thanks for replying.

 

Howver added the revised code, I am still facing an error, and the error comes from my else statement

 

else {

      echo '<p class="error">There was a problem accessing your profile.</p>';

  }

 

What could be the possible reason?

 

[attachment deleted by admin]

[mikosiko]

well... if you are seeing the message echoed in you else that is not an error.... your code is doing exactly what is supposedly to do... you have:

if (mysqli_num_rows($data3) == 1) {

  // more code here

} else {
      echo '<p class="error">There was a problem accessing your profile.</p>';
   }

 

which means that your mysqli_num_rows($data3) != 1, therefore your SELECT is returning more than 1 row or none.