www.webdeveloper.com
Results 1 to 7 of 7

Thread: retrieving and echoing multiple values from mysql column

  1. #1
    Join Date
    Jun 2014
    Posts
    16

    retrieving and echoing multiple values from mysql column

    I have created these 3 columns in my mysql table so I can have a list of each users transactions.
    Code:
    ticket_date, ticket_num, ticket_result
    I also created a function to echo the value in those columns and it worked successfully.
    PHP Code:
    $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
    PHP Code:
    <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
    Code:
    ticket_num
    column separately. I was thinking of something like an array where I can call
    PHP Code:
    $row->ticket_num['0'
    .

  2. #2
    Join Date
    May 2014
    Posts
    69
    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:

    Code:
    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.

    Code:
    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 AS. 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.

  3. #3
    Join Date
    Jun 2014
    Posts
    16
    Quote Originally Posted by Gravy View Post
    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:

    Code:
    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.

    Code:
    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 AS. 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.

  4. #4
    Join Date
    May 2014
    Posts
    69
    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.

    PHP Code:
    MEMBERS
    id
    usernamepassword
    1
    gravy, ****
    2john, ****
    3mike, ****

    TRANSACTIONS
    id
    memberidticket_dateticket_numticket_result
    1
    12014/05/07571
    2
    22014/05/07581
    3
    32014/05/07591
    4
    12014/05/07601
    5
    32014/05/0761
    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.

  5. #5
    Join Date
    Jun 2014
    Posts
    16
    Quote Originally Posted by Gravy View Post
    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.

    PHP Code:
    MEMBERS
    id
    usernamepassword
    1
    gravy, ****
    2john, ****
    3mike, ****

    TRANSACTIONS
    id
    memberidticket_dateticket_numticket_result
    1
    12014/05/07571
    2
    22014/05/07581
    3
    32014/05/07591
    4
    12014/05/07601
    5
    32014/05/0761
    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?

  6. #6
    Join Date
    May 2014
    Posts
    69
    Quote Originally Posted by georgeblaze View Post
    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. =]

  7. #7
    Join Date
    May 2014
    Posts
    69
    Quote Originally Posted by georgeblaze View Post
    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.

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