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)?

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.

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?

except there’s no relationship between these tables. JOIN requires a relationship. right?

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.

use two separate queries

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/en/if-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.

what you’re probably thinking of is php logic

the mysql IF statement is for use within stored procedures

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?

http://dev.mysql.com/doc/refman/5.0/en/commit.html

where exactly do the two unrelated tables come into this scenario?

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…


$accepted += 1;
strSQL = "UPDATE plan SET " . (($accepted > $decisionPoint) ? " price = price + increase, accepted = 0" : "accepted = {$accepted}") . " WHERE id = {id}";

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…

the only example of “if you code it right” worth shít –

START TRANSACTION … la de dah … COMMIT

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…

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)?

fear, uncertainty, and doubt, eh? :wink:

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

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?

using a mysql transaction is doing it correctly :slight_smile:

sorry, the only tutorial i’ve ever used was the mysql manual

OK, then I’ll pursue START TRANSACTION and open another topic when needed.

Thanks for your help r937 and DaveMaxwell.