PDA

View Full Version : new to PDO, PDO not working?


jmichae3
08-22-2013, 02:36 AM
this is a web counter. it ALWAYS gives me 1 for a count. rowCount always gives me 0, fetchColumn always gives me an empty string, and the db is never created. I have tried rearranging code, different things. nothing works. beginning to think this deprecation of MYSQL is a mistake.

<?php
$url = $_SERVER['HTTP_REFERER'];//$_SERVER['REQUEST_URI']; //http_referer if using iframe,
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $uid, $pwd) or die("connection oops");

$query=0;
$counter=0;
$row=array();

//mysql_select_db($dbname,$link) or die("oops:".mysql_error());

//row exist already?
$sth0 = $dbh->prepare("SELECT COUNT(counter) AS cc FROM counters WHERE url = ?", array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth0->bindParam(1,$url,PDO::PARAM_STR);
$sth0->execute();
$rowcount=$sth0->rowCount(); //always 0, no table
//WHEN do I get the error code? after bindparam or after execute or after fetch or after prepare?
//always success with no table for some reason, no docs on correct error code
$failed=is_null($sth0->errorCode()) || ""===$sth0->errorCode() || "0"===$sth0->errorCode();

$rowcount = $sth0->fetchColumn(); // you should expect exactly one row
echo "failed=$failed,rc=$rowcount,url=$url,";//DEBUG
$sth0->closeCursor();

if (0==intval($rowcount)) { //if not, create one for the new unique URL.
//if ($failed) {
$dbh->exec("
CREATE TABLE IF NOT EXISTS `counters` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`url` LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`counter` BIGINT(20) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
)
");
//$rowcount=0;
//}
echo "i";
//entry not found, so insert new entry with counter value 1. since we have just hit a page, count as one.
$sth1=$dbh->prepare("INSERT INTO counters(url,counter) VALUES(?,1)");
$sth1->bindparam(1,$url,PDO::PARAM_STR);
$sth1->execute();
$sth1->closeCursor();
echo 1;
} else { //just created the row. use the last insert ID. no need to do anything except display a 1.
echo "u";
$sth2=$dbh->prepare("UPDATE counters SET counter = counter + 1 WHERE url=?");
$sth2->bindparam(1,$url,PDO::PARAM_STR);
$sth2->execute();
$sth2->closeCursor();

$sth3=$dbh->prepare("SELECT counter FROM counters WHERE url=?", array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth3->bindParam(1,$url,PDO::PARAM_STR);
$sth3->execute();
while ($row = $sth3->fetch(PDO::FETCH_ASSOC)) {
echo $row['counter'];//.",";
}
$sth3->closeCursor();
}

?>

jmichae3
08-22-2013, 04:14 AM
this mysqli version fails too, at the prepare statement, every time.

<?php
$url = $_SERVER['HTTP_REFERER'];//$_SERVER['REQUEST_URI']; //http_referer if using iframe,
$dbh = new mysqli($hostname,$uid,$pwd,$db) or die("connection oops");

$counter=0;
$rowcount=0;


//row exist already?
echo 'sC';
$sth0 = $dbh->prepare("SELECT COUNT(counter) AS cc FROM counters WHERE url=?");
if (false===$sth0) {
echo 'errc';
$dbh->query("
CREATE TABLE IF NOT EXISTS `counters` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`url` LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`counter` BIGINT(20) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
)
");
//$rowcount=0;
} else {
$sth0->bind_param('s',$url);
$sth0->bind_result($cc);
$sth0->execute();
$rowcount=$sth0->num_rows;
$success=""==$sth0->error;
$sth0->fetch(); // you should expect exactly one row
echo "success=$success,rc=$cc,url=$url,";//DEBUG
$sth0->close();
}



if (0==intval($rowcount)) { //if not, create one for the new unique URL.
echo "i";
//entry not found, so insert new entry with counter value 1. since we have just hit a page, count as one.
$sth1=$dbh->prepare("INSERT INTO counters(url,counter) VALUES(?,1)");
if (false!==$sth1) {
$sth1->bind_param('s',$url);
$sth1->execute();
$sth1->close();
echo 1;
} else {
echo 'err';
}
} else { //just created the row. use the last insert ID. no need to do anything except display a 1.
echo "u";
$sth2=$dbh->prepare("UPDATE counters SET counter = counter + 1 WHERE url=?");
if (false!==$sth2) {
$sth2->bind_param('s',$url);
$sth2->execute();
$sth2->close();
} else {
echo 'err';
}
echo 's';
$sth3=$dbh->prepare("SELECT counter FROM counters WHERE url=?");
if (false!==$sth3) {
$sth3->bind_param('s',$url);
$sth3->bind_result($counter);
$sth3->execute();
while ($sth3->fetch()) {
echo $counter;//.",";
}
$sth3->close();
} else {
echo 'err';
}
}
$dbh->close();
?>

jmichae3
08-22-2013, 08:01 AM
...I discovered I have to add stmt_init(). but this did not fix the errors I am getting with prepare().


<?php
//outputs serrc!BADa!ierr
$url = $_SERVER['HTTP_REFERER'];//$_SERVER['REQUEST_URI']; //http_referer if using iframe,
$dbh = new mysqli($hostname,$uid,$pwd,$db);
if ($dbh->errno) {
//printf("connection oops:%s",$dbh->error);
echo "connection oops";
$dbh->close();
exit;
}

$counter=0;
$rowcount=0;
//row exist already?
echo 's';//DEBUG
$sth0=$dbh->stmt_init();
$result0=$sth0->prepare("SELECT COUNT(counter) AS cc FROM counters WHERE url=?");
if (false===$result0) { //failed, so create the table
echo 'errc';//DEBUG
$resulta=$dbh->multi_query("
CREATE TABLE IF NOT EXISTS counters (
id INT NOT NULL AUTO_INCREMENT,
url LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
counter BIGINT NOT NULL DEFAULT '1',
PRIMARY KEY (id)
)");
if (false===$resulta) {
echo "!BADa!";
} else {
echo 'a';//DEBUG
$resultb=$dbh->multi_query("CREATE UNIQUE INDEX jnjc_idx ON counters(url)");
if (false===$resultb) {
echo "!BADb!";
} else {
$result->close();
echo 'b';//DEBUG
}
}
} else {
$sth0->bind_param('s',$url);
$sth0->bind_result($cc);
$sth0->execute();
$rowcount=$sth0->num_rows;
$success=""==$sth0->error();
$sth0->fetch(); // you should expect exactly one row
echo "success=$success,cc=$cc,url=$url,";//DEBUG
$sth0->close();
}



if (0==intval($rowcount)) { //if not, create one for the new unique URL.
echo "i";//DEBUG
//entry not found, so insert new entry with counter value 1. since we have just hit a page, count as one.
$sth1=$dbh->stmt_init();
$result1=$sth1->prepare("INSERT INTO counters(url,counter) VALUES(?,1)");
if (false===$result1) {
echo 'err';//DEBUG
} else {
$sth1->bind_param('s',$url);
$sth1->execute();
$sth1->close();
echo 1;
}
} else { //just created the row. use the last insert ID. no need to do anything except display a 1.
echo "u";//DEBUG
$sth2=$dbh->stmt_init();
$result2=$sth2->prepare("UPDATE counters SET counter = counter + 1 WHERE url=?");
if (false===$result2) {
echo 'err';//DEBUG
} else {
$sth2->bind_param('s',$url);
$sth2->execute();
$sth2->close();
}
echo 's';//DEBUG
$sth3=$dbh->stmt_init();
$result3=$sth3->prepare("SELECT counter FROM counters WHERE url=?");
if (false===$result3) {
echo 'err';//DEBUG
} else {
$sth3->bind_param('s',$url);
$sth3->bind_result($counter);
$sth3->execute();
while ($sth3->fetch()) {
echo $counter;//.",";
}
$sth3->close();
}
}
$dbh->close();
?>

edbr
08-23-2013, 02:29 AM
if you need any more editing of these please let me know.
As for your problem , one of mt what the hell is going on links should be able to help you , its covers similar ground http://www.kitebird.com/articles/php-pdo.html

edbr
08-24-2013, 03:46 AM
i also think , and it is just a think that you need to bind url
such as i used here



$url= $_GET['url'];
$stmt = $dbh->prepare('select * from table where url=:url);
$stmt->bindParam('url', $url);

jmichae3
08-26-2013, 11:52 PM
the man pages on PDO showed both styles of bindParam (with ? and with named). I may have to submit a bug report against PDO as well... like I said, the prepare seems to fail when I check the status in mysqli and I am guessing the same with PDO as well - maybe they are using a common interface and it's hosed.

jmichae3
08-27-2013, 12:09 AM
there are some serious bugs already logged against PDO.

edbr
08-27-2013, 03:35 AM
http://stackoverflow.com/questions/4973575/pdo-bindparam-behaving-strangely

jmichae3
08-27-2013, 06:58 AM
working now, with BOTH, must have been a bad day. will try on prod.
logged a bug report against mysqli.

<?php
$url = $_SERVER['HTTP_REFERER'];//$_SERVER['REQUEST_URI']; //http_referer if using iframe,
try {
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $uid, $pwd) or die("connection oops");
} catch (PDOException $e) {
echo "<p>Count not Connect to server.\n";
//echo $e->getMessage()."\n</p>";
exit;
}

$query=0;
$counter=0;
$row=array();
$createsql="
CREATE TABLE IF NOT EXISTS `counters` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`url` LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`counter` BIGINT(20) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
)
";

//mysql_select_db($dbname,$link) or die("oops:".mysql_error());

//row exist already?
try {
$sth0 = $dbh->prepare("SELECT COUNT(counter) AS cc FROM counters", array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth0->bindParam(1, $url,PDO::PARAM_STR);
$sth0->execute();
$rowcount=$sth0->rowCount(); //always 0, no table
//WHEN do I get the error code? after bindparam or after execute or after fetch or after prepare?
//always success with no table for some reason, no docs on correct error code
$failed=is_null($sth0->errorCode()) || ""===$sth0->errorCode() || "0"===$sth0->errorCode();

$rowcount = $sth0->fetchColumn(); // you should expect exactly one row
echo "?rowcount=$rowcount,url=$url,";//DEBUG
$sth0->closeCursor();
} catch (PDOException $e) {
echo "C";
$dbh->exec($createsql);
}
if (0==$rowcount) {
echo "C";
try {$dbh->exec($createsql);} catch(PDOException $e) {echo "OOPS-C!";}
}
try {
$sth0 = $dbh->prepare("SELECT COUNT(counter) AS cc FROM counters WHERE url = ?", array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth0->bindParam(1, $url,PDO::PARAM_STR);
$sth0->execute();
$rowcount=$sth0->rowCount(); //always 0, no table
//WHEN do I get the error code? after bindparam or after execute or after fetch or after prepare?
//always success with no table for some reason, no docs on correct error code
$failed=is_null($sth0->errorCode()) || ""===$sth0->errorCode() || "0"===$sth0->errorCode();

$rowcount = $sth0->fetchColumn(); // you should expect exactly one row
echo "srowcount=$rowcount,";//DEBUG
$sth0->closeCursor();

if (0==intval($rowcount)) { //if not, create one for the new unique URL.
echo "i";
//entry not found, so insert new entry with counter value 1. since we have just hit a page, count as one.
$sth1=$dbh->prepare("INSERT INTO counters(url,counter) VALUES(?,1)");
$sth1->bindParam(1, $url,PDO::PARAM_STR);
$sth1->execute();
$sth1->closeCursor();
echo 1;
} else { //just created the row. use the last insert ID. no need to do anything except display a 1.
echo "u";
$sth2=$dbh->prepare("UPDATE counters SET counter=counter+1 WHERE url=?");
$sth2->bindParam(1, $url,PDO::PARAM_STR);
$sth2->execute();
$sth2->closeCursor();

$sth3=$dbh->prepare("SELECT counter FROM counters WHERE url=?", array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth3->bindParam(1, $url,PDO::PARAM_STR);
$sth3->execute();
while ($row = $sth3->fetch(PDO::FETCH_ASSOC)) {
echo $row['counter'];//.",";
}
$sth3->closeCursor();
}
} catch (PDOException $e) {
echo "other-OOPS!";
}
?>

jmichae3
08-27-2013, 07:00 AM
by default, PDO throws exceptions and outputs stack traces if there is any PDO error. you can control that, thanks for the link.
I was getting a "missing driver" error at one point at the 'new' statement with PDO about 10 minutes ago. forgot how I resolved it.