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

Recommended Answers

All 7 Replies

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

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

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

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

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?

Member Avatar for diafol

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, networking, learning, and sharing knowledge.