PDO Mysql table linking and displaying

[whiteyoh]

Hi all,

The following code should link 2 tables, showing: student id, surname, firstname, study type, start year.

However, its not :-). its just a white screen. ive got php5.ini with ERROR_REPORTING (E_ALL); in it but im still getting nothing at all.

The tables definitly exist!

have i missed something stupid?

<?php
require_once("myfunctions.php");

 $db = getConnection(); 
 $stmt = $db->query( "SELECT student.studentCode, student.Surname, student.Forename, studyType.typeDesc ".
					" FROM student, studyType ");
 
 	while ( $obj = $stmt->fetchObject()){    	
	
	echo $obj->student.studentCode . ": " . $obj->student.Surname . ": " . $obj->student.Firstname . ": " . $obj->studyType.typeDesc . $obj->studyType.startYear "<br />\n";
	
	}
	}catch( PDOException $e ) {    echo $e->getMessage();}
?>

[ShawnCplus]

If you get no errors and no exception is through but it's still a white page that means the query returned no results. Execute the query directly in MySQL or phpmyadmin or whatever you're using to directly access the database and see if it returns any rows.

[jigarvyas]

In addition to this, I believe if you are linking two tables, there needs to be some kind of relation defined between them.
Something like: "Select..... where student.studentCode = studyType.studentCode..."

Although in your case, i agree with ShawnCplus, you definitely ought to try out your query directly first

[whiteyoh]

Hi,

I looked at this from an alternative perspective, and i get a little further, but i dont get why no results appear, when there are definitly items in the table.

this will display the grid, but populates nothing........ im baffled, and note, fairly new ish.

I have double checked all the column names and everything matches.

The headers and the grid displays. Im wondering if maybe an sql syntax error perhaps? its linking 2 tables.

<h1>Student Listing</h1>

<?php

// Connect to the mysqldatabase
$link = mysqli_connect("localhost","root","root","university")
or die("ERROR: Could not connect : " . mysql_error());

// Set up a query to select the required fields and records
echo $result = mysqli_query($link, "SELECT studentCode, forename, surname, startYear, typeDesc FROM student, studyType WHERE student.studyTypeID = studyType.studyTypeID")
or die("ERROR:query failed (Student) - " . mysql_error());

$studentResult = mysqli_query($link, $sql);
?>

<!-- Move out of PHP mode and start the table tags (note you
could have stayed in PHP mode and achieved the same
result by placing these tags within echo statements. -->

<table width="70%" border="1">
<tr>
<td width="15%"><strong>Student Code</strong></td>
<td width="25%"><strong>Surname</strong></td>
<td width="25%"><strong>Forename</strong></td>
<td width="20%"><strong>Study Type</strong></td>
<td width="15%"><strong>Start Year</strong></td>
</tr>

<?php

// For every row that was returned by the query, do something.
// In this example, display data from each row within an XHTML table.

// NOTE: The array keys (the field names are the array keys, the values are the field values returned by the SQL query) 

               echo "<tr>\n";
               echo "<td>&nbsp;</td>\n";
               echo "<td>".$studentResult['studentCode']."</td>\n";
               echo "<td>".$studentResult['surname']."</td>\n";
               echo "<td>".$studentResult['forename']."</td>\n";
               echo "<td>".$studentResult['studyType']."</td>\n";
               echo "<td>".$studentResult['startYear']."</td>\n";
               echo "</tr>\n";	
	
mysqli_free_results($studentResult);
// General clean-up of resources (i.e. memory).

mysqli_free_result($studentResult);
mysqli_close($link);

?>

</body>
</html>

[whiteyoh]

Hi,

Please excuse my Nativity, ive never thought of ever doing that!!!!! brilliant idea.

I ran

SELECT studentCode, forename, surname, startYear, typeDesc FROM student, studyType WHERE student.studyTypeID = studyType.studyTypeID

directly in mysql and i got the results i expected, so there has to be a common stupid mistake in both scripts.

baffling

[whiteyoh]

Hi,

I found the issue, i was missing the fundimental loop for showing every record DOH.

Heres my working code

<?php

      $mysqli = new mysqli('localhost','root','root','university');
   
    // sql query, including the linking of two tables, if it is TRUE
   
   
      if ($result = $mysqli->query("SELECT studentCode, forename, surname, startYear, typeDesc FROM student, studyType WHERE student.studyTypeID = studyType.studyTypeID ORDER BY surname, forename")) {
   
      // if it is true this repeats through all of the records displaying the desired items 
		
   
      echo 'there are '.$result->num_rows.' records.' . "<br>";
  
      // The "fetch_object()" is the new fancy pants OO function of the old mysql_fetch_object() function. 
  
      while ($row = $result->fetch_object()) {
  		
		echo $row->studentCode.' ';
		echo $row->forename.' ';
		echo $row->surname.' ';
		echo $row->startYear.' ';
		echo $row->typeDesc.' ';
		echo "<br>";

 
  
      }
	   
  
      } else {
  
     //error display.  this must be activated in the http conf file error_reporting
  
      // This means the query failed
 
      echo $mysqli->error;
  
      } // if no error, close the connection
  
      $mysqli->close();
  
      ?>