PHP MYSQL Search user that has a specific field inside another search

Question

I have this: A table where i have CONTACTS, each contact is linked to a COMPANY, the companies are in another table. Then i have the INFORMS table in where i have a list of informs with some data and everyone is linked to a CONTACT. I need to make a sql that filter that informs list but only show the informs of an specific COMPANY, so it has to search all the contacts that has the same COMPANY and show me the INFORMS of that company. Any ideas?

UPDATE:

Table COMPANY:

id: 02 Name: Google

Table CONTACT:

id: 01 Name: John Company: 02 - So is linked to Google

Table INFORMS:

id: 01 Data: Some data Contact: 01 - So is linked to john
id: 02 Data: Some data Contact: 01 - Another one linked to john
id: 03 Data: Some data Contact: 02 - not linked to john

I need to make a search query that showme only the informs linked to GOOGLE, so all the Informs linked to contacts that has GOOGLE as company. SO Inform 01 and 02 I`m trying something like this but dont work:

$sql = "SELECT * FROM polar_companies WHERE id IN (SELECT * FROM polar_contacts WHERE Empresa = '$v_empresa');"
HERE I DONT KNOW HOT TO PERFORM A INFORM SEARCH
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo HERE I NEED THE INFORMS
}
} else {
    echo "<p>No informs</p>";
}

I don't have a very good idea because I can't see your data. Please take a few moments and show some sample data here, ideally with expected output. Stack Overflow is not a free code writing service. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 21, 2017 at 1:45
I hate when I see a question but no codes. That shows you have not put any effort yourself. Help me with this code but I have no code. No thx — SG_Rowin
– SG_Rowin, Commented Jul 21, 2017 at 1:51
No code from you, no code from me hehe At least show the data structure ... And give it a shot and show us where you block. That's the kind of question that deserves an answer — Patrick Simard
– Patrick Simard, Commented Jul 21, 2017 at 1:54
Sorry. My code is in another languaje i thought it will be dificult to understand. — Cristian Parada
– Cristian Parada, Commented Jul 21, 2017 at 2:09
What you're looking for is a basic sql query. select informs.* from informs, contact, company where informs.contact=contact.id and contact.company=company.id and company.id=? — Tim Morton
– Tim Morton, Commented Jul 21, 2017 at 2:41

Tim Morton · Accepted Answer · 2017-07-21 03:25:13Z

First, you need a query that links the tables together.

Given this table schema:

Companies         Contacts         Informs
---------         --------         -------
company_id --+    contact_id --+   inform_id
name         |    name         |   some_data
             +--  company_id   +-- contact_id

Make your query like so:

$sql = "SELECT informs.* " .
       "FROM companies, contacts, informs " . 
       "WHERE companies.company_id=contacts.company_id AND " . 
       "      contacts.contact_id=informs.contact_id AND " . 
       "      companies.company_id=?";

Drawing the schema out makes forming the query so much easier. See how each table connects with the other?

Since SQL injection is a Bad Thing, use prepared statements. Never make a query with '$some_variable' in the query.

// assuming you have already made connection
// assuming you assign $company_id

$result = $db->prepare($sql);
$result->execute(array($company_id));

$rows = $result->fetchAll();

Word of advice, use tools such as phpMyAdmin to experiment with different queries. If you get good at making queries, you won't go looping through multiple queries (which is horribly inefficient as your tables get larger).

Solor · Accepted Answer · 2017-07-21 03:45:21Z

0

could do something like this

$sql = "SELECT * FROM polar_company WHERE `name`='$name'";

$result = $conn->query($sql);
if ($result->num_rows > 0) {
    $n = 1; 
    $row = $result->fetch_assoc(MYSQLI_ASSOC);
    $company_id = $row['id'];
    $query = "SELECT * FROM polor_contact WHERE `company`='$company_id'";
    $contact_result = $conn->query($query);
    while ($contact_row = $contact_result->fetch_array(MYSQLI_ASSOC)) {
        $contact_id = $contact_row['id'];
        $query = "SELECT * FROM `polor_informs` WHERE `contact_id`='$contact_id'";
        $informs_result = $conn->query($query);

        while ($informs_row = $informs_result->fetch_array(MYSQLI_ASSOC)) 
        {
            $informs[$n] = $informs_row;
            $n++;
        }
    }
}
print_r($informs);
} else {
    echo "<p>No informs</p>";
}

also this question may be useful as tim mentioned the run time problem and suggested using only 1 query {INNER LEFT JOIN} select left join table use and condition

edited Jul 21, 2017 at 3:45

answered Jul 21, 2017 at 2:39

Solor

397 bronze badges

1 Comment

Tim Morton Over a year ago

Yes, you could do something like this. But if the lists are very large, you will have exponentially increasing run times as the data set increases. BTW, proper indenting would really help the readability of the answer. Also, even though the variables you're putting in the queries come from the database, it's still better practice to always use prepared statements.

Collectives™ on Stack Overflow

PHP MYSQL Search user that has a specific field inside another search

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related