SitePoint Sponsor |
|
User Tag List
Results 1 to 14 of 14
Thread: SQL query...
-
Oct 4, 2001, 05:22 #1
- Join Date
- Feb 2000
- Location
- England
- Posts
- 568
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
SQL query...
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.
-
Oct 4, 2001, 05:23 #2
- Join Date
- Feb 2000
- Location
- England
- Posts
- 568
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Oct 4, 2001, 07:18 #3
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Not sure if this is it, but maybe:
Code:SELECT id, related_article FROM articles OUTER JOIN related_articles ON related_article = id WHERE id = $some_id
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.Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Oct 4, 2001, 07:32 #4
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
To clarify, I was talking of something like this:
PHP Code:<?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
?>Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Oct 4, 2001, 07:34 #5
- Join Date
- Aug 2001
- Location
- Amsterdam
- Posts
- 788
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
combining two queries
I think what you mean is combining a querie to cross reference in two tables ... I would work something like this...
Code:Select * FROM table1, table2 WHERE table1.relatedid = table2.relatedid
Good luck,
PeanutsLast edited by peanuts; Oct 4, 2001 at 07:37.
the neigbours (free) WIFI makes it just a little more fun
-
Oct 6, 2001, 07:06 #6
- Join Date
- Feb 2000
- Location
- England
- Posts
- 568
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
Code:ARTICLE TABLE: | id | title | article | RELATIONS TABLE: | show_article | related_article |
Hope that explains it better
-
Oct 6, 2001, 07:13 #7
- Join Date
- Feb 2000
- Location
- England
- Posts
- 568
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Re: combining two queries
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...
Code:Select * FROM table1, table2 WHERE table1.relatedid = table2.relatedid
Good luck,
Peanuts
-
Oct 6, 2001, 07:29 #8
- Join Date
- Feb 2000
- Location
- England
- Posts
- 568
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
ok at the moment i am cycling through and doing an sql query for each article (ek).:
PHP Code: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');
-
Oct 6, 2001, 11:22 #9
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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..
).
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.
Code:SELECT id, related_article FROM articles, related_articles WHERE related_article = id AND id = $some_id
Code:SELECT id, related_article FROM articles, INNER JOIN related_articles ON related_article = id WHERE id = $some_id
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:
Code:SELECT id, related_article FROM articles LEFT JOIN related_articles ON related_article = id WHERE id = $some_id
“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.Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Oct 6, 2001, 11:50 #10
- Join Date
- Feb 2000
- Location
- England
- Posts
- 568
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
doh.. outer join is what i want
-
Oct 6, 2001, 11:55 #11
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hmm looking at your latest post maybe the join is not kosher.
Code: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?
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:
Code:SELECT title, id, related_article FROM kb_articles LEFT OUTER JOIN kb_related_article ON related_article = id AND show_article = $id
Code:+--------------+----+-----------------+ | title | id | related_article | +--------------+----+-----------------+ | matt is cool | 1 | NULL | | matt 0wnz | 2 | 2 | | matt lottery | 3 | 3 | | unrelated | 4 | NULL | | lottery | 5 | NULL | +--------------+----+-----------------+
PHP Code:if( $query[ related_article ] != '' ) {
$checked = "checked";
} else {
$checked = "";
} // end if
echo "<input type=.. $checked>";
So you wouldn't have the if .. block but simply:
PHP Code:$query = "select title, if( related_article is not null ) { 'checked' } else { null } )
from table... ";
while( loop ) {
echo "<input type.... $query[ checked ]>";
} // end while
.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Oct 6, 2001, 11:59 #12
- Join Date
- Feb 2000
- Location
- England
- Posts
- 568
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
Code:SELECT id, related_article FROM kb_articles LEFT JOIN kb_related ON related_article = id WHERE id = '4'
Code:show_article related_article Edit Delete 4 9 Edit Delete 4 8 Edit Delete 4 12 Edit Delete 4 13
Code: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
-
Oct 6, 2001, 12:02 #13
- Join Date
- Feb 2000
- Location
- England
- Posts
- 568
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Matt, you star:
Code:SELECT title, id, related_article FROM kb_articles LEFT OUTER JOIN kb_related ON related_article = id AND show_article = '$id'
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.
-
Oct 6, 2001, 12:14 #14
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
No prob, glad I could help!
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
Bookmarks