retrieving and echoing multiple values from mysql column

Question

I have created these 3 columns in my mysql table so I can have a list of each users transactions.

ticket_date, ticket_num, ticket_result

I also created a function to echo the value in those columns and it worked successfully.

function.php:

$sql = "SELECT * 
        FROM members 
        WHERE user_id = '{$_SESSION['user_id']}'"; 
           $query = $this->db_connection->query($sql); 

           while ($row = $query->fetch_object()) { 
           global $me, $me2, $date; 
           $me = $row->ticket_num; 
           $me2 = $row->ticket_result; 
           $date = $row->ticket_date;  
           }

transactions.php

<table class="table">
<thead>
<th>Date</th>
<th>Ticket ID</th>
<th>Result</th>
</thead>
<tr>
<td><?php echo $date; ?> </td>
<td><?php echo $me; ?> </td>
<td><?php echo $me2; ?> </td>
</tr>
</table>

My problem now is that if a user has more than one transaction how would I be able to echo each value from a particular column separately. I am trying to echo each transaction into a . Would I be able to store each value as an array so I could call it like this?

$row->ticket_num['0']

EDIT: I meant to ask how can I store the transactions into their respective columns. Such as storing more than one $ticket_date that apply to each transaction. Could I store information into the columns as an array so I can call each transaction using the array pointer?

No. The while loop is in a function and the echo is in a separate file. — user3718799
– user3718799, Commented Jun 8, 2014 at 1:59
Can you try something and then see if you run into an error? — Ali Gajani
– Ali Gajani, Commented Jun 8, 2014 at 2:04

Adi Bradfield · Accepted Answer · 2014-06-09 17:59:30Z

1

I think the best thing to do, would be not to use global variables for this, unless there is any reason that your function can't return this data, i.e. it's returning something else. You could have something like this in your function.php:

$sql = "SELECT * 
        FROM members 
        WHERE user_id = '{$_SESSION['user_id']}'"; 
           $query = $this->db_connection->query($sql); 
           $return = array();

           while ($row = $query->fetch_object()) { 
            array_push($return, array($row->ticket_num, $row->ticket_result, $row->ticket_date ));
           }
        return $return

Then you can do this in your transactions.php:

<table class="table">
<thead>
<th>Date</th>
<th>Ticket ID</th>
<th>Result</th>
</thead>
<?php
$ticket = Whatever_Function();
foreach($ticket as $t){
echo"<tr> <td> ".$t[0]." </td><td>".$t[1]."</td><td>".$t[2]."</td></tr>";
}
?>
</table>

Edit

In relation to your additional question in the comments, a database structure like this should be set up:

DB Structure

By doing this, you are separating it, so that every table belongs to one thing or action. These can then be related to each other using an SQL Join like this:

SELECT * FROM Transactions tr
JOIN Users u ON u.UID = tr.UID
JOIN Tickets ti ON ti.TID = tr.TID

By looking at the above SQL code snippet, you should be able to see how it matches up the columns on the different tables. This creates one big virtual table that you can then search for stuff with, where their column names prepended with their given pseudonyms.

So if you wanted to get the email address of everyone that bought the ticket whose price was over £20, even though the tables you need aren't directly connected you could do:

SELECT u.email FROM Transactions tr
JOIN Users u ON u.UID = tr.UID
JOIN Tickets ti ON ti.TID = tr.TID
WHERE ti.Price > 20

edited Jun 9, 2014 at 17:59

answered Jun 8, 2014 at 2:18

Adi Bradfield

2,0931 gold badge17 silver badges27 bronze badges

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

8 Comments

user3718799 Over a year ago

Thanks, that is a better way I could return the value. I forgot to ask in my question, how would I be able to store each transaction in the mysql column? For example, if I have more than one ticket id how would I be able to store so that I could call like this $t['0'] or $t['1']. Is there a way I could store the information into the db as an array?

Adi Bradfield Over a year ago

@user3718799 I would advise never to store array data into a database. It becomes a massive pain if you ever need to search through it! I would create a new table that stores all transactions as individual rows. You can then get all the transactions for a particular ticket by doing a select on all transactions with the tickets ID. Anyway, I'm heading off to a festival today, so I may not be able to provide any more help until tomorrow!

user3718799 Over a year ago

If I created a new row for the ticket information what would be the best way to link that ticket id to the user who made the transaction.

Adi Bradfield Over a year ago

Okay, I'd have 3 separate tables. One for all the separate tickets you're selling, which includes name, details price, id etc. Then I'd have a table for users with contact details and everything else you'd need. Then you have your transaction table which will have the id of the ticket, the id of the user, and any other details specific to the transaction, like payment method.

Adi Bradfield Over a year ago

I can provide a more detailed explanation at some point tomorrow if required

|

hlscalon · Accepted Answer · 2014-06-08 02:08:01Z

0

Do something like this:

global $me, $me2, $date; 
while ($row = $query->fetch_object()) { 
    $me[] = $row->ticket_num; 
    $me2[] = $row->ticket_result; 
    $date[] = $row->ticket_date;  
}

transactions.php

<table class="table">
<thead>
<th>Date</th>
<th>Ticket ID</th>
<th>Result</th>
</thead>
<?php foreach($me as $k => $m){ 
    echo "<tr>";
    echo "<td>".$date[$k]."</td>";
    echo "<td>".$m."</td>";
    echo "<td>".$me2[$k]."</td>";
    echo "</tr>";
?>
</table>

answered Jun 8, 2014 at 2:08

hlscalon

7,6004 gold badges35 silver badges41 bronze badges

Collectives™ on Stack Overflow

retrieving and echoing multiple values from mysql column

2 Answers 2

Edit

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related