php mysql where clause

Question

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.

Not sure what you're attempting to do with the line $randomids[] .= $row['wqpid'];, but the [] means that you are appending a value onto an array, so you just turned $randomids into an array, after initializing it as an empty string. Then, you use the operator .= which is a string concatenator. I have no idea what the end result of that is, but I don't think it's what you're trying to do. — Travesty3
– Travesty3, Commented Jan 17, 2012 at 17:17

Rudi Visser · Accepted Answer · 2012-01-17 17:14:59Z

1

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.

answered Jan 17, 2012 at 17:14

Rudi Visser

22.1k6 gold badges74 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jeroen · Accepted Answer · 2012-01-17 17:16:08Z

1

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

answered Jan 17, 2012 at 17:16

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

1 Comment

gary_col Over a year ago

Thanks, I'll try using one query

Collectives™ on Stack Overflow

php mysql where clause

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related