Update if Exists If Not Show Error

[brandon66]

Hey everyone having some troubles here with an update statement. I want to update a value(scanISN) if it exists and show information updated and if it doesn't exist show an error right now it show information updated even if it doesn't exist. How would I do that?

<?php

        if(!isset($_POST['addMe'])){
        //show form
        include_once('View/Scan.html.php');
        }else{
        //process the form
        //connect to the database
        include('Model/DBAdapter.php');


        //values to store in RMA Database
        $receiveDate = date('Y-m-d');   
        $rmaStatus = "Received";
        $scanISN = htmlspecialchars($_POST['scanISN'], ENT_QUOTES, 'UTF-8');

        //create sql query
        $sql = ("UPDATE RMA SET Received_Date = '$receiveDate', RMA_Status = '$rmaStatus' 
                WHERE Unit_Serial_Number = '$scanISN'");

        $result = $connection->query($sql);
        if(!$result){
        //ISN Does not exist
        $output = "ISN does not exist please contact support";
        }else{
        $output = "RMA information updated.";
        }

        include('View/Scan.html.php');
}

[brandon66]

<?php
		
		if(!isset($_POST['addMe'])){
		//show form
		include_once('View/Scan.html.php');
		}else{
		//process the form
		//connect to the database
		include('Model/DBAdapter.php');
		
		
		//values to store in RMA Database
		$receiveDate = date('Y-m-d');	
		$rmaStatus = "Received";
		$scanISN = htmlspecialchars($_POST['scanISN'], ENT_QUOTES, 'UTF-8');
		$rmaNumber = htmlspecialchars($_POST['rmaNumber'], ENT_QUOTES, 'UTF-8');

		
		//create sql query
		$sql = ("UPDATE RMA SET Received_Date = '$receiveDate', RMA_Status = '$rmaStatus' 
				WHERE Unit_Serial_Number = '$scanISN' AND RMA_Number = '$rmaNumber'");
		//run sql query
		$result = $connection->query($sql);
		
		//create sql query
		$sql2 = ("SELECT Received_Date, RMA_Status FROM RMA WHERE Unit_Serial_Number = '$scanISN' AND RMA_Number = '$rmaNumber'"); 
		
		//run sql query
		$result2 = $connection->query($sql2);
		
		//loop through data
		while ($row = $result2->fetch()){
		
		$check1 = $row['Received_Date'];
		$check2 = $row['RMA_Status'];
		
				
		if($check1 == $receiveDate || $check2 == $rmaStatus) {
    	 //success
    	$output = "RMA information updated.";
		} else {
   		  //error
   		 $output = "RMA does not exist please contact support";

		include('View/ScanOutput.html.php');
		}
}}

Looks like my copy didn't get all the code the first time

[mikosiko]

from the manual:

"

 

 Return Values

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query."

 

 

 

http://php.net/manual/en/function.mysql-query.php

 

a syntactically correct UPDATE that doesn't affect any record doesn't return an error (FALSE), it only doesn't return a resultset.

 

[EDIT] You posted a new code while I was replying to your first code... but same answer apply... not idea why now you included a SELECT only to check if the record was updated or not... 

[brandon66]

So Ill get rid of the second sql statement and do an if statement.

Would that be the way to do it?

if( mysql_affected_rows() = 0){
//show error
}else{
//show rma information updated

}

[mikosiko]

Yes... but remember that "=" is the assignment operator

http://www.php.net/manual/en/language.operators.assignment.php

 

no the comparison operator (== or ===)  

http://www.php.net/manual/en/language.operators.comparison.php

 

BTW: mysql_ API is already deprecated, you should be using the mysqli_ or PDO Apis instead.

[brandon66]

This is what I have it seems to work but when I try to update the same twice it will say it doesn't exist. Am I doing this right?

<?php
		
		if(!isset($_POST['addMe'])){
		//show form
		include_once('View/Scan.html.php');
		}else{
		//process the form
		//connect to the database
		include('Model/DBAdapter.php');
		
		
		//values to store in RMA Database
		$receiveDate = date('Y-m-d');	
		$rmaStatus = "Received";
		$scanISN = strtoupper(htmlspecialchars($_POST['scanISN'], ENT_QUOTES, 'UTF-8'));
		$rmaNumber = htmlspecialchars($_POST['rmaNumber'], ENT_QUOTES, 'UTF-8');

		
		//create sql query
		$sql = $connection->prepare("UPDATE RMA SET Received_Date = '$receiveDate', RMA_Status = '$rmaStatus' 
				WHERE Unit_Serial_Number = '$scanISN' AND RMA_Number = '$rmaNumber'");
		//run sql query
		$sql->execute();
		$affected = $sql->rowCount();
				
		if($affected <= 0){
    	     	 $output = "RMA does not exist please contact support";
    	
		} else {
   		 $output = "RMA information updated.";
		
		}include('View/ScanOutput.html.php');
}

[Barand]

If the the record already contains the new values then no update occurs (as it is not required). In this case, affected rows will be zero.

[brandon66]

How would one know if the unit that is being scanned in has been updated already or it doesn't exist at all in the db? Would I maybe use a switch or something instead of the if?

[kicken]

Do a SELECT before hand to test for the existence of the record.

[brandon66]

I think this works for that but the only other question is when one record is updated already it will say it doesn't exist. im doing something wrong here

<?php
		
		if(!isset($_POST['addMe'])){
		//show form
		include_once('View/Scan.html.php');
		}else{
		//process the form
		//connect to the database
		include('Model/DBAdapter.php');
		
		
		//values to store in RMA Database
		$receiveDate = date('Y-m-d');	
		$rmaStatus = "Received";
		$scanISN = strtoupper(htmlspecialchars($_POST['scanISN'], ENT_QUOTES, 'UTF-8'));
		$rmaNumber = htmlspecialchars($_POST['rmaNumber'], ENT_QUOTES, 'UTF-8');
		
		//create sql query to check if unit exists
		$sql1 = $connection->prepare("SELECT Received_Date,RMA_Status FROM rma WHERE Unit_Serial_Number = '$scanISN' AND RMA_Number = '$rmaNumber'");
		//run sql query
		$sql1->execute();
		$affected1 = $sql1->rowCount();
		
		//create sql query
		$sql = $connection->prepare("UPDATE RMA SET Received_Date = '$receiveDate', RMA_Status = '$rmaStatus' 
				WHERE Unit_Serial_Number = '$scanISN' AND RMA_Number = '$rmaNumber'");
		//run sql query
		$sql->execute();
		$affected = $sql->rowCount();		
		
		if($affected <= 0 || $affected1 <= 0){
    	 //error
    	 $output = "RMA does not exist please contact support";
    	
		} else {
   		  //success
   		 $output = "RMA information updated.";
		
		}include('View/ScanOutput.html.php');
}

[kicken]

There's no need to do the update if the row does not exist.

//create sql query to check if unit exists
$sql1 = $connection->prepare("SELECT Received_Date,RMA_Status FROM rma WHERE Unit_Serial_Number = '$scanISN' AND RMA_Number = '$rmaNumber'");
//run sql query
$sql1->execute();
if ($sql1->rowCount() > 0){
	//create sql query
	$sql = $connection->prepare("UPDATE RMA SET Received_Date = '$receiveDate', RMA_Status = '$rmaStatus' 
			WHERE Unit_Serial_Number = '$scanISN' AND RMA_Number = '$rmaNumber'");
	//run sql query
	if (!$sql->execute()){
		$output = "RMA updated failed.";
	}
	else {
		$output = "RMA information updated.";
	}
}
else {
	$output = "RMA does not exist please contact support";
}

[brandon66]

That works! except if the row has been updated before it will  update it with a new date which is bad! is there a way to check the value of the status/received date in a select statement?

[brandon66]

I achieved what I wanted by changing

<?php
		
		if(!isset($_POST['addMe'])){
		//show form
		include_once('View/Scan.html.php');
		}else{
		//process the form
		//connect to the database
		include('Model/DBAdapter.php');
		
		
		//values to store in RMA Database
		$receiveDate = date('Y-m-d');	
		$rmaStatus = "Received";
		$scanISN = strtoupper(htmlspecialchars($_POST['scanISN'], ENT_QUOTES, 'UTF-8'));
		$rmaNumber = htmlspecialchars($_POST['rmaNumber'], ENT_QUOTES, 'UTF-8');
		
		//create sql query to check if unit exists
		$sql1 = $connection->prepare("SELECT Received_Date,RMA_Status FROM rma WHERE Unit_Serial_Number = '$scanISN' AND RMA_Number = '$rmaNumber'");
		//run sql query
		$sql1->execute();
		
		//if the query executes do the update statement
		if ($sql1->rowCount() > 0){
		//create sql query
		$sql = $connection->prepare("UPDATE RMA SET Received_Date = '$receiveDate', RMA_Status = '$rmaStatus' 
				WHERE Unit_Serial_Number = '$scanISN' AND RMA_Number = '$rmaNumber' AND RMA_Status != '$rmaStatus'");
		
		//if the update statement does not execute
		if ($sql->rowCount() <= 0){
			$output = "RMA updated failed.";
		}
		else {
			$output = "RMA information updated.";
		}
}
else {
	$output = "RMA does not exist please contact support";
	}
			
		include('View/ScanOutput.html.php');
		
}

 the last if and adding another AND to the last sql statement! Thanks everyone for your help I really appreciate it! I look forward to learning more from you