Multi Query PDO causing blank errors

[simona6]

$params = array();
  $sqlFilters = "";
  if(isset($filtertown)) { $sqlFilters .= " AND town =:town"; $params["town"] = $filtertown; }
  if(isset($filterinstagram) && !empty($filterinstagram)) { $sqlFilters .= " AND instagram >=:instagram"; $params["instagram"] = $filterinstagram; }
  if(isset($filtertwitter) && !empty($filtertwitter)) { $sqlFilters .= " AND twitter >=:twitter"; $params["twitter"] = $filtertwitter; }
  if(isset($filterfacebook) && !empty($filterfacebook)) { $sqlFilters .= " AND facebook >=:facebook"; $params["facebook"] = $filterfacebook; }  
  if(isset($filterpinterest) && !empty($filterpinterest)) { $sqlFilters .= " AND pinterest >=:pinterest"; $params["pinterest"] = $filterpinterest; }  
  if(isset($filteryoutube) && !empty($filteryoutube)) { $sqlFilters .= " AND youtube >=:youtube"; $params["youtube"] = $filteryoutube; } 

      if (isset($userstatus))
      {
      if ($userstatus == "active") { $sqlFilters .=" AND profilecomplete = 'yes'";}
      if ($userstatus == "incomplete") { $sqlFilters .=" AND profilecomplete IS NULL";}
      if ($userstatus == "block") { $sqlFilters .=" AND status = 'block'";}
      if ($userstatus == "cancel") { $sqlFilters .=" AND status = 'cancel'";}
      if ($userstatus == "showall") { $sqlFilters .=" AND status IS NULL";}    
      }
      if (isset($search))
      {
      if (isset($searchfirstname)) { $sqlFilters .= " AND firstname LIKE :searchfirstname"; $params["searchfirstname"] = $searchfirstname; }
      if (isset($searchsurname)) { $sqlFilters .= " AND surname LIKE :searchsurname"; $params["searchsurname"] = $searchsurname; }
      }
      
  if (isset($sort))
  {  
    $sqlStart = "SELECT * FROM users WHERE usertype = 'influencer'"; 
    $sqlEnd = " ORDER BY `$col` $order LIMIT $offset, $rowsPerPage;"; 
    $sql = $sqlStart.$sqlFilters.$sqlEnd;
    $sqlfiltercount = $sqlStart.$sqlFilters;
    
    $result = $pdo->prepare($sql); $result->execute($params);
    $resultfiltercount = $pdo->prepare($sqlfiltercount); $resultfiltercount->execute($params);
    
    $num_users = $result->rowCount();
    $num_usersfiltered = $resultfiltercount->rowCount();
  }
  else
  {
  $sqlStart = "SELECT * FROM users WHERE usertype = 'influencer'"; 

  $sqlEnd = "  LIMIT $offset, $rowsPerPage;"; 
  $sql = $sqlStart.$sqlFilters.$sqlEnd;
  
  // sqlfiltercount removes the LIMIT to show ALL users on this userstatus filter
  $sqlfiltercount = $sqlStart.$sqlFilters;
  
  $result = $pdo->prepare($sql); 
  $result->execute($params);
  $num_users = $result->rowCount();
  
  echo "$sqlfiltercount";
  $resultfiltercount = $pdo->query($sqlfiltercount);
  $num_usersfiltered = $resultfiltercount->rowCount();
  }

Hi there.

I am writing a tool that displays a list of users.  The page has pagination, set at 99 per page.

This script combines the various filters added by the administrator, but if they select Active, Blocked or a few others, it bypasses that and runs the bottom {} brackets of query.

You have the $num_users which is the total on screen at the time, plus the secondary count of TOTAL users in the system.  This count is then show by 'users' on the page.

 

This runs fine on my local machine running php7.2, but on the live 7.2 server, it's showing nothing.  No errors.

If I comment out the $resultfiltercount lines, the page loads, but of course no count.

Why might it be getting upset at that, and why would it not throw errors on screen at me?

[Barand]

Have you checked the error log?

[simona6]

Yes.  Nothing showing in there.  I've raised it with our hosts to see if they can spot anything that the Apache Errors are not showing.

It's not showing errors on screen my end either.

Both versions are PHP7.2.27.

[mac_gyver]

