Jump to content

how am i to add the where clause


lovephp
Go to solution Solved by lovephp,

Recommended Posts

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

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

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.

  • Like 1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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

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.