Need Help With Inner Join With Where Clause

[chop]

I have a similar SELECT query to the one below that works fine. The only difference is that it doesn't have a WHERE clause. I'm not really sure if I have it in the correct place. I want the WHERE to apply to 'DBTABLE' table.

As a result I get this warning:

 

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

 

which I assume means there's an error in the way I set up the query

 

$q = "SELECT
".DBTABLE.".imageID,
".DBTABLE.".imageName,
".DBTABLE.".H,
".DBTABLE.".W,
".DBTABLE.".matted,
".DBTABLE.".media,
".DBTABLE.".framed,
".DBTABLE.".imageTitle,
".DBTABLE.".imageDescription,
".DBTABLE.".comment,
".DBTABLE.".thePrice,
".DBTABLE.".available,
".DBTABLE.".hold,
".DBTABLE.".imageID,
".DBTABLE.".isSold,
".DBTABLE.".dateSold,
".DBTABLE.".sellPrice,
".DBTABLE.".soldTo,
".DBTABLE.".contactInfo,
".DBTABLE.".dateCreated,
".GALLERY.".oil,
".GALLERY.".water,
".GALLERY.".airlight,
".GALLERY.".equine,
DATE_FORMAT(dateAdded, '%M %d, %Y') AS da
FROM ".DBTABLE."
INNER JOIN ".GALLERY."
ON ".DBTABLE.".imageID=".GALLERY.".imageID
WHERE imageID = $imageID";

[HartleySan]

imageID in the WHERE clause is ambiguous. You need to specify which table's imageID you want. I can't say more than that, since I don't know the values of DBTABLE and GALLERY.

[chop]

Yes, it appears you are correct. I assumed that the database name was optional where you have it mentioned in the FROM. It seems that you can eliminate it from some of the items but not from others. I thought that maybe, because the DBTABLE is the one "calling" the other table in on the Join, that the only required mention, in this case, is the GALLERY table. I actually tried eliminating the ".DBTABLE.". part from several items under the Select and it still works.

But, I suppose the WHERE could be applied to either of the databases? thank you

[Larry]

Is this query actually across two different databases (as you keep saying) or two different tables?

[chop]

Sorry, I misused the term. I am actually talking about tables in the same database as the constants DBTABLE and GALLERY (both tables)

[Larry]

In that case, then, yes, the where clause could be applied to either table's imageID value because they need to be the same for the JOIN to work.

[chop]

That makes sense now.

 

My need for databases / tables has been on a simple level so far. But every so often I get into a situation where I know, conceptually, what needs to be done. Realizing that I'm not the first person to encounter this, I know there must be a solution though have no idea what it might be, or be called. So, every once in a while it's good to re-visit the book(s) just to see what I missed (or skimmed over because I didn't think I needed it at the time) or to get back to this forum. It's a matter of "filling in the holes" within a conceptual framework that becomes more solid with time. It's my non-linear way of learning something which has always been my style.

 

I'm so appreciative of having the great reference books around even years after I've finished "studying them". They sit on my bookshelf like soldiers at constant attention waiting to be called into active duty. The forums add a dynamic and human character to the overall process trying to understand something that sometimes seems beyond the bounds one would normally venture without either of these two aids.

 

I wish you could see my copy of PHP 6 and MySQL 5. The pages are turning soft with wear, there are 32 plastic tabs that identify all the "most important" ideas and, because it was left out on my deck in a thunderstorm two years ago, the binding is peeling back and the pages are wavy and curled at the edges making the book itself about 20% thicker than when it was new. It's more valuable to me now despite the beating it has taken. I imagine a Kindle might not have fared quite as well ( and where do the tabs go?).

 

Here's to all the good books and to the people of these forums.

Chop

[HartleySan]

Good post. Thanks for sharing that.

My 3rd edition of the book got caught in the rain as well, and I used it for years with the pages all stuck together before finally buying the 4th edition.

And yeah, I learn the same way you do. When I read a book like this, I start out with the simple stuff and/or the stuff I think I need, and then on subsequent reads (which there are many of), I slowly fill in the gaps.

Thanks a lot for coming around to the forums and sharing your experiences.

Best of luck with your current project.