Php - Mysql Search multiple values in the same field

[seularts]

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

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

[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

[seularts]

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

[diafol]

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?

[pty]

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

[diafol]

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 :)

[Bacchus_1]

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:

employees:
id  |  name
1     user1
2     user2
2     user3

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

shop_employees:
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:

<?php

...

try
{
    $DB_con = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass);
    $DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
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");
    $stmt->execute(array(":id"=>$id));
    $editRow=$stmt->fetch(PDO::FETCH_ASSOC);
    return $editRow;
}

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

    if($stmt->rowCount()>0)
    {
        while($row=$stmt->fetch(PDO::FETCH_ASSOC))
        {

            ?>

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

            <?php

        }
    }

        else
        {
            ?>
            <tr>
            <td colspan="8" class="text-center danger"> Nothing founds...</td>
            </tr>
            <?php
        }

    }

}

$crud = new crud($DB_con);

?>

<table>

<?php

    $query = "

    SELECT emp.id,
           emp.name,
           sh.shop
    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,
             sh.shop;";

    $crud->dataview($query);

?>

</table>

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?

[diafol]

Please start your own thread