Putting Values Into Chosen Columns In Sql Table

[miocene]

OK I have an sql table that has 3 columns for each person: payed, fairshare and topayeach of these column headings will be prefixed by a number (e.g. 2payed) to indicate which user they apply to.The number of persons is subject to change so I want my script to write to the table according to the number of users there are.I need my sql statment to change for each run of the loop - I.e. the column headings in the INSERT command need to change each time from 0payed to 1payed to 2payed etcThe variables $personpaid, $user_fs and $topay are all arrays with the number of items = to the number of users ($num_users)Here is my code:

for ($i=0; $i<=$num_users - 1; $i++){$sql="INSERT INTO payments ([$i]payed, [$i]fs, [$i]topay)VALUES('$personpaid[$i]', '$user_fs[$i]' , '$topay[$i]')";}

[smerny]

"each of these column headings will be prefixed by a number (e.g. 2payed) to indicate which user they apply to"that doesn't make sense, you can't change the heading names... how about adding a column called ID where the number auto increments for each row?

[miocene]

"each of these column headings will be prefixed by a number (e.g. 2payed) to indicate which user they apply to"that doesn't make sense, you can't change the heading names... how about adding a column called ID where the number auto increments for each row?

I don't want to change the heading names I just want to reference to them without having loads of code. All the heading names (3 columns for each user) are already there in the table.I just want to write to each of the column.E.g. for just user 1 I would do

$sql="INSERT INTO payments (0payed, 0fs, 0topay)VALUES('$personpaid[0]', '$user_fs[0]' , '$topay[0]')";

and for user 2 I would do:

$sql="INSERT INTO payments (1payed, 1fs, 1topay)VALUES('$personpaid[1]', '$user_fs[1]' , '$topay[1]')";

so rather than have a code block like that I want to have a loop that changes the array indexs and column references for each user

[boen_robot]

Why not have a separate column for the user ID, and a second column with the "payed" data? The user ID doesn't need to be a key or a unique value. It will just reference the user the "payed" applies to. It will be just:

$userid = 0;$sql="INSERT INTO payments (userid, payed, fs, topay)VALUES($userid, '$personpaid[$userid]', '$user_fs[$userid]' , '$topay[$userid]')";

That's not "loads of code", is it?

[miocene]

Why not have a separate column for the user ID, and a second column with the "payed" data? The user ID doesn't need to be a key or a unique value. It will just reference the user the "payed" applies to. It will be just:

$userid = 0;$sql="INSERT INTO payments (userid, payed, fs, topay)VALUES($userid, '$personpaid[$userid]', '$user_fs[$userid]' , '$topay[$userid]')";

That's not "loads of code", is it?

Yes that is one way but there is other info in the table that applies only to the payment like 'companyname' and 'item'If I do it your way I will have 4 rows (for 4 users) for each payment. This will take up unnecessary space in the database by repeating values in different rows (e.g. having the companyname 4 times).I did try doing it this way all the same but found it very difficult to extract the data and tablulate it on an html page the way I wanted.Is there no way to do it the way i explained in my first post?

[boen_robot]

That's why they call them "relational" databases you know. You can have another list of company names and item names, associated with a number, then just write in the numbers.For example, the "companies" table will have "companyID" (int; key) and "companyName"(varchar) column. "items" table wil have "itemID" (int; key) and "itemName" (varchar). "users" table will have (for example... I'm not sure of your complete scenario) "userID" (int; key), "userName" (varchar), "companyID" (int; key reference), and your "payments" table may have (again for example) "paymentID" (int; key), "userID" (int; key reference), "payed" (double).Want to find out the company that made the payment? Join the payments table with the users and company tables, and get the company names along with the payment sums.As for the space it will take up, it will certainly take less than if you start writing out the company names directly. Numbers take up somewhere between a byte and 4 bytes or so (depending on the type of the number... double obviously takes more than int), whereas a varchar can take at least 2 bytes (one byte for the size, a second empty one), or more if there's a name (e.g. a company with a name of 5 letters will take 6 bytes).It's not that what you want is impossible... it's that it's not practical, and won't make up the kind of savings you're after.

[miocene]

That's why they call them "relational" databases you know. You can have another list of company names and item names, associated with a number, then just write in the numbers.For example, the "companies" table will have "companyID" (int; key) and "companyName"(varchar) column. "items" table wil have "itemID" (int; key) and "itemName" (varchar). "users" table will have (for example... I'm not sure of your complete scenario) "userID" (int; key), "userName" (varchar), "companyID" (int; key reference), and your "payments" table may have (again for example) "paymentID" (int; key), "userID" (int; key reference), "payed" (double).Want to find out the company that made the payment? Join the payments table with the users and company tables, and get the company names along with the payment sums.As for the space it will take up, it will certainly take less than if you start writing out the company names directly. Numbers take up somewhere between a byte and 4 bytes or so (depending on the type of the number... double obviously takes more than int), whereas a varchar can take at least 2 bytes (one byte for the size, a second empty one), or more if there's a name (e.g. a company with a name of 5 letters will take 6 bytes).It's not that what you want is impossible... it's that it's not practical, and won't make up the kind of savings you're after.

OK but that would involve rewriting a lot of my code and fiddling my tables, and as a php beginner it will take a long time.How can I do what I asked originally? (i.e. what is the syntax?)When I get more experienced I will try and make my code more efficient

[justsomeguy]

I'm not entirely certain you can start a column name with a number, but..$sql="INSERT INTO payments ({$i}payed, {$i}fs, {$i}topay)VALUES('{$personpaid[$i]}', '{$user_fs[$i]}' , '{$topay[$i]}')";

[miocene]

I'm not entirely certain you can start a column name with a number, but..$sql="INSERT INTO payments ({$i}payed, {$i}fs, {$i}topay)VALUES('{$personpaid[$i]}', '{$user_fs[$i]}' , '{$topay[$i]}')";

that's great. I put the numbers on the end to be safe. (e.g. payed1 rather than 1payed)so how do I do:

include("connection.php");$result = mysql_query("SELECT * FROM payments");while($row = mysql_fetch_array($result)){for ($i=0;$i<=$num_users - 1; $i++)	   {echo $row['payed{$i}'];}}

tried the above but no luck

[justsomeguy]

Use double quotes instead of single quotes, when you write payed{$i} in single quotes it uses the string "payed{$i}", it doesn't replace the variable with the value. Variable replacement only happens in double-quoted strings, not single-quoted.

[miocene]

Use double quotes instead of single quotes, when you write payed{$i} in single quotes it uses the string "payed{$i}", it doesn't replace the variable with the value. Variable replacement only happens in double-quoted strings, not single-quoted.

Awesome thankyou