how am i to add the where clause

[lovephp]

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

[Jacques1]

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.

[lovephp]

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

[lovephp]

i get it like this LIMIT :rowperpage OFFSET :offset ?

Edited August 14, 2017 by lovephp

[lovephp]

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

[Jacques1]

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.

[Sepodati]

Yeah, and looking at your first two lines, why do you think you get that error?

[ginerjm]

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.

[lovephp]

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

[lovephp]

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.

[ginerjm]

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

[lovephp]

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

[lovephp]

i also tried BindParam but same no records gets fetched

[lovephp]

oh god my mistake i should have made this

 

$results= $sth->fetchall(PDO::FETCH_ASSOC);

 

not

 

$rows = $sth->fetchall(PDO::FETCH_ASSOC);

 

now fixed

[Sepodati]

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.

[lovephp]

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

[Sepodati]

lol... yeah, I bet.