Jump to content

Multi Query PDO causing blank errors


simona6

Recommended Posts

$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?

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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. :)

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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()

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  $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?

Link to comment
Share on other sites

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";
    }
}

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.