Need help converting a script from PDO to Mysql

Question

Im trying to convert this script, in my Zend program.

http://github.com/jackmoore/colorrating/raw/master/rating/rating.php

<?php
class rating{

 public $average = 0;
 public $votes;
 public $status;
 public $table;
 private $path;

 function __construct($table){
  try{
   $pathinfo = pathinfo(__FILE__);
   $this->path = realpath($pathinfo['dirname']) . "/database/ratings.sqlite";
   $dbh = new PDO("sqlite:$this->path");
   $this->table = $dbh->quote($table);
   // check if table needs to be created
   $table_check = $dbh->query("SELECT * FROM $this->table WHERE id='1'");
   if(!$table_check){
    // create database table
    $dbh->query("CREATE TABLE $this->table (id INTEGER PRIMARY KEY, rating FLOAT(3,2), ip VARCHAR(15))");
    $dbh->query("INSERT INTO $this->table (rating, ip) VALUES (0, 'master')");    
   } else {
    $this->average = $table_check->fetchColumn(1);
   }
   $this->votes = ($dbh->query("SELECT COUNT(*) FROM $this->table")->fetchColumn()-1);
  }catch( PDOException $exception ){
    die($exception->getMessage());
  }
  $dbh = NULL;  
 }

 function set_score($score, $ip){
  try{
   $dbh = new PDO("sqlite:$this->path");
   $voted = $dbh->query("SELECT id FROM $this->table WHERE ip='$ip'");
   if(sizeof($voted->fetchAll())==0){

    $dbh->query("INSERT INTO $this->table (rating, ip) VALUES ($score, '$ip')");
    $this->votes++;

    //cache average in the master row
    $statement = $dbh->query("SELECT rating FROM $this->table");
    $total = $quantity = 0;
    $row = $statement->fetch(); //skip the master row
    while($row = $statement->fetch()){
     $total = $total + $row[0];
     $quantity++;
    }
    $this->average = round((($total*20)/$quantity),0);
    $statement = $dbh->query("UPDATE $this->table SET rating = $this->average WHERE id=1");
    $this->status = '(thanks!)';
   } else {
    $this->status = '(already scored)';
   }

  }catch( PDOException $exception ){
    die($exception->getMessage());
  }
  $dbh = NULL;
 }
}

function rating_form($table){
 $ip = $_SERVER["REMOTE_ADDR"];
 if(!isset($table) && isset($_GET['table'])){
  $table = $_GET['table'];
 }
 $rating = new rating($table);
 $status = "<div class='score'>
    <a class='score1' href='?score=1&amp;table=$table&amp;user=$ip'>1</a>
    <a class='score2' href='?score=2&amp;table=$table&amp;user=$ip'>2</a>
    <a class='score3' href='?score=3&amp;table=$table&amp;user=$ip'>3</a>
    <a class='score4' href='?score=4&amp;table=$table&amp;user=$ip'>4</a>
    <a class='score5' href='?score=5&amp;table=$table&amp;user=$ip'>5</a>
   </div>
 ";
 if(isset($_GET['score'])){
  $score = $_GET['score'];
  if(is_numeric($score) && $score <=5 && $score >=1 && ($table==$_GET['table']) && isset($_GET["user"]) && $ip==$_GET["user"]){
   $rating->set_score($score, $ip);
   $status = $rating->status;
  }
 }
 if(!isset($_GET['update'])){ echo "<div class='rating_wrapper'>"; }
 ?>
 <div class="sp_rating">
  <div class="rating">Rating:</div>
  <div class="base"><div class="average" style="width:<?php echo $rating->average; ?>%"><?php echo $rating->average; ?></div></div>
  <div class="votes"><?php echo $rating->votes; ?> votes</div>
  <div class="status">
   <?php echo $status; ?>
  </div>
 </div>
 <?php
 if(!isset($_GET['update'])){ echo "</div>"; }
}

if(isset($_GET['update'])&&isset($_GET['table'])){
 rating_form($_GET['table']);
}

How can I change this to Mysql?

Im using $dbh = Zend_Registry::get ( "db" ); normally to get my sql access.

Thanks everyone, hopefully there isnt too many changes invloved

Hope you dont find this cheeky, I know people dont want to 'do your work for you' but I'm having trouble knowing exactly what to change, I reckon its about 4 or 5 lines in total, and of course you learn these things by example — bluedaniel
– bluedaniel, Commented Mar 1, 2010 at 23:29

Tim Lytle · Accepted Answer · 2010-03-06 18:57:45Z

1

If the db object in the registry is one of Zend_Db then:

$dbh->quote() becomes $db->quote() (the easy one)

$dbh->query() statements that manipulate data could translate to $dbh->query() (again, easy). However, it would be better to update the code to use $db->insert($table, $data) - or use Zend_Db_Table objects with $table->insert($data).

$dbh->query() statments that return data can become $db->fetchAll($sql) statements, but you need to update the code to expect Zend_Db Row and Rowset objects.

I'd suggest reading the Zend_Db documentation to understand the what functions map to the different PDO functions.

If you just need this to work with MySQL change the DSN string to your MySQL connection. Something like:

$dbh = new PDO("mysql:dbname=testdb;host=127.0.0.1", $user, $pass);

See the PDO documentation for details.

If the db object in the registry a PDO instance just grab that object and use it, instead of creating a new PDO object.

edited Mar 6, 2010 at 18:57

answered Mar 1, 2010 at 23:54

Tim Lytle

17.6k10 gold badges63 silver badges92 bronze badges

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

3 Comments

bluedaniel Over a year ago

I dont know, Im still gettings errors all over the show, I thought the whole point of PDO was that the syntac remained the same throughout?

Tim Lytle Over a year ago

If you're trying to use a Zend_DB object, then you're not using PDO. If all you need is that to work with MySQL, just change the DSN string.

bluedaniel Over a year ago

cheers, the script doesnt seem to be working all that well still, your new PDO shout was a good one though

Collectives™ on Stack Overflow

Need help converting a script from PDO to Mysql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related