0

i have a MySQL statement which works - i can get the records requested - movies.* & groups.name. 

    $stmt= $mysqli->query("SELECT DISTINCT ebooks.*, groups.name FROM ebooks 
INNER JOIN ebooks_groups ON ebooks.uuid = ebooks_groups.ebookuuid 
INNER JOIN groups_users ON ebooks_groups.groupuuid = groups_users.groupuuid 
INNER JOIN groups ON groups_users.groupuuid = groups.uuid 
WHERE useruuid=".$get_useruuid." 
ORDER BY groups.name");

1/ However i need to grab another column from the groups table - namely groups.uuid

i tried

SELECT DISTINCT movies.*, groups.* FROM movies, groups

&

SELECT DISTINCT movies.*, groups.name, groups.uuid FROM movies, groups

but it retrieved no records.

2/ Then I had another look at my original code - ... FROM movies ... - how is this even working if i'm not selecting FROM movies, groups tables?

miken32
  • 42,008
  • 16
  • 111
  • 154
boomshanka
  • 186
  • 2
  • 11
  • 3
    **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST` or `$_GET` data directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Feb 14 '17 at 23:25
  • The question's about MySQL, so, if it was me I'd forget about all the other stuff and focus on that. Accordingly, see http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Feb 15 '17 at 01:07

1 Answers1

0

AFAIK, this is pure MySQL. PHP or not doesn't come into play.

First to understand is the implicit join: Explicit vs implicit SQL joins That understanding should solve at least half of your problem.

Secondly, I'd never code a SELECT * without a very good reason (and there's few). It makes much more sense to select just the columns you need instead of getting them all and even if you need all that are currently there, if you work on the database model later on, there might be more (or less!!) columns in the database and it'll be much harder to detect that your code needs updating if you don't have them explicitly listed.

For the rest I build my SQL queries slowly step by step. That helps a lot to debugging your queries esp. as you have the actual tables and some sample data ... [That should solve your other half of the question]

Community
  • 1
  • 1
  • Thanks, this got me on the right track to solving - the 'INNER JOIN' wasn't needed & my column names across tables were conflicting. – boomshanka Feb 15 '17 at 03:01