Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    30
    Rep Power
    1

    LEFT JOIN tables


    I'm trying to join 2 tables and make post if two of the variables are the same.

    Code:
    $r = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT table1.id, table1.url, table1.name, table1.size, table1.added, table1.views, table1.hits, table1.category, table2.url FROM table1 LEFT JOIN table2 ON table1.url = table2.url WHERE table1.url = table2.url ORDER BY added DESC") or sqlerr(__FILE__, __LINE__);
    just trying to post related things when the 2 url fields from both tables are the same
    the query above doesn't give any results
  2. #2
  3. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,106
    Rep Power
    9644
    Code:
    WHERE table1.url = table2.url
    Get rid of that.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    30
    Rep Power
    1
    don't need the where statement? hmm
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    30
    Rep Power
    1
    removed it, still doesn't display the results. hmm
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    30
    Rep Power
    1
    ohh wait.. it displays now but also have results that don't have anything in their url field. that's why i've placed that where statement...hmm
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,679
    Rep Power
    4288
    Originally Posted by pwpx2
    ohh wait.. it displays now but also have results that don't have anything in their url field. that's why i've placed that where statement...hmm
    are you sure you wouldn't prefer an INNER JOIN then?

    because rows that "don't have anything in their url field" are exactly what the LEFT JOIN is supposed to give you

    Comments on this post

    • pwpx2 agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,106
    Rep Power
    9644
    You have two "url"s in that query, and the second one (really, anything from table2) will be NULL. That's the one you'll see in your code.

    But... this doesn't make sense. Why are you bothering with the second table when there isn't anything in it in the first place?

    Comments on this post

    • pwpx2 agrees
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    30
    Rep Power
    1
    both tables have info in it
    all fields from both tables have info in it
    i'm just trying to display results that have the url field equal because it will show in a list of related things from the second table

    table2 --> i insert some details about a show.. this has name description and url/picture from lets say imdb or tvrage/tvdb and so on
    table1 ---> this is the table with content/comments/other stuff about other shows also

    on the show's info page would display all content/comments related to it; the thing that get them connected is the url field in both tables which i suppose need to match so that it can display related content to their respective show page

    don't know to explain it better
    the INNER JOIN shows no results so far; LEFT JOIN showed all results not just the ones with the same url as the show itself
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    30
    Rep Power
    1
    The INNER JOIN keyword selects records that have matching values in both tables.
    yes. i did some search about inner join and should do what i want but it doesnt display anything when changing to it.. hmm
    yes i have matching values in both tables for url field
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    30
    Rep Power
    1
    fixed it.. yeah inner join works. in the url field i had at the end "/" in one field and in the other field i didnt.
    wow.. that was easy as pie but hard for me since its the first time doing this
    thank you for all the help
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    30
    Rep Power
    1
    Hey guys..
    Have another dillema.. How to join multiple tables?

    I have this code for the moment

    Code:
    //---- Make sure shows exists
    $res = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT * FROM shows") or sqlerr(__FILE__, __LINE__);
    
    
    $arr = mysqli_fetch_assoc($res);
    $name = htmlspecialchars($arr["name"]);
    $showsid = htmlspecialchars($arr["id"]);
    $url = htmlspecialchars($arr["url"]);
    
    // === PM subscribed members
    $res_sub = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT ss.userid FROM subscriptions_shows AS ss INNER JOIN users as u ON ss.userid = u.id WHERE u.subscription_shows_pm = 'yes' AND ss.showid = " . sqlesc($showsid) . "") or sqlerr(__FILE__, __LINE__);
    while ($row = mysqli_fetch_assoc($res_sub)){
    $msg = "Hey there!!! \n A new article related to the show: " . htmlspecialchars($arr["name"]) . " has been uploaded!\n click HERE to see the show page!\n\nTo view your subscriptions, or un-subscribe, click [url=" . $URL . "/subscriptions_shows.php][b]HERE[/b][/url].\n\ncheers.";
    mysqli_query($GLOBALS["___mysqli_ston"], "INSERT INTO messages (sender, receiver, added, msg) VALUES('System', " . sqlesc($row["userid"]) . ", '" . get_date_time() . "', " . sqlesc($msg) . ")") or sqlerr(__FILE__, __LINE__);
    }
    // ===end
    Ok so remember when i wanted to make that page of some shows and then show related articles for the shows?
    That worked great. Now i would like to make a subscription thing so that if a user has it enable into his profile,he would receive a PM notification about an article being uploaded to a subscribed show.
    In theory that would work great, right!? Well i'm having trouble connecting the article url field to the show url field since both should be the same to appear into their respective show page.
    How to add into that query table1 url field to be equal/the same with table2 url field so that the system would know that the article belongs to the show with the same url field and then based on user's subscription for that show id field will get a notification...
    in theory...
    Because at the moment the system uploads articles but doesn't know on which show page it should place them and the end user doesnt receive any notification if subscribed; but on the show page they are displayed propely since that part was done already.
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,679
    Rep Power
    4288
    could you repeat the database question please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    30
    Rep Power
    1
    I have some pages that are displaying information about different tv-shows added by members. When articles are added on the site and match the tv-show name it will be displayed on their respective tv-show page.
    If article is about Prison Break it will appear on the Prison Break tv-show page. This is done by matching the article name or part of the name to the tv-show name.
    So far there are in use articles table and shows table doing an INNER JOIN for this.
    Then i wanted to make a subscribe system so if a member will subscribe to the Prison Break tv-show page will get a notification every time a new article related to Prison Break is added. For this i made a table called subscriptions and added a new field into the members table so they can select in their profile if they want to receive or not notifications.
    The subscribe is done on the tv-show page id; subscriptions table has 3 fields. id, userid and showid. When article is added the member userid would get notification to the respective showid.
    Problem i am facing is that notification is not sent at all, even if member has set to receive notification because system can't tell which article goes on which tv-shows page.
    So i wanted to add into the big query above a way to link them via url field from articles table and url field from shows table because this would be the only thing linking them, besides the name.
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,679
    Rep Power
    4288
    Originally Posted by pwpx2
    So i wanted to add into the big query above a way to link them via url field from articles table and url field from shows table ...
    maybe it would help if you showed the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    30
    Rep Power
    1
    Code:
    $res_sub = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT ss.userid FROM subscriptions_shows AS ss INNER JOIN users as u ON ss.userid = u.id WHERE u.subscription_shows_pm = 'yes' AND ss.showid = " . sqlesc($showsid) . "") or sqlerr(__FILE__, __LINE__);
    When the articles url field is matching OR similar to the shows url field, the system will check if the userid is subscribed to a show id and if user has notification turned on will send a notification to that member userid.
    This is what i'm trying to achieve.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo