www.webdeveloper.com
Results 1 to 14 of 14

Thread: fetching all data from a column mysqli php

  1. #1
    Join Date
    Jun 2014
    Posts
    16

    fetching all data from a column mysqli php

    I need some help trying to fetch and echo all data from a column in a mysql table. Take a look at the code I posted below. In the mysql table "tickets" I have two rows with column name "ticket_result" One of the rows for "ticket_result" has "winner" and the other says "loser".


    PHP Code:
    public function tickets() {
           
    $this->db_connection = new mysqli('''''''');


                     
    $sql "SELECT tickets.ticket_result AS ticket_result
                             FROM users, tickets
                             WHERE tickets.member_id = users.user_id AND users.user_id = '1'"
    ;
                     
    $query $this->db_connection->query($sql);
                   
                   while (
    $row $query->fetch_object()) {
                   global 
    $me2;
                   
    $me2 $row->ticket_result;
                   
                  
                 }
         } 
    HTML Code:
    <?php $classLogin->tickets(); ?>
              
    <table>
    
    <thead>
    <th>Result</th>
    </thead>
    
    <tr>
    <td><?php echo $me2; ?> </td>
    </tr>
    
    </table>
    The code echoes works but it only echoes "loser" into the table and not winner also. I tried adding a foreach loop before <table> but it doesn't work because I guess "$me2" isn't an array. I also tried changing the php code from "fetch_object" to "fetch_assoc" and "fetch_array" but that doesn't work either.

    The only way I was able to get both "winner" and "loser" to be echoed to the page was when I changed the php code to this.

    PHP Code:
       $query $this->db_connection->query($sql);
                   
                   while (
    $row $query->fetch_object()) {
                   echo 
    $row->ticket_result;
                   
                  
                 } 
    Is there a better way I can do this so that I can add a foreach loop to the table and echo all data from the column?

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,399
    To work with what I'm guessing is your general app structure, you would want to put the results of the query into an array; then within your HTML output you could do a foreach() on that array to output each array element (result row).

    PS: In order to avoid unnecessary use of "global" (which is probably a redundant phrase), I'd much rather see you have your tickets() function return that array result, so that your output code can just call it and assign the result to a variable at that point, and not be dependent on a global variable (shudder).

    Quick-and-dirty example:
    PHP Code:
    class YourClass
    {
        public function 
    tickets() {
            
    $this->db_connection = new mysqli('''''''');
            
            
    $sql "SELECT tickets.ticket_result AS ticket_result
                    FROM users, tickets
                    WHERE tickets.member_id = users.user_id AND users.user_id = '1'"
    ;
            
    $query $this->db_connection->query($sql);
            
            
    $me2 = array();
            while (
    $row $query->fetch_object()) {
                
    $me2[] = $row->ticket_result;    
            }
            return 
    $me2;
        }

    Using it:
    PHP Code:
    <?php $me2 $classLogin->tickets(); ?>
              
    <table>

    <thead>
    <th>Result</th>
    </thead>

    <tr>
    <?php foreach($me2 as $value): ?>
    <td><?php echo $value?> </td>
    <?php endforeach; ?>
    </tr>

    </table>
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    795
    $me2 .= $row->ticket_result;

    Of course this will necessitate some additional formatting, but you'll get all your data

  4. #4
    Join Date
    Jun 2014
    Posts
    16
    Thanks Nogdog, I was getting an invalid foreach() error until I realized I forgot to add
    PHP Code:
    <?php $me2 $classLogin->tickets(); ?>
    Is there a reason why that line is necessary? I would think that it should know that $me2 is from the tickets() function since I called that function earlier in the code

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,399
    Quote Originally Posted by georgeblaze View Post
    Thanks Nogdog, I was getting an invalid foreach() error until I realized I forgot to add
    PHP Code:
    <?php $me2 $classLogin->tickets(); ?>
    Is there a reason why that line is necessary? I would think that it should know that $me2 is from the tickets() function since I called that function earlier in the code
    Because I got rid of the global variable declaration and instead had the method return the array, since global variables are evil. (I'll leave it to you to search the web for the many, many discussions about that; but in short, the main reasons they are evil is that they closely couple the function with the rest of the code -- and we should always be striving for loose coupling -- and they can cause maintenance/debugging nightmares.)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Jun 2014
    Posts
    16
    Ok. What if I want to have more columns from my my db. Aren't you only allowed to return one variable from a function?

    I want to add this:

    PHP Code:
    $me2 = array(); 
    $me3 = array(); 
            while (
    $row $query->fetch_object()) { 
                        
    $me2[] = $row->ticket_result;     

                        
    $me3[] = $row->ticket_number;     
            } 

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,399
    I'd probably make a multi-dimensional array, and since MySQLi will do that for you with one command (instead of looping):
    PHP Code:
        $me2 $query->fetch_all(MYSQLI_ASSOC)); 
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  8. #8
    Join Date
    Jun 2014
    Posts
    16
    Quote Originally Posted by NogDog View Post
    I'd probably make a multi-dimensional array, and since MySQLi will do that for you with one command (instead of looping):
    PHP Code:
        $me2 $query->fetch_all(MYSQLI_ASSOC)); 
    If I use your method above how will I be able to call each column separately like "ticket_result"? Wouldn't all the values from the query be thrown into one array?

  9. #9
    Join Date
    May 2014
    Posts
    1,035
    I'm surprised nobody is pointing out how... pointless the query itself is.

    Code:
    WHERE tickets.member_id = users.user_id AND users.user_id = '1'
    If you only want values from tickets, and you know the ID you want is one, why are you even getting the users table involved?!?

    Code:
      SELECT ticket_result
      FROM tickets 
      WHERE member_id = '1'
    Should be all you need, unless somehow you have records in tickets with member_id's that don't exist in users; which seems unlikely.

    Though yeah, it looks like you have gotten WAY too complex... and very wasteful of memory. Why not just return the statement and iterate it skin-side? Don't waste time making giant memory wasting copies of data for nothing!

    Code:
    class YourClass { 
    	public function tickets() { 
    		$this->db_connection = new mysqli('', '', '', ''); 
    		return $this->db_connection->query('
    			SELECT ticket_result
    			FROM tickets
    			WHERE member_id = 1
    		');
    	} 
    } 
    
    $tickets = $classLogin->tickets();
               
    echo '
    	<table> 
    		<thead> 
    			<th>Result</th> 
    		</thead><tbody>';
    		
    while ($row = $tickets->fetch_object()) echo '
    			<tr>
    				<td>', $row->ticket_result, '</td>
    			</tr>';
    			
    echo '
    		</tbody>
    	</table>';
    Should do what you are thinking. Never make extra copies of values that already exist in memory, and that INCLUDES result sets. A lesson I ended up teaching myself the hard way years ago when I first started using PDO and discovered ::fetchAll and started over-using it. Suddenly queries that normally used half a megabyte memory footprint were sucking down half a gig...
    Last edited by deathshadow; 06-20-2014 at 03:57 PM.
    Java is to JavaScript as Ham is to Hamburger.

  10. #10
    Join Date
    Jun 2014
    Posts
    16
    Thanks for the tips deathshadow.

    If I have 2 arrays returned from my function how will I be able to call each array separately.
    I don't see how this line would work.
    $me2 = $query->fetch_all(MYSQLI_ASSOC));

  11. #11
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    795
    What you're being told is to return the result of the query, not some other form of the query results.

    I do believe you need to read up on some things and come to understand query results, functions, and how to manipulate data in your programming.

  12. #12
    Join Date
    Jun 2014
    Posts
    16
    Quote Originally Posted by ginerjm View Post
    What you're being told is to return the result of the query, not some other form of the query results.
    I don't understand. Could you explain more?

    Quote Originally Posted by ginerjm View Post
    I do believe you need to read up on some things and come to understand query results, functions, and how to manipulate data in your programming.
    Are there any favorite links you could point me to.

  13. #13
    Join Date
    May 2014
    Posts
    1,035
    Quote Originally Posted by georgeblaze View Post
    If I have 2 arrays returned from my function how will I be able to call each array separately.
    Make it two separate functions, or return the statement handlers for each query as an array of statements... though you are thinking arrays, when you should be thinking statement objects; again, STOP trying to fetch/return the whole thing as one giant array!

    Though if they're related/linked data, that's JOIN's job.
    Java is to JavaScript as Ham is to Hamburger.

  14. #14
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    795
    Sorry georgeblaze but your skill set is too small to take direction accurately and you apparently have not done any reading on things as I previously suggested. Good luck. Signing off.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles