Php Transaction

[newbie14]

Dear All,
I am trying to apply transaction into my codes. Below is what I have done. It works fine. I would just like to know incase is there something wrong or missing in my way ? Thank you. What is different between mysql and mysqli ?

$rollbackStatus=False;
$link = mysql_connect(dbHost, dbUser, dbPassword);
if(!$link)
{
		die('Failed to connect to server: ' . mysql_error());
}
	
//Select database
$db = mysql_select_db(dbDatabase);
if(!$db) 
{
		die("Unable to select database");
}
mysqli_autocommit($link, FALSE);


$selectQuery1 ="Select clientName From tblClient Where clientName='".$clientName."'"; 
$result1 = mysql_query($selectQuery1);
if ($result1 !== TRUE) 
{
    mysqli_rollback($link);  
    $rollbackStatus=True;
}

$row1 = mysql_fetch_array($result1, MYSQL_ASSOC);
$n1 = mysql_num_rows($result1);

	if($n1>0)
	{
	  $status="<span class=\"statusFailed\">: Client ".$clientName." Alread Exist.</span>";
	}
	else
	{
	
	 $insertQuery1 =  "Insert Into tblClient Set ". 
							   	 "clientName='".$clientName."', ".
									 "clientContactPerson='".$clientContactPerson."',".
									 "clientAddress='".$clientAddress."',".
									 "clientContactNumber='".$clientContactNumber."',".
									 "clientStatus='a'";
           $result2 = mysql_query($insertQuery1,$link);					 
	   if ($result2 !== TRUE) 
	   {
	    $status="<span class=\"statusFailed\">: Error adding adding Client:" . mysql_error() . "</span>";
	    
	    mysqli_rollback($link);  
	    $rollbackStatus=True;
	   } 
	   else
	   {
	    $status="<span class=\"statusOk\">: Client ".$clientName." Added Succesfully.</span>";
			
	   }
		 
	}
if($rollbackStatus=="False")
{
	 mysqli_commit($link);

}
mysqli_close($link);
}

[diafol]

mysqli = mysql improved. mysqli is an object and is faster. Both use same SQL syntax. Heard mysql may be dropped/deprecated in php6 - don't know if this is true.

I don't know why you're mixing both here though. Just use mysqli.

[newbie14]

Dear Ardav,
I tried mysqli_select_db but it say unknown database. So what else I much changed? Should I used the normal mysql or mysqli? Which you advice? How about the transaction am I doing it correctly?

[diafol]

you need to use the connection as a parameter. see the manual OR use it in the connection string

$mysqli = new mysqli("localhost", "my_user", "my_password", "test");

(from the manual)

where test is the DB

[newbie14]

Dear Ardav,
Thank you. So I guess the rest like mysql_query mysql_fetch_array,mysql_error etc should all be changed right? So how about the transaction flow is it correctly done?

[urtrivedi]

I think parameter place in mysql and mysqli is interchanged. so you need to modify everywhere only adding 'i' is not sufficient.

[newbie14]

Dear Urtrivedi,
I am confuse you said is interchange then you said I need to modify every where? Everything to modify I dont think so my queries need modification too right?

[urtrivedi]

not need to change query, just way of passing parameter is to be changed. Check manual of function before changing any code

mysqli_select(link,query)
mysql_select(query,link)

[diafol]

..he he he urti, I understood you :)

Tends to make sense when you RTFM anyway.

[newbie14]

Dear All,
I have done like below. The problem now I get error 'Failed to connect to server: ' but it never print the error itself. The dbHost,dbUser,dbPassword,dbDatabase I have define in an external config file.

$link = mysqli_connect(dbHost, dbUser, dbPassword, dbDatabase);


if(!$link)
{
		die('Failed to connect to server: ' . mysqli_error());
}
	
mysqli_autocommit($link, FALSE);

[newbie14]

Dear All,
I have replace everthing will real values like this $link = mysqli_connect("localhost:3306", "*****", "*******", "****"); even then is nothing blank page and even I put :3306 it shows me the fail error.

