CODEWITHSUNDEEP
phphtmlmysqldatabase
Aditya K
Aditya K

Reputation: 163

Updating the mysql database based on user input

I want to insert the selected status timestamps in the corresponding column which user has chosen from the menu.
Brief explanation
Start from the database i have created columns like snap below.
enter image description here Initially user will insert the sonumber and status, Now i need database to update current time and date in the database, remaining column initialized to zero.
Next user will select update option, here user will enter so-number as well as new status(from dropdown). Now i need database to update the new status with current time and store in the particular status column.

Starting with insert page saved as "insert.html" and "insert.php" Respectively 

<form id="form1" name="form1" method="post" action="insert.php" >
<p>
<lable>ENTER SO NUMBER</lable>
<input type="text"  name="soid" id="soid" maxlength="6" required>


<p>
<lable>SELECT DEPARTMENT</lable>
<select type="text"  name="dept" id="dept">

<option value="NGS Analysis">NGS Analysis</option>
<option value="E-Array">E-Array</option>
<option value="Micro-Array">Micro-Array</option>
<option value="NGS Data">NGS Data</option>


</select>
</p>

<p>
<lable>SELECT STATUS</lable>
<select type="text"  name="status" id="status">

<option value="Sample Recived">Sample Recived</option>
<option value="Mol Bio Extraction">Mol-Bio Extraction</option>
<option value="Extraction QC">Extraction QC</option>
<option value="Library Prep">Library Prep</option>
<option value="Library QC">Library QC</option>
<option value="Sequencing">Sequencing</option>
<option value="Data check">Data Check</option>
<option value="Re-Sequencing">RE-Sequencing</option>
<option value="QC Check">QC Check</option>
<option value="Analysis Started">Analysis Started</option>
<option value="Analysis Completed">Analysis Completed</option>
<option value="Report">Report</option>
<option value="Outbound">Outbound</option>
</select>
</p>



<p><button><img src="http://brandonmadeawebsite.com/images/art/icons/insert_icon.png"  height="50" />INSERT</button></p>
</form>

insert.php 

<?php


 $so = $_POST['soid'];

 $dp = $_POST['dept'];

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "status";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_errno) {
    printf("Connect failed: %s\n", $conn->connect_error);
    exit();
}
$result = mysqli_query($conn, "INSERT INTO $dbname.statusinfo (soid, dept ) VALUES ( '$so','$dp')") or die(mysqli_error($conn));
echo "Inserted sucessfully with So Number <u><b>$so</b></u> Corresponding Status is <u><b>$st</b></u>";


$conn->close();

?>

Now below update script which is saved as "update.html","update.php" respectively 

<form action="update.php" method="post" name="form2">
<p>
<lable>ENTER SO NUMBER</lable>
<input type="text"  name="soid" id="soid" required>

<p>
<lable>SELECT STATUS</lable>
<select type="text"  name="status" id="status">

<option value="Sample Recived">Sample Recived</option>
<option value="Mol Bio Extraction">Mol-Bio Extraction</option>
<option value="Extraction QC">Extraction QC</option>
<option value="Library Prep">Library Prep</option>
<option value="Library QC">Library QC</option>
<option value="Sequencing">Sequencing</option>
<option value="Data check">Data Check</option>
<option value="Re-Sequencing">RE-Sequencing</option>
<option value="QC Check">QC Check</option>
<option value="Analysis Started">Analysis Started</option>
<option value="Analysis Completed">Analysis Completed</option>
<option value="Report">Report</option>
<option value="Outbound">Outbound</option>


</select>
</p>

<p><button><img src="http://icons.iconarchive.com/icons/icons8/windows-8/32/User-Interface-Available-Updates-icon.png"  height="50" /> UPDATE</button></p>
</form>

update.php 

<?php
 $so = $_POST['soid'];
 $st = $_POST['samplerecived'];
 $st1 = $_POST['molbioextraction'];
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "status";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_errno) {
    printf("Connect failed: %s\n", $conn->connect_error);
    exit();
}
   switch ($st):

case $st:$result = mysqli_query($conn, "UPDATE statusinfo SET `samplerecived`= CURTIME()  WHERE soid='$so' ") or die(mysqli_error($conn));
    break;
case $st1:$result1 = mysqli_query($conn, "UPDATE statusinfo SET  `molbioextraction`= CURTIME() WHERE soid='$so' ") or die(mysqli_error($conn));
    break;
echo "Updated sucessfully with So Number $so Current Status is set to $st ";

  $conn->close();

   ?>

Kindly help me to do so, if you guys need any more information feel free to ask.
Thanks in advance

Upvotes: 2

Views: 4756

Answers (1)

Acharya Anurag
Acharya Anurag

Reputation: 661

If I understand your problem correctly, you only need the user selected fields to be inserted and the other to be either NULL or some default value.

You have the default value for all the fields set as CURRENT_TIMESTAMP. This is why all the fields are being assigned a default value of the current timestamp. Remove the default value, allow them to be NULL (or set some default value other than CURRENT_TIMESTAMP) and your problem will be solved.

Upvotes: 1

Related Questions