Search MySQL database with an array

Question

I hope I am doing this correctly..

Using this code I am grabbing records from one table and storing them in an array:

$result = mysql_query("SELECT username FROM '$tbl_name'");
$storeArray = Array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $storeArray[] =  $row['username'];  
}

The result of the query is (using: print_r($storeArray);):

Array ( [0] => username1 [1] => username2 [2] => username3)

I want to use this array to search another table. Improvised code would be:

SELECT * FROM $users_tbl WHERE username = username1, username2, username3

Could anyone help me generate the MySQL query that would do this?

I hope this is clear enough..

`$query = "SELECT * FROM $users_tbl WHERE username IN (".implode(",",$storeArray).");"` - But ensure that your `$storeArray` values are properly sanitized / escaped or you're vulnerable to SQL Injections. — Rem.co, Jun 18 '12 at 16:14
@RemcoOverdijk Using your code I get the error: `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE username IN (username1,username2,username3)' at line 1` — user1449737, Jun 18 '12 at 16:17
Where does the column `following` come from? And typically you don't have quotes around table names. — Nick Rolando, Jun 18 '12 at 16:18
@user1449737 You're right, all values have to be quoted before using them in an `IN` clause, making it impossible to use a single `implode()` without further processing. (And as I've mentioned before you have to sanitize/escape them anyway). So I was wrong, Ignore my comment! — Rem.co, Jun 18 '12 at 16:20

score 0 · Answer 1 · answered Jun 18 '12 at 16:14

0

You need to use JOIN, faster and easier.

answered Jun 18 '12 at 16:14

HBv6

3,487
4
30
43

Nick Rolando · Accepted Answer · 2012-06-18T17:25:39.083

0

You can do this in one query using a subquery:

"SELECT * FROM `$users_tbl` WHERE `username` IN (SELECT username FROM `$tbl_name`)"

Also note the mysql extension is being deprecated, and it is recommended to use mysqli

edited Jun 18 '12 at 17:25

answered Jun 18 '12 at 16:16

Nick Rolando

25,879
13
79
119

score 0 · Answer 3 · answered Jun 18 '12 at 16:16

0

Perhaps

SELECT * FROM $tbl_name
JOIN $users_tbl
ON $tbl_name.username = $users_tbl.following

answered Jun 18 '12 at 16:16

Rey Gonzales

836
1
8
17

score 0 · Answer 4 · edited May 23 '17 at 12:12

0

foreach($storeArray as $value) {
    $inClause[] = '"'.filter_var($value,FILTER_SANITIZE_STRING).'"';
    // or apply your own -better- sanitizing/escaping instead of the filter_var
}
$query = "SELECT * FROM $users_tbl WHERE username IN (".implode(",",$inClause).");";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
    //Do something usefull with the rows here, just dumping their values for the example
    var_dump($row);  
}

is the best I can come up with without using the MUCH safer PDO Parameterized Queries

edited May 23 '17 at 12:12

Community

1
1

answered Jun 18 '12 at 16:31

Rem.co

3,813
3
29
37

That works perfectly but it only displays the first record in the column.. How would I get it to output the whole column where the requirements are met? – user1449737 Jun 18 '12 at 16:36
Why do two separate queries and all this array work when you only need one query to do what you're trying to do? – Nick Rolando Jun 18 '12 at 16:54
@Shredder I tried your subquery idea but again it still only returned the one record, not the whole column.. – user1449737 Jun 18 '12 at 17:02
@user1449737 You mean one column and not entire record? Using `SELECT * FROM $users_tbl` will pull the entire record (all columns) from `$users_tbl` for each record that matches the criteria. – Nick Rolando Jun 18 '12 at 17:08
@Shredder I want it to pull every record with either username1, username2 or username3 in the username column. – user1449737 Jun 18 '12 at 17:16
1

@user1449737 If `SELECT username FROM $tbl_name` gets those usernames, and they can be found in the `username` column in `$users_tbl` then you should be getting them. Each will have its own record/row though; are you iterating through all the rows? – Nick Rolando Jun 18 '12 at 17:21
@Shredder is right, all **columns** are in there, because we `SELECT *`, and all **rows** should be in there as well, but you have to iterate through the results, like you do with `mysql_fetch_array` in your initial example (Or use [mysqli](http://php.net/manual/en/book.mysqli.php)/[PDO](http://nl3.php.net/manual/en/ref.pdo-mysql.php) as we _should_ be advising you). @Shredder was _also_ right that these queries should be combined, but I gave this example because it was the actual question, and it might be plausible when used in different parts of the script. – Rem.co Jun 18 '12 at 17:42
@RemcoOverdijk Could you explain how I would iterate though the results please? – user1449737 Jun 18 '12 at 18:45
@user1449737 See the updated answer. This should `var_dump` each iterated row from the resultset. It's up to you do something useful with the rows ;) – Rem.co Jun 18 '12 at 19:09

Search MySQL database with an array

4 Answers4