mysql WHERE clause a variable which holds multiple values - is not working

Question

I have fetched a column values in a variable from table1 and trying to use that variable to fetching another column values from table2 with WHERE clause.

I'm trying the below code, where $theseOpCode is holding the OpCode column values from user_profile table. I want to fetch values from $table WHERE OpCode='$theseOpCode'. I also tried WHERE IN ($theseOpCode) but no luck.

Someone please show me the right way.

index.php

$query=mysql_query("SELECT * FROM user_profile WHERE email='$thisEmail'") or die(mysql_error());
while($row = mysql_fetch_array($query)) {
  $theseOpCode = $row['OpCode'];  
  $_SESSION['Op'] = $theseOpCode;
}

I m trying to get the $theseOpCode as a session, and use this variable in WHERE clause in another file where my show class is.

showClass.php

    class showClass{

public function showUser($table){

    $theseOpCodeVal = $_SESSION['Op'];

        $query=mysql_query("SELECT * FROM $table WHERE OpCode='$theseOpCodeVal'") or die(mysql_error()); 
        $data=NULL;
        if(mysql_num_rows($query)>0){
            while($rows=mysql_fetch_assoc($query)){
            $data[]=$rows;
            }
            return $data;
        }else{
            echo '<span class="text-info success">No Account Found.</span>';
        exit();
        }


    }

}

My code is working but only showing the last value from WHERE clause. But I have 6 values in the $theseOpCodeVal variable. I want to fetch all values that matches $theseOpCodeVal variable values not only the last value that matched.

[Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Phil, Jun 25 '14 at 05:29
You have to use `IN()` with comma separated values. Also there is probably one value in `$_SESSION['Op']` since you override it each time. — Class, Jun 25 '14 at 05:29
What you mean by `I have 6 values in the $theseOpCodeVal variable` — Sadikhasan, Jun 25 '14 at 05:30
@Sadikhasan I mentioned that, I tried using IN($theseOpCodeVal) but no luck. it is still fetching the last row that matches the last value from WHERE clause. — msahed0, Jun 25 '14 at 05:31
@Sadikhasan I meant $theseOpCodeVal holds 6 OpCode from user_profile table. I fetched it in index.php and trying to use this variable in showClass.php to fetch WHERE $theseOpCodeVal to get the rows that matches $theseOpCodeVal — msahed0, Jun 25 '14 at 05:34
Can you give how to store value in `$theseOpCodeVal` variable? — Sadikhasan, Jun 25 '14 at 05:37
@user3673207 I think you'll find that `$_SESSION['Op']` and thus `$theseOpCodeVal` only contains the last OpCode from the first query because you are overwriting it in each loop iteration. — Phil, Jun 25 '14 at 05:38
yes exactly @Phil, can u tell me how avoid overwriting and keep all values in single variable to use in my WHERE clause? — msahed0, Jun 25 '14 at 05:40

Phil · Answer 1 · 2014-06-25T05:53:13.793

Why not use your relational database as was intended (using PDO, just because)...

class showClass {
    private $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    public function showUser($table, $email) {
        $stmt = $this->pdo->prepare("
            SELECT a.* FROM `$table` a
            INNER JOIN user_profile b ON a.OpCode = b.OpCode
            WHERE b.email = ?");
        $stmt->execute([$email]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

$pdo = new PDO('mysql:host=localhost;dbname=whatever;charset=utf8', 'username', 'password', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$showClass = new showClass($pdo);
$thisEmail = 'wherever you got the value from in the first place';
$table = 'some_table';

$data = $showClass->showUser($table, $thisEmail);

thanks Phil for your help. This will work also but cant change my structure now. I have very short time left for this project. Will apply this in future projects must.. thanks. — msahed0, Jun 25 '14 at 05:57

score -1 · Answer 2 · answered Jun 25 '14 at 05:35

-1

First thing you have to make '$theseOpCodeVal' commas separated value and then use 'IN' operator like:

'WHERE OpCode IN ($theseOpCodeVal)'; instead of WHERE IN.

answered Jun 25 '14 at 05:35

Aasim Afridi

1
1

can u please show me how to make $theseOpCodeVal commas separated ? – msahed0 Jun 25 '14 at 05:36

Fabricator · Accepted Answer · 2014-06-25T05:45:38.350

-1

First store all opCodes

$query=mysql_query("SELECT * FROM user_profile WHERE email='$thisEmail'") or die(mysql_error());
while($row = mysql_fetch_array($query)) {
  $theseOpCode = $row['OpCode'];  
  $_SESSION['Op'][] = $theseOpCode;
}

Next, query with IN operator

$query=mysql_query("SELECT * FROM $table WHERE OpCode IN ('".implode("','", $theseOpCodeVal)."')") or die(mysql_error());

edited Jun 25 '14 at 05:45

answered Jun 25 '14 at 05:39

Fabricator

12,722
2
27
40

thanks but im getting this error in my eclipse for the last query ---- Multiple annotations found at this line: - Line Breakpoint: showClass.php [line: 14 ] – msahed0 Jun 25 '14 at 05:44
@user3673207, sorry. I forgot to close a parenthesis after `$theseOpCodeVal` – Fabricator Jun 25 '14 at 05:46
Better hope none of those op code values in session contain any SQL breaking characters – Phil Jun 25 '14 at 05:55

mysql WHERE clause a variable which holds multiple values - is not working

3 Answers3