SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    access two tables simultaneously

    I need to access two tables simultaneously that don't share a common column. Is that possible? If so what's the keyword(s)?

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    What do you mean don't have a common column? How would they be related?

    You need to look into the JOIN syntax. Here's a pretty basic but good explanation.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    I need to access two tables simultaneously that don't share a common column. Is that possible? If so what's the keyword(s)?
    yes it's possible, but the "keyword" depends on what's in those tables

    can you give an outline of the columns in rach table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    except there's no relationship between these tables. JOIN requires a relationship. right?

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    one table has 15 columns the other only has one. the table with one col tracks transactions codes. If a transaction codes exists no more queries will be executed with that code - a kind of elseif control for queries.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    one table has 15 columns the other only has one.
    use two separate queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That's what I'm working with at this moment. mysql if...elseif...else is what I really need. I've looked at http://dev.mysql.com/doc/refman/5.0/...statement.html.

    Is it as easy as it looks? If so, why isn't it used more? Seems WHERE is usually used in place of IF.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    mysql if...elseif...else is what I really need.
    what you're probably thinking of is php logic

    the mysql IF statement is for use within stored procedures
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK.

    The issue I'm struggling with is locking down a table row long enough to to deal with all the if-then-and-buts.

    for example here are two simple queries coded to run one right after the other:

    UPDATE plan SET accepted = accepted + 1 WHERE id = '" . $id . "'"

    UPDATE plan SET price = price + increase, accepted = 0 WHERE id = '" . $id . "' AND accepted => decision_point"

    as written, I think the value in the accepted column could exceed the the decision point by quite a bit depending on other users that mange to get in between these two lines of code with a line of code that just says: UPDATE plan SET accepted = accepted + 1 WHERE id = '" . $id . "'" . I'd like to avoid that. Any ideas?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    Any ideas?
    http://dev.mysql.com/doc/refman/5.0/en/commit.html

    where exactly do the two unrelated tables come into this scenario?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    You're going to need to go outside SQL to do that, and it's pretty easy if you've already selected the accepted and decision point fields from the database...

    Code:
    $accepted += 1;
    strSQL = "UPDATE plan SET " . (($accepted > $decisionPoint) ? " price = price + increase, accepted = 0" : "accepted = {$accepted}") . " WHERE id = {id}";
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    ...if you've already selected the accepted and decision point fields from the database...
    then they can be updated by somebody else before you get there!!

    do a search for race condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    then they can be updated by somebody else before you get there!!

    do a search for race condition
    Yes, I know - but unless your connection SUCKS, the chances of you getting rickrolled like that aren't super high, especially for a case like this. You'd have to have people clicking within milliseconds of this happening if you code it right....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the only example of "if you code it right" worth shít --

    START TRANSACTION ... la de dah ... COMMIT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Normally I'd agree with you, but in php/mysql, not so much. I've had to rip some transaction/commit stuff out because the performance sucked. Might be the framework involved, but it definitely caused a LOT of problems.....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  16. #16
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    GET_LOCK seems to be the answer. The discussions aren't real encouraging about its desirability. Sounds like GET_LOCK causes it own problems. What's your opinion (lock/don't lock)?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    Might be the framework involved, but it definitely caused a LOT of problems.....
    fear, uncertainty, and doubt, eh?

    the original poster clearly stated:
    I think the value in the accepted column could exceed the the decision point by quite a bit depending on other users that mange to get in between these two lines of code
    so in this case i would say just ignoring the race condition is ~not~ the right choice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry about that. I missed your #14 post: START TRANSACTION ... la de dah ... COMMIT ... la de dah ... COMMIT (if done correctly).

    What shout I be on the look-out for to do it "correctly"?

    EDIT:

    Any START TRANSACTION tutorials you can recommend?

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    using a mysql transaction is doing it correctly

    sorry, the only tutorial i've ever used was the mysql manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK, then I'll pursue START TRANSACTION and open another topic when needed.

    Thanks for your help r937 and DaveMaxwell.


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
  •