www.webdeveloper.com
Page 2 of 14 FirstFirst 123412 ... LastLast
Results 16 to 30 of 198

Thread: My questions thread.

  1. #16
    Join Date
    Jul 2012
    Location
    Vancouver
    Posts
    61
    Quote Originally Posted by evenstar7139 View Post
    Okay, I changed the code, and the error I'm getting changed to this:



    PHP Code:
    if (!$this->DBH
       {
         
    $connectString="mysql:host={$this->dbHost};dbname={$this->dbName};"
         
    $this->DBH = new PDO($connectString$this->dbUser$this->dbPass); 
         
    $DBH->setAttributePDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION ); //this is line 22
       

    change $dbh to $this->dbh

    What you should be doing though is doing these calls in the __construct() function.. that way it init these things when you create init your pdo class..

  2. #17
    Join Date
    Jul 2012
    Location
    Vancouver
    Posts
    61
    Something like

    PHP Code:

      
    class db 
        private 
    $db NULL
        private 
    $db_name 'db';
        private 
    $host 'localhost';
        private 
    $username 'username';
        private 
    $password 'password';
        
        public function 
    __construct() { 
            try { 
                
    $this->db = new PDO"mysql:dbname={$db_name};host={$this->host};"$this->username$this->password ); 
            } 
            catch (
    Exception $e) { 
               
    // $sys->log->insert(0,1,$e);
            

        } 


  3. #18
    Join Date
    Jan 2009
    Posts
    3,346
    if (!$this->DBH)
    Do you mean to check if a variable exists or if the variable is equal to false?
    Code:
    if(!isset($this->DBH))

  4. #19
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Quote Originally Posted by mistin.ca View Post
    change $dbh to $this->dbh

    What you should be doing though is doing these calls in the __construct() function.. that way it init these things when you create init your pdo class..
    My PDO class? The class we've been discussing is my database class. Do I need to make a separate class for PDO or something?

    Quote Originally Posted by criterion9 View Post
    Do you mean to check if a variable exists or if the variable is equal to false?
    Code:
    if(!isset($this->DBH))
    I wanted to see if it was false. I was under the impression that if a database connection fails, false was returned. Granted, I was using mysql_connect when I first wrote this class. Maybe it's different with PDO.

  5. #20
    Join Date
    Jan 2009
    Posts
    3,346
    If your block of code was before it had attempted a connection the variable might not be set at all (can't magically become a false value when the variable doesn't yet exist)...we would have to see the updated class in full to be sure.

  6. #21
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    If you're going to use PDO, an alternative is to simply extend the PDO class and add/modify methods as needed for your specific purposes:
    PHP Code:
    class MyDb extends PDO
    {
       private 
    $dsn    "mysql:host={$this->dbHost};dbname={$this->dbName};";
       private 
    $dbUser 'foo';
       private 
    $dbPass 'bar';

       public function 
    __construct()
       {
          
    parent::__construct($this->dsn$this->dbUser$this->dbPass);
       }

       
    // any other method you need to create or override...

    To take it to another level, you could consider using the "singleton pattern", so that every usage within a given script would use the same instance and therefore the same DB connection, but I'll leave that to you to search on should you feel so inclined.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #22
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Quote Originally Posted by criterion9 View Post
    If your block of code was before it had attempted a connection the variable might not be set at all (can't magically become a false value when the variable doesn't yet exist)...we would have to see the updated class in full to be sure.

    It exists. Higher up in the script I have this:
    PHP Code:
    private $DBH false
    Anyway, the script is connecting now.

    Now I'm having trouble getting things to insert into the database. Why is the following putting 1, 2, and 3 in the database instead of the values these numbers are supposed to represent? (starry, fufu, and email@somesite.com)

    PHP Code:
             $statement="INSERT INTO `test` ( `username`, `password`, `email` ) values ( 1, 2, 3)";
             
    $STH $this->DBH->prepare($statement);
             
             
    $username='starry';
             
    $password='fufu';
             
    $email='email@somesite.com';
             
             
    $STH->bindParam(1$username);
             
    $STH->bindParam(2$password);
             
    $STH->bindParam(3$email);
             
             
    $STH->execute(); 

  8. #23
    Join Date
    Jan 2009
    Posts
    3,346
    I've only ever done it using question marks....not sure if having a numeric key is a problem...
    PHP Code:
    $stmt $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
    $stmt->bindParam(1$name);
    $stmt->bindParam(2$value); 

  9. #24
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Quote Originally Posted by criterion9 View Post
    I've only ever done it using question marks....not sure if having a numeric key is a problem...
    PHP Code:
    $stmt $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
    $stmt->bindParam(1$name);
    $stmt->bindParam(2$value); 

    Okay, doing it your way made it work. Now I'm having a problem where what it is inserting into the database, is the third paramater three times. Username, password, and email are all getting email's value.

    The relevant part of my class:
    PHP Code:
         public function insert($tableName$colsAndVals//cols are the columns and values are their respective values, it expects an array
         
    {
             
    /* FUTURE IMPROVEMENTS:
             *  Check for database connection.
             *  Check if table exists.
             *  Check if $tableName or $colsAndVals is null.
             *  Check that $colsAndVals was filled out properly and is an array.
             *  Maybe check if column names exist.
             *  Check that query was successful.
             */
             
             
    $tableName='`'.$tableName.'`'// puts `` around the table name
             
    $cols=array();
             
    $vals=array();
             
             foreach (
    $colsAndVals as $col => $val
             {
                
    $cols[]='`'.$col.'`'//$cols prepares column names for implosion
                 
    $vals[]=$val;    //values go in an array so we can use foreach later on  
             
    }
             
             
    $columnNames=implode($cols', ');
             
             
    $totalValues=count($vals);     
                       
             for (
    $x=0$x $totalValues$x++) //decide how many question marks we need for the values
             
    {
                 
                 if (
    $x == 0)
                 {
                     
    $colString='?';
                 }
                 else
                 {
                     
    $colString.=', '.'?';
                 }
             }
             
             
    $statement="INSERT INTO $tableName ( $columnNames ) values ( $colString )"// if I print $statement it outputs INSERT INTO `test` ( `username`, `password`, `email` ) values ( ?, ?, ? )
             
    try {
             
    $STH $this->DBH->prepare($statement);
             
             
    $num=1;
             foreach (
    $vals as $value//binding the values with bindParam
                  
                
    $STH->bindParam($num$value);
                 
    $num++;
             }
             
              
    $STH->execute(); 
             } catch (
    Exception $e)
             { print 
    $e->getMessage(); }
             
         } 
    The object that is calling this method:

    PHP Code:
    $accounts=new Database();
    $accounts->connect();
    $values=array(
    'username' => 'cuddles',
    'password' => 'fuzzy',
    'email' => 'cuddles@email.com',
    );
    $accounts->insert('test'$values);
    ?> 
    Last edited by evenstar7139; 08-01-2012 at 12:05 AM.

  10. #25
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    I'm wondering something about PDO::prepare() verses PDO::query(). I'm reading that prepare() is only good for submitting thousands of identical queries at once (with different data, of course.) What about a single query that affects a gazillion rows?

    For example, when I had my dog game, I had a cron job run this every day:
    PHP Code:
    mysql_query("UPDATE dogs SET age = age + 1"); 
    This aged all the dogs by 1 day.

    Would this have been best executed with prepare()?

  11. #26
    Join Date
    Jan 2009
    Posts
    3,346
    Not sure if this is the problem but are you missing an opening bracket here?
    foreach ($vals as $value){ //binding the values with bindParam

    $STH->bindParam($num, $value);
    $num++;
    }

  12. #27
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    Quote Originally Posted by evenstar7139 View Post
    I'm wondering something about PDO:repare() verses PDO::query(). I'm reading that prepare() is only good for submitting thousands of identical queries at once (with different data, of course.) What about a single query that affects a gazillion rows?

    For example, when I had my dog game, I had a cron job run this every day:
    PHP Code:
    mysql_query("UPDATE dogs SET age = age + 1"); 
    This aged all the dogs by 1 day.

    Would this have been best executed with prepare()?
    In my experience, the difference for a one-off query is minimal, and if you are using the bindParam() method to sanitize external inputs, it is about the same (since the bindParam() is essentially taking the place of any escaping function you would use if not using prepared statements).

    But in any case, in your example, it is only one update query. Where prepare would come into play would be something like:
    PHP Code:
    $sql "UPDATE dogs SET age = age + 1 WHERE dog_id = :id";
    $stmt $db->prepare($sql);
    foreach(array(
    1,2,3,5,8,13,21) as $dogId) {
       
    $stmt->bindParam(':id'$dogIdPDO::PARAM_INT);
       
    $stmt->execute();

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  13. #28
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    I keep reading stuff online and I'm getting confused if I should be using PDO at all or if I should use mysqli_* The whole premise of running queries with PDO is preparing them before being sent to the database, yes? Well, it seem that PDO::prepare() is almost always not worth using because it is so slow.

  14. #29
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,634
    It is not slow. It may not be quite as fast as just calling query(), but we're talking modern computers here that do things really, really fast. We use PDO prepared queries all the time at work, often with some pretty hairy queries with several joins and sub-queries, and performance is not an issue.

    But that is really beside the point, since you can use (or not use) prepared statements with MySQLi, too. Whether or not to use PDO vs. MySQLi is whether or not there is a desire to make your application relatively RDBMS-agnostic. We use PostgreSQL at work as the RDBMS, but if we ever find we need to change to MySQL or some other alternative, the impact will be minimized since (in theory) we would only need to change the DNS string in one place. (In practice some queries would need to be changed as some undoubtedly use PostgreSQL-specific features/syntax.)

    So, long story short: use PDO if you want to be flexible as to the actual RDBMS used, otherwise use whatever you want if you're sure you'll never want/need to change it. Oh, and don't stress over a millisecond here and a couple microseconds there: concentrate on the real performance issues (99% of which usually end up being DB design/indexing and the related queries themselves -- not the DB extension you're using in PHP).
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  15. #30
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    Hmm, how about quoting strings in single quotes verses double quotes? I was always told to do this. I was told my scripts would parse faster or something. I'm guessing it doesn't help performance much? Do PHP programmers just do this because it's easy?

    Speaking of DB designs and performance, I once had a database that drove me nuts on a daily basis. Some tables had over 2 million rows and as many as 100 columns. My site had lots of queries going to those tables, and some of them had joins. The MySQL service was shutting off several times a day and I'd have to manually turn it back on. The CPU was always at like 100% usage and sometimes the server would lock up and have to be rebooted. And I had scheduled tasks that were supposed to run every night at midnight, like, one that ages all the dogs by a day. A lot of times it would quit before it was done so I'd have a bunch of dogs that aged, and a bunch that did not. I'd have to go figure out which ones didn't get aged and manually fix it. Same goes for any other scheduled task that wasn't completed. Ugh, nightmare. I felt like I could never go anywhere because of the need to babysit that server.

    I don't know what I did wrong. I had primary keys and other indexes. I indexed everything that seemed feasible to index. Do you think I just messed up somewhere in the optimization, or that those queries were too much for my little server to handle? My site had up to 100 people on it at any given time. The server had 2 GB ram, 2.2 ghz dual core processor, and was a Windows machine.

    If this is better posted in a MySQL forum, let me know. You just seemed like somebody who would know.
    Last edited by evenstar7139; 08-02-2012 at 11:37 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles