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%')";

Recommended Answers

All 7 Replies

Member Avatar

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

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

Member Avatar
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?

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

commented: 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! +3
commented: Good +15
Member Avatar

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?

Member Avatar

Please start your own thread

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.