Mysql JOIN

[Luignata]

I'm trying to use join two queries into a single query using JOIN. However... doesn't seem to be working.

$getData = @mysql_query("SELECT `a`.`user`,`a`.`data`,`a`.`cost`,`b`.`name` FROM `table_a` `a` JOIN `table_b` `b` ON `a`.`user`=`b`.`user`");

I'm unsure how to display my tables, so I resorted to just creating two simple tables on my own website. If you do not feel it's safe to start randomly clicking links, below is a copy of the HTML code that you can post somewhere so you don't have to click the link if you feel unsafe about it.

http://www.intotheunknownls.com/tables.html

<TABLE BORDER=1><TR><TD COLSPAN=3>Table A</TD></TR>
<TR><TD>User</TD><TD>Data</TD><TD>Cost</TD></TR>
<TR><TD>1</TD><TD>Data1</TD><TD>Cost1</TD></TR>
<TR><TD>2</TD><TD>Data2</TD><TD>Cost2</TD></TR></TABLE>


<TABLE BORDER=1><TR><TD COLSPAN=2>Table B</TD></TR>
<TR><TD>User</TD><TD>Name</TD></TR>
<TR><TD>1</TD><TD>Name1</TD></TR>
<TR><TD>2</TD><TD>Name2</TD></TR></TABLE>

I think I'm going about joining in all the wrong ways but.. bah. I just need to be able to use the "User" column from Table A to determine which rows to get in Table B. "User" will always be the same in both tables.

[ninnypants]

Try this:
"SELECT a.user, a.data, a.cost, b.name FROM table_a as a, table_b as b where a.user = b.user"
It's simpler than an actual join and probably works better for what you're doing here. also I don't think you need the ` around the shorthand table then again around the column I believe it should go around both. You actually don't even really need them.

[Luignata]

Hmmm, that didn't seem to work.

SELECT `a`.`user`,`a`.`data`,`a`.`cost`,`b`.`name` FROM `table_a` AS `a`, `table_b` AS `b` WHERE `a`.`user`='1');

Didn't work. Echoing mysql_error() resulted in nothing. So I tried:

SELECT `a`.`user`,`a`.`data`,`a`.`cost`,`b`.`name` FROM `table_a` AS `a`, `table_b` AS `b` ON `a`.`user`=`b`.`user` WHERE `a`.`user`='1'

But that gave me an error in my Syntax when echoing mysql_error().

:confused:

[ninnypants]

Sorry I forgot to tell you that you need to specify what b.user needs to be equal to like this:
"SELECT a.user, a.data, a.cost, b.name FROM table_a as a, table_b as b where a.user = 1 AND b.user = a.user"

This will select the data from tables a and b where a.user is equal to one and there is a b.user equal to the a.user

[bazz]

or better:


SELECT a.user
, a.data
, a.cost
, b.name
FROM table_a a
INNER
join table_b b
on a.user = b.user
and a.user = ?

? is the variable for the user whose data you want. leave that last line out if you want to get all users data, where they are in both tables.


bazz

[oesxyl]

or better:


SELECT a.user
, a.data
, a.cost
, b.name
FROM table_a a
INNER
join table_b b
on a.user = b.user
and a.user = ?

? is the variable for the user whose data you want. leave that last line out if you want to get all users data, where they are in both tables.

bazz
php don't have a bind like perl for variables so op must use a php variable in query instead of question mark ?.

best regards

[bazz]

yeh sorry for being confusing; I knew that.

I was just trying to show the query and I don't know the variable he/she would be using. ? was just a substitute for that and was not meant as a placeholder. Thanks for pointing it out oesxyl.

bazz

[Fou-Lu]

MySQLi will allow you to bind you're parameters to mysqlstmt objects, but mysql will not.

$stmt = $mysqli->prepare("SELECT a.user, a.data, a.cost, b.name
FROM table_a a
LEFT JOIN table_b b ON (a.user = b.user)
WHERE a.user = ?");
$stmt->bind_param('i', $userid);
$stmt->execute();

The huge advantage of course is that we don't need to formulate the proper quotations and we can start batch inserts without rewriting the actual query.