fetching multiple columns of mysql table and echoing each row

Question

I have this php/html code that echos a column from a mysql table called "tickets". These are the 3 columns in the table (member_id, ticket_id, ticket_result).

I want to be able to grab the info from the other columns in a specific row and echo that with the foreach loop. I just don't know how I would be able to do that because you can only have 1 array in a foreach loop so if I was to make add this line to the PHP Code I don't see how I could echo it in the foreach loop.

 $me3 = array(); 
$me2[] = $row->ticket_result;

PHP Code:

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

    $sql = "SELECT ticket_result 
            FROM tickets 
            WHERE member_id = '1'"; 
    $query = $this->db_connection->query($sql); 

    $me2 = array(); 
    while ($row = $query->fetch_object()) { 
        $me2[] = $row->ticket_result;     
    } 
    return $me2; 
  } 
}

HTML Code:

<?php $me2 = $classLogin->tickets(); ?> 
       <?php foreach($me2 as $value) { ?> 
     <table> 
<thead> 
<th>Result</th> 
<th>ID</th>
</thead> 

<tr> 

<td><?php echo $value; ?> </td> 
<td>  </td>
<?php } ?> 
</tr> 

</table>

Select the other 2 columns in your query, then $me2[] = $row->ticket_id; etc. should do the trick. You may need to use fetch_assoc() though. Try that. — Funk Forty Niner
– Funk Forty Niner, Commented Jun 21, 2014 at 4:54
In your query -> ` $sql = "SELECT member_id, ticket_id, ticket_result...` and in your php -> $me2[] = array('member_id'=>$row->member_id, 'ticket_id'=>$row->ticket_id, 'ticket_result'=>$row->ticket_result);? — Sean
– Sean, Commented Jun 21, 2014 at 4:57
That was a rookie mistake forgetting to add the other 2 columns into the query. — user3718799
– user3718799, Commented Jun 21, 2014 at 5:12

arielnmz · Accepted Answer · 2014-06-21 05:11:59Z

3

You're only selecting one column of your table:

$sql = "SELECT ticket_result FROM tickets WHERE member_id = '1'";

Your statement should look like this:

$sql = "SELECT member_id, ticket_id, ticket_result FROM tickets WHERE member_id = '1'";

Or:

$sql = "SELECT * FROM tickets WHERE member_id = '1'";

Then you can retrieve your data with this:

while ($row = $query->fetch_object()) { 
    $me2[$row->ticket_id]['ticket_result'] = $row->ticket_result;
    $me2[$row->ticket_id]['member_id'] = $row->member_id;
}

So you'll have an array ($me2) that contains keys according to your ticked_ids and each key is an array with the keys ticket_result and member_id which contain that data from your db.

Then you can implement your foreach like this:

foreach($me2 as $key => $value) {
    echo $key;
    echo $value['ticket_result'];
    echo $value['member_id'];
}

Also, if the HTML you have to print is not that much, I suggest you better use echo and concatenate a string withing a single php clause rather than opening and closing multiple php tags for every little php code you have to execute, your code would look like this:

<?php
    $me2 = $classLogin->tickets();
    echo
        '<table>
            <thead>
                <th>Result</th>
                <th>ID</th>
            </thead>';
    foreach($me2 as $key => $value) {
        echo
            '<tr>
                <td>'.$value['ticket_result'].'</td>
                <td>'.$value['member_id'].'</td>
            </tr>';
    }
    echo '</table>';
?>

edited Jun 21, 2014 at 5:11

answered Jun 21, 2014 at 4:56

arielnmz

9,21510 gold badges43 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

user3718799 Over a year ago

I used your code above last night and it was working fine. I was playing with it as well and for some reason today it is only displaying one table with one row from my db instead of the 4 tables it was displaying yesterday. Could you take a look at this code and see if I could have made a mistake?

user3718799 Over a year ago

$sql = "SELECT * FROM tickets WHERE member_id = '1'"; $query = $this->db_connection->query($sql); $me2 = array(); while ($row = $query->fetch_object()) { $me2[$row->ticket_id]['ticket_result'] = $row->ticket_result; $me2[$row->ticket_id]['member_id'] = $row->member_id; } return $me2; }

user3718799 Over a year ago

<?php foreach ($me2 as $key => $value) { ?> <table class="table table-bordered "> <thead> <th>Date</th> <th>Member ID</th> <th>Result</th> </thead> <tr> <td><?php echo $key ?></td> <td><?php echo $value['member_id']; ?></td> <td><?php echo $value['ticket_result'];?> </td> </tr> </table> <?php } ?>

arielnmz Over a year ago

Did you make any change on the database? or did it just stopped working out of the nothing? if you use the code above again does it works again? chances are that something changed on your database.

user3718799 Over a year ago

I just added a couple of new rows into the db so I could see if it would display each new row and it did. And today it works but it is only displaying one row from the database.

|

Collectives™ on Stack Overflow

fetching multiple columns of mysql table and echoing each row

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related