I want to search a combination of values from any given field that has a list of items.

id | nume | denumiri_job
1 | my_list | chair,apple,snake,can

How can I search for 2 or more elements from the field list? For example if I input in my search bar the values 'chair snake' I want to get all the rows that contain these 2 items in the denumiri_job column. Right now I can only get the results if I use one search key at a time.

So far this is how I managed my code:

$query = "SELECT id, data, nume, prenume, email, telefon, limbi_straine, denumiri_job, cv, observatii, interview, acceptat, plecat, stare FROM candidati WHERE (data LIKE '%$val%' 
                                          OR nume LIKE '%$val%' 
                                          OR prenume LIKE '%$val%' 
                                          OR email LIKE '%$val%'  
                                          OR telefon LIKE '%$val%'  
                                          OR limbi_straine LIKE '%$val%'  
                                          OR denumiri_job LIKE '%$val%'  
                                          OR observatii LIKE '%$val%'  
                                          OR interview LIKE '%$val%'  
                                          OR acceptat LIKE '%$val%'  
                                          OR plecat LIKE '%$val%'
                                          OR stare LIKE '%$val%')";
2 Months
Discussion Span
Last Post by diafol

Why do you have multiple values in one field. Data should be atomic where possible. You may benefit from normalising. See my tutorial on DW for explanation

Edited by diafol


I totally agree with you, sadly this is not a table done by me.

Edited by seularts

SELECT * FROM `candidati` WHERE CONCAT(",", `denumiri_job`, ",") REGEXP ",(chair|snake),"

should work - however you'll need to build the REGEXP clause via php - e.g. replace commas in search string with '|' and remove any spaces.

//EDIT - sorry that's an 'OR' not an 'AND' - which do you want?

Edited by diafol


Or, add a fulltext index to your table (across all the columns you care about) and use match (cols...) against syntax.

Edited by pty

Votes + Comments
This did the trick. The stupid script that generated the table with its content is not all that retarded because it had added fulltext indexes!

Aha. I assumed fulltext not implemented. Adding fti across all text-based fields can slow down writing operations, especially on wide tables with big text fields, but that's the trade-off for fast reading :)


Since we are on the subject of normalization...

I am rather new to Normalization. I understand the concept of how it organizes the tables but I fail to see how I can use the data to show up correctly in my query using pdo.

I have 3 tables:

id  |  name
1     user1
2     user2
2     user3

shop_id  | shop
1          shop1
2          shop2
3          shop3
4          shop4
5          shop5

shops_employees_id  |  employee_id  |  shop_id
1                      1               1
2                      1               2
3                      2               4
4                      3               3
5                      3               5

but I get:
1 user1 shop1
1 user1 shop2
2 user2 shop4
3 user3 shop3
3 user3 shop5

How can I get them in one row like:
1 user1 shop1 shop2
2 user2 shop4
3 user3 shop3 shop5

this is my code:



    $DB_con = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass);
catch(PDOException $e)
    echo $e->getMessage();

class crud
    private $db;

function __construct($DB_con)
    $this->db = $DB_con;

public function getID($id)
    $stmt = $this->db->prepare("SELECT * FROM employees WHERE id=:id");
    return $editRow;

public function dataview($query)
    $stmt = $this->db->prepare($query);



            <td><?php print($row['id']); ?></td>
            <td><?php print($row['name']); ?></td>
            <td><?php print($row['shop']); ?></td>



            <td colspan="8" class="text-center danger"> Nothing founds...</td>



$crud = new crud($DB_con);




    $query = "

    SELECT emp.id,
    from employees AS emp 
         INNER JOIN shops_employees AS se ON (emp.id     = se.employee_id)
         INNER JOIN shops           AS sh ON (sh.shop_id = se.shop_id)
    ORDER BY emp.name,




Can you guys give me an idea how should I write my code to get all results for the empolyees in corelation with the other table?

Edited by Bacchus_1

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.