chop Posted April 1, 2016 Share Posted April 1, 2016 I have 2 tables for which I want to write a select query containing an inner join: Table 1 is "art" Table 2 is "other_sales" the (simplified) query so far looks like this (without the JOIN) : $q = "SELECT image_id, image_title FROM art ORDER BY $order_by"; The" image_id" here is a primary key which is also used as a foreign key in the table "other_sales". Let's say that the image_id in "art" = 7. The number 7 may appear any amount of times in the table "other_sales" under the foreign key "image_id" I am trying to write a query that creates a report something like this" A Sunny Day, 7, 12 Where "A Sunny Day" is the image_title (from "art") 7 is the image_id (from "art"- primary key) 12 is the amount of times that the number (7) appears in the column "image_id" (foreign key) in table "other_sales" I have tried many different ways to work this with only the error " no result for $r" -- which basically means the query isn't working. Here is one example of what I have tried: $q = "SELECT image_id, image_title FROM art INNER JOIN other_sales ON other_sales.image_id = art.image_id $order_by"; I know that I have to include a COUNT(*) somewhere in there and have tried in many ways. I sifted through the joins section in the book and found COUNT() on page 178 but that just returns the number of values in a column. I have used inner joins before but not this complicated. I hope I made this clear without going on too much. thanks Link to comment Share on other sites More sharing options...
Larry Posted April 5, 2016 Share Posted April 5, 2016 It should be something along the lines of SELECT image_title, image_id, COUNT(other_sales.*) FROM art JOIN other_sales ON (art.id=other_sales.image_id) GROUP BY (other_sales.image_id) The key being that you need to do the COUNT() on the same table as you do a GROUP BY. Link to comment Share on other sites More sharing options...
chop Posted April 5, 2016 Author Share Posted April 5, 2016 (edited) thank you for that work.. I assume you meant art.image_id in "ON (art.id=other_sales.image_id)" I tried the following and it works: 1. THIS WORKS: $q = "SELECT image_id, image_title FROM art ORDER BY $order_by"; 2. THIS DOES NOT WORK: $q = "SELECT image_id, image_title, COUNT(other_sales.*) FROM art JOIN other_sales ON (art.image_id=other_sales.image_id) GROUP BY (other_sales.image_id)" 3. THIS DOES NOT WORK: $q = "SELECT image_id, image_title, COUNT(other_sales.*) FROM art" I did number 3 just to test if the error might be in the JOIN part of number 2 In each case I get: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given I also tried it without the comma after "image_title," in case the COUNT() didn't count as as the last item in the list. ------- I didn't know that you could put COUNT(other_sales.*) before the FROM art because it is referring to another table (it is to the left of FROM art. I'm glad to have learned that! Sorry to be a pain! I've searched other sites for the answer but I can't find anything that specifically illustrates this. Also, other forums don't seem to explain things quite as well as you (and some others) on your forums. Also, I rechecked the table names.. no problem there. Would it help if I included a snapshot of the mySQL tables? Probably not but here they are anyway: table art table other_sales on this table, for instance, I am trying to count how many times image_id 369 is listed in the image_id column. The 369 refers to the unique image_id in the art table. Sorry if I'm over explaining, but it's as much for me as anyone else. Edited April 5, 2016 by chop Link to comment Share on other sites More sharing options...
Larry Posted April 13, 2016 Share Posted April 13, 2016 When trying to figure out a query like this, you really must run it directly on the database. It'll be faster, easier, AND you'll see the error messages. Speaking of which, can you convert your "it didn't work" statements into the actual MySQL error messages? "It didn't work" could mean almost anything, whereas error messages will tell us exactly _why_ it didn't work. The mysqli_fetch_array() error message isn't relevant here because that only says the query returned nothing. We want to investigate deeper. Link to comment Share on other sites More sharing options...
chop Posted April 14, 2016 Author Share Posted April 14, 2016 (edited) Don't know why I didn't think about that. Probably because all the queries I've ever needed have been simple enough to just write them out and they'd work. If I remember correctly, PHP admin will also give me the php code for queries I run. Queries with joins (of different sorts) have always been a weakness of mine. So perhaps messing around in this way will be good exercise. Thanks for the suggestion, I will post the result if I get something that works. Edited April 14, 2016 by chop Link to comment Share on other sites More sharing options...
chop Posted April 19, 2016 Author Share Posted April 19, 2016 I ran your suggested query in phpMyAdmin. Here are the results: SELECT image_title, image_id, COUNT(other_sales.*) FROM art JOIN other_sales ON (art.id=other_sales.image_id) GROUP BY (other_sales.image_id) LIMIT 0, 30 MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM art JOIN other_sales ON (art.id=other_sales.image_id) GROUP BY (other_s' at line 1 Open new phpMyAdmin window I've been trying other variations and have downloaded the MySQL Manual (1000 pages) to find a close example. Link to comment Share on other sites More sharing options...
Larry Posted April 20, 2016 Share Posted April 20, 2016 That's helpful! Try changing COUNT(other_sales.*) to COUNT(other_sales.id) or whatever column is your PK there. Link to comment Share on other sites More sharing options...
chop Posted April 20, 2016 Author Share Posted April 20, 2016 I tried that first thing: SELECT image_title, image_id, COUNT(other_sales.image_id) FROM artJOIN other_sales ON (art.id=other_sales.image_id) GROUP BY (other_sales.image_id) Now I get: #1052 - Column 'image_id' in field list is ambiguous I don't know why it is ambiguous. COUNT(other_sales.image_id) is the column where I wan to count how many times the number (image_id) is listed. I couldn't find any examples like this on a Google search. Link to comment Share on other sites More sharing options...
Larry Posted April 21, 2016 Share Posted April 21, 2016 It's the other image_id that's ambiguous: the one you're selecting without referencing it as table_name.image_id. Link to comment Share on other sites More sharing options...
chop Posted April 21, 2016 Author Share Posted April 21, 2016 (edited) Wow! Works Perfectly now. Who'd a thunk it? For the record, this is the working code; SELECT image_title, art.image_id, COUNT(other_sales.image_id) FROM art JOIN other_sales ON (art.image_id=other_sales.image_id) GROUP BY (other_sales.image_id) I changed : "SELECT image_title, image_id" to "SELECT image_title, art.image_id" Just one last thought about ambiguity: Why, then, doesn't it think that within "SELECT image_title, art.image_id," that 'image_title' is also ambiguous and should be 'art.image_title' ? Could it be that 'image_id' is used more that once and is being referenced from 2 different tables? I'm hanging this one on my wall. Edited April 21, 2016 by chop Link to comment Share on other sites More sharing options...
chop Posted April 21, 2016 Author Share Posted April 21, 2016 (I tried to edit above once more but ran out of time so... last words) My own way of thinking is that "it" would have known that the first 'image_id' meant 'art.image_id' because it is followed by the phrase 'FROM art' which, should be the default table for any column to its left that is NOT prefaced by 'table_name.' . Make sense? Anyway, I'm not going to write anyone a letter explaining the world according to Chop. I have what I need, working code Thanks for hanging in there, Larry. Link to comment Share on other sites More sharing options...
Larry Posted April 22, 2016 Share Posted April 22, 2016 So glad to hear it and thanks for letting us know! As for your remaining question, my assumption is that "image_title" is only a column in one of the tables. Link to comment Share on other sites More sharing options...
Recommended Posts