0

Trying to fetch columns for my project, never done so in PDO, tried looking at the documentations but it didn't really help.

  $sql = $CONNECT_TO_DATABASE->query("SELECT COUNT(*) FROM comments");
    $result = $sql->fetch(PDO::FETCH_ASSOC);
    $r = $result->fetchColumn();

What have I done wrong?

Fatal error: Call to a member function fetchColumn() on a non-object in C:\xampp\htdocs\project\events.php on line 6

$r = $result->fetchColumn();

Thanks!

Connection:

<?php
/**
* Connect to Database
* @Author Jony <artemkller@gmail.com>
**/
    ##################CHANGE THIS####################
        /**                                         #   
        * MYSQL HOST HERE (Example: localhost)      #
        **/                                         #
                                                    #
        DEFINE ("MYSQL_HOST", "localhost");         #
                                                    #
        /**                                         #
        * MYSQL USER HERE (Example: root)           #
        **/                                         #
                                                    #
        DEFINE ("MYSQL_USER", "root");              #
                                                    #   
        /**                                         #       
        * MYSQL PASSWORD HERE (Example: password)   #
        **/                                         #           
                                                    #   
        DEFINE ("MYSQL_PASSWORD", "");              #
                                                    #   
    #################################################

    # Connect to the database using PDO.
    $CONNECT_TO_DATABASE = new PDO('mysql:host='.MYSQL_HOST.';dbname=project', MYSQL_USER, MYSQL_PASSWORD);

    # See if theres an error while connecting to the database
    try {
        $CONNECT_TO_DATABASE->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch(PDOException $ERROR_MESSAGE)
    {
        echo 'ERROR: ' . $ERROR_MESSAGE->getMessage();
    }


?>
Jony Kale
  • 189
  • 1
  • 5
  • 17

2 Answers2

6

Your $result variable is a mere array, and doesn't have no methods.

You don't need second fetch call. Either use fetch or fetchColumn. You need a latter one in your case:

$sql = $CONNECT_TO_DATABASE->query("SELECT COUNT(*) FROM comments");
$count = $sql->fetchColumn();

By the way, don't you think that $CONNECT_TO_DATABASE is a bit long? What about making it $pdo instead?

As you posted you connection code, here is how it have to be:

# Set PDO connection options
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
# Connect to the database using PDO.
$dsn = 'mysql:host='.MYSQL_HOST.';dbname=project';
$pdo = new PDO($dsn, MYSQL_USER, MYSQL_PASSWORD);

That's all. PHP can already handle that echo 'ERROR: business. no intervention required

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
0

Try this assuming your construction for PDO is accurate. 

 $sql = $CONNECT_TO_DATABASE->prepare("SELECT COUNT(*) FROM comments"); // Prepare your query with PDO
 $sql->execute(); // Once it is prepared execute it

    if ($sql) { // Check if $sql is executed will return TRUE or FALSE
        if ($sql->rowCount() > 0 ) { // If you get any rows back then
           $r = $sql->fetchColumn(); // Get your results
        }
    }
Rixhers Ajazi
  • 1,303
  • 11
  • 18
  • Not my DV but it's worth noting that you don't really need prepared statements if you don't have parameters and the query will run once. Additionally, with `PDO::ERRMODE_EXCEPTION` you can simplify error checking a lot: try/catch and you're done. – Álvaro González Apr 16 '13 at 11:51
  • 1
    Well yes try/catch, I understand that, but in his code he didn't have that, and I typed this up before seeing his connection options so I just typed up a quick way to check if it is a go or no go. And yea you have a point with prepare, but its "always" good to get used to preparing your queries. Especially since the guy said he was new with PDO in his OP, so why not try to take him down the right path? :) – Rixhers Ajazi Apr 16 '13 at 11:53
  • count() will always return a row, so, `if` become useless and makes code unnecessarily bloated. $sql->fetchColumn() won't return anything as previous fetch will snatch the only row returned. – Your Common Sense Apr 16 '13 at 11:55
  • 1
    @ÁlvaroG.Vicario **PLEASE** do not advertise this bad practice of using try catch just to handle an error *message*. http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15990858 – Your Common Sense Apr 16 '13 at 12:00
  • No offense but you could show me where it says count will always return a row? in the PDO doc I do not see that. And as for the fetch and fetchColumn I never heard of that nor have I tried to fetch using two different functions calls, so you may or may not be correct I don't know. But I would like to read up on the rowCount part that you mentioned as I can't see that in the DOCs. - Thanks – Rixhers Ajazi Apr 16 '13 at 12:00
  • @YourCommonSense - You're not a newbie so I must be missing something. What's your suggestion? Check the return value of every single PDO method you call or let the app crash at any random point whenever the DB server goes down? (Your answer just warns against *printing* the error message, something I haven't suggested.) – Álvaro González Apr 16 '13 at 12:18
  • @ÁlvaroG.Vicario you didn't, my bad. It's just implied too often, every time you see a catch - you see echo or die in the next line. So, I just overlooked. But yeah, in all applications I am working on a failed SQL means halt for the whole app, as behavior hardly can be predicted otherwise. Try-catch is used, but relatively seldom. And of course not to handle every single query but rather to handle certain code block or a service call. – Your Common Sense Apr 16 '13 at 12:52