logo-dw

Go Back   Dreamweaver Club Forums > Hand Coders Forum > PHP
Register FAQ Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Display Modes
Old 08-22-2013, 01:36 AM   #1
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default new to PDO, PDO not working?

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 Code:
<?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();
}

?>
__________________
------------
Jim Michaels
HTML Code:
improperly<strong>nested<em>elements</strong>cause</em>
browser confusion (I believe the term is 'tag soup')!

Last edited by edbr; 08-23-2013 at 01:26 AM..
jmichae3 is offline   Reply With Quote
Old 08-22-2013, 03:14 AM   #2
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default

this mysqli version fails too, at the prepare statement, every time.
PHP Code:
<?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();
?>
__________________
------------
Jim Michaels
HTML Code:
improperly<strong>nested<em>elements</strong>cause</em>
browser confusion (I believe the term is 'tag soup')!

Last edited by edbr; 08-23-2013 at 01:22 AM..
jmichae3 is offline   Reply With Quote
Old 08-22-2013, 07:01 AM   #3
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default

...I discovered I have to add stmt_init(). but this did not fix the errors I am getting with prepare().

PHP Code:
<?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();
?>
__________________
------------
Jim Michaels
HTML Code:
improperly<strong>nested<em>elements</strong>cause</em>
browser confusion (I believe the term is 'tag soup')!

Last edited by jmichae3; 08-22-2013 at 07:11 AM..
jmichae3 is offline   Reply With Quote
Old 08-23-2013, 01:29 AM   #4
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

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
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 08-24-2013, 02:46 AM   #5
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

i also think , and it is just a think that you need to bind url
such as i used here


Code:
$url= $_GET['url'];
$stmt = $dbh->prepare('select * from table  where url=:url);
$stmt->bindParam('url', $url);
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 08-26-2013, 10:52 PM   #6
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default

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.
__________________
------------
Jim Michaels
HTML Code:
improperly<strong>nested<em>elements</strong>cause</em>
browser confusion (I believe the term is 'tag soup')!
jmichae3 is offline   Reply With Quote
Old 08-26-2013, 11:09 PM   #7
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default

there are some serious bugs already logged against PDO.
__________________
------------
Jim Michaels
HTML Code:
improperly<strong>nested<em>elements</strong>cause</em>
browser confusion (I believe the term is 'tag soup')!
jmichae3 is offline   Reply With Quote
Old 08-27-2013, 02:35 AM   #8
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

http://stackoverflow.com/questions/4...ving-strangely
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 08-27-2013, 05:58 AM   #9
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default

working now, with BOTH, must have been a bad day. will try on prod.
logged a bug report against mysqli.
PHP Code:
<?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!";
}
?>
__________________
------------
Jim Michaels
HTML Code:
improperly<strong>nested<em>elements</strong>cause</em>
browser confusion (I believe the term is 'tag soup')!
jmichae3 is offline   Reply With Quote
Old 08-27-2013, 06:00 AM   #10
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default

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.
__________________
------------
Jim Michaels
HTML Code:
improperly<strong>nested<em>elements</strong>cause</em>
browser confusion (I believe the term is 'tag soup')!
jmichae3 is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 12:51 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Copyright 2006 DreamweaverClub.com