simona6 Posted February 11, 2020 Share Posted February 11, 2020 $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? Quote Link to comment Share on other sites More sharing options...
Barand Posted February 11, 2020 Share Posted February 11, 2020 Have you checked the error log? Quote Link to comment Share on other sites More sharing options...
simona6 Posted February 11, 2020 Author Share Posted February 11, 2020 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 11, 2020 Share Posted February 11, 2020 (edited) 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 Quote Link to comment Share on other sites More sharing options...
simona6 Posted February 11, 2020 Author Share Posted February 11, 2020 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? Quote Link to comment Share on other sites More sharing options...
simona6 Posted February 11, 2020 Author Share Posted February 11, 2020 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. Quote Link to comment Share on other sites More sharing options...
kicken Posted February 11, 2020 Share Posted February 11, 2020 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. Quote Link to comment Share on other sites More sharing options...
simona6 Posted February 11, 2020 Author Share Posted February 11, 2020 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted February 11, 2020 Share Posted February 11, 2020 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() Quote Link to comment Share on other sites More sharing options...
simona6 Posted February 11, 2020 Author Share Posted February 11, 2020 This is PDO. Thanks for the note though. Quote Link to comment Share on other sites More sharing options...
simona6 Posted February 11, 2020 Author Share Posted February 11, 2020 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. Quote Link to comment Share on other sites More sharing options...
simona6 Posted February 12, 2020 Author Share Posted February 12, 2020 $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? Quote Link to comment Share on other sites More sharing options...
Barand Posted February 12, 2020 Share Posted February 12, 2020 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"; } } Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 12, 2020 Share Posted February 12, 2020 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? Quote Link to comment Share on other sites More sharing options...
simona6 Posted February 12, 2020 Author Share Posted February 12, 2020 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.