Geek asks and answers

MySQL did not connect to the AS keyword

I like to use self-explanatory names for associative samples, and sometimes it’s even necessary to avoid duplicates, so I use the AS alot keyword. But this gives me some problems with left joins.

It works:

$sql = "SELECT *,
        projects.id as projects_id
       FROM projects";

$sql .= " LEFT JOIN".
    " (SELECT 
        projectfiles.id as projectfiles_id,
        projectfiles.fileID as projectfiles_fileID,
        projectfiles.projectID as projectfiles_projectID
       FROM projectfiles
       ) AS projectfiles".
    " ON projects.id = projectfiles_projectID";

However, now I get useless data from projects, because it also captures fields userIDand namethat I don't need. It also raises the identifier twice.

So I tried to change it to;

$sql = "SELECT 
        projects.id as projects_id
       FROM projects";

When the line ON becomes

" ON projects_id = projectfiles_projectID";

But it gave an error Unknown column projects_id

So i tried

" ON projects.projects_id = projectfiles_projectID";

But still the same mistake

Then I started experimenting and tried (as a test)

$sql = "SELECT id,name,userID FROM projects";

$sql .= " LEFT JOIN".
    " (SELECT 
        projectfiles.id as projectfiles_id,
        projectfiles.fileID as projectfiles_fileID,
        projectfiles.projectID as projectfiles_projectID
       FROM projectfiles
       ) AS projectfiles".
    " ON projects.id = projectfiles_projectID";

And, to my surprise, the LEFT JOIN did not seem to be collecting anything.

the code:

$sql = "SELECT id,name,userID FROM projects";

$sql .= " LEFT JOIN".
    " (SELECT 
        projectfiles.id as projectfiles_id,
        projectfiles.fileID as projectfiles_fileID,
        projectfiles.projectID as projectfiles_projectID
       FROM projectfiles
       ) AS projectfiles".
    " ON projects.id = projectfiles_projectID";

$res = mysql_query($sql);
if(!$res) die(mysql_error());

if(mysql_num_rows($res) > 0)
{
    $rownum = 0;
    while($row = mysql_fetch_assoc($res))
    {
        print_r($row);
        echo "<br/><br/>";
    $rownum++;
    }
}

Conclusion:

problem

, projects, 3 projectfiles projectID... ?

+5
5

projectfiles:

$sql = "SELECT projectfiles.*,
        projects.id as projects_id
       FROM projects";

// rest of the code is the same


$sql = "SELECT projectfiles.* FROM projects";
// rest of the code is the same
+1

:

$sql = "SELECT projects.id,projects.name,projects.userID FROM projects LEFT JOIN
       projectfiles ON projects.id = projectfiles.projectID";
+1
SELECT p.*, pf.id, pf.fileId
FROM projects p LEFT JOIN projectfiles pf 
on p.id = pf.projectID

You can use the "how" to do the same as you. No subquery needed.

+1
source 
$sql = "SELECT p.id,p.name,p.userID FROM projects p";

$sql .= " LEFT JOIN".
    " projectfiles pf ".
    " ON p.id = p.projectID";
0
source 
$sql = "SELECT prj.id as prjId,
    prj.name as prjName,
    prj.userID as prjUid,
    pf.id as pfId, 
    pf.fileID as pfFileId, 
    pf.projectID as pfProjecId 
FROM projects as prj 
LEFT JOIN projectfiles AS pf 
ON prj.id = pf.projectID";
0
source

More articles:


All Articles