www.webdeveloper.com
Results 1 to 5 of 5

Thread: storing account history table in mysql

  1. #1
    Join Date
    Jun 2014
    Posts
    16

    storing account history table in mysql

    I want to insert a table like this into a mysql table and it is sort of like the account history of a user. I want to have it stored so that it can be echoed back to the user when they take a look at their account history.

    HTML Code:
    <table>
            					<thead>
              						<tr>
                							<th>Ticket #</th>
                							<th>Date</th>
                							<th>Type</th>
                							<th>Result</th>
    
              						</tr>
            					</thead>
            					<tbody>
              						<tr>
                							<td>1</td>
                							<td>2014-03-07</td>
                							<td>N/A</td>
                							<td>Winner</td>
    
              						</tr>
              				
            					</tbody>
          					</table>
    Would it be best to create a column for Ticket#, Date,Type ,and Result? I also want to have it ordered by the most recent date.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,322
    Ordering is a function of how you query the database, not how it is stored in the database -- well, not the order in which it is stored; you need a column on which you can do a meaningful ORDER BY clause, which presumably would either be the date field being entered, or possibly a separate timestamp column that is automatically set as part of the INSERT query.
    "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
    Jun 2014
    Posts
    16
    Quote Originally Posted by NogDog View Post
    Ordering is a function of how you query the database, not how it is stored in the database -- well, not the order in which it is stored; you need a column on which you can do a meaningful ORDER BY clause, which presumably would either be the date field being entered, or possibly a separate timestamp column that is automatically set as part of the INSERT query.
    okay I understand what you're saying about the ordering but I still need help with the first question "I want to insert a table like this into a mysql table and it is sort of like the account history of a user. How can I have it stored so that each transaction can be echoed back to the userwhen they take a look at their account history?

    Thanks

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,322
    Looks like you would need to add a column that indicates who the user is. Normally this would be the primary key of your "users" table (or whatever you call it). Then your query would be something like:
    Code:
    SELECT ticket_nbr, ticket_date, ticket_type, result
    FROM transaction_table
    WHERE user_id = :user_id
    ORDER BY ticket_date DESC
    As far as the mechanics of doing so in PHP, it depends to some degree on which database extension you choose to use. For a MySQL database, my first recommendation would be PDO (for ease of porting to a different DBMS if ever needed), otherwise the MySQLi extension (not the deprecated MySQL extension). Then you have to decide if you're going to use object-oriented syntax or procedural syntax. If none of that makes any sense to you, you've got some reading to do.
    "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

  5. #5
    Join Date
    Jun 2014
    Posts
    16
    Quote Originally Posted by NogDog View Post
    Looks like you would need to add a column that indicates who the user is. Normally this would be the primary key of your "users" table (or whatever you call it). Then your query would be something like:
    Code:
    SELECT ticket_nbr, ticket_date, ticket_type, result
    FROM transaction_table
    WHERE user_id = :user_id
    ORDER BY ticket_date DESC
    As far as the mechanics of doing so in PHP, it depends to some degree on which database extension you choose to use. For a MySQL database, my first recommendation would be PDO (for ease of porting to a different DBMS if ever needed), otherwise the MySQLi extension (not the deprecated MySQL extension). Then you have to decide if you're going to use object-oriented syntax or procedural syntax. If none of that makes any sense to you, you've got some reading to do.
    I am using mysqli and oop. I understand how would I retrieve the information from each column and echo it onto a table if there is more than one ticket. Would I store them into their columns as an array then use a foreach loop so I could do something like this

    Code:
    $query = "SELECT ticket_nbr, ticket_date, ticket_type, result
    FROM transaction_table
    WHERE user_id = :user_id
    ORDER BY ticket_date DESC"
    $result = mysqli_fetch_array($query);
    foreach ($result as $trans) { ?>
    
    			<thead>
              						<tr>
                							<th>Ticket #</th>
                							<th>Date</th>
                							<th>Type</th>
                							<th>Result</th>
    
              						</tr>
            					</thead>
            					<tbody>
              						<tr>
                							<td><?php echo $ticketnum ?></td>
                							<td><?php echo $date ?></td>
                							<td><?php echo $type ?></td>
                							<td><?php echo $result ?></td>
    
              						</tr>
              				
            					</tbody>
          					</table>
    <?php } ?>

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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