Finding One Row In A Couple Tables

[nodirtyrockstar]

I am a beginner with MySQL and was wondering what method advanced users would recommend to perform the simple task of looking through two tables to find one row. I don't need code from you, just a general idea of what type of query is best for this situation. If you can offer an explanation of why you like the method, that's even better.

 

Thanks!

[nodirtyrockstar]

Am I making this too hard? Can I just write:

 

SELECT * FROM tbl1, tbl2 WHERE id = tmpId;

 

??

[nodirtyrockstar]

I tried this and it is saying my field name is ambiguous.

[Jessica]

First of all, you should be explicit with your join.

 

A join would look like this:

SELECT t1.fields, t2.fields 
FROM t1
LEFT (or INNER) JOIN t2 
ON t1.fk = t2.pk
WHERE t1.field = 'value';

 

However, you need to explain your goal a little better. " looking through two tables to find one row" doesn't make much sense.

Edited December 18, 2012 by Jessica

[nodirtyrockstar]

Alrighty, I tried a number of different approaches and came up with this:

 

SELECT * FROM `records` WHERE id = 'ccr-01-001' UNION SELECT * FROM `merch` WHERE id = 'ccr-01-001';

[nodirtyrockstar]

Oh hey Jessica, sorry, I didn't see your reply. What I meant is that I have one ID that should only appear in one row in one of my two tables. The query I posted above seems to work. Thank you!

[nodirtyrockstar]

Actually, I have another question related to this. At runtime, my script actually has the table value stored in a PHP variable. Is it possible to use bind_params when preparing the query to plug in the table name?

 

$stmt = $mysqli->prepare("SELECT * FROM ? WHERE `id` = ?;");
$stmt->bind_param('ss', $tbl, $id);

 

I tried the code above and it isn't working.

[Jessica]

I don't think you can bind a table name, it's for values only.

[kimi2k]

mysqli_stmt::bind_param

 

http://php.net/manua....bind-param.php =>

Binds variables to a prepared statement as parameters

Edited December 18, 2012 by kimi2k

[nodirtyrockstar]

I found the same answer at this link, Jessica.

 

It basically says that you need to plug the table name in using a variable, which means you'll have to sanitize/validate it first.

 

Thanks for your time everyone!

Edited December 18, 2012 by nodirtyrockstar