how to use 2 SELECT statements with prepared statements

Can you please share what you are trying to achieve and what database structure you have.

When a user clicks on a link, it uses a get request and the url ends up like:

somepage.php?prodId=105

I need to use the product id to retrieve records from 2 different tables. I tried an inner join but it did not work. I don't think I can use an inner join to say ON v.productId = p.productId WHERE productId = ?

Well, it didn't work for me anyway. So,  I want to use 2 Select statements, both using WHERE = ? and to retrieve records from both tables based on the ID.

I hope that makes sense?

In PHP, when an expression ends in a set of parentheses, either with or without anything inside, it's assumed to be a function call.

$x = ABC; // Find the defined constant 'ABC' and assign its value to $x
$x = ABC(); // Run the function named 'ABC' (or 'abc') and assign its value to $x

This explains why PHP thought you were trying to get to a function with the parentheses in the assignment statement for $stmt.

Without seeing your data structure and test data, we may give you incomplete answers, but you could try something like this...

$stmt = $link->prepare("SELECT `sometable.somedata`, `anothertable.moredata` FROM `sometable`, `anothertable` WHERE `product_id` = ?");
$stmt->bind_param("i", $safe_prodId);

Select allOpen in new window

@ Ray,

I was just about to ask that. Could I not just have two $stmt = $link->prepare(".....") together? That would make life easier. I was just wondering how that would effect performance?

We can solve the same issue in different ways. That's why we are asking for Database schema. We want to suggest you what is better not that makes life easier.

@ Mukesh Yadav,

Sorry, it's just hard to try explain everything in words.

In haste I have probably left info out. I have 2 tables, a product table and coupon table. The coupon table has a product ID in it for each product. As mentioned, I am using GET to retrieve the product ID. I want to get the product ID from both the products table and coupon table. In the coupon and product table there is a supplier id. I have to join either table to the supplier table so that I can get a proper supplier name and not just their ID.

On the page I want to display the coupons for that product, show the supplier name and product name. That is the goal.

They all center around the product ID. I just noticed though that rightly or wrongly, the product id is not called the same thing across tables.

So, one table has product_id and the other prod_id. I did that on purpose so that it didn't get confusing having everything in multiple tables called product_id.

But looking back at that now, it probably wasn't the correct thing to do on my part.

Performance doesn't matter at this point in the application's life cycle.  Thinking about performance now is like worrying about spark plug choices when you don't know whether you're getting a truck or a tractor.  Just build something that works -- a functional unit -- then as time permits later, come back to the unit and refactor the code that needs to be cleaned up, optimized, etc.

When you ask questions like this at E-E, there are a couple of concepts that will make it much easier to get good answers quickly.  These are the SSCCE and the MCVE.  Take a moment to read those, and you will immediately understand why (1) we can't give you the best answer, and (2) we are asking you for the database schema and your test data.

Based on Ray's answer, I like the concept of just getting something to work and then coming back to refactor it. So, I think it might be best for me to just use 2 separate prepared statements which is what I did now and it works. So, instead of sitting here for another few hours trying to get a potential performance boost, I have something which is working! :)

But, in saying that it would be nice to know how to optimize it for best performance for when I do come back and refactor.

Well, "performance" doesn't matter but this does!

rightly or wrongly, the product id is not called the same thing across tables

That is something I would stop work and fix right now.

Database elements are generally named with a hierarchy like this:

DatabaseName . TableName . ColumnName

You can omit parts of the hierarchy, starting from the left, so long as these parts are somehow evident to the DB engine.  The DatabaseName may be in a SELECTDB query, or a connection string.  The TableName may be in a FROM clause.  When queries are organized correctly, these parts will all be there, somehow, in a way that is unambiguous and consistent with your thinking.  

You can always be explicit about these parts.  The explicit hierarchy disambiguates, so it's perfectly reasonable to use one name for the product id in any number of different tables.  What's not reasonable is to give different names to the same information element.  That's a recipe for confusion.

Aha, well I am glad that has been cleared up. At least now I know that product_id should be product_id across however many tables I have that requires a product ID in it. Thank you!

to know how to optimize it for best performance for when I do come back and refactor

That's a separate question, and you should come back to E-E with a new question when you're ready to cross that bridge.  We will be glad to help.  When you do, please bring us the database schema and some test data so we can reproduce exactly what you're looking at.

Best regards, ~Ray

Thanks so much Julian. My supplier table doesn't have a product ID in it.

It just has :

supplier_id
supplier_name
and other details like email address, telephone number etc.

Coupon table:

coupon_id
product_id

Products table:

prod_id (which should be the the same as coupon table, so product_id)
supplier_id
prod_name

So, I need to get the product name from products table to echo out. I need to get the supplier name by joining supplier_id from products table to supplier table. Then also show all the coupons with the product ID from the coupon table.

When we started this an hour ago, we were talking about two tables.  Now we seem to be talking about three tables.

And that is why the SSCCE and MCVE are important concepts!

Yeah, this is my problem and I need to break that habit. I take something and try to make it simpler for the sake of asking the question because I find it hard to express in words what I am trying to do. So, I make it a smaller problem to try and adapt that to my actual problem. This is an epic fail method. Maybe if I tell myself that every time I don't ask a question properly a kitten will also die, I will ask properly!

I think because I have made a hash of this question, we should all just leave it for now. And when I am ready to come back to this and refactor it, I will ask PROPERLY.

Man, I feel so bad. So many kittens must have died because of me.

That is not an issue we just change the way in which we join the two tables

$query = <<< QUERY
SELECT
   *
FROM
   Products P LEFT JOIN Suppliers S ON P.supplier_id = S.supplier_id
   LEFT JOIN Coupons C on P.product_id = C.prod_id
WHERE
  P.product_id = ?
QUERY;

Select allOpen in new window

The principle remains the same. I think where you might be getting stuck is on the JOIN field and the WHERE (filter) field. There does not have to be a relationship between these two.

The JOIN just tells the database how we want to JOIN two tables together - what to take from a row in one table and what to match it to in a row from a different table.
The WHERE operates on the result of the above - it goes through all the joined records and finds the ones that match the condition in the where clause.

Thanks Julian,

I will definitely come back to this when I rework the code.