Re: [PHP] Inner join woes... and sweet tea!
On 5 July 2010 17:27, Gary wrote: > > "Richard Quadling" wrote in message > news:aanlktinredvfb5cjran9ati-ildvzctlkat-i7amb...@mail.gmail.com... >> On 5 July 2010 14:48, Pete Ford wrote: P.S. I don't have an emu. >>> >>> Clearly, or you'd know that they can't fly either... >>> :) >> >> GIGO!!! > > > Does that mean you do have a deathwatch beetle? > > Gary No. It doesn't mean I've got a deathwatch beetle. That would be a fallacious assumption -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Inner join woes... and sweet tea!
On Jul 5, 2010, at 9:14 AM, Peter Lind wrote: On 5 July 2010 15:02, Jason Pruim 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: ".$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: ".$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: $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
Re: [PHP] Inner join woes... and sweet tea!
"Richard Quadling" wrote in message news:aanlktinredvfb5cjran9ati-ildvzctlkat-i7amb...@mail.gmail.com... > On 5 July 2010 14:48, Pete Ford wrote: >>> P.S. I don't have an emu. >> >> Clearly, or you'd know that they can't fly either... >> :) > > GIGO!!! Does that mean you do have a deathwatch beetle? Gary __ Information from ESET Smart Security, version of virus signature database 5252 (20100705) __ The message was checked by ESET Smart Security. http://www.eset.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Inner join woes... and sweet tea!
On 5 July 2010 14:48, Pete Ford wrote: >> P.S. I don't have an emu. > > Clearly, or you'd know that they can't fly either... > :) GIGO!!! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Inner join woes... and sweet tea!
On 05/07/10 14:38, Richard Quadling wrote: On 5 July 2010 14:02, Jason Pruim wrote: Hi everyone, I'll admit right now that I'm still trying to wrestle with inner joins... It is all about set theory. Imagine two circles, which overlap (http://en.wikipedia.org/wiki/Venn_diagram#Example as an example). For that example, simplistically, A contains me and my emu. B contains my emu and the my deathwatch beetle. SELECT * FROM A,B WHERE A.id = B.id (My emu) SELECT * FROM A INNER JOIN B ON A.id = B.id (My emu) SELECT * FROM A LEFT OUTER JOIN B ON A.id = B.id (Me and My emu) SELECT * FROM A RIGHT OUTER JOIN B ON A.id = B.id (My emu and my deathwatch beetle) SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id returns in interesting set (essentially all things but 1 column for each table). Me, null My emu, my emu null, My deathwatch beetle. If you were using ISNULL ... SELECT ISNULL(A.name, B.name) AS name FROM A FULL OUTER JOIN B ON A.id = B.id would return all things Me My emu My deathwatch beetle. And, (I think), finally, an inversion of the inner join. SELECT ISNULL(A.name, B.name) AS name FROM A FULL OUTER JOIN B ON A.id = B.id WHERE A.id IS NULL OR B.id IS NULL returns Me My deathwatch beetle. All things except those 2 legged things that can fly. I hope that helps. Regards, Richard. P.S. I don't have an emu. Clearly, or you'd know that they can't fly either... :) -- Peter Ford, Developer phone: 01580 89 fax: 01580 893399 Justcroft International Ltd. www.justcroft.com Justcroft House, High Street, Staplehurst, Kent TN12 0AH United Kingdom Registered in England and Wales: 2297906 Registered office: Stag Gates House, 63/64 The Avenue, Southampton SO17 1XS -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Inner join woes... and sweet tea!
On 5 July 2010 14:02, Jason Pruim wrote: > Hi everyone, > > I'll admit right now that I'm still trying to wrestle with inner joins... It is all about set theory. Imagine two circles, which overlap (http://en.wikipedia.org/wiki/Venn_diagram#Example as an example). For that example, simplistically, A contains me and my emu. B contains my emu and the my deathwatch beetle. SELECT * FROM A,B WHERE A.id = B.id (My emu) SELECT * FROM A INNER JOIN B ON A.id = B.id (My emu) SELECT * FROM A LEFT OUTER JOIN B ON A.id = B.id (Me and My emu) SELECT * FROM A RIGHT OUTER JOIN B ON A.id = B.id (My emu and my deathwatch beetle) SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id returns in interesting set (essentially all things but 1 column for each table). Me, null My emu, my emu null, My deathwatch beetle. If you were using ISNULL ... SELECT ISNULL(A.name, B.name) AS name FROM A FULL OUTER JOIN B ON A.id = B.id would return all things Me My emu My deathwatch beetle. And, (I think), finally, an inversion of the inner join. SELECT ISNULL(A.name, B.name) AS name FROM A FULL OUTER JOIN B ON A.id = B.id WHERE A.id IS NULL OR B.id IS NULL returns Me My deathwatch beetle. All things except those 2 legged things that can fly. I hope that helps. Regards, Richard. P.S. I don't have an emu. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Inner join woes... and sweet tea!
On 5 July 2010 15:02, Jason Pruim 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:
>
>
> 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:
>
>
> $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
--
WWW: http://plphp.dk / http://plind.dk
LinkedIn: http://www.linkedin.com/in/plind
BeWelcome/Couchsurfing: Fake51
Twitter: http://twitter.com/kafe15
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Inner join woes... and sweet tea!
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: $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: $sql = "SELECT * FROM ".$cfgtableContent." INNER JOIN ".$cfgtableContent." WHERE ".$cfgtableCategories.".postCat = ".$cat." ORDER BY ".$cfgtableContent.".postNumber DESC"; ?> with no change... Any ideas what I'm missing? :) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
