Jump to content


Photo

move from mysql to pdo


  • Please log in to reply
7 replies to this topic

#1 Tanja

Tanja
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts
  • LocationGermany
  • Age:48

Posted 17 September 2017 - 05:59 AM

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?

 



#2 dalecosp

dalecosp
  • Members
  • PipPipPip
  • Advanced Member
  • 397 posts
  • LocationMissouri

Posted 19 September 2017 - 07:17 PM

First issue is here:

$sql = new stdClass();

 

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


"God doesn't play dice" --- Albert Einstein
"Perl is hardly a paragon of beautiful syntax." --- Weedpacket

#3 requinix

requinix
  • Administrators
  • Maddening Administrator
  • 9,466 posts
  • LocationWA

Posted 19 September 2017 - 07:29 PM

$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.
The Reimann Zeta Function Trolley Problem | "Summer is when I, the great ice fairy, can show my true power!"

#4 Tanja

Tanja
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts
  • LocationGermany
  • Age:48

Posted 20 September 2017 - 05:54 PM

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



#5 cyberRobot

cyberRobot
  • Moderators
  • Much to Learn
  • 2,764 posts

Posted 20 September 2017 - 06:47 PM


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


#6 cyberRobot

cyberRobot
  • Moderators
  • Much to Learn
  • 2,764 posts

Posted 20 September 2017 - 06:57 PM

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.


#7 SteamingAlong

SteamingAlong
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 26 November 2017 - 12:39 AM

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 by SteamingAlong, 26 November 2017 - 12:40 AM.


#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 17,913 posts

Posted 26 November 2017 - 05:14 PM

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)
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users