How to perform different update queries based on user selection using php

[andrelemonius2]

I have a database with a table called memberloaninfo with 6 columns which are log/loanprocess/interviewtime/inspectiontime/approvaltime/disbursaltime.

I want to insert timestamps into timefields once user makes loan process selection from a drop down selection box. So if user for ex. selects inspection as loan process it would process the query that updates inspectiontime with timestamp and loanprogress column with the value inspection and visa versa for other selection values. Problem is that the code isn’t working at all. How can i fix this? Any and all help will be appreciated.

My html and php code’s are:

if(isset($_POST['update'])){
$log = $_POST['log'];
$loanprogress = $_POST['loanprogress'];
$interviewtime = date('y-m-d H:i:s');
$inspectiontime = date('y-m-d H:i:s');
$approvaltime = date('y-m-d H:i:s');
$disbursaltime = date('y-m-d H:i:s');
 
      
if ($loanprogress == '$inspection'){ 
//mysqli_query($connection," INSERT INTO memberloaninfo interviewtime VALUE $interviewtime WHERE interviewtime is NULL");
mysqli_query($connection," UPDATE memberloaninfo SET loanprogress = '$inspection', inspectiontime = '$inspectiontime' WHERE log = '$log'");	
}
else if($loanprogress == '$approval'){
          
//mysqli_query($connection," INSERT INTO memberloaninfo interviewtime VALUE $interviewtime WHERE interviewtime is NULL");
mysqli_query($connection," UPDATE memberloaninfo SET loanprogress = '$approval', approvaltime = '$approvaltime' WHERE log = '$log'");

}
else if ($loanprogress == '$disbursal'){
       
//mysqli_query($connection," INSERT INTO memberloaninfo interviewtime VALUE $interviewtime WHERE interviewtime is NULL");
mysqli_query($connection," UPDATE memberloaninfo SET loanprogress = '$disbursal', disbursaltime = '$disbursaltime' WHERE log = '$log'");

}
}

            ?>
 <form method = "post" action = "">
                  <table width = "400" border =" 0" cellspacing = "1" 
                     cellpadding = "2">
                  
                     <tr>
                        <td width = "100">Enter Log Number</td>
                        <td><input name = "log" type = "text" 
                           id = "log"></td>
                     </tr>               
                                     
		     <tr>
                        <td width = "100">Select Loan Progress</td>
                        <td><p>
				  <select >
				  <option value="">Select...</option>
				  <option  value="inspection">inspection</option>
				  <option  value="approval">approval</option>
 				  <option  value="disbursal">disbursal</option>
			</select>
			</p></td>
			                   	                    
                     <tr>
                        <td width = "100"> </td>
                        <td>
                           <input  type = "submit" value = "Submit">
                        </td>
                     </tr>
                  
                  </table>
               </form>

[gmrstudios]

What error message are you getting? More than likely you will get a syntax error that will lead you in the right direction.

INSERT INTO memberloaninfo interviewtime VALUE $interviewtime WHERE interviewtime is NULL

Should be:

INSERT INTO your_table_name (interviewtime) VALUES ($interviewtime) WHERE interviewtime IS NULL

Also, the correct date() format should be date("Y-m-d H:i:s") right now you are trying to insert/update a 2-digit representation of the year. It looks like you are just trying to insert the current time stamp which can be done by modifying your SQL statement and using NOW() or by modifying your column with CURRENT_TIMESTAMP

INSERT INTO your_table_name (interviewtime) VALUES (NOW()) WHERE interviewtime IS NULL

[coderboy]

Try using datetime format:

date('Y-m-d H:i:s') //Y not y that takes 4 digit year

Also, you can use the MySQL’s NOW() function in the query as such:

mysqli_query($connection," UPDATE memberloaninfo SET loanprogress = '$inspection', inspectiontime = NOW() WHERE log = '$log'");	

And, it’s always safe to use prepared statements.

// prepare the query where ? represents the parameters
if($stmt = mysqli_prepare($connection," UPDATE memberloaninfo SET loanprogress=?, inspectiontime=? WHERE log=?")) {

    // bind the parameters
    mysqli_stmt_bind_param($stmt, "sss", $inspection, $inspectiontime, $log);

    // execute the statement
    mysqli_stmt_execute($stmt);

    printf("Error: %s.\n", mysqli_stmt_error($stmt));

    // close the statement
    mysqli_stmt_close($stmt);
} else {
    //Error
    printf("Prep error: %s.\n", mysqli_error());
}

[andrelemonius2]

I have tried with the prepared statement that you suggested but code is still not executing. Not sure what is happening since im not getting an error report.

[chorn]

let display error messages with mysqli_error()

[coderboy]

Updated the answer with the error functions. What errors do you get?

[colshrapnel]

if ($loanprogress == '$inspection'){
                      ^ here 
                      this $ mark makes absolutely no sense.

[Dormilich]

or rather, the quote marks don’t make any sense.

[andrelemonius2]

You are right taken that out + adjusting the query a bit made the difference. But only for the update part of the code but i cant get the insert query part to work along side the update query.

[colshrapnel]

it is commented out

[andrelemonius2]

@andrelemonius2

/mysqli_query($connection," INSERT INTO memberloaninfo interviewtime VALUE $interviewtime WHERE interviewtime is NULL");

I mean after i take out the // and running the code. When i run the code the update query works and the insert query doesnt. here is code that i am using

if ($loanprogress == ‘inspection’){
mysqli_query($connection," INSERT INTO memberloaninfo (interviewtime) VALUE (‘$interviewtime’) WHERE interviewtime is NULL and log = ‘log’“);
mysqli_query($connection,” UPDATE memberloaninfo SET loanprogress = ‘$loanprogress’, inspectiontime = ‘$inspectiontime’ WHERE log = ‘$log’");

[Dormilich]

if you’d used db error handling, mysqli would have told you why.

[colshrapnel]

You see, we have no idea what you mean. All we can see is the code you wrote.
If you happen to ask a question and expect an answer, post a code you run, not mean.

From what can see, the error is as silly as the previous one - again some stranded $ is lurking in your queries.
Besides, I have no idea why do you need an insert query here at all and what it’s intended to do.

[droopsnoot]

@colshrapnel

From what can see, the error is as silly as the previous one - again some stranded $ is lurking in your queries.

Is it correct to use VALUE as a keyword when there’s only a single value being inserted, or should it always be VALUES? I would have presumed the latter.

[Dormilich]

@droopsnoot

Is it correct to use VALUE as a keyword when there’s only a single value being inserted, or should it always be VALUES?

This question is best answered by the MySQL docs: https://dev.mysql.com/doc/refman/5.7/en/insert.html

[droopsnoot]

Ah, thanks. I did a search for reserved words which didn’t seem to have VALUE in it, but didn’t go any further.