46 minutes ago, simona6 said:

$pdo->query($sqlfiltercount);

the code at the end is using the query() method to execute a prepared query that has named place-holders in it. the only difference in the last two sections of code is the $sort - ORDER BY ... part of the query. instead of duplicating code (which is where the ->query() vs ->prepare() and ->execute() mistake is at), just conditionally include the ORDER BY part and have a single instance of the rest of the code.

do you have any error handling (exceptions would be a good choice) for the pdo statements that can fail?

also, you should be using a SELECT COUNT(*) ... query to get the total number of matching rows for pagination. you are currently SELECTing all the columns and rows of matching data just to get the row count.

your name LIKE search doesn't have any apparent wild-card characters, and should just use an equal = comparison.

 

Edited February 11, 2020 by mac_gyver

[simona6]

I thought query() was used when it is not prepared.  That's when you prepare().

How would you write that final bit of code then to make it work?

[simona6]

BTW... I can see what you mean about the $sort.  I can perhaps append that into just one query, but the second bit must be in two queries as part of it is down to the LIMIT per page, and the other count is TOTAL.

Are you able to assist?  btw nice to get help from a fellow UK person.

[kicken]

1 hour ago, simona6 said:

I thought query() was used when it is not prepared.  That's when you prepare().

How would you write that final bit of code then to make it work?

query may be used when there is no need to prepare because there are no bound parameters.  In your cases though your $sqlFilters variable is being used in the query and it does potentially have bound parameters defined in it.  As such, you need to use prepare instead of query.

Note you can always use prepare/execute if you want and not worry about the difference.  query is just a handy shortcut for simple queries.

 

[simona6]

I did try it with Prepare and using the Array in the Execute, but the same problem occurs.

I also don't understand why it works on my laptop but not on the live server - when both are the same PHP versions.

[Barand]

34 minutes ago, kicken said:

Note you can always use prepare/execute if you want and not worry about the difference.  query is just a handy shortcut for simple queries

NOTE TO READERS:

The above statement applies only to those using PDO. If you are using mysqli then prepared statements are a completely different ballpark to those using query()

[simona6]

This is PDO.  Thanks for the note though.

[simona6]

I'm keen to learn how you might all code this differently, so I can understand an improved way to do it.

I am genuinely stuck.  I thought I had done a good job, as appending submitted variables to a query is new to me - only learnt it in the last few months.  We need to only show the first rowsPerPage at the start, but within that query, we also need to show ALL those found where their userstatus is as submitted.  ie.  to see all Blocked, or all Active.

[simona6]

  $params = array();
  $sqlFilters = "";
  if(isset($filtertown)) { $sqlFilters .= " AND town =:town"; $params["town"] = $filtertown; }
  if(isset($filterinstagram) && !empty($filterinstagram)) { $sqlFilters .= " AND instagram >=:instagram"; $params["instagram"] = $filterinstagram; }
  if(isset($filtertwitter) && !empty($filtertwitter)) { $sqlFilters .= " AND twitter >=:twitter"; $params["twitter"] = $filtertwitter; }
  if(isset($filterfacebook) && !empty($filterfacebook)) { $sqlFilters .= " AND facebook >=:facebook"; $params["facebook"] = $filterfacebook; }  
  if(isset($filterpinterest) && !empty($filterpinterest)) { $sqlFilters .= " AND pinterest >=:pinterest"; $params["pinterest"] = $filterpinterest; }  
  if(isset($filteryoutube) && !empty($filteryoutube)) { $sqlFilters .= " AND youtube >=:youtube"; $params["youtube"] = $filteryoutube; } 

      if (isset($userstatus))
      {
      if ($userstatus == "active") { $sqlFilters .=" AND profilecomplete = 'yes'";}
      if ($userstatus == "incomplete") { $sqlFilters .=" AND profilecomplete IS NULL";}
      if ($userstatus == "block") { $sqlFilters .=" AND status = 'block'";}
      if ($userstatus == "cancel") { $sqlFilters .=" AND status = 'cancel'";}
      if ($userstatus == "showall") { $sqlFilters .=" AND status IS NULL";}    
      }
      if (isset($search))
      {
      if (isset($searchfirstname)) { $sqlFilters .= " AND firstname LIKE :searchfirstname"; $params["searchfirstname"] = $searchfirstname; }
      if (isset($searchsurname)) { $sqlFilters .= " AND surname LIKE :searchsurname"; $params["searchsurname"] = $searchsurname; }
      }
      
  $sqlStart = "SELECT * FROM users WHERE usertype = 'influencer'"; 
  
  // only use $sqlSort to sort columns if it is selected, else add a blank to the query
  if (isset($sort)) { $sqlSort = " ORDER BY `$col` $order ";} else { $sqlSort = "";}
  
  $sqlEnd = "  LIMIT $offset, $rowsPerPage;"; 
  
  $sql = $sqlStart.$sqlFilters.$sqlSort.$sqlEnd;
  
  // sqlfiltercount removes the LIMIT to show ALL users on this userstatus filter
  $sqlfiltercount = $sqlStart.$sqlFilters;
  
  $result = $pdo->prepare($sql); 
  $result->execute($params);
  $num_users = $result->rowCount();
  
  echo "$sqlfiltercount";
  $resultfiltercount = $pdo->prepare($sqlfiltercount);
  $resultfiltercount->execute($params);
  $num_usersfiltered = $resultfiltercount->rowCount();

