Compare values from array mysql php

Question

Hi I'm trying to compare values from 2 arrays with this query, which is the only way I know:

$session = "1,2,3,"
$table_name = "table1";
$column_name = "data1"; // value for test is 1,4,5,
$sql = "";
$sql .= "SELECT * FROM $table_name WHERE ";
$franquia = array();
$franquia = explode(",", $session);
if (!empty($franquia)) {

$final_id = array();
foreach ($franquia as $val) {
    if (trim($val) != '') {
        $final_id[] = $val;
    }
}

$count_data = count($final_id);
foreach ($final_id as $key => $id) {
    if ($id > 0) {
        $sql .= " $id IN ($column_name) ";
        if ($key < $count_data - 1) {
            $sql .= "OR ";
        }
    }
}
}


echo $sql;

I have values 1,2,3 on $session and 1,4,5on $data1 so the comparison between $session and $data1 was suposed to return true due to both of them have value 1, but I don't get any results.

Actually it only works if both arrays are the same, like $session = 1,2,3 and $data1 = 1,2,3

What am I doing wrong?

Just a question; Why: `$sql = "";` + `$sql .= "SELECT * FROM $table_name WHERE ";` ? — Wouter Dorgelo, Jul 02 '12 at 20:22
This code looks pretty butchered to me, and it feels like you got lost halfway through whatever you were trying to do (it certainly makes little sense to me). Perhaps revisit your approach but by first clearly stating what you want it to do. — Bob Davies, Jul 02 '12 at 21:02

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

You are using the IN clause wrong; In a nutshell, instead of

WHERE value IN (column)

The correct usage is:

WHERE column IN (value, value, value)

Which in turn will not help with what you are trying to do (more about the IN clause). Rather, try the following:

foreach ($final_id as $key => $id) {
    if ($id > 0) {
        $sql .= "$column_name LIKE %$id,%";
        if ($key < $count_data - 1) {
            $sql .= "OR ";
        }
    }
}
}

This should work, but there are a couple things you should be aware of. First you probably are vulnerable to SQL Injections, and second you should consider either using a different scheme with your data in your DB, since using LIKE %% to search for numeric values is a waste, or fetch in advance the entry from the DB and search in the resulting string. Consider the code above nothing but a quick and dirty hack that you should rather avoid :)

Hi Mahn i know the code looks crazy, but for sure i dont know how to do this, WHERE value IN (column) i know this is wrong, but the problem is both VALUE and COLUMN are like a array value can be = 1,2,3 and Column can be = 1,5,6 so i need to check if min 1 number is on both for return true, and i got not solution for this at this time.. i really dont know how do this.. thanks, i will keep trying. — Alvaro, Jul 02 '12 at 23:18

Compare values from array mysql php

1 Answers1