Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder MrBiggZ's Avatar
    Join Date
    Apr 2005
    Location
    Indianapolis IN
    Posts
    325
    Thanks
    42
    Thanked 2 Times in 2 Posts

    I'm confused as why I can't reuse MySQL database results

    Hi!

    I'm trying to create a select form element from the query results and obviously I'm doing something wrong and I'm not quite sure what it is.

    I call for the database and do the queries. This part works fine
    PHP Code:
    include_once("config/config.db.php");

    $mysqli_db = new mysqli($server$db_user$db_pass$db_name);

    $mgmt_query "SELECT `teams`.`team_name`,`mgmt`.`mgmt-name`,`mgmt`.`email`,`mgmt`.`phone`,`coach-roles`.`mgmt_role`, `coach-roles`.`mgmt_position`, `mgmt`.`mgmt_id`,`teams`.`team_id` FROM teams LEFT JOIN `mgmt` ON `teams`.`team_id` = `mgmt`.`team_id` LEFT JOIN `coach-roles` ON `mgmt`.`mgmt_role` = `coach-roles`.`mgmt_role` WHERE(( team_name = \"Extreme\"));";
    $mgmt_query_result $mysqli_db->query($mgmt_query);

    $mgmtroles_query "SELECT `coach-roles`.`mgmt_role`, `coach-roles`.`mgmt_position` FROM `ftsc`.`coach-roles`;";
    $mgmtroles_query_result $mysqli_db->query($mgmtroles_query);

    $mysqli_db->close(); 
    Further down the page I loop through the results
    PHP Code:
    //echo "<select>\n";

    while ($mgmtrows $mgmt_query_result->fetch_assoc()) {

        echo 
    "<pre>";
        echo 
    print_r($mgmtrows);
        echo 
    "</pre><br>";


        while (
    $mgmtrolerows $mgmtroles_query_result->fetch_assoc()) {

    //        echo $mgmtrolerows['mgmt_role'] . " -- " . $mgmtrows['mgmt_role'];
    //        if ($mgmtrolerows['mgmt_role'] == $mgmtrows['mgmt_role']) {
    //            echo "<option value=\"" . $mgmtrolerows['mgmt_role'] . "\" selected>" . $mgmtrolerows['mgmt_position'] . "</option>";
    //        } 
    //        else {
    //            echo "<option value=\"" . $mgmtrolerows['mgmt_role'] . "\">" . $mgmtrolerows['mgmt_position'] . "</option>";
    //        }


        
    echo "<pre>";
        echo 
    print_r($mgmtrolerows);
        echo 
    "</pre><br>";

        }

    }

    //echo "</select>\n"; 
    The results from that while loop look like this
    Array
    (
    [team_name] => Extreme
    [mgmt-name] => James
    [email] => *****@yahoo.com
    [phone] => 317-***-****
    [mgmt_role] => mgmt0
    [mgmt_position] => Head Coach
    [mgmt_id] => 1
    [team_id] => 7
    )
    1

    Array
    (
    [mgmt_role] => mgmt0
    [mgmt_position] => Head Coach
    )
    1

    Array
    (
    [mgmt_role] => mgmt1
    [mgmt_position] => Assistance Coach
    )
    1

    Array
    (
    [mgmt_role] => mgmt2
    [mgmt_position] => Team Manager
    )
    1

    Array
    (
    [team_name] => Extreme
    [mgmt-name] => David
    [email] => *****@gmail.com
    [phone] => 317-***-****
    [mgmt_role] => mgmt1
    [mgmt_position] => Assistance Coach
    [mgmt_id] => 2
    [team_id] => 7
    )
    1

    Array
    (
    [team_name] => Extreme
    [mgmt-name] => Julie
    [email] => *****@att.net
    [phone] => 317-***-****
    [mgmt_role] => mgmt2
    [mgmt_position] => Team Manager
    [mgmt_id] => 3
    [team_id] => 7
    )
    1
    It only hits $mgmtrolerows one time. I would have figured it would just re-display the results every time I loop through $mgmtrows.

    Rather confused at the moment. I'll keep staring at it .. maybe something will come to the forefront.

    Thanks for your time and help, it is appreciated!
    “No matter how slick the demo is in rehearsal, when you do it in front of a live audience, the probability of a flawless presentation is inversely proportional to the number of people watching, raised to the power of the amount of money involved.” ~ Mark Gibbs

  • #2
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,338
    Thanks
    13
    Thanked 348 Times in 344 Posts
    you should be able to loop through the last $mgmtrows and $mgmtrolerows as often as you want but the query objects are empty after you loop through them because they are not arrays, more like buffers.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • #3
    Regular Coder MrBiggZ's Avatar
    Join Date
    Apr 2005
    Location
    Indianapolis IN
    Posts
    325
    Thanks
    42
    Thanked 2 Times in 2 Posts
    Dormilich I finally got that figured out late late last night. I assigned the results to an array and I was in business! Thanks for your help!
    “No matter how slick the demo is in rehearsal, when you do it in front of a live audience, the probability of a flawless presentation is inversely proportional to the number of people watching, raised to the power of the amount of money involved.” ~ Mark Gibbs

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,067
    Thanks
    2
    Thanked 319 Times in 311 Posts
    You would need to perform a data seek operation (whatever the actual mysqli statement for that would be) in order to be able to loop over the result set again.

    However, your presentation code is too closely coupled to your database statements. A more general purpose approach would be to store the result from each query into an array, then loop over those arrays in your presentation code. Your presentation code (views) should not have any knowledge of the type of database statements you are using.

    edit: which you posted about while I was writing this (I thought this forum s/w notified you of posts made while you are writing a reply?)
    Last edited by CFMaBiSmAd; 08-02-2014 at 04:01 PM.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    Regular Coder MrBiggZ's Avatar
    Join Date
    Apr 2005
    Location
    Indianapolis IN
    Posts
    325
    Thanks
    42
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    ... edit: which you posted about while I was writing this (I thought this forum s/w notified you of posts made while you are writing a reply?)
    You would think but you have no idea of how busy the server is. It might be just a cron job that runs every five minutes or so (guessing, but makes sense).

    I thank you for your input though as it brought even more insight as what I should not do! =)
    “No matter how slick the demo is in rehearsal, when you do it in front of a live audience, the probability of a flawless presentation is inversely proportional to the number of people watching, raised to the power of the amount of money involved.” ~ Mark Gibbs


  •  

    Posting Permissions

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