SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Thread: SQL query...

  1. #1
    SitePoint Evangelist
    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.

  2. #2
    SitePoint Evangelist
    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.

  3. #3
    Database Jedi MattR's Avatar
    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
    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.

  4. #4
    Database Jedi MattR's Avatar
    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
    ?>
    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.

  5. #5
    SitePoint Guru
    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
    Something like that anyway..

    Good luck,

    Peanuts
    Last edited by peanuts; Oct 4, 2001 at 07:37.
    the neigbours (free) WIFI makes it just a little more fun

  6. #6
    SitePoint Evangelist
    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 |
    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

  7. #7
    SitePoint Evangelist
    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
    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.

  8. #8
    SitePoint Evangelist
    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'); 

  9. #9
    Database Jedi MattR's Avatar
    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..
    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.
    Code:
    SELECT id,
           related_article
      FROM articles, 
           related_articles 
     WHERE related_article = id
       AND id = $some_id
    This can be re-written as:
    Code:
    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:
    Code:
    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.

  10. #10
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    doh.. outer join is what i want

  11. #11
    Database Jedi MattR's Avatar
    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?
    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:
    Code:
    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:
    Code:
    +--------------+----+-----------------+
    | 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:
    PHP Code:
    if( $queryrelated_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:
    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 
    That way you could dump the PHP logic and echo it right out. That is an exercise left up to you .

  12. #12
    SitePoint Evangelist
    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'
    On a set of 10 or so articles and some related data

    Code:
      show_article  related_article  
    Edit  Delete  4 9 
    Edit  Delete  4 8 
    Edit  Delete  4 12 
    Edit  Delete  4 13
    That query returned only

    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

  13. #13
    SitePoint Evangelist
    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'
    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.

  14. #14
    Database Jedi MattR's Avatar
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •