lovephp Posted August 14, 2017 Share Posted August 14, 2017 whatis is the procedure to add the where clause to this query? i need to display where catid = 1; $catid = 1; $rowperpage = 3; $offset = 0; $query = "SELECT count(id) AS allcount FROM posts"; $allcount = $db->query($query)->fetchColumn(); $qry = "SELECT * FROM posts ORDER BY id ASC LIMIT ?,? "; $stm = $db->prepare($qry); $stm->execute(array($offset,$rowperpage)); $results = $stm->fetchall(PDO::FETCH_ASSOC); do i have to add $catid = 1; $rowperpage = 3; $offset = 0; $query = "SELECT count(id) AS allcount FROM posts WHERE cat = :cat"; $stmt->bindParam(':cat', $catid, PDO::PARAM_STR); $allcount = $db->query($query)->fetchColumn(); $qry = "SELECT * FROM posts WHERE cat = :cat ORDER BY id ASC LIMIT ?,? "; $stm = $db->prepare($qry); $stm->execute(array($offset,$rowperpage)); $results = $stm->fetchall(PDO::FETCH_ASSOC); please guide Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/ Share on other sites More sharing options...
Jacques1 Posted August 14, 2017 Share Posted August 14, 2017 Let's skip the guess-what-my-problem-is game and just say what's wrong. If you aren't getting an error message, enable exceptions, turn your error reporting all the way up and either display the messages (during development) or log them (in production). You cannot mix named and positional parameters. Pick one. You also have to bind all parameters. 1 Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549746 Share on other sites More sharing options...
lovephp Posted August 14, 2017 Author Share Posted August 14, 2017 Let's skip the guess-what-my-problem-is game and just say what's wrong. If you aren't getting an error message, enable exceptions, turn your error reporting all the way up and either display the messages (during development) or log them (in production). You cannot mix named and positional parameters. Pick one. You also have to bind all parameters. hehehe guess-what-my-problem-is game i get u soi do bind to all but offset and perpage limit how am i to bind them? $query = "SELECT count(id) AS allcount FROM posts WHERE cat = :cat"; $stmt->bindParam(':catUrl', $url, PDO::PARAM_INT); $stmt->execute(); $allcount = $db->query($query)->fetchColumn(); $qry = "SELECT * FROM posts WHERE cat = :cat ORDER BY id ASC LIMIT ?,? "; $stm = $db->prepare($qry); $stm->execute(array($offset,$rowperpage)); $results = $stm->fetchall(PDO::FETCH_ASSOC); Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549747 Share on other sites More sharing options...
lovephp Posted August 14, 2017 Author Share Posted August 14, 2017 (edited) i get it like this LIMIT :rowperpage OFFSET :offset ? Edited August 14, 2017 by lovephp Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549748 Share on other sites More sharing options...
lovephp Posted August 14, 2017 Author Share Posted August 14, 2017 i did this $query = "SELECT count(id) AS allcount FROM posts WHERE cat :cat"; $stmt->bindParam(':cat', $catid, PDO::PARAM_INT); $stmt->execute(); $allcount = $db->query($query)->fetchColumn(); $qry = "SELECT * FROM posts WHERE cat :cat ORDER BY id ASC LIMIT LIMIT :rowperpage OFFSET :offset"; $stm = $db->prepare($qry); $stm->bindParam(':cat', $catid, PDO::PARAM_INT); $stm->bindParam(':rowperpage', $rowperpage, PDO::PARAM_INT); $stm->bindParam(':offset', $offset, PDO::PARAM_INT); $stm->execute(); $results = $stm->fetchall(PDO::FETCH_ASSOC); and i get error Fatal error: Call to a member function bindParam() on a non-object Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549749 Share on other sites More sharing options...
Jacques1 Posted August 14, 2017 Share Posted August 14, 2017 Are you waiting for my permission? Just do it. PHP will tell you when it's wrong. The first statement is broken. You aren't creating a prepared statement at all, then you try to bind values to the nonexistent $stmt variable, and then you suddenly decide to make a plain query instead. This goes back to what I said earlier: Learn PDO. Don't just copy and paste random code snippets you found somewhere. 1 Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549750 Share on other sites More sharing options...
Sepodati Posted August 14, 2017 Share Posted August 14, 2017 Yeah, and looking at your first two lines, why do you think you get that error? 1 Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549751 Share on other sites More sharing options...
ginerjm Posted August 14, 2017 Share Posted August 14, 2017 Why don't you look at a manual to learn how to write sql and how to use PDO? You are just throwing guesses into code and none of it is correct and you don't even have a clue as to what you are doing. For example - do execute a query in the first example and then you use something called $db to do another query (????) to fetch a column. Do you know what any of that code is used for? Hint: You write a query and assign it to a var. Then you prepare the query using that var and assign that to a query statement variable. Then you use the query statement variable in a call to Execute and THEN you use that same variable to do a fetch of the result row. Now rather than take my hint and slap it into another feeble attempt, read up on what I just told you and fully understand it. It's not that hard. 1 Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549753 Share on other sites More sharing options...
lovephp Posted August 15, 2017 Author Share Posted August 15, 2017 Are you waiting for my permission? Just do it. PHP will tell you when it's wrong. The first statement is broken. You aren't creating a prepared statement at all, then you try to bind values to the nonexistent $stmt variable, and then you suddenly decide to make a plain query instead. This goes back to what I said earlier: Learn PDO. Don't just copy and paste random code snippets you found somewhere. doing that now. now i be serious Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549756 Share on other sites More sharing options...
lovephp Posted August 15, 2017 Author Share Posted August 15, 2017 Why don't you look at a manual to learn how to write sql and how to use PDO? You are just throwing guesses into code and none of it is correct and you don't even have a clue as to what you are doing. For example - do execute a query in the first example and then you use something called $db to do another query (????) to fetch a column. Do you know what any of that code is used for? Hint: You write a query and assign it to a var. Then you prepare the query using that var and assign that to a query statement variable. Then you use the query statement variable in a call to Execute and THEN you use that same variable to do a fetch of the result row. Now rather than take my hint and slap it into another feeble attempt, read up on what I just told you and fully understand it. It's not that hard. going through it now mate. Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549757 Share on other sites More sharing options...
ginerjm Posted August 15, 2017 Share Posted August 15, 2017 Its about time you became serious. If I had know you weren't already serious, I wouldn't have bothered to reply to your post. And - I am not your mate Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549762 Share on other sites More sharing options...
lovephp Posted August 16, 2017 Author Share Posted August 16, 2017 (edited) ok this time serious not guessing i have got rest of it working but running into something which i guess you guys will know better than me for sure i am getting results if i query it like $results = $db->query('SELECT * FROM blog_posts WHERE catID = '.$cat.' ORDER BY postID DESC LIMIT '.$row.','.$records_per_page.'')->fetchAll(); but when i use $cat = $_POST['catid']; $row = $_POST['row']; global $records_per_page; $sth = $db->prepare("SELECT * from blog_posts WHERE catID = ? ORDER BY postID ASC LIMIT ?,?"); $sth->execute(array($cat,$row,$rowperpage)); $sth->execute(); $rows = $sth->fetchall(PDO::FETCH_ASSOC); even with Bind like $sth = $db->prepare("SELECT * from blog_posts WHERE catID = :catID ORDER BY postID ASC LIMIT :row, :records_per_page"); $sth->bindParam(':catID', $cat, PDO::PARAM_INT); $sth->bindParam(':row', $row, PDO::PARAM_INT); $sth->bindParam(':records_per_page', $records_per_page, PDO::PARAM_INT); $sth->execute(); all i get is blank or 0 value but why $results = $db->query('SELECT * FROM blog_posts WHERE catID = '.$cat.' ORDER BY postID DESC LIMIT '.$row.','.$records_per_page.'')->fetchAll(); fetches records without issue? Edited August 16, 2017 by lovephp Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549834 Share on other sites More sharing options...
lovephp Posted August 16, 2017 Author Share Posted August 16, 2017 i also tried BindParam but same no records gets fetched Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549835 Share on other sites More sharing options...
Solution lovephp Posted August 16, 2017 Author Solution Share Posted August 16, 2017 oh god my mistake i should have made this $results= $sth->fetchall(PDO::FETCH_ASSOC); not $rows = $sth->fetchall(PDO::FETCH_ASSOC); now fixed Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549836 Share on other sites More sharing options...
Sepodati Posted August 16, 2017 Share Posted August 16, 2017 Where's your error reporting? Things don't generally fail without an error. For the first one, you call global $records_per_page and then bind $rowperpage. Then execute the query all over again with no parameters. This is you being serious? You still have to get the records in either case, you know? execute() doesn't just magically make the query results (if it ever runs successfully) just appear. Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549837 Share on other sites More sharing options...
lovephp Posted August 16, 2017 Author Share Posted August 16, 2017 Where's your error reporting? Things don't generally fail without an error. For the first one, you call global $records_per_page and then bind $rowperpage. Then execute the query all over again with no parameters. This is you being serious? You still have to get the records in either case, you know? execute() doesn't just magically make the query results (if it ever runs successfully) just appear. fixed it all now all working fine Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549840 Share on other sites More sharing options...
Sepodati Posted August 16, 2017 Share Posted August 16, 2017 lol... yeah, I bet. 1 Quote Link to comment https://forums.phpfreaks.com/topic/304603-how-am-i-to-add-the-where-clause/#findComment-1549845 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.