Asked  7 Months ago    Answers:  5   Viewed   40 times

If I have an array of say, some ID's of users. How could i do something like this:

$array = array(1,40,20,55,29,48);
$sql = "SELECT * FROM `myTable` WHERE `myField`='$array'";

Is there a simple way to do this, I thought about looping through array items and then building up one big "WHERE -- OR -- OR -- OR" statement but i thought that might be a bit slow for large arrays.

 Answers

82

Use IN:

$sql = "SELECT * FROM `myTable` WHERE `myField` IN (1,40,20,55,29,48)";

you can use implode(",", $array) to get the list together from the array.

Wednesday, March 31, 2021
 
laurent
answered 7 Months ago
19

You are doing too much actually:

$query = $dbh->prepare("SELECT * FROM students");
$query->execute();
$result = $dbh->query($query);

The problematic line is:

$result = $dbh->query($query);

Check with http://php.net/pdo.query, the parameter is a string, actually the SQL string you already use above, not the result value of a PDO::prepare() call.

For your simple query you can just do:

$result = $dbh->query("SELECT * FROM students");

Or if you like to prepare:

$query = $dbh->prepare("SELECT * FROM students");
$query->execute();
$result = $query;

The later is some boilerplate if you want to insert variables into the query, that's why you prepare it.


The next problem is with the foreach line:

foreach($result as $row);

You are terminating the loop immediately because of the semicolon ; at the end. Remove that semicolon so that the following angle-bracketed code-block becomes the body of the foreach-loop.

Wednesday, March 31, 2021
 
anjan
answered 7 Months ago
37

There's no way to do exactly what you're trying to. You could do another query first to fetch all the column names, then process them in PHP and build the second query, but that's probably more complex than just writing out the names that you want.

Or is there a reason this query needs to be dynamic? Will the table's structure change often?

Monday, June 14, 2021
 
kinske
answered 4 Months ago
48

PHP's timstamps are a simple integer, whereas MySQL's now() returns a datetime value. Most likely this will fix up the query:

SELECT ... WHERE user_regdate < unix_timestamp(now() - interval 7 day)) ...

Basically, without the unix_timstamp() call, you're comparing apples and oranges.

Wednesday, August 4, 2021
 
Shamoon
answered 3 Months ago
88

For your first query:

SELECT username
FROM online
WHERE time > NOW() - INTERVAL 15 MINUTE

And for your second:

SELECT username
FROM online
WHERE time BETWEEN NOW() - INTERVAL 60 MINUTE AND NOW() - INTERVAL 15 MINUTE

Both these queries assume that each user only appears once in the online table (and if this is indeed the case you should add a UNIQUE constraint to enforce that).

If a username can appear more than once in the table you just need to add DISTINCT after SELECT for your first query, but you need a slightly different approach for your second query:

SELECT DISTINCT username
FROM online
WHERE time > NOW() - INTERVAL 60 MINUTE
AND NOT EXISTS
(
    SELECT *
    FROM online
    WHERE time > NOW() - INTERVAL 15 MINUTE
)
Saturday, September 4, 2021
 
Robert M.
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :