Re: Inner join woes... and sweet tea!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Jul 5, 2010, at 9:14 AM, Peter Lind wrote:

On 5 July 2010 15:02, Jason Pruim <lists@xxxxxxxxxxxxxxxxxxxx> wrote:
Hi everyone,

I'll admit right now that I'm still trying to wrestle with inner joins...
Which leaves me with this code right here:

<?PHP

if(isset($_GET['cat'])) {
           $cat = mysql_real_escape_string($_GET['cat']);

       }

       if(isset($cat)) {
              $sql = "SELECT * FROM ".$cfgtableContent." INNER JOIN
".$cfgtableCategories." WHERE ".$cfgtableContent.".postCat = ". $cat." ORDER
BY ".$cfgtableContent.".postNumber DESC";

       }

?>

Now... I know the problem is probably staring an experienced inner join master in the face and I don't even have to say it... But for those who don't know or you might be searching the archives and want to learn from my blatant misunderstanding of inner joins I'll outline said problem in the
following lines.

What I want to achieve: Add navigation by category to my blog. IE: If I want to display just Personal posts click on the "Personal" link and all other
posts magically disappear.

What I'm getting now: I have 5 categories in my blog now... I get 5 copies
of each post repeated down the site all with a different category...

All the variables are filled in correctly which I have checked with simple echo's. I have also tried removing the escaping of the data with no change
so if possible I'd like to leave it in there as is :)

I have also tried switching it from the above to this:

<?PHP

              $sql = "SELECT * FROM ".$cfgtableContent." INNER JOIN
".$cfgtableContent." WHERE ".$cfgtableCategories.".postCat = ". $cat." ORDER
BY ".$cfgtableContent.".postNumber DESC";

?>

with no change...


There's no join clause in your query, i.e. nothing to tie the two
tables together. That will leave you with a cartesian product, i.e.
for every row in table a you'll get the entire table b joined on. You
need to specify how the tables you're joining together are related.

Something like

SELECT
 a.row
FROM
 a
 JOIN b ON b.a_id = a.id

Regards
Peter

Hi Peter,

Thanks for point it out... I thought I had it set right but obviously not quite... You were able to put me onto the right path and after playing with the variables I ended up with this:

<?PHP

        if(isset($cat)) {
$sql = "SELECT * FROM ".$cfgtableContent.",". $cfgtableCategories." WHERE ".$cfgtableCategories.".catID = ".$cat." AND ".$cfgtableContent.".postCat = ".$cfgtableCategories.".catID ORDER BY ".$cfgtableContent.".postNumber DESC";
               }

?>

Which works just fine for me! :)

Joins can be some tricky stuff if you don't understand it completely...



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux