SQL results being limited for some reason

PHP
1

Hey all,

This project of learning MySQLi Prepared Statements is quite the adventure :rolleyes:

Below is the dynamic query function I wrote so I could display multiple queries on the same page. If you read here: Multiple Queries Using Prepared SQL Statements, You’ll understand what that issue was, but now I ran into another snag.

Here is the code:


<?php

function DB_Query(){
   $databasefields = array();
   $results = array();

@$Mconn = new mysqli(DBHOST, DBUSER, DBPWRD, DBNAME);

   if (!@mysqli_connect(DBHOST, DBUSER, DBPWRD, DBNAME)){
   die(DBERROR);
   }

$query = "SELECT `results`, `Success`, `Failure`, `Counter`, `Grades`, `Classes`, `Special_Id`, `SpecialCondition`
FROM `Courses` INNER JOIN `Students` ON `id` = `Students_Id` JOIN `Conditions` ON `id` = `Special_Id`";

$stmt = $Mconn->prepare($query);
$stmt->execute();
$meta = $stmt->result_metadata();

   while($field = $meta->fetch_field()){
   $databasefields[] = &$row[$field->name];
   }

   call_user_func_array(array($stmt, 'bind_result'), $databasefields);
   while($stmt->fetch()){
      $queried = array();
      foreach($row as $key => $vector){
           $queried[$key] = $vector;
      }

      $results[] = $queried;
   }

   return $results;
}

$results = DB_Query();  // placed in include file so I don't have to keep calling it on each page a query is needed.
?>

What’s happening is now the returned results is limited to only 9 rows even though quite a few exists like so:

<?php

foreach($results as $row){
	echo $row['grades'].'<br />';
}

Returns exactly 9 rows even though over 100 exists.

To test what the possibility could be, I removed the JOINED tables and everything worked fine. So I went back into phpMyAdmin and performed a SQL query joining the tables and while it comes back as ok, it only shows the first 9 rows as it does on the page which is odd.

Could someone please advise me what I’m doing wrong? Is it how I’m joining, what I’m joining, something I need to make sure “before” joining etc? Do the column names have to be exactly the same and if so, why does phpMyAdmin return a positive join?

Thank you for the extra pair of eyes and brain cells :smiley:

2

If its an SQL problem, take the query out of PHP and work it in phpmyadmin (or whatever you may use). I am sure if you post your table information more can help with the actual query.

3

i think it’s quite likely an error in the join

you lost me on both of those questions

do me a favour, please, and for every column in your query, please indicate the table it belongs to

so replace every instance of tablename with the appropriate table name –

SELECT [i]tablename.[/i]results
     , [i]tablename.[/i]Success
     , [i]tablename.[/i]Failure
     , [i]tablename.[/i]Counter
     , [i]tablename.[/i]Grades
     , [i]tablename.[/i]Classes
     , [i]tablename.[/i]Special_Id
     , [i]tablename.[/i]SpecialCondition
  FROM Courses 
INNER 
  JOIN Students 
    ON [i]tablename.[/i]id = [i]tablename.[/i]Students_Id 
  JOIN Conditions 
    ON [i]tablename.[/i]id = [i]tablename.[/i]Special_Id
4

Hey guys,

Thanks for your replies. I realized what I was doing wrong, I forgot to add a (FK) and select the fields properly. :rolleyes: