[SOLVED] PDO Conversion Need Help

[devxtec]

Alright I'm a bit stuck at the moment. I'm in the process of a rewrite of some code that was written for PHP4. I'm rewriting it to PHP5 standards with a class and using PDO for database connections. All the queries will need to be performed in the order which they are just need to change the code doing the queries to PDO. From what I heard PDO can't support nested queries?

 

So my question is what would be the best way to convert the following code to use PDO and increase the efficiency at the same time and keeping it database independent? An example would help a lot as I'm faily new to classes and PDO. 

 

The code can be found here on this pastbin site. http://pasteninja.org/paste/299

(Note don't worry about the code starting with an else if this is just the lines of code out of a large file that I'm currently stuck on converting over.)

 

Thanks for any help in advance

[premiso]

Below replaces lines 14-17.

        foreach ($pdo->query($qry) )

 

You need to initiate the pdo connection at the top of the file or in the db include file like:

 

$pdo = new PDO(DSN_STRING_NEEDED_HERE, "dbusername", "dbpassword");

The above would replace the mysql_connect and mysql_select_db

 

http://us2.php.net/manual/en/pdo.construct.php see that for more information on the DSN and pdo for more information on each function etc.

[waynewex]

Do some reading and practise using the PDO first as your SELECTS etc will also be affected.

[devxtec]

Below replaces lines 14-17.

        foreach ($pdo->query($qry) )

 

 

How will this code work in a nested situation? In the code I pasted it runs through multiple sql queries. Does this replace all occurrences? Sorry if this seems like a silly question but I'm trying to teach myself as I go along.

 

As far as initiating the PDO connection, that is all done in my __construct() of my class I'm building.

 

Do some reading and practise using the PDO first as your SELECTS etc will also be affected.

 

I have done reading on  PDO but in none of my reading does it give an example of how to handle this type of situation where PDO is handling nested queries. Maybe you could provide me some material that will show me?

 

I guess worst case scenario if I can't accomplish this all in one method of a class I'll just split it up into multiple methods.

[premiso]

Ummm...

 

while( $row = mysql_fetch_assoc( $res ) )
18.	
        {
19.	
            $qry = "select LinkIndex from Links where CustomerNum = ".$custId." and LinkOrder = ".$newOrder;
20.	
            $res2 = mysql_query( $qry, $conn );
21.	
            if( $res2 )
22.	
            {
23.	
                while( $row2 = mysql_fetch_assoc( $res2 ) )
24.	
                {

 

Would be:

 

        foreach ($pdo->query($qry as $row) ){
               $qry2 = "select LinkIndex from Links where CustomerNum = ".$custId." and LinkOrder = ".$newOrder;
               foreach ($pdo->query($qry2) as $row2) {

               }
        }

 

etc etc

[devxtec]

Does this look right?

 

   $sql = "select LinkIndex from Links where CustomerNum = ".$custId." and LinkOrder = ".$linkorder;
foreach ($dbObj->query($sql) as $row) 
{
	$sql2 = "select LinkIndex from Links where CustomerNum = ".$custId." and LinkOrder = ".$newOrder;
        foreach ($dbObj->query($sql2) as $row2) 
        {
            $sql3 = "update Links set LinkOrder = " . $linkorder . " where CustomerNum = ".$custId." and LinkIndex = ".$row2['LinkIndex'];
            $result = $dbObj->query($sql3);
            if( $result )
            {
                $sql4 = "update Links set LinkOrder = " . $newOrder . " where CustomerNum = ".$custId." and LinkIndex = ".$row['LinkIndex'];
                $dbObj->query($sql4);
            }
        }
     }

 

With this way is it possible to include some error checking? In my other methods I use a try catch format when running queries. I'd think this could be implemented some how.

[premiso]

   $sql = "select LinkIndex from Links where CustomerNum = ".$custId." and LinkOrder = ".$linkorder;
   $dbArray1 = $dbObj->query($sql);
   if (!is_array($dbArray1) && count($dbArray1) > 0) { 
   foreach ($dbArray1 as $row)
   {
       
       // modify the rest as follows
     }
}

 

That would give you your error checking back. Make sure it returned an array and there is more than 1 element in an array, if not either there is no data or there was an error.

[devxtec]

   $sql = "select LinkIndex from Links where CustomerNum = ".$custId." and LinkOrder = ".$linkorder;
   $dbArray1 = $dbObj->query($sql);
   if (!is_array($dbArray1) && count($dbArray1) > 0) { 
   foreach ($dbArray1 as $row)
   {
       
       // modify the rest as follows
     }
}

 

That would give you your error checking back. Make sure it returned an array and there is more than 1 element in an array, if not either there is no data or there was an error.

 

 

I see where you are coming from on this but I am wondering if this code would work as well.

 

    $sql = "select LinkIndex from Links where CustomerNum = ".$custId." and LinkOrder = ".$linkorder;
    try {
    foreach ($dbObj->query($sql) as $row) 
    {
        try {
	        $sql2 = "select LinkIndex from Links where CustomerNum = ".$custId." and LinkOrder = ".$newOrder;
	        foreach ($dbObj->query($sql2) as $row2) 
	        {
	            try {
		            $sql3 = "update Links set LinkOrder = " . $linkorder . " where CustomerNum = ".$custId." and LinkIndex = ".$row2['LinkIndex'];
		            $result = $dbObj->query($sql3);
		            if( $result )
		            {
		                try {
		                    $sql4 = "update Links set LinkOrder = " . $newOrder . " where CustomerNum = ".$custId." and LinkIndex = ".$row['LinkIndex'];
		                    $dbObj->query($sql4);
		                } catch (PDOException $error){
		                    echo 'PDO Exception Caught. ';
		                    echo 'Error with the database: <br />';
		                    echo 'SQL Query: ', $sql4;
		                    echo 'Error: ' . $error->getMessage();
		                    $errorMsg = $error->getMessage();
		                }
		            }
	            } catch (PDOException $error){
                        echo 'PDO Exception Caught. ';
                        echo 'Error with the database: <br />';
                        echo 'SQL Query: ', $sql3;
                        echo 'Error: ' . $error->getMessage();
                        $errorMsg = $error->getMessage();
	            }
	        }
        } catch (PDOException $error){
                echo 'PDO Exception Caught. ';
                echo 'Error with the database: <br />';
                echo 'SQL Query: ', $sql2;
                echo 'Error: ' . $error->getMessage();
                $errorMsg = $error->getMessage();
        }
    }
} catch(PDOException $error) {
        echo 'PDO Exception Caught. ';
        echo 'Error with the database: <br />';
        echo 'SQL Query: ', $sql;
        echo 'Error: ' . $error->getMessage();
        $errorMsg = $error->getMessage();    
}

[premiso]

No clue, test it and see.

 

It seems like alot of extra code imo, why not just surround the top most element with the try { } catch, if it is thrown on the 2nd or 3rd nested loop it will still catch it and avoid having that extra, unessecary code in there.

[devxtec]

Try catch code works. Now time to improve my error logging.