Bad query - INNER JOIN

Question

I am getting a bunch of undefinded index warnings when i print out my data from a SQL query, when i remove the INNER JOINS most of the warnings disappear. I am not sure what is causing that error.

My code is here:

<?php
    $id      = $_GET['id'];
    $sql     = "SELECT * FROM updates INNER JOIN clients ON updates.c_id = clients.c_id INNER JOIN pages ON updates.page = pages.p_id INNER JOIN projects ON updates.p_id = projects.p_id WHERE u_id='$id' LIMIT 1";
    echo $sql;
    $result  = mysql_query($sql) or die(mysql_error());
    $row     = mysql_fetch_assoc($result);

    // put update_id in hidden form and pass it to the next page
    $u_id = $row['u_id'];
?>
<h4>Viewing update for <i><? echo $row['fname'] ." ". $row['lname']  ?></i> for their <i><a href="<? echo $row['p_url']; ?>" title="View <? echo $row['p_title']; ?>"><? echo $row['p_title']; ?></a> project</i></h4>
<h4><b>Posted on: </b> <? echo $row['date_submitted'] = date("F j, Y, g:i a"); ?></h4>

Any idea on what I can do? The reason I have the INNER JOIN for CLIENTS is because "fname" and "lname" are stored there

clients.c_id = updates.c_id

Where I have: "p_url" "p_title" those are stored in the table PROJECTS which is also:

clients.c_id = projects.c_id

Thanks,

Ryan

---

Edit with new problem

My code is here:

$sql     = "SELECT 
    updates.u_id AS u_id,
    updates.date_submitted AS date_submitted,
    updates.deadline AS deadline,
    updates.description AS description,
    updates.priority AS priority,
    pages.page_name AS page_name,
    clients.fname AS fname,
    clients.lname AS lname,
    projects.p_url AS p_url,
    projects.p_title AS p_title,
    FROM updates INNER JOIN clients ON updates.c_id = clients.c_id INNER JOIN pages ON updates.page = pages.p_id INNER JOIN projects ON updates.p_id = projects.p_id WHERE u_id='$id' LIMIT 1";

ERROR:

SELECT updates.u_id AS u_id, updates.date_submitted AS date_submitted, updates.deadline AS deadline, updates.description AS description, updates.priority AS priority, pages.page_name AS page_name, clients.fname AS fname, clients.lname AS lname, projects.p_url AS p_url, projects.p_title AS p_title FROM updates,projects,clients,pages INNER JOIN clients ON updates.c_id = clients.c_id INNER JOIN pages ON updates.page = pages.p_id INNER JOIN projects ON updates.p_id = projects.p_id WHERE u_id='1' LIMIT 1Not unique table/alias: 'clients'

Answer 1

Edited answer:

Ah, I incorrectly assumed it had to do with SQL indexes. It appears it's actually a PHP error, related to you trying to print out array elements that don't exist.

For all of your prints that include elements of $row ($row['deadline'], etc), you need to make sure that there are actually columns named that being returned by your query. If there's not a column named "deadline", that attempt to print it is going to generate the warning.

---

Edit again: since this got bumped up, I guess I'll go into a little more detail.

First of all, as bobince points out, you have SQL injection possible. The first line should be:

$id      = intval($_GET['id']);

if $id will always be an integer, and mysql_real_escape_string() if it could be a string.

Second, SELECT * is generally bad form, especially in a case with joins. I don't know exactly which tables particular fields come from, but your query should look more like this, where you select only the fields you're actually going to use:

$sql = "SELECT clients.fname, clients.lname, projects.p_url, projects.p_title, updates.date_submitted ".
        "FROM updates ".
            "INNER JOIN clients ON updates.c_id = clients.c_id ".
            "INNER JOIN pages ON updates.page = pages.p_id ".
            "INNER JOIN projects ON updates.p_id = projects.p_id ".
        "WHERE updates.u_id='$id' ".
        "LIMIT 1";

Next, $u_id gets set to exactly the same value as $id already had, so it's kind of a pointless variable.

Finally, on the last line, you have:

<? echo $row['date_submitted'] = date("F j, Y, g:i a"); ?>

I'm not sure what you're expecting this to do, but it's going to assign date("F j, Y, g:i a"); to $row['date_submitted'] and then end up printing out "true" or "1" or something, that's probably not what you were going for.

---

Newest problem: You both try to select from clients, and join clients, you can't do both, at least without giving one of them an alias.

Answer 2

I don't think this has anything to do with the SQL (but I could be wrong). You might take a look at this thread for a start.

Answer 3

SELECT * FROM updates INNER JOIN clients

When you ‘SELECT *’ you get each column from both tables. Because columns can have the same names, the column names generated automatically by ‘*’ are prefixed with the table name. So your associative array will contain indexes like:

updates.u_id
clients.c_id
...

So when you try to access the array using an unprefixed column name such as 'page_name', it fails because that index isn't there.

You can use the full column name ('pages.page_name'), or you can explicitly give your own column names by saying:

SELECT updates.u_id AS u_id, pages.page_name AS page_name, ...
FROM updates JOIN client ...

u_id='$id'

Whoops, SQL injection hole. Congratulations, you are this week's 1000th winner of the obligatory xkcd link.

mysql_real_escape_string() is your friend. (Even better: mysqli parameterised queries.)

<? echo $row['deadline']; ?>

Whoops, HTML injection hole. htmlspecialchars() is your friend.