first thing is name="likefootball" id="likefootball1"
name and id should be the same.
You'll need to build a dynamic query. It's pretty simple when you understand concatenation. Concatenation is simple adding data to an existing variable. So first add some data to the $query var
$query = "INSERT INTO formdata (userid, fieldid, value)
VALUES";
now using .= (dot equals) you can add more data (concatenate) to the variable $query.
$query = "INSERT INTO formdata (userid, fieldid, value)
VALUES ";
$query .="('$userid', '$fieldid', '$value')";
this is the same as
$query = "INSERT INTO formdata (userid, fieldid, value)
VALUES('$userid', '$fieldid', '$value')";
Why is this useful? Now you can build the query in aloop and insert all your form field data in one go.
$query = "INSERT INTO formdata (userid, fieldid, value)
VALUES ";
doaloop(){
$q.=" ('$userid', '$fieldid', '$value') ";
}
so for what ever condition calls a loop you can insert as many values as you need. Just build the query dynamically and insert the data in a single statement. It's really easy to get syntax errors doing this so you need to echo the query out to check it.
So you have a POST array. It consists of the key as the field name and the value. Have a look to see what is contains using print_r($_POST) after submitting the form.
You have some choices. You could have an array of filed names and only dynamically build the query if the $POST key is in your array or prepend the fileds you want to submit with a common name like insertme_filedname. You can then preg teh key for insertme and explode the key on the and take the second half as the key name to insert. Either works as well as the other. I'll use the array method in this example.
You could build the array form the form fields table if you have one.
//build fileds to insert array
$fieldstoinsert=array('red','gotmilk','likefootball','drinkbeer');
//start building query
$query = "INSERT INTO formdata (userid, fieldid, value) VALUES "
//loop through the post array
foreach($_POST as $fieldid => $value){
if(in_array($fieldid,$fieldstoinsert)){
if(trim($value)!=""){//only basic validation here
$q.="('$userid', '$fieldid', '$value')";
}
}
}
You'll now have multiple records in formdata.
You're right you'll need to join to get all the data in a single query. You dont have to join though. You could select the user info then select the formdata, build the array and compare the field names and echo the data if it exists.
if you wanted to join you could do this
select * from users
INNER JOIN formdata on users.userid=formdata.userid
Read wikipedias excellent explanation on joins
http://en.wikipedia.org/wiki/Join_(SQL)