select only one column distinct mysql

Question

I have 3 tables - users, journals, journaltags. I select data from 3 tables using chosen tags.

$sqltheme="SELECT users.id as uid, users.name, users.surname, users.avatar, journals.id, journals.author_id, journals.title, journals.text, journals.create_date, journaltags.name as jname FROM users
INNER JOIN journals ON users.id=journals.author_id
INNER JOIN journaltags ON journaltags.journal_id = journals.id WHERE journals.create_date LIKE ? AND journals.author_id=? AND (".$expression.") ORDER BY journals.id DESC LIMIT 10";
$stmtheme=$conn->prepare($sqltheme);
$stmtheme->execute($array);

But if two tags is the same for one journal then it is selected the same journal two times. How can I make DISTINCT journals.id. I tried GROUP BY journals.id but it didnt help.

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, May 15 '17 at 14:47
I use prepared statements $stmtheme=$conn->prepare($sqltheme); $stmtheme->execute($arraytags); So, The string is safe — Laravelchik, May 15 '17 at 14:51
@JayBlanchard, woow! That's so cool. I mean, I'm going through it.. :) — lu5er, May 15 '17 at 14:52
@Jay Blanchard Learn about prepared statements for pdo)) mysqli is deprecated) — Laravelchik, May 15 '17 at 14:57
@Laravelchik MySQLi *is not deprecated*. The MySQL (`mysql_*`) API is the one which is deprecated. — Jay Blanchard, May 15 '17 at 15:00
Masivuye Cokile DISTINCT is not working. I want only DISTINCT journals.id. It can repeat other columns. — Laravelchik, May 15 '17 at 15:02
Here is the reference for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) — Jay Blanchard, May 15 '17 at 15:02
@Laravelchik you need to get in the habit of [accepting answers](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) which help you to solve your issues. You'll earn points and others will be encouraged to help you. — Jay Blanchard, May 15 '17 at 15:03
@ Jay Blanchard My code is safe. I use pdo prepared statements. I want to get answer the question. — Laravelchik, May 15 '17 at 15:07

score 0 · Answer 1 · answered May 15 '17 at 16:22

If I understand correctly, your problem is that the journaltags table may have one or more rows with a duplicated journal_id and name column value, right? You can simply add a distinct clause to your select statement, after the word SELECT:

SELECT DISTINCT users.id as uid, users.name, users.surname, users.avatar, journals.id, journals.author_id, journals.title, journals.text, journals.create_date, journaltags.name as jname FROM users
INNER JOIN journals ON users.id=journals.author_id
INNER JOIN journaltags ON journaltags.journal_id = journals.id WHERE journals.create_date LIKE ? AND journals.author_id=? AND (".$expression.") ORDER BY journals.id DESC LIMIT 10

The reason that your GROUP BY journals.id did not work, is because you had other columns that needed to be included in the grouping as well. Adding distinct is essentially a short way of writing group by [all selected columns]

For example. when user choose two tags and these tags exists in some journal then this journal duplicated. I solved my question using php functions) Maybe its bad practice but it works) — Laravelchik, May 15 '17 at 19:45
Ah, I see. `distinct` will resolve that issue as well. Glad to hear you got it resolved though! I'll mention that as a rule of thumb, you should have your database doing as much of the work as you can. It's usually far more efficient (a lot of very smart people have put a lot of thought into how the database should handle things like returning distinct records, and it doesn't waste overhead sending your application records that are not needed), and makes hunting down problems (both bugs and performance) easier. — RToyo, May 15 '17 at 21:22

select only one column distinct mysql

1 Answers1