PHP update several rows in MySQL with one submit

Question

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.

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Jan 27 '16 at 18:58
You need to write some programming to determine what the mcID would be then. Whats the issue with that? — Fallenreaper, Jan 27 '16 at 19:04
@JayBlanchard thanks for the info. I am still learning so could you elaborate on why my code is at risk? — Craig Howell, Jan 28 '16 at 00:12
@Fallenreaper the issue is that I am new and I am not sure how this is done. Do you have any pointers that would put me in the right direction? — Craig Howell, Jan 28 '16 at 00:14

score 0 · Accepted Answer · answered Jan 27 '16 at 19:06

0

MySQL has an 'IN' syntax so that if you are updating several rows with the same property, you can do so like this:

$usqlMC = "UPDATE tblmaincircles SET mcName= '".$_POST['mcName']."' WHERE mcID IN ('M1', 'M2');";

answered Jan 27 '16 at 19:06

Richthofen

2,076
19
39

This replaced every mcName with "" – Craig Howell Jan 28 '16 at 00:52
That means your POST variable was empty. Notice your form contains no inputs named 'mcName' so this is expected behavior. – Richthofen Jan 28 '16 at 15:41
I changed the id and name of the input to = "mcName" and it instead of blanking out all fields it changed all of the fields to what one field. I have added what I just tried based on your notes to my original post. Please help and thanks for your patience with this newbie. – Craig Howell Jan 28 '16 at 21:31

PHP update several rows in MySQL with one submit

1 Answers1