SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot zook's Avatar
    Join Date
    May 2001
    Location
    Houston, tx
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem With Long Select Query

    I have a really long select query that is causing a lot of frustration. Here's what my tables look like

    table ARTICLES:
    ID | CATID | SUBCATID | AUTHID | TITLE | DATE | RATING | NUMRATINGS

    table AUTHORS (authid in articles is associated with the id in this table)
    ID | NAME | EMAIL | IMAGEURL

    what i need to do is select all the articles with whatever subcatid specified. then i need to use a while loop to display all of these. where i'm having problems is getting "name" from the authors table.

    here's the code i have:

    PHP Code:
    $result mysql_query("SELECT name.authors as name, 
    id.articles as id, title.articles as title, 
    intro.articles as intro, rating.articles as rating, 
    DATE_FORMAT(date.articles, '%b-%d-%Y') AS nicedate 
    FROM articles, authors WHERE CATID.articles=
    $catid AND 
    SUBCATID.articles=
    $subcatid ORDER by $order DESC");
    while ( 
    $row mysql_fetch_array($result) ) { 
    $id $row["id"];
    $title $row["title"];
    $intro $row["intro"];
    $date $row["nicedate"];
    $rating $row["rating"];
    $author $row["name"];

    i need an ID for the authors table in order to select the appropriate name. this ID must equal authid from the articles table. i have no clue how to do this. does anyone have any ideas?

  2. #2
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, try with this query
    Code:
    $q = "SELECT name, articles.id as id, title, intro, rating, DATE_FORMAT(date, '%b-%d-%Y') AS nicedate FROM articles, authors WHERE CATID=$catid AND SUBCATID=$subcatid AND AUTHID=authors.ID ORDER by $order DESC"
    by the way, where's column intro in table Articles? I don't see it in the definition.

  3. #3
    SitePoint Zealot zook's Avatar
    Join Date
    May 2001
    Location
    Houston, tx
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks Paul, that query gave me exactly what i was looking for.

    by the way, where's column intro in table Articles? I don't see it in the definition.
    intro is definately in the table, I must have left it out though when i posted that

  4. #4
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by zook
    thanks Paul, that query gave me exactly what i was looking for.
    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
  •