CODEWITHSUNDEEP
phpmysqlhtmldatabase
Mike George
Mike George

Reputation: 521

How can I update mySQL key on duplicate column value

I am writing a php application that will post to a mySQL database. I have that part working but I have a column named repID that will contain the clients repair ID.

The way the content gets added to the database is through an admin from on my website. I would like to make it so if a technician enters an existing repID that it will just update the key for that. Instead of making a duplicate with a different repair status

PHP

$repID = mysql_real_escape_string($_POST['repID']);
$clientName = mysql_real_escape_string($_POST['clientName']);
$devModel = mysql_real_escape_string($_POST['devModel']);
$repStatus = mysql_real_escape_string($_POST['repStatus']);
$tracking = mysql_real_escape_string($_POST['tracking']);
$sql = mysql_query("INSERT INTO status (`repID`, `clientName`, `devModel`, `repStatus`,     `tracking`) VALUES ('$repID','$clientName','$devModel','$repStatus', '$tracking');");

INPUT PAGE

<?php
include_once('../resources/init.php');
$query = "SELECT * FROM status WHERE repStatus != 'Finished';";
$result = mysql_query($query);
$num = mysql_numrows($result);
mysql_close();
$random = rand(1000000000, 9999999999);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"      "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html>
    <head>
        <title>Add A Repair</title>
    </head>
    <body>
        <div id="wrapper">
            <div id="application">
                <div id="randomNum">
                    <?php echo $random; ?>
                </div>
                <form method="post" action="insert.php">
                    <div id="repID">
                        <label for="repID">Repair ID</label>
                        <input type="text" name="repID" />
                    </div>
                    <div id="clientName">
                        <label for="clientName">Client Name</label>
                        <input type="text" name="clientName" />
                    </div>
                    <div id="devModel">
                        <label for="devModel">Device Model</label>
                        <input type="text" name="devModel" />
                    </div>
                    <div id="repStatus">
                        <label for="repStatus">Repair Status</label>
                        <select name="repStatus">
                            <option value="Diagnosis Stage">Diagnosis Stage</option>
                            <option value="Problem Found">Problem Found</option>
                            <option value="Possible Solution">Possible Solution</option>
                            <option value="Parts Ordered">Parts Ordered</option>
                            <option value="Parts Recieved">Parts Recieved</option>
                            <option value="Parts/Software Installation Stage">Parts/Software       m  Installation Stage</option>
                            <option value="Testing Stage">Testing Stage</option>
                            <option value="Finished">Finished</option>
                        </select>
                    </div>
                    <div id="tracking">
                        <label for="tracking">Tracking Number</label>
                        <input type="text" name="tracking" />
                    </div>
                    <div id="submit">
                        <input type="submit" value="Submit" />    
                    </div>
                </form>
                <div id="currentClients">
                    Current Clients
                    <br /><br />
                    <table border="0" cellspacing="2" cellpadding="2">
                        <tr>    
                            <th>Repair ID</th>
                            <th>Client Name</th>
                            <th>Device Model</th>
                            <th>Repair Status</th>
                            <th>Tracking</th>
                        </tr>
                        <?php
                        $i = 0;
                        while ($i < $num) {
                            $v1 = mysql_result($result, $i, "repID");
                            $v2 = mysql_result($result, $i, "clientName");
                            $v3 = mysql_result($result, $i, "devModel");
                            $v4 = mysql_result($result, $i, "repStatus");
                            $v5 = mysql_result($result, $i, "tracking");
                            ?>
                            <tr>
                                <td><?php echo $v1; ?></td>
                                <td><?php echo $v2; ?></td>
                                <td><?php echo $v3; ?></td>
                                <td><?php echo $v4; ?></td>
                                <td><?php echo $v5; ?></td>
                            </tr>
                            <?php
                            $i++;
                        }
                        ?>
                    </table>
                </div>
            </div>
        </div>
    </body>
</html>

Upvotes: 3

Views: 2608

Answers (1)

JohnD
JohnD

Reputation: 4002

Use MySQL's INSERT ... ON DUPLICATE KEY UPDATE. Of course make sure repID is a primary or unique key.

Upvotes: 3

Related Questions