Textpattern CMS

#1 2007-05-19 00:08:58

001181

New Member

Registered: 2007-05-11

Posts: 5

Can I use"INNER JOIN"?

Hi all,

I am trying to write a sql query which includes “INNER JOIN”, and then the sql is going to be invoked by using getrows() function.

but doesnt work. so question is

can I use INNER JOIN to be a part of my sql query?

thanks

#2 2007-05-19 00:31:34

zem

Developer Emeritus

From: Melbourne, Australia

Registered: 2004-04-08

Posts: 2,579

Re: Can I use"INNER JOIN"?

Sure. Use an implicit inner join, as shown here in item 5.

---

Alex

#3 2007-05-19 13:27:17

001181

New Member

Registered: 2007-05-11

Posts: 5

Re: Can I use"INNER JOIN"?

zem wrote:

Sure. Use an implicit inner join, as shown here in item 5.

Thanks mate, but I still havent got any clue of how to implement this sql query below by using safe_rows function, please help.

Many thanks.
—————————————————————————————————————————————————-
SELECT L.page,A.Title FROM txp_log AS L INNER JOIN (SELECT Title FROM textpattern WHERE Section="article") AS A ON L.page = CONCAT("/chinese/",A.Title)

Last edited by 001181 (2007-05-19 13:30:54)

#4 2007-05-19 13:39:16

ruud

Developer Emeritus

From: a galaxy far far away

Registered: 2006-06-04

Posts: 5,068

Website

Re: Can I use"INNER JOIN"?

001181 wrote:

SELECT L.page, A.Title FROM txp_log AS L INNER JOIN (SELECT Title FROM textpattern WHERE Section="article") AS A ON L.page = CONCAT("/chinese/",A.Title)

If that’s the same as:

SELECT txp_log.page, textpattern.Title 
FROM txp_log, textpattern 
WHERE textpattern.section = 'article' AND txp_log.page = CONCAT('/chinese/', textpattern.Title)

Then you can write it as:

$rows = safe_rows(
          'txp_log.page, textpattern.Title', 
          'txp_log, textpattern', 
          "textpattern.section = 'article' AND txp_log.page = CONCAT('/chinese/', textpattern.Title)"
        );

Last edited by ruud (2007-05-19 13:41:46)

#5 2007-05-19 14:45:26

001181

New Member

Registered: 2007-05-11

Posts: 5

Re: Can I use"INNER JOIN"?

ruud wrote:

Then you can write it as:

$rows = safe_rows(
          'txp_log.page, textpattern.Title', 
          'txp_log, textpattern', 
          "textpattern.section = 'article' AND txp_log.page = CONCAT('/chinese/', textpattern.Title)"
        );

THANKS, DOES IT WORK IN VER4.0.3? I TRIED BUT ….

Last edited by 001181 (2007-05-19 14:45:53)

#6 2007-05-20 02:17:01

Mary

Sock Enthusiast

Registered: 2004-06-27

Posts: 6,236

Re: Can I use"INNER JOIN"?

THANKS, DOES IT WORK IN VER4.0.3

No, it wouldn’t. Do:

$rs = getRows("SELECT L.page, A.Title FROM txp_log AS L INNER JOIN (SELECT Title FROM textpattern WHERE Section = 'article') AS A ON L.page = CONCAT('/chinese/', A.Title)");

Edit: code updated.

Last edited by Mary (2007-05-20 18:22:58)

#7 2007-05-20 12:31:23

001181

New Member

Registered: 2007-05-11

Posts: 5

Re: Can I use"INNER JOIN"?

Mary wrote:

bq. THANKS, DOES IT WORK IN VER4.0.3

No, it wouldn’t. Do:

$q = safe_query("SELECT L.page, A.Title FROM txp_log AS L INNER JOIN (SELECT Title FROM textpattern WHERE Section = 'article') AS A ON L.page = CONCAT('/chinese/', A.Title)");

$rs = getRows($q);

Thanks for your reply, but doesnt work, I check define the getRows function, found that actually the inside of getRows function invokes safe_query function

function getRows($query,$debug='') 
{
 if ($r = safe_query($query,$debug)) {
 if (mysql_num_rows($r) > 0) {
 while ($a = mysql_fetch_assoc($r)) $out[] = $a; 
 mysql_free_result($r);
 return $out;
 }
 }
 return false;
 }

#8 2007-05-20 18:23:11

Mary

Sock Enthusiast

Registered: 2004-06-27

Posts: 6,236

Re: Can I use"INNER JOIN"?

Whoops. Okay, try the updated.

#9 2007-05-20 22:04:28

001181

New Member

Registered: 2007-05-11

Posts: 5

Re: Can I use"INNER JOIN"?

Mary wrote:

Whoops. Okay, try the updated.

Thanks, I have found the reason, why it works when i use LEFT JOIN instead of INNER JOIN.

_!

Last edited by 001181 (2007-05-20 23:09:27)