Databases
StackOverflow2013
Postsdbo
POHow to run UPDATE and SELECT statement after confirmation?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to run UPDATE and SELECT statement after confirmation?
    primarykey
    data
    text
    <p>I have an application where the user displays their assessment name, date and time in the relevant text inputs. Now what happens is that when the user submits the form, it displays a confirmation.</p> <p>Below is the code (editsessionadmin.php):</p> <pre><code> &lt;script&gt; function showConfirm(){ var examInput = document.getElementById('newAssessment').value; var dateInput = document.getElementById('newDate').value; var timeInput = document.getElementById('newTime').value; var confirmMsg=confirm("Are you sure you want to update the following:" + "\n" + "Exam: " + examInput + "\n" + "Date: " + dateInput + "\n" + "Time: " + timeInput); } &lt;/script&gt; &lt;?php $sessionquery = " SELECT SessionId, SessionName, SessionDate, SessionTime, ModuleId FROM Session WHERE (ModuleId = ?) ORDER BY SessionName "; $sessionqrystmt=$mysqli-&gt;prepare($sessionquery); // You only need to call bind_param once $sessionqrystmt-&gt;bind_param("s",$moduleId); // get result and assign variables (prefix with db) $sessionqrystmt-&gt;execute(); $sessionqrystmt-&gt;bind_result($dbSessionId,$dbSessionName,$dbSessionDate,$dbSessionTime, $dbModuleId); $sessionqrystmt-&gt;store_result(); $sessionnum = $sessionqrystmt-&gt;num_rows(); if($sessionnum ==0){ echo "&lt;p&gt;Sorry, You have No Assessments under this Module&lt;/p&gt;"; } else { $sessionHTML = '&lt;select name="session" id="sessionsDrop"&gt;'.PHP_EOL; $sessionHTML .= '&lt;option value=""&gt;Please Select&lt;/option&gt;'.PHP_EOL; while ( $sessionqrystmt-&gt;fetch() ) { if(time() &gt; strtotime($dbSessionDate." ".$dbSessionTime)){ $class = 'red'; } else { $class = 'green'; } $sessionHTML .= sprintf("&lt;option value='%s' style='color: %s'&gt;%s - %s - %s&lt;/option&gt;", $dbSessionId, $class, $dbSessionName, date("d-m-Y",strtotime($dbSessionDate)), date("H:i",strtotime($dbSessionTime))) . PHP_EOL; } $sessionHTML .= '&lt;/select&gt;'; $assessmentform = "&lt;form action='".htmlentities($_SERVER['PHP_SELF'])."' method='post'&gt; &lt;p&gt;&lt;strong&gt;Assessments:&lt;/strong&gt; {$sessionHTML} &lt;/p&gt; &lt;/form&gt;"; echo $assessmentform; } $editsession = "&lt;form action=".htmlentities($_SERVER['PHP_SELF'])." method='post' id='updateForm'&gt; &lt;p&gt;&lt;strong&gt;New Assessment's Date/Start Time:&lt;/strong&gt;&lt;/p&gt; &lt;table&gt; &lt;tr&gt; &lt;th&gt;Assessment:&lt;/th&gt; &lt;td&gt;&lt;input type='text' id='newAssessment' name='Assessmentnew' readonly='readonly' value='' /&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;th&gt;Date:&lt;/th&gt; &lt;td&gt;&lt;input type='text' id='newDate' name='Datenew' readonly='readonly' value='' /&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;th&gt;Start Time:&lt;/th&gt; &lt;td&gt;&lt;input type='text' id='newTime' name='Timenew' readonly='readonly' value=''/&gt;&lt;span class='timepicker_button_trigger'&gt;&lt;img src='Images/clock.gif' alt='Choose Time' /&gt;&lt;/span&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;div id='datetimeAlert'&gt;&lt;/div&gt; &lt;p&gt;&lt;input id='updateSubmit' type='submit' value='Update Date/Start Time' name='updateSubmit' onClick='myClickHandler(); return false;'/&gt;&lt;/p&gt; &lt;/form&gt; "; echo $editsession; } ?&gt; &lt;script type="text/javascript"&gt; function myClickHandler(){ if(editvalidation()){ showConfirm(); } } &lt;/script&gt; </code></pre> <p>Now I want to run an UPDATE command to update the exam's date and time in the database after the user has confirmed the confirmation and compile a SELECT query so that if the update happened, then echo it was a success, else echo there was an error. </p> <p>My question is first of all is the code below correct and second of all where do I place this code so that it runs the commands after the confirmation has been confirmed?</p> <pre><code> &lt;?php $sessionname = (isset($_POST['Assessmentnew'])) ? $_POST['Assessmentnew'] : ''; $sessiondate = (isset($_POST['Datenew'])) ? $_POST['Datenew'] : ''; $sessiontime = (isset($_POST['Timenew'])) ? $_POST['Timenew'] : ''; $updatesql = "UPDATE Session SET SessionDate = ?, SessionTime = ? WHERE SessionName = ?"; $update = $mysqli-&gt;prepare($updatesql); $update-&gt;bind_param("sss", $sessiondate, $sessiontime, $sessionname); $update-&gt;execute(); $query = "SELECT SessionName, SessionDate, SessionTime FROM Session WHERE SessionName = ?"; // prepare query $stmt=$mysqli-&gt;prepare($query); // You only need to call bind_param once $stmt-&gt;bind_param("sss", $sessionname, $sessiondate, $sessiontime); // execute query $stmt-&gt;execute(); // get result and assign variables (prefix with db) $stmt-&gt;bind_result($dbSessionName, $dbSessionDate, $dbSessionTime); //get number of rows $stmt-&gt;store_result(); $numrows = $stmt-&gt;num_rows(); if ($numrows == 1){ echo "&lt;span style='color: green'&gt;Your Assessment's new Date and Time have been updated&lt;/span&gt;"; }else{ echo "&lt;span style='color: red'&gt;An error has occurred, your Assessment's new Date and Time have not been updated&lt;/span&gt;"; ?&gt; </code></pre> <p><strong>UPDATE:</strong></p> <p>Below is code for editsessionadmin.php</p> <pre><code>&lt;script&gt; function showConfirm(){ var examInput = document.getElementById('newAssessment').value; var dateInput = document.getElementById('newDate').value; var timeInput = document.getElementById('newTime').value; var confirmMsg=confirm("Are you sure you want to update the following:" + "\n" + "Exam: " + examInput + "\n" + "Date: " + dateInput + "\n" + "Time: " + timeInput); if (confirmMsg==true) { submitform(); } } function submitform() { $.post("updatedatetime.php", $("#updateForm").serialize() ,function(data){ var updateFormO = document.getElementById("updateForm"); updateFormO.submit(); }); } &lt;/script&gt; &lt;?php $sessionquery = " SELECT SessionId, SessionName, SessionDate, SessionTime, ModuleId FROM Session WHERE (ModuleId = ?) ORDER BY SessionName "; $sessionqrystmt=$mysqli-&gt;prepare($sessionquery); // You only need to call bind_param once $sessionqrystmt-&gt;bind_param("s",$moduleId); // get result and assign variables (prefix with db) $sessionqrystmt-&gt;execute(); $sessionqrystmt-&gt;bind_result($dbSessionId,$dbSessionName,$dbSessionDate,$dbSessionTime, $dbModuleId); $sessionqrystmt-&gt;store_result(); $sessionnum = $sessionqrystmt-&gt;num_rows(); if($sessionnum ==0){ echo "&lt;p&gt;Sorry, You have No Assessments under this Module&lt;/p&gt;"; } else { $sessionHTML = '&lt;select name="session" id="sessionsDrop"&gt;'.PHP_EOL; $sessionHTML .= '&lt;option value=""&gt;Please Select&lt;/option&gt;'.PHP_EOL; while ( $sessionqrystmt-&gt;fetch() ) { if(time() &gt; strtotime($dbSessionDate." ".$dbSessionTime)){ $class = 'red'; } else { $class = 'green'; } $sessionHTML .= sprintf("&lt;option value='%s' style='color: %s'&gt;%s - %s - %s&lt;/option&gt;", $dbSessionId, $class, $dbSessionName, date("d-m-Y",strtotime($dbSessionDate)), date("H:i",strtotime($dbSessionTime))) . PHP_EOL; } $sessionHTML .= '&lt;/select&gt;'; $assessmentform = "&lt;form action='".htmlentities($_SERVER['PHP_SELF'])."' method='post'&gt; &lt;p&gt;&lt;strong&gt;Assessments:&lt;/strong&gt; {$sessionHTML} &lt;/p&gt; &lt;/form&gt;"; echo $assessmentform; } $editsession = "&lt;form action=".htmlentities($_SERVER['PHP_SELF'])." method='post' id='updateForm'&gt; &lt;p&gt;&lt;strong&gt;New Assessment's Date/Start Time:&lt;/strong&gt;&lt;/p&gt; &lt;table&gt; &lt;tr&gt; &lt;th&gt;Assessment:&lt;/th&gt; &lt;td&gt;&lt;input type='text' id='newAssessment' name='Assessmentnew' readonly='readonly' value='' /&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;th&gt;Date:&lt;/th&gt; &lt;td&gt;&lt;input type='text' id='newDate' name='Datenew' readonly='readonly' value='' /&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;th&gt;Start Time:&lt;/th&gt; &lt;td&gt;&lt;input type='text' id='newTime' name='Timenew' readonly='readonly' value=''/&gt;&lt;span class='timepicker_button_trigger'&gt;&lt;img src='Images/clock.gif' alt='Choose Time' /&gt;&lt;/span&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;div id='datetimeAlert'&gt;&lt;/div&gt; &lt;p&gt;&lt;input id='updateSubmit' type='submit' value='Update Date/Start Time' name='updateSubmit' onClick='myClickHandler(); return false;'/&gt;&lt;/p&gt; &lt;/form&gt; "; echo $editsession; } ?&gt; &lt;script type="text/javascript"&gt; function myClickHandler(){ if(editvalidation()){ showConfirm(); } } &lt;/script&gt; </code></pre> <p>In the above code I added a jquery/ajax function <code>submitform()</code> where after the confirmation it will perform a post in the background to the <code>updatedatetime.php</code> page. There is where it displays the update and select statments. Problem is that it is not doing the update or displaying the echo in the editsessionadmin.php page</p> <p>Below is code for updatedatetime.php:</p> <pre><code> &lt;?php // connect to the database include('connect.php'); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); die(); } $sessionname = (isset($_POST['Assessmentnew'])) ? $_POST['Assessmentnew'] : ''; $sessiondate = (isset($_POST['Datenew'])) ? $_POST['Datenew'] : ''; $sessiontime = (isset($_POST['Timenew'])) ? $_POST['Timenew'] : ''; $updatesql = "UPDATE Session SET SessionDate = ?, SessionTime = ? WHERE SessionName = ?"; $update = $mysqli-&gt;prepare($updatesql); $update-&gt;bind_param("sss", $sessiondate, $sessiontime, $sessionname); $update-&gt;execute(); $query = "SELECT SessionName, SessionDate, SessionTime FROM Session WHERE SessionName = ?"; // prepare query $stmt=$mysqli-&gt;prepare($query); // You only need to call bind_param once $stmt-&gt;bind_param("sss", $sessionname, $sessiondate, $sessiontime); // execute query $stmt-&gt;execute(); // get result and assign variables (prefix with db) $stmt-&gt;bind_result($dbSessionName, $dbSessionDate, $dbSessionTime); //get number of rows $stmt-&gt;store_result(); $numrows = $stmt-&gt;num_rows(); if ($numrows == 1){ echo "&lt;span style='color: green'&gt;Your Assessment's new Date and Time have been updated&lt;/span&gt;"; }else{ echo "&lt;span style='color: red'&gt;An error has occured, your Assessment's new Date and Time have not been updated&lt;/span&gt;"; ?&gt; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USuser1819709
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to run UPDATE and SELECT statement after confirmation?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to run UPDATE and SELECT statement after confirmation?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to run UPDATE and SELECT statement after confirmation?
      1. This table or related slice is empty.
      1. VTDownMod
    1. COFrom my opinion, first you should break your code into different functions. Otherwise it will grow and become almost unreadable.Try to write a update and a select functions. Also you may know, buy the result of your Update query if there were changes made to rows in the table Session.
      singulars
      1. POHow to run UPDATE and SELECT statement after confirmation?
      1. USMário Rodrigues
    2. COcan wrap a `try/catch ` around the update and send error message to browser in the `catch` if update fails
      singulars
      1. POHow to run UPDATE and SELECT statement after confirmation?
      1. UScharlietfl
    3. COJS+HTML+PHP+SQL code in one file = [what the hell code](http://wthcode.blogspot.com/)
      singulars
      1. POHow to run UPDATE and SELECT statement after confirmation?
      1. USStasGrin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload