Page 1 of 1

Not unique table/alias

Posted: Thu Aug 24, 2006 8:33 pm
by cturner
Can someone please tell me why I am getting this error? Not unique table/alias. Thanks in advance.

Here is my code:

Code: Select all

require "config2.php";

if(!isset($_GET['page'])) {
    $page = 1;
} else {
    $page = $_GET['page'];
}

// Define the number of results per page
$max_results = 1;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);

// Perform MySQL query on only the current page number's results
$sql = mysql_query("SELECT * FROM items LIMIT $from, $max_results") or die ("Could not select the database because: " . mysql_error());

while($row = mysql_fetch_array($sql)){
    // Build your formatted results here.
    echo $row['selectDay'].'-'.$row['selectMonth'].'-'.$row['selectYear'].'<br>'.$row['article_item'].'<br />
	<a href="modify_article.php?id='. $row['id'] .'">Modify</a>
    <a href="delete_article.php?id='. $row['id'] .'">Delete</a>';
}

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) AS Num FROM items"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// select the article with the matching comments
$query = "SELECT article_item, COUNT(comments) AS numCom FROM users, items INNER JOIN items ON users.comments = items.article_item";

$result = mysql_query($query) or die (mysql_error());

if ($result < 0) {
	echo "(0) comments";
} else {
	echo "(".$result.") comments";
}

// Build Page Number Hyperlinks
echo "<center>";

if ($page == 1) {
	echo "Previous ";
} else {
	echo "<a href=\"".$_SERVER['PHP_SELF']."?page=".($page-1)."\">Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
        } else {
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
    }
}

if ($page == $total_pages) {
	echo " Next";
} else {
	echo " <a href=\"".$_SERVER['PHP_SELF']."?page=".($page+1)."\">Next</a>";
}

echo "</center>";

// and close the database connection
mysql_close();
My code was working until I placed the following code into it:

Code: Select all

$query = "SELECT article_item, COUNT(comments) AS numCom FROM users, items INNER JOIN items ON users.comments = items.article_item";

$result = mysql_query($query) or die (mysql_error());

if ($result < 0) {
	echo "(0) comments";
} else {
	echo "(".$result.") comments";
}

Posted: Thu Aug 24, 2006 8:38 pm
by feyd
Your inner join is implicitly and explicitly joining the same table.

Can I ask you to not make a new thread for the same piece of code when you could continue a thread you've already got open for this?

Posted: Thu Aug 24, 2006 8:47 pm
by cturner
Thanks for your reply feyd. I don't understand what you mean.

Posted: Thu Aug 24, 2006 9:05 pm
by blackbeard
You're trying to do a self join, and you need to use a table alias.

http://www.google.com/search?hl=en&q=my ... gle+Search

Try the above for more help on it, I've never had a reason to use a self join personally.