CODEWITHSUNDEEP
phpmysqlpdowhere-clause
gary_col
gary_col

Reputation: 33

php mysql where clause

try {
    $dbh = new PDO('mysql:host=localhost;dbname=wqposts', 'root', '');
    $query = "SELECT wqpid FROM threads WHERE posted ='0'";
    $randomids = '';
    foreach ($dbh->query($query) AS $row) {

      $randomids[] .= $row['wqpid'];

    }

  } catch (PDOException $exception) {
    echo "Connection error: " . $exception->getMessage();
  }
  $post = array_rand($randomids, 1);


  try {
    $dbh = new PDO('mysql:host=localhost;dbname=wqposts', 'root', '');
    $stmt = $dbh->prepare("SELECT * FROM threads WHERE wqpid = :wqpid");
    $stmt->bindParam(':wqpid', $post, PDO::PARAM_INT);
    $stmt->execute();

    while ($row = $stmt->fetch()) {
//output html
}

Somehow WHERE posted ='0' in the first statement isn't working because results in the second statement where posted = 1 are showing.

If I run select * from threads where posted=0; in mysql I see 400-500 results which is correct. In case it's needed posted is tinyint with length of 1.

Upvotes: 1

Views: 339

Answers (2)

jeroen
jeroen

Reputation: 91734

Not a direct answer to your question, but you don't need two queries just to select a random row.

If you want to do it in one query:

$query = "SELECT * FROM threads WHERE posted=0 ORDER BY rand() LIMIT 1";

Upvotes: 1

Rudi Visser
Rudi Visser

Reputation: 21979

Your code will not work as expected, no, because array_rand does not return a random value but instead a random key in the array, which will be 0-400~ depending on the amount of results.

Your code should be as follows:

$stmt->bindParam(':wqpid', $randomids[$post], PDO::PARAM_INT);

This will ensure that the actual ID is being passed over, not the index of the ID within the array.

Upvotes: 1

Related Questions