move from mysql to pdo

[Tanja]

i have an old script, written in mysql.
The most other ones are up to date in pdo. MySqli - the paramenter binding is not easy and the list() parameter will get in PHP7 another direction ...
The old script (it is from Barand), creating a pedigree table with pictures and healthresults with variabel generations

function printTree($dogid, $name, $healthstatus, $N, $max) { 
//database-connection
        if ($name == '') $name = ' ';
		if ($healthstatus == '') $healthstatus = ' ';
         $rspan = pow(2, $max-$N);
		
		if(file_exists("photo/".$dogid."/".$dogid.".jpg"))
			$img = "/photo/".$dogid."/".$dogid.".jpg";
			else
			$img = "/main/img/platzhalter.png";
       
		 $name = "<a href=".$dogid.">".$name."</a><br><img src='$img' height='50' alt='' /><br>".$healthstatus;
		 
		 
		 if ($rspan > 1)
             echo "\t<td rowspan='$rspan' >".$name."</td>\n";
         else
             echo "\t<td>$name</td>\n";


         if ($N == $max) echo "</tr>\n<tr>\n";


         if ($N < $max) {
	
		  $sql = "
			SELECT 
			a.father_id, 
			a.mother_id, 
			CONCAT('<span class=\'warning\'>',s.dog_warning,'</span><br>',s.dogname), 
			CONCAT('<span class=\'warning\'>',d.dog_warning,'</span><br>',d.dogname), 
			CONCAT(s.hd,'<br>DM:',s.dm,'<br>DW:',s.dw), 
			CONCAT(d.hd,'<br>DM:',d.dm,'<br>DW:',d.dw)
			FROM dog a 
			INNER JOIN dog s ON a.father_id = s.id
			INNER JOIN dog d ON a.mother_id = d.id
			WHERE a.id = '$dogid' 
			";
			 
			$res = $dbi->query($sql);
			list($s, $d, $sn, $dn, $shealth, $dhealth) = mysqli_fetch_row($res);

			printTree($s, $sn, $shealth, $N+1, $max);
            printTree($d, $dn, $dhealth, $N+1, $max);
		   

         }
}

Very bad is dogid here - it comes from url ...
I´m not able to switch the line with list() to pdo ...

$sql = new stdClass();
$result = $sql->query="
 SELECT 
	...
	WHERE a.id = :dogid 
	";
	$stmt = $conn->prepare($result);
	$stmt->bindParam(':dogid', $givenid, PDO::PARAM_INT);
	$stmt->execute();


....

	while (list($s, $d, $sn, $dn, $shealth, $dhealth) =$stmt->fetch());
		 
           {  printTree($s, $sn, $shealth, $N+1, $max);
             printTree($d, $dn, $dhealth, $N+1, $max);
		   }

i tried with serval fetch methods, with and without while...
 

Any ideas?

 

[dalecosp]

First issue is here:

$sql = new stdClass();

 

$sql should be a PDO object, not a StandardClass object.

[requinix]

$sql should be a PDO object, not a StandardClass object.

Actually it's okay: the code isn't using $sql for anything as the query is prepared from $result and $conn. Of course it's definitely weird and shouldn't be there, but it isn't a problem by itself.

[Tanja]

without i get Warning: Creating default object from empty value ...

[cyberRobot]

without i get Warning: Creating default object from empty value ...

 

You should be able to change this

$sql = new stdClass();
$result = $sql->query="
 SELECT

 

To this

$result = "
 SELECT

[cyberRobot]

I'm not able to switch the line with list() to pdo ...

 

Are you getting an error? If so, what does it say?

 

I assume there shouldn't be a semi-colon after the while loop here:

while (list($s, $d, $sn, $dn, $shealth, $dhealth) =$stmt->fetch());

If that's not it, have you tried something like this

while ($row = $stmt->fetch()) {
    printTree(..., $row['shealth'], ...);
}

 

Note that "shealth" in the $row variable needs to match whatever you're using for the column name.

[SteamingAlong]

I believe cyberRobot is correct. However, you also have and empty $stmt->fetch();

 

Read up on the manual for the correct parameter to use eg

$stmt->fetch(PDO::FETCH_ASSOC);
$stmt->fetch(PDO::FETCH_BOTH);
$stmt->fetch(PDO::FETCH_BOUND);

... and so on

PHP PDO MANUAL

 

Update.....Ooops .... its an old post but i'll leave it here for those who couldn't find it.

Edited November 26, 2017 by SteamingAlong

[Barand]

FYI all the fetch() params are optional. Read the manual

public mixed PDOStatement::fetch ([ int $fetch_style [, int $cursor_orientation = PDO::FETCH_ORI_NEXT [, int $cursor_offset = 0 ]]] )

If no fetch style then it will use the default. For list() to work the array must have numeric keys (PDO::FETCH_NUM)