Converting MYSQL script to PDO

Question

I am trying to convert an existing MYSQL script into PDO. My MYSQL script works but my PDO version does not.

The aim of the script is to SELECT from the TABLE schedule certain UID's where UID's that are the same are chosen by the lowest alphabetical letter of their corresponding cif_stp_indicator.

So for example I am trying to locate UID number 'Y63553'. There are two UID's with this number. One has an cif_stp_indicator of 'N' and one with a cif_stp_indicator of 'O'. I wish to chose the UID with an STP indicator of 'N'

so example data............

cif_train_uid               cif_stp_indicator            Other Data
   Y63553                          N                          x
   Z45789                          O                          x 
   T78456                          O                          x
   Y63553                          O                          x

I wish to choose the Y63553 with an cif_stp_indicator of 'N'

The below MYSQL script achieves that

 $cif_stp_indicator=null;
 $cif_train_uid='Y63553';


 $b="SELECT s1.cif_train_uid,s1.cif_stp_indicator,s1.schedule_start_date
 FROM schedule s1
 LEFT JOIN schedule s2
 ON (s1.cif_train_uid=s2.cif_train_uid AND s1.cif_stp_indicator>s2.cif_stp_indicator)
 WHERE s2.cif_stp_indicator is NULL AND s1.cif_train_uid='$cif_train_uid' ";

 $l=mysqli_query($mysql_link,$b);   

 if ($l) {

  while($berths=mysqli_fetch_array($l,MYSQLI_ASSOC))
  { 

  echo $berths['cif_train_uid'];    
  echo $berths['cif_stp_indicator'];

  }   
}

The below PDO script runs but it gives no results. I am sure its to do with the way the NULL part is configured as I can get the script to show both cif_train_uid's of Y63553 by removing the relevant parts of the script. I have had help from this forum to get this far with the PDO script and it is now just trying to understand why the NULL part of the script is not working. ie not reporting back Y63553 and N

 $cif_stp_indicator=null;
 $cif_train_uid='Y63553';

 $sql="SELECT s1.cif_train_uid,s1.cif_stp_indicator,s1.schedule_start_date
 FROM schedule s1
 LEFT JOIN schedule s2
 ON (s1.cif_train_uid=s2.cif_train_uid AND s1.cif_stp_indicator>s2.cif_stp_indicator)
 WHERE s2.cif_stp_indicator is NULL AND s1.cif_train_uid=:s1_cif_train_uid ";

 $movedata=$mysql_link->prepare($sql); 

 $movedata->bindValue(':s1_cif_train_uid',$cif_train_uid, PDO::PARAM_STR);
 $movedata->execute();

 $array=$movedata->fetchAll();
 for($i=0;$i<sizeof($array);$i++){
 echo $array[$i]['cif_train_uid'];
 echo $array[$i]['cif_stp_indicator'];
 }

Code for connecting

try
{
$mysql_link= new PDO("mysql:host=$servername;dbname=db;charset=utf8",$username,$password);
$mysql_link->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
$mysql_link->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
echo "Connected Success";
}
catch(PDOException $e)
{
echo $e->getMessage();
}

Dominique · Accepted Answer · 2014-11-04 22:22:01Z

1

foreach($movedata as $row2) {
  echo $row2['cif_train_uid'];  
  echo $row2['cif_stp_indicator'];
}

With PDO you should either foreach as if it's an array... or fetchAll in an array and then loop through it as you would with any other array.

The alternative being:

$array = $movedata->fetchAll();
for($i=0;$i<sizeof($array);$i++) {
  echo $array[$i]['cif_train_uid'];  
  echo $array[$i]['cif_stp_indicator'];
}

It's one of those weird things with PDO and foreach... it has a special function.. practical but confusing.

answered Nov 4, 2014 at 22:22

Dominique

1,12914 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

20 Comments

user2635961 Over a year ago

Thanks Dominique. Still not getting any results even with that change

Dominique Over a year ago

I'll have a close look.. hang on.

user2635961 Over a year ago

will try the alternative

Dominique Over a year ago

try adding $mysql_link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);right after your connection string definition, it will give you some extra error info

Dominique Over a year ago

Did you define $mysql_link the way you should for PDO? it's not the same

|

Collectives™ on Stack Overflow

Converting MYSQL script to PDO

1 Answer 1

20 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

20 Comments

Your Answer

Sign up or log in

Post as a guest

Related