This is still showing a blank on our live server.

I've added the Sort only if it is submitted, but within the same query, and it works on Localhost.
Still unclear why it's not showing anything, simply because of $resultfiltercount.

Any ideas?

[Barand]

13 hours ago, simona6 said:

I'm keen to learn how you might all code this differently,

Here's an example

//
// set default values
//
$senderid = $_GET['sender'] ?? '0';
$recipid = $_GET['recipient'] ?? '0';
$page = $_GET['page'] ?? 1;
if (isset($_GET['btnSub'])) $page = 1; // new search was requested
$total_recs = 0;
$search_results = '';

if (isset($_GET['page'])) {      // was form submitted?
    //
    // build query WHERE clause
    //
    $whereclause = '';
    $where = [];
    $params = [];
    if ($senderid != 0) {
        $where[] = "(sender_id = ?)";
        $params[] = $senderid;
    }
    if ($recipid != 0) {
        $where[] = "(recipient_id = ?)";
        $params[] = $recipid;
    }
    if ($where) {
        $whereclause = "WHERE " . join(' AND ', $where);
    }
    
    // get total records from the search
    $count = $db->prepare("SELECT COUNT(*)
                            FROM notes n 
                                   INNER JOIN
                                 user s ON n.sender_id = s.user_id
                                   INNER JOIN
                                 user r ON n.recipient_id = r.user_id
                            $whereclause");
    $count->execute($params);
    $total_recs = $count->fetchColumn();
    
    // now get the search results
    if (intval($page) == 0) $page = 1;
    $offset = ($page - 1) * PERPAGE;
    $limit = PERPAGE;
    
    $stmt = $db->prepare("SELECT n.id
                                 , s.username as sender
                                 , r.username as recipient
                                 , n.message
                                 , date_format(time_sent, '%a %b %D %l:%i %p') as sent
                            FROM notes n 
                                   INNER JOIN
                                 user s ON n.sender_id = s.user_id
                                   INNER JOIN
                                 user r ON n.recipient_id = r.user_id
                            $whereclause
                            ORDER BY time_sent
                            LIMIT $offset, $limit     
                            ");
    $stmt->execute($params);
    foreach ($stmt as $rec) {
        $search_results .= "
            <div class='result'>
                <div class='fromto'>
                     <div class='label'>From:</div>". esc($rec['sender']) . "<br>
                     <div class='label'>To:</div>". esc($rec['recipient']) . "<br><br>
                     <div class='label'>Sent:</div>". esc($rec['sent']) . "<br>
                </div>
                <div class='msg'>". esc($rec['message']) . "</div>
                <div style='clear:both'></div>
            </div>\n";
    }
}

 

[mac_gyver]

2 hours ago, simona6 said:

Any ideas?

 

17 hours ago, mac_gyver said:

do you have any error handling (exceptions would be a good choice) for the pdo statements that can fail?

also, are php's error related settings set to report and either display or log all errors?

[simona6]

Ooo thanks.  BTW the reason why it wasn't working or generating errors was a RAM issue.

It appears that the RAM was set wrong... it showed nothing.  As soon as I made it 128mb, it all loaded.