Totally Stuck with Query!

[benji87]

Hi all im trying to create an update query but im stuck as to how to do it so any help would be great if i try and explain wat im trying to do:

 

This is what my sqltable looks like:

 

|  ID  |  SUBJECT  |  VALUE  |  USERNAME  |

    1        SUBJECT1        0              USER

    2        SUBJECT2        0              USER

    3        SUBJECT3        0              USER

 

---------------------------------------------------

 

Now the user fills in a form in which they tick next to the subjects that they would like to learn about which have tick boxes next to them. These tick boxes are named the same as the subjects and have a value of '1'.

 

When the user submits the form i want the table to update the values the column where the user had ticked the checkboxes with the subject name of the subject column. This could be either one column being updated or up to 20.

 

I just cant seem to figure out a query for this that would work 

[tauchai83]

the ID column is auto increment number?

 

 

Basic idea:

<?php
$sql="UPDATE tablename SET Subject='$subject' WHERE ....... ";
$result="mysql_query($sql);
?>

[tauchai83]

your problem is you did not do a proper plan. You should do normalization to reduce the reduduncy.

 

if many-to-many relationship, you should create another table. DO you understand?

 

the DB design should have one table for Subject: subject ID, subject name

 

new table: subject ID, user ID

 

cheers. hope this help.

 

 

[benji87]

So how would i link the two tables in one query? I cant really see how that would solve my problem

[benji87]

Can nobody help???

[suzzane2020]

ur gonna need javascript for this

 

 

<script language="JavaScript" type="text/javascript">
       function MM_callJS()
            { 
                                
	document.form1.chkIds.value=document.form1.links.value
	for (i=0; i<document.form1.links.length; i++)
                       {
		 if (document.form1.links[i].checked==true)
			{

				document.form1.chkIds.value=document.form1.chkIds.value+","+document.form1.links[i].value

					}   
					  }
					         
		return true;		
		  }
</script>

 

 

now here u wud need a hidden field which is "chkIds" here and the name of the checkbox wud be "links"

as the checkbox is in the loop, the javascript takes the length or number of checkboxes generated-" document.form1.links.length" and gets the value of each selected box-"  if (document.form1.links.checked==true)"

 

then its passes it to the hidden field- "  document.form1.chkIds.value=document.form1.chkIds.value+","+document.form1.links.value"

 

with a separator '+' in between

 

let me knw if u gt this till here

[benji87]

Thanks but cant seem how to get it to work. I obviously need to put the javascript in the page with the form that is being submitted. I then named all the checkboxes 'links' and put in a hidden field called 'chkIds'.

 

When i submit the form i thought i would try and echo the 'chkIds' post data but nothing is returned?

[suzzane2020]

The checkboxes are generated in a loop rite?

 

as in a checkbox for each subject?

 

 

[suzzane2020]

ok wat i meant is

u wud have a table for subjects and a query to get them in the form

<form name="">

<table>

$res=mysql_query("select * from subject_tbl");

while($row=mysql_fetch_array($res))

{

?>

 

<tr>

<td><input type="checkbox" name="links" value="<?php echo $subject_name?>"></td><td><?php echo $subject_name;?></td>

</tr>

<?php }?>

</table>

<input type="hidden" name="chkIds">

</form>

 

 

[benji87]

No i dont want to get the database data into the form i want to get the form data in the database