SQL query...

[padders]

Having a bit of trouble with the SQL query/logic. It *should* be simple

I have a table of articles and a table of related articles. Now i am calling a "edit related articles page" and what it should do is list all the articles with a checkbox next to them. Ok, that is all fine. What i want however is for the checkbox to be selected if there is this article is already marked as "related."

The tables are:

articles (where name, id) are needed
and then
related_articles

where it has

show_article
related_article

So at the moment i am just doing

$db->query("SELECT title,id FROM article WHERE id != '$article_id'");

and looping through.

but what i need is some sort of JOIN (i think) that checks the other table first. I am not getting far at this, all the joins I am doing are resulting in the list of the articles more than once if they are also related to other articles (ie the ones we are not dealing with).

The only easy solution for me at the moment is just doing a 2nd SQL query on each article returned but that is far from optimim.

Any ideas?

thanks.

[padders]

p.s. what i want in the results i just a field that i can do some validation on that says "this article is already related to this article". Then i just do an if/else display of a checkbox that is checked or not checked.

[MattR]

Not sure if this is it, but maybe:

SELECT id,
       related_article
  FROM articles 
 OUTER JOIN related_articles 
    ON related_article = id
 WHERE id = $some_id

Then if related_article is null you do not have a checkbox. If it is not null, you make a check.

What would be best is if you use some MySQL logic to use a 'checked' or '' depending on the content of related_article to simplify your PHP code.

However, you need to explain what the keys are in the tables and what the columns mean though since we're just guessing without a table schema.

[MattR]

To clarify, I was talking of something like this:

<?php
  $query = "SELECT title, if not null somecol then 'checked' AS checked";

  while( fetch_query( $query ) ) {
    echo "<option name=\"$query[title]\" $query[checked]>";
  } // end while
?>

So the point is that you save PHP code by a simple addition to the SQL query. I'm not sure on the syntax for MySQL but it should be easy to find.

[peanuts]

I think what you mean is combining a querie to cross reference in two tables ... I would work something like this...

Select * FROM table1, table2 
WHERE table1.relatedid = table2.relatedid

Something like that anyway..

Good luck,

Peanuts

[padders]

thanks guys for the help but I am having a problem still getting this working. Matt, OUTER JOIN is not, i don't think mySQL? it was giving me errors..

Table Structure:

ARTICLE TABLE:

| id | title | article |

RELATIONS TABLE:

| show_article | related_article |

now the idea is the add relations page lists every article but knows already if there is corresponding entry match for show_entry (ie the one we are adding relations to) for that related entry (hence the already matched checkbox).

Hope that explains it better

[padders]

Originally posted by peanuts
I think what you mean is combining a querie to cross reference in two tables ... I would work something like this...

Select * FROM table1, table2 
WHERE table1.relatedid = table2.relatedid

Something like that anyway..

Good luck,

Peanuts

unfortunatly not, i want *all* the articles in the table to be listed but also know if the article being listed (as i print through the array) is already related to the current article in question.

[padders]

ok at the moment i am cycling through and doing an sql query for each article (ek).:

echo table_start('Edit Related Articles', 'faq.php', '2');

