Select with 3 tables and where

Question

I had two tables using INNER JOIN appears where the user information page. Now I needed to create another table and I am not able to do funcioncar with 3 tables together.

Follows the old code (with two tables) and below the current code with error (with three tables).

OLD CODE:

 // Pega subdomínio
    $urlExplode = explode('.', $_SERVER['HTTP_HOST']);
    if (count($urlExplode) > 2 && $urlExplode[0] !== 'www') {
        $subdomain = $urlExplode[0];    
    //  echo $subdomain;
    }    

    // Diz que o usuário é igual ao subdomínio
    $usuario = $subdomain;

    // Select DB da Tabela TEXTOS
$sql = "SELECT * FROM vms_textos i INNER JOIN vms_users u on u.id = i.id where u.usuario='$usuario'";    


        $result = mysql_query($sql); 
        if($result === FALSE) { 
        die(mysql_error());
         // TODO: better error handling 
        }
    else {
    $row = mysql_fetch_array($result);

    // Tabela Textos
    $userKeywords = $row['userKeywords'];
    $userDesc = $row['userDesc'];
    $userTitleSite = $row['userTitleSite'];
    $userTextSobre = $row['userTextSobre'];
    $userTextContatos = $row['userTextContatos'];
    $userTextMaisInfos = $row['userTextMaisInfos'];

    }

CURRENT CODE

// Pega subdomínio
$urlExplode = explode('.', $_SERVER['HTTP_HOST']);
if (count($urlExplode) > 2 && $urlExplode[0] !== 'www') {
    $subdomain = $urlExplode[0];    
//  echo $subdomain;
}


// Diz que o usuário é igual ao subdomínio
$usuario = $subdomain;

// Select DB da Tabela TEXTOS
$sql = "SELECT * FROM (vms_textos t INNER JOIN vms_users u ON u.id = t.id) INNER JOIN vms_cores c ON u.id = c.id  where u.usuario='$usuario'";


    $result = mysql_query($sql); 
    if($result === FALSE) { 
    die(mysql_error());
     // TODO: better error handling 
    }
else {
$row = mysql_fetch_array($result);

// Tabela Textos
$userKeywords = $row['userKeywords'];
$userDesc = $row['userDesc'];
$userTitleSite = $row['userTitleSite'];
$userTextSobre = $row['userTextSobre'];
$userTextContatos = $row['userTextContatos'];
$userTextMaisInfos = $row['userTextMaisInfos'];

}

Thank you in advance for your help.

It looks like you might need to join tables `c` and `t` in some way? Other than that, there doesn't appear anything to be way off in the query. What errors are you getting? — Fluffeh, Oct 28 '13 at 03:27
if it is not finding the user is probably because you are joining with data that doesn't exists on all tables of the join clause. — Jorge Campos, Oct 28 '13 at 03:30
@JorgeCampos but with two tables functioned normally finding the user — Vítor de Sousa, Oct 28 '13 at 03:33
@VítordeSousa And if the third table doesn't have a matching row, it won't bring back ANY of the rows. Have a look at my answer below. — Fluffeh, Oct 28 '13 at 03:34

score 0 · Answer 1 · answered Oct 28 '13 at 03:28

0

I never liked this sql 92 pattern I find it hard to read, so, here it is a more easy way to do it:

$sql = "SELECT * FROM vms_textos t ,
                      vms_users u ,
                      vms_cores c 
                  where u.id = t.id
                    and u.id = c.id
                    and u.usuario='$usuario'";

answered Oct 28 '13 at 03:28

Jorge Campos

22,647
7
56
87

does not find the user too :( – Vítor de Sousa Oct 28 '13 at 03:31
sounds like the @Fluffeh answer suits better for you then... It is a left join. – Jorge Campos Oct 28 '13 at 03:32
@Fluffeh yes.. I did, I did not know that the problem was the data until he say about it in the comments. As I dont like sql 92 i though maybe could be wrong. – Jorge Campos Oct 28 '13 at 03:38

score 0 · Accepted Answer · edited May 23 '17 at 12:28

0

Sounds like you need to use an outer join instead then (from the comments)

SELECT 
    * 
FROM 
    vms_textos t 
        INNER JOIN vms_users u 
            ON u.id = t.id
        left outer JOIN vms_cores c 
            ON u.id = c.id  
where 
    u.usuario='$usuario'

basically, using inner joins, if the data isn't present in ALL the tables, it won't be returned. Using a combination of inner/outer joins you can pinpoint what you need to bring back.

Based on the comments below, the query is now:

SELECT 
    * 
FROM 
    vms_cores c 
        INNER JOIN vms_users u 
            ON u.id = t.id
        left outer JOIN vms_textos t 
            ON u.id = c.id  
where 
    u.usuario='$usuario'

It might also be a good idea to read this Q&A I wrote that goes into the details of this answer in a LOT more detail.

edited May 23 '17 at 12:28

Community

1
1

answered Oct 28 '13 at 03:31

Fluffeh

33,228
16
67
80

Plus explanation: What is saing is like: Give me all the data where the user_id is present on vms_users and vms_textos at the same time and if it is or not on vms_cores table. The database you bring all the results that exists for the two first tables (join) and if that user id exists on the third it will came otherwise all the data for the third table will show as null. – Jorge Campos Oct 28 '13 at 03:35
@Fluffeh, Is finding the table data "vms_cores" but not finding the table "vms_textos" ... – Vítor de Sousa Oct 28 '13 at 03:42
so you should change the joing order. join vms_users with vms_cores then left join with vms_textos – Jorge Campos Oct 28 '13 at 03:43
@JorgeCampos, too is finding the table data "vms_cores" but not finding the table "vms_textos" ... – Vítor de Sousa Oct 28 '13 at 03:53
is now well: $sql = "SELECT * FROM vms_cores c INNER JOIN vms_users u ON u.id = c.id left outer JOIN vms_textos t ON u.id = t.id where u.usuario='$usuario'"; – Vítor de Sousa Oct 28 '13 at 03:54
I'm sorry I had done something wrong at the time of the UPDATE that erased all data that user. so I said I had not found you. Checked and now is working. Thank you! – Vítor de Sousa Oct 28 '13 at 04:14
@VítordeSousa I also added a link to a tutorial that I once wrote that might help you out, it goes into this sort of things in a huge amount of detail and really explains what is happening and why. – Fluffeh Oct 28 '13 at 04:17

Select with 3 tables and where

2 Answers2