Getting Data From A Sql Table And Making A Table

[miocene]

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:Edit: 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):I 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

[justsomeguy]

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?

[miocene]

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

[justsomeguy]

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.

[miocene]

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:I still can't extract the data the way I want...

[justsomeguy]

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.

[miocene]

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

[justsomeguy]

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.