Jump to content

Getting Data From A Sql Table And Making A Table


miocene
 Share

Recommended Posts

OK I have a mysql table containing payments made by housemates. There is a different row for each house member for each payment (i.e if there are 4 housemates there will be 4 rows in the table per payment)Here is the table containing 2 payments for 4 housemates:xpzr4x.jpgEdit: I do not know why the id numbers are mixed up. How can I delete all the records and start again from 1I want to extract this data and put it in an html table with the headings as follows with 1 row per payment (as opposed to 4 in the sql table):t7iej5.pngI have written php code already that generates the above html table and can easily accept more users being added in a seperate "users" table.I just want to put the data from the mysql table at the top of this post in this html tableHere is the code that generates the html table so far:

<?phpinclude("selectusers.php");include("connection.php");echo "<table id=\"statement\"><tr><td></td>"; //starts the master table with blank cell topleft//makes first row - the user namesfor ($u=0;$u<=$num_users-1;$u++){echo "<td class=\"users\">" . $users[$u] . "</td>";}echo "</tr>";//END first row//makes second row - column headingsecho "<tr><td><table><tr><td>Edit</td><td>Company Name</td><td>Item</td></tr></table></td>";for ($u=0;$u<=$num_users-1;$u++){echo "<td><table><tr><td>Paid</td><td>Fair Share</td><td>To Pay</td></tr></table></td>";}echo "</tr>";//END column headings row

Edited by miocene
Link to comment
Share on other sites

You'll want to add another column in the table to identify each payment. We can see 4 rows for "Rent" on 22/7/09, but there's no data to indicate that those 4 rows are part of the same payment. Each payment needs an ID or something so that you can easily get all data for a single payment to fill in your HTML table. Once you do that, it's as easy as selecting the rows where the payment ID is the same, putting the data in an array, and printing parts of the array into your table.It's generally not necessary to reset the autonumbering, why do you think that's necessary?

Link to comment
Share on other sites

You'll want to add another column in the table to identify each payment. We can see 4 rows for "Rent" on 22/7/09, but there's no data to indicate that those 4 rows are part of the same payment. Each payment needs an ID or something so that you can easily get all data for a single payment to fill in your HTML table. Once you do that, it's as easy as selecting the rows where the payment ID is the same, putting the data in an array, and printing parts of the array into your table.It's generally not necessary to reset the autonumbering, why do you think that's necessary?
thanks for the replyOK how do I assign a unique id to each payment when it is entered? I can add the column in the sql table but how I make sure the id for each payment is a)unique b)incremental c)the same for all 4 records of each payment?here is the form page
Link to comment
Share on other sites

You can't have 2 autoincrement columns in the same table. If you require that the ID is an incremental ID instead of simply unique, then it's probably best to store payment information in one table, and payments made in another. The word "payment" is getting confusing, so I'm going to call one the charges, and one the payments. Each of the four people make payments on a charge. So you would insert the charge into its table with whatever information you want to track about it, get the new ID, and then insert the payments into the other table with the ID of the charge that they apply to.

Link to comment
Share on other sites

You can't have 2 autoincrement columns in the same table. If you require that the ID is an incremental ID instead of simply unique, then it's probably best to store payment information in one table, and payments made in another. The word "payment" is getting confusing, so I'm going to call one the charges, and one the payments. Each of the four people make payments on a charge. So you would insert the charge into its table with whatever information you want to track about it, get the new ID, and then insert the payments into the other table with the ID of the charge that they apply to.
OK I've figured out how to add a payment id that is the same for any given payment to my table apon submit:aufoy8.jpgI still can't extract the data the way I want...
Link to comment
Share on other sites

Why not? If you run this:SELECT * FROM payments WHERE paymentid=1You get 4 rows in the result, with 1 row for each person. You can loop over the rows and use the data to fill out your HTML table.
Yes but I only want the "company name" and "item" from the first row of each payment record
Link to comment
Share on other sites

SELECT companyname, item FROM payments WHERE paymentid=1If those are going to be the same for each payment for a certain charge, you might want to move those into the table for the charges instead of the table for the payments.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...