retrieving and echoing multiple values from mysql column

This site is best viewed in a modern browser with JavaScript enabled.

retrieving and echoing multiple values from mysql column

georgeblaze

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.

$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;

<td><?php echo $date; ?> </td>
<td><?php echo $me; ?> </td>
<td><?php echo $me2; ?> </td>

My problem now is that if a user has more than one transaction how would I be able to echo each value from the let's say

ticket_num

column separately. I was thinking of something like an array where I can call

$row->ticket_num['0']

Gravy

I'm confused... which table did you add "ticket_date, ticket_num, ticket_result" to? What tables and relationships do you have?

I'm going to make a few assumptions here...

I'll assume you're putting the purchase info in the member/user table. This isn't the right way to do it.

You would want 2 tables:

Members:
id, name, ...

Transactions:
id, member_id, ticket_date, ticket_num, ticket_result, ...

In this way you can have many transactions in the transactions table and join it to the members table where the transaction.membe_id is equal to the member.id.

E.g.

This will get all data from the transaction table only
SELECT transactions.* FROM transactions WHERE member.id = THEIDOFTHEMEMBER;

This will get all data from both tables, but since both have an "id" field they will collide and you wont know which one is which, so you can give them aliases using [B]AS[/B]. We already have a member_id field in the transaction table so we don't need to alias it.
SELECT *, transactions.id AS transaction_id FROM members, transactions WHERE transactions.member_id = members.id AND member.id = THEIDOFTHEMEMBER;

It is usually better to only SQL out the particular fields you need.

In might be worth reading up on relational databases.

georgeblaze

Gravy;1337639 wrote:
I'm confused... which table did you add "ticket_date, ticket_num, ticket_result" to? What tables and relationships do you have?

I'm going to make a few assumptions here...

I'll assume you're putting the purchase info in the member/user table. This isn't the right way to do it.

You would want 2 tables:
Members:
id, name, ...

Transactions:
id, member_id, ticket_date, ticket_num, ticket_result, ...
In this way you can have many transactions in the transactions table and join it to the members table where the transaction.membe_id is equal to the member.id.

E.g.
This will get all data from the transaction table only
SELECT transactions.* FROM transactions WHERE member.id = THEIDOFTHEMEMBER;

This will get all data from both tables, but since both have an "id" field they will collide and you wont know which one is which, so you can give them aliases using [B]AS[/B]. We already have a member_id field in the transaction table so we don't need to alias it.
SELECT *, transactions.id AS transaction_id FROM members, transactions WHERE transactions.member_id = members.id AND member.id = THEIDOFTHEMEMBER;

It is usually better to only SQL out the particular fields you need.
In might be worth reading up on relational databases.

I have a mysql table called members that already has a username and password column for each user. I added the three columns ticket_date, ticket_num, ticket_result to that table.

I am sort of understanding what you are saying but I still don't understand how I can store multiple transactions of a user.

Would I create a new row for each transaction of a user.

Gravy

You don't want transaction data in the member table.

The transaction table would contain all transaction data, plus a member id.
So, one member can have many transactions.

MEMBERS
id, username, password
1, gravy, ****
2, john, ****
3, mike, ****

TRANSACTIONS
id, memberid, ticket_date, ticket_num, ticket_result
1, 1, 2014/05/07, 57, 1
2, 2, 2014/05/07, 58, 1
3, 3, 2014/05/07, 59, 1
4, 1, 2014/05/07, 60, 1
5, 3, 2014/05/07, 61, 1

You match the data from both tables

Gravy bought 2 tickets (57 and 60)
John bought 1 ticket (58)
Mike bought 2 tickets (59 and 61)

So by searching for all transactions in the transaction table which have a memberid of #, you can get all the transactions by user #. In this example Gravy was user #1 and therefore all the transactions in the transaction table with memberid 1, were transcations made by Gravy.

You really should read a book about relational databases.

georgeblaze

Gravy;1337771 wrote:
You don't want transaction data in the member table.

The transaction table would contain all transaction data, plus a member id.
So, one member can have many transactions.
MEMBERS
id, username, password
1, gravy, ****
2, john, ****
3, mike, ****

TRANSACTIONS
id, memberid, ticket_date, ticket_num, ticket_result
1, 1, 2014/05/07, 57, 1
2, 2, 2014/05/07, 58, 1
3, 3, 2014/05/07, 59, 1
4, 1, 2014/05/07, 60, 1
5, 3, 2014/05/07, 61, 1
You match the data from both tables

Gravy bought 2 tickets (57 and 60)
John bought 1 ticket (58)
Mike bought 2 tickets (59 and 61)

So by searching for all transactions in the transaction table which have a memberid of #, you can get all the transactions by user #. In this example Gravy was user #1 and therefore all the transactions in the transaction table with memberid 1, were transcations made by Gravy.

You really should read a book about relational databases.

Would there be a problem with storing a user's account balance in the table with the username and password or should a new table be created just for that?

Gravy

georgeblaze;1338233 wrote:
Would there be a problem with storing a user's account balance in the table with the username and password or should a new table be created just for that?

My 4:44 am head says that should be fine. =]

Gravy

georgeblaze;1338233 wrote:
Would there be a problem with storing a user's account balance in the table with the username and password or should a new table be created just for that?

And by "that" should be fine, I mean you can put the balance in the user table.

Since you only need 1 balance per member, it seems the best choice.

I hate when people are ambiguous, I hate it even more when the ambiguous one is me.