$db->query("SELECT title, id
            FROM kb_articles
            WHERE id != '$id'    
            ");

while ($article = $db->row_array()) {
    
$db2->query("SELECT COUNT(*) AS count FROM kb_related WHERE show_article = '$id' AND related_article = '$article[id]'");
$result = $db2->row_array();
if ($result[count] == "1") {
    $array1[] = "<input type=\"checkbox\" name=\"related_id[$article[id]]\" value=\"1\" checked>";
} else {
    $array1[] = "<input type=\"checkbox\" name=\"related_id[$article[id]]\" value=\"1\">";
}

$array2[] = $article[title];
    }

echo table_row6($array1, $array2);
$extra .= form_hidden('id', $id);
$extra .= form_hidden('action', 'related2');
echo table_end($extra, '2'); 

[MattR]

Originally posted by padders
thanks guys for the help but I am having a problem still getting this working. Matt, OUTER JOIN is not, i don't think mySQL? it was giving me errors..

Try LEFT JOIN (MySQL likes to shake things up and deviate from standard SQL ).

Let me explain what my query was doing and how it can help you.

We all know that this will join the two tables together and give you Ids and Related_articles.

SELECT id,
       related_article
  FROM articles, 
       related_articles 
 WHERE related_article = id
   AND id = $some_id

This can be re-written as:

SELECT id,
       related_article
  FROM articles, 
 INNER JOIN related_articles 
    ON related_article = id
 WHERE id = $some_id

However this query will not return what we want. It will give us IDs with the requirement that they must have a related article. Obviously this isn’t the case for all articles.

So, Cobb back-in-the-day when he invented SQL took the time to invent different kinds of JOINs. There are very many and the MySQL ones are listed here:
http://www.mysql.com/doc/J/O/JOIN.html

The kind of join we’re looking for is the OUTER JOIN:

SELECT id,
       related_article
  FROM articles 
  LEFT JOIN related_articles 
    ON related_article = id
 WHERE id = $some_id

This new construct says ‘give me article IDs along with their corresponding related articles if they have any’. Maybe that is not the best way to write it but what MySQL will do is grab EVERY article – whether or not it has a related article – and display them to you.

“Then, what is the contents of ‘related_article’ for an ID without one”, you ask? Well, most RDBMS’ will use ‘NULL’. I suspect MySQL does the same thing.

So, your application should check for NULL in related_article and if so, then there are no related articles. If there is a non-null ID, then it *is* a related article.

[padders]

doh.. outer join is what i want

[MattR]

Hmm looking at your latest post maybe the join is not kosher.

ARTICLE TABLE:
| id | title | article TEXT ?|

RELATIONS TABLE:
| show_article FOREGIN KEY on ARTICLE( ID )| related_article FOREGIN KEY on ARTICLE( ID ) |
What is the primary key??  Both?

So, if you have 5 articles,
1, 'matt is cool', 'sdad'
2, 'matt 0wnz j00!', 'yes'
3, 'matt wins the lottery, eleventy-billion!', '$$$$'
4, 'unrelated', 'unrelated'
5, 'lottery', 'lottery news'

How would the relations table be? Obviously the first 3 are related since they talk about me .

So you could potentially have 6 rows in relations based upon the matt connection:
1, 2
1, 3
2, 1
2, 3
3, 1
3, 2

And two? for the lottery:
4, 5
5, 4

Seems a little odd but I think that is the only way to handle this sort of thing (unless you only go one direction then it would be cut in half).

So your application flow will be something like this:
Give me an article ID.
Show me all articles in the database but a checkmark on ones which are related to this one.

So, you would run something like this:

SELECT title,
       id,
       related_article
  FROM kb_articles
  LEFT OUTER JOIN kb_related_article
    ON related_article = id
   AND show_article = $id

If you run that with show_article = 1:

+--------------+----+-----------------+
| title        | id | related_article |
+--------------+----+-----------------+
| matt is cool |  1 |            NULL |
| matt 0wnz    |  2 |               2 |
| matt lottery |  3 |               3 |
| unrelated    |  4 |            NULL |
| lottery      |  5 |            NULL |
+--------------+----+-----------------+

Then in your loop run this:

if( $query[ related_article ] != '' ) {
  $checked = "checked";
} else {
  $checked = "";
} // end if

echo "<input type=.. $checked>"; 

What I was hinting to earlier is that if you know a little bit about MySQL you can set it up to return a 'checked' or '' value depending on the value of related_article.

So you wouldn't have the if .. block but simply:

$query = "select title, if( related_article is not null ) { 'checked' } else { null } )
from table... ";

while( loop ) {
  echo "<input type.... $query[ checked ]>";
} // end while 

That way you could dump the PHP logic and echo it right out. That is an exercise left up to you .

[padders]

ok this was my first reply before i made a mistake:

Hi Matt,

LEFT JOIN is the one I normally use for my joins and does not work here (it is was i tried first). Using your SQL query:

SELECT id,
       related_article
  FROM kb_articles 
  LEFT JOIN kb_related
    ON related_article = id
 WHERE id = '4'

On a set of 10 or so articles and some related data

  show_article  related_article  
Edit  Delete  4 9 
Edit  Delete  4 8 
Edit  Delete  4 12 
Edit  Delete  4 13

That query returned only

 id  related_article  
Edit  Delete  4

now, i have also tried with

OUTER JOIN = error (not valid syntax)
RIGHT OUTER JOIN = no results
LEFT OUTER JOIN = same as above result

[padders]

Matt, you star:

SELECT title,
       id,
       related_article
  FROM kb_articles
  LEFT OUTER JOIN kb_related
    ON related_article = id
   AND show_article = '$id'

works perfectly!

not too worried about a bit of php, it was saving the 150 queries i was worried about (this is only on admin side so not used on every page but still annoying if it was that many queries!).

thanks.

[MattR]

No prob, glad I could help!