SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Posts
    85
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    SQL results being limited for some reason.

    Hey all,

    This project of learning MySQLi Prepared Statements is quite the adventure

    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:
    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:

    Code:
    <?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

  2. #2
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by netgrubber View Post
    Is it how I'm joining, what I'm joining, something I need to make sure "before" joining etc?
    i think it's quite likely an error in the join

    Quote Originally Posted by netgrubber View Post
    Do the column names have to be exactly the same and if so, why does phpMyAdmin return a positive 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 --
    Code:
    SELECT tablename.results
         , tablename.Success
         , tablename.Failure
         , tablename.Counter
         , tablename.Grades
         , tablename.Classes
         , tablename.Special_Id
         , tablename.SpecialCondition
      FROM Courses 
    INNER 
      JOIN Students 
        ON tablename.id = tablename.Students_Id 
      JOIN Conditions 
        ON tablename.id = tablename.Special_Id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Posts
    85
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hey guys,

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •