I have a MySQL database split into three tables (tblmaincircles, tblsmallcircles, tbltabs) and a PHP file that updates data within these tables.
tblmaincircles:
mcID | mcName
-------------
M1 | M1name
M2 | M2name
M3 | M3name
... | ...
tblsmallcircles:
scID | scVisibleID | scVisible | mcID |scName
------------------------------------------------
M1s1 | M1s1v | visible | M1 | M1s1name
M1s2 | M1s2v | visible | M1 | M1s2name
M1s3 | M1s3v | hidden | M1 |
M2s1 | M2s1v | visible | M2 | M2s1name
... | ... | ... | ... | ...
tbltabs:
tabID | scID | mcID | tabName | tabURLID | tabURL
-------------------------------------------------------------
M1s1t1 | M1s1 | M1 | M1s1t1name | M1s1t1url | M1s1t1urlname
M1s1t2 | M1s1 | M1 | M1s1t2name | M1s1t2url | M1s1t2urlname
M1s2t1 | M1s2 | M1 | M1s2t1name | M1s2t1url | M1s2t1urlname
... | ... | ... | ... | ... | ...
My php form shows all of the current values to the user which includes information from all three tables and multiple rows from tblsmallcircles and tbltabs. A shortened version of my form looks like this:
<body>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<ul>
<li>
<label class="mctext">Main Circle:</label>
<input type="text" class="textfield" id="MC" name="MC" value="<?php echo htmlspecialchars($M1); ?>"/>
</li>
<ul>
<li class="sctext">
<img src="images/triangle_right.png" alt="none" class="tri" id="sc1tri">
<label>Small circle 1:</label>
<input type="text" class="textfield" id="SC1" name="SC1" value="<?php echo htmlspecialchars($M1s1); ?>"/>
</li>
<ul>
<div class="doit">
<li>
<label class="tabtext">Tab 1:</label>
<input type="text" class="textfield" id="SC1t1" name="SC1t1" value="<?php echo htmlspecialchars($M1s1t1); ?>"/>
</li>
<li>
<label class="urltext">Tab 1 URL/File:</label>
<input type="text" class="textfield" id="SC1t1url" name="SC1t1url" value="<?php echo htmlspecialchars($M1s1t1url); ?>"/>
</li>
<li>
<label class="pdftext">Tab 1 PDF:</label>
<input type="button" class="button" id="SC1t1pdf" name="SC1t1pdf" value="View" onclick="openM1s1t1url()"/>
<span> </span>
<input type="file" name="SC1t1pdfup" id="SC1t1pdfup"/>
</li>
<li>
<label class="tabtext">Tab 2:</label>
<input type="text" class="textfield" id="SC1t2" name="SC1t2" value="<?php echo htmlspecialchars($M1s1t2); ?>"/>
</li>
<li>
<label class="urltext">Tab 2 URL/File:</label>
<input type="text" class="textfield" id="SC1t2url" name="SC1t2url" value="<?php echo htmlspecialchars($M1s1t2url); ?>"/>
</li>
<li>
<label class="pdftext">Tab 2 PDF:</label>
<input type="button" class="button" id="SC1t2pdf" name="SC1t2pdf" value="View" onclick="openM1s1t2url()"/>
<span> </span>
<input type="file" name="SC1t2pdfup" id="SC1t2pdfup"/>
</li>
<li class="lipsace"></li>
</div>
<li>
<input type="submit" class="button" name='submit' value="Submit"/>
<span> </span>
<input type="button" class="button" name='cancel' value="Cancel" onclick="history.go(0)"/>
</li>
</ul>
</ul>
</ul>
</form>
</body>
Here are the SQL update statements:
$usqlMC = "UPDATE tblmaincircles SET mcName= '".$_POST['mcName']."' WHERE mcID=???;";
$usqlSC = "UPDATE tblsmallcircles SET scVisible= '".$_POST['scVisible']."', scName= '".$_POST['scName']."' WHERE scID=???;";
$usqlt = "UPDATE tbltabs SET mcName= '".$_POST['mcName']."' WHERE tabID=???;";
I am stuck with what I should place in the ??? for mcID= because it varies based on the which fields have been complted.
Hopefully I provided enough information and this is not too much either! Any assistance with this would be great. I am new to PHP and MySQL to any comments are welcome. Thanks in advance!
UPDATE
Here is the updated lines of code I have made. This now changed all mcNames to the fields input value instead of just M1.
HTML
<input type="text" class="textfield" id="mcName" name="mcName" value="<?php echo htmlspecialchars($M1); ?>"/>
SQL
$usqlMC = "UPDATE tblmaincircles SET mcName= '".$_POST['mcName']."' WHERE mcID IN ('M1','M2','M3','M4','M5');";
My goal is to only have M1 be updated but I currently have other inputs for M2, M3, etc. on the same form so those may need to be updated at the same time. Is there a way to do this without writing an individual SQL for M1, M2, etc.
I can achieve this by writing 5 separate SQL statements like this:
$usqlM1 = "UPDATE tblmaincircles SET mcName= '".$_POST['mcNameM1']."' WHERE mcID='M1';";
$usqlM2 = "UPDATE tblmaincircles SET mcName= '".$_POST['mcNameM2']."' WHERE mcID='M2';";
$usqlM3 = "UPDATE tblmaincircles SET mcName= '".$_POST['mcNameM3']."' WHERE mcID='M3';";
$usqlM4 = "UPDATE tblmaincircles SET mcName= '".$_POST['mcNameM4']."' WHERE mcID='M4';";
$usqlM5 = "UPDATE tblmaincircles SET mcName= '".$_POST['mcNameM5']."' WHERE mcID='M5';";
but I would prefer one if that is possible.