[newbie14]

Dear All,
It works fine now. Can you check anything else I have not coverted to mysqli.

$link = mysqli_connect("localhost", dbUser, dbPassword, dbDatabase); 

if (!$link) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

mysqli_autocommit($link, FALSE);

$selectQuery1 ="Select clientName From tblClient Where clientName='".$clientName."'"; 
$result1 = mysqli_query($link,$selectQuery1);
if ($result1 !== TRUE) 
{
    mysqli_rollback($link);  
    $rollbackStatus=True;
}

$row1 = mysqli_fetch_array($result1, MYSQL_ASSOC);
$n1 = mysqli_num_rows($result1);

	if($n1>0)
	{
		$status="<span class=\"statusFailed\">: Client ".$clientName." Alread Exist.</span>";
	}
	else
	{
	
	   $insertQuery1 =  "Insert Into tblClient Set ". 
							   	 "clientName='".$clientName."', ".
									 "clientContactPerson='".$clientContactPerson."',".
									 "clientAddress='".$clientAddress."',".
									 "clientContactNumberss='".$clientContactNumber."',".
									 "clientStatus='a'";
		 $result2 = mysqli_query($link,$insertQuery1);					 
	   if ($result2 !== TRUE) 
	   {
	    $status="<span class=\"statusFailed\">: Error adding adding Client:" . mysqli_error($link) . "</span>";
	    
	    mysqli_rollback($link);  
	    $rollbackStatus=True;
	   } 
	   else
	   {
			$status="<span class=\"statusOk\">: Client ".$clientName." Added Succesfully.</span>";
			$clientID="";
		  		 }		 
	} 

if($rollbackStatus=="False")
{
	 mysqli_commit($link);

}
mysqli_close($link);

[diafol]

It works fine now. Can you check anything else I have not coverted to mysqli.

Good. Use your text editor or IDE to search for 'mysql_'. It'll save you and us having to search by hand (or eye!) :)

[newbie14]

Dear Ardav,
In the event I got an error with my query. Do you think I am putting all this in the right posistion or should I put it in else part? How about my overall transaction codes is it ok and is the correct flow?

$selectQuery1 ="Select clientName From tblClient Where clientName='".$clientName."'"; 
$result1 = mysqli_query($link,$selectQuery1);
if ($result1 !== TRUE) 
{
    mysqli_rollback($link);  
    $rollbackStatus=True;
}

$row1 = mysqli_fetch_array($result1, MYSQL_ASSOC);
$n1 = mysqli_num_rows($result1);

[newbie14]

Dear Ardav,
I got another question here why when I run this echo "Result1 :".$result1; my whole page goes blank I know according to the manual it says it return object so how to check it was success or not?

$selectQuery1 ="Select clientName From tblClient Where clientName='".$clientName."'"; 
$result1 = mysqli_query($link,$selectQuery1);
echo "Result1 :".$result1;
if ($result1 !== TRUE) 
{
    mysqli_rollback($link);  
    $rollbackStatus=True;
}

$row1 = mysqli_fetch_array($result1, MYSQL_ASSOC);
$n1 = mysqli_num_rows($result1);

[diafol]

$selectQuery1 ="Select clientName From tblClient Where clientName='".$clientName."'"; 
$result1 = mysqli_query($link,$selectQuery1);
if(mysqli_num_rows($result1)>0){
    mysqli_rollback($link);  
    $rollbackStatus=True;
    $row1 = mysqli_fetch_array($result1, MYSQL_ASSOC);
}else{
//echo out a message
}

I think.

You can't echo out an object. It contains data that you have to access in order to echo out. The usual method for this would be with a loop and mysqli_fetch_array. Although with mysqli you can just use mysqli_fetch_all(). Check the php.net manual.

[newbie14]

Dear Ardav,
I just want to check whether the select was sucessfully queries even without any records if fine.

[diafol]

should be fine. if($result1)

[newbie14]

Dear Ardav,
Thank you. So actually what will give output when I do this if($result1)? Just a true value is it?

[diafol]

think so.