Need help with multiple PDO queries (creating web statistic)

[tomailas]

Hi Guys,

 

(sorry for childish  question - I am not a PHP developer - more likely hobbits, but trying hard and did research already - unfortunately without luck as I cannot see online example)

 

I am having problems with PDO while creating web stats page (to be honest it is very difficult for me to explain what I am trying to achieve but here we go..).

 

I have created a class which contains function below:

 

 // Function: getWebStats [Getting All ]
  public static function getWebStats( $numRows=1000000,  $order="visitedOn DESC" ) {
    $conn = new PDO( DB_DSN, DB_UNAME DB_PASS );
    $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM web_stats ORDER BY " . mysql_real_escape_string($order) . " LIMIT :numRows";
    $st = $conn->prepare( $sql );
    $st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
    $st->execute();
    $list = array();

    while ( $row = $st->fetch() ) {
      $record = new cmsStats( $row );
      $list[] = $record;
    }
    // Now get the total number of stats that matched the criteria    
    $sql = "SELECT FOUND_ROWS() AS totalRows";
    $totalRows = $conn->query( $sql )->fetch();
    
    // Now get the total number of clicks that matched the criteria    
    $sql = "SELECT visitedPageTitle, SUM(clickCount) AS totalClicks FROM web_stats GROUP BY visitedPageTitle";
    $totalClicks = $conn->query( $sql )->fetch();
    
    // Close connection
    $conn = null;
    // Return data array
    return ( array ( "results" => $list, "totalRows" => $totalRows[0], "totalClicks" => $totalClicks[0] ) );

 

I am calling this class:

 

function viewStats() {
  $results = array();
  // getList
  $data = cmsStats::getWebStats();
  $results['ListWebStats'] = $data['results'];
  $results['totalRows'] = $data['totalRows'];

  $results['totalClicks'] = $data['totalClicks'];

 

 

And trying to insert data below:

 

<?php foreach ( $results['ListWebStats'] as $ListWebStats ) { ?>

<ul class="chartlist">
      <li>
        <a href="<?php echo $ListWebStats->visitedURL?>"><?php echo $ListWebStats->visitedPageTitle?></a>
        <span class="count">echo $results['totalClicks']</span>   // This is there I am struggling, as it doesn’t returns data correctly
        <span class="index" style="width: 42%">(42%)</span>
      </li>
    </ul>

 

Problem:

 

I know that my code isn't correct (my function needs to be reviewed).

 

I would be thankful if you could point me to right direction or tell me what I am doing wrong.

 

Many thanks!

[ginerjm]

Not to scare you off or tell you what to do, but why would you choose to begin your php development with classes and their compications? If you're trying to learn php, simply begin with your idea, layout your work plan, develop some needed functions and put it together. IMHO, classes are not everyday things, at least not at the beginning of your learning cycle. Plus - classes are great when you have plans to implement the same actions repeatedly, as in a large project/application, but not worth the effort for a single usage. At least not until you become proficient at writing them.

 

As for your example - it is rather dis-joint. Do you have error checking turned on to pick up any errors that may have occurred in the class functions?

Edited June 25, 2013 by ginerjm

[tomailas]

Hi Actually I managed to create full CMS based on online examples and etc. however stuck with PDO as was more familiar with SQL queries previously (but obviously – PDO is correct way to go). By profession - I am IT engineer (not developer). Yes I have switched logging on my server. I would appreciate if somebody could let me know what is wrong with my class/function and how I could correct this - it seems that many googler's looking for similar to my questions.

[boompa]

First thing to do is check the value of $data here:

 

 

function viewStats() {
  $results = array();
  // getList
  $data = cmsStats::getWebStats();
  $results['ListWebStats'] = $data['results'];
  $results['totalRows'] = $data['totalRows'];
  $results['totalClicks'] = $data['totalClicks'];

 

Using print_r(), var_dump(), or var_export(). See if it contains what you think it should.

 

I also don't think you should be mixing PDO with mysql_real_escape_string. Your ORDER BY variable should also be part of the prepared query.

[tomailas]

Thanks for response boompa,

 

I have checked this:

 

function viewStats() {
  $results = array();
  // getList
  $data = cmsStats::getWebStats();
  $results['ListWebStats'] = $data['results'];
  $results['totalRows'] = $data['totalRows'];
  $results['totalClicks'] = $data['totalClicks'];

 

and it looks correct.

 

I am 100% that problem is with my PDO parts, as data being returned, but is not calculating properly clickCounts.

[rwhite35]

Couple things with your SQL

// $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM web_stats ORDER BY " . mysql_real_escape_string($order) . " LIMIT :numRows";
$sql = "SELECT * FROM web_stats ORDER BY vistedOn DESC LIMIT 1000000";

/*
* Unless visitedOn and $numRows are dynamic, I wouldn't make them variables.  Also,  SQL_CALC_FOUND_ROWS gives you 
* the total rows the query would have returned if the total of db records were larger than 1000000(in your case).
*/    
 $st = $conn->prepare( $sql );
 $st->execute();

Couple additional comments.  PDO optimizes input for you, so if you're using the prepare, bindParam, execute machinery, also using mysql_real_escape_string isn't necessary. That part of the appeal to PDO. 

 

And this is nit pic I know, but inside the Class, what you refer to as a "function" is actually a Class Method.  You can have a function inside a Method.  But getWebStats is actually a Class Method. 

 

Any rate, try the edited query statement and see what errors or results you get from that. 

Good Luck.

 

EDIT:  I should ask, do you really mean to output 1000000 records?  Because that's what you're asking the query to do.  LIMIT the total records returned to N.

Edited June 25, 2013 by rwhite35

[tomailas]

Thanks, but there is nothing wrong with:

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM web_stats ORDER BY " . mysql_real_escape_string($order) . " LIMIT :numRows";

As it is returning what I need perfectly, however I am not sure if I need this statment at all, maybe I should replace it somehow with - only:

$sql = "SELECT visitedPageTitle, SUM(clickCount) AS totalClicks FROM web_stats GROUP BY visitedPageTitle";

As I said - PDO is great, but is no unclear to me.

 

Without messing with PDO I can always do something like this:

            $query = "SELECT ip, SUM(count) FROM table GROUP BY ip";

            $result = mysql_query($query) or die(mysql_error());

            echo "<table border='1' width='210' bordercolor='#232323' cellpadding='1' cellspacing='2'>
            <tr>
            <th>IP address</th>
            <th>Total clicks</th>
            </tr>";


           while($row = mysql_fetch_array($result)){
           echo "<tr>";
             echo "<td align='center'>". $row['ip']. "</td>";
              echo "<td align='center'>". $row['SUM(count)']. "</td>";
           echo "</tr>";
           }
           echo "</table>";

           mysql_close($con);

But Anybody could tell me how to convert this query to PDO (as per my original post)?