My SQL select command confusion

for a newbie

[stardoc]

I am trying to write a command in My SQL to do these three functions:

1. Select only the first word of the sentence from a table column (this column stores full sentences)

2. After this it arranges them alphabetically.

3. Third thing I want is that this should exclude duplicate words and then present the final data.

I tried something like this using the substring function, but I know the query is flawed.

SELECT sentence_field, sentence_id FROM table_sentences where sentence_field like '".$xyz."%' SUBSTRING_INDEX( `sentence_field` , ' ', 1 ) AS `first1` ORDER BY sentence_field

Obviously, it is giving me lots of errors.

Can someone help me by giving an example of correct select statement by which I can get it working. I will appreciate furthermore if any helping person can point out my error also (will help me in learning more).

Thanks in advance

[coopster]

Welcome to WebmasterWorld, stardoc.

You may find it easier to write out your queries in plain language, first, which you have already done. Next, write each piece out and test it...

1. Select only the first word of the sentence from a table column (this column stores full sentences)

SELECT SUBSTRING_INDEX(sentence_field, ' ', 1 ) AS first1;

2. After this it arranges them alphabetically.

SELECT SUBSTRING_INDEX(sentence_field, ' ', 1 ) AS first1 ORDER BY first1;

3. Third thing I want is that this should exclude duplicate words and then present the final data.

SELECT DISTINCT SUBSTRING_INDEX(sentence_field, ' ', 1 ) AS first1 ORDER BY first1; 
-- OR  
SELECT SUBSTRING_INDEX(sentence_field, ' ', 1 ) AS first1 GROUP BY first1 ORDER BY first1;

The problem you are going to run into is that you are trying to pull in an additional column here called

sentence_id

, which sounds like a PRIMARY KEY or UNIQUE column. There is no way you are going to be able to pull in DISTINCT or GROUPed rows based on the first word of a sentence if you throw in a key like that. Make sense? Have a look at this sample data...

+-------------------------------+---+ 
¦ This is the first sentence... ¦ 1 ¦ 
¦ That is the next sentence.... ¦ 2 ¦ 
¦ This is the third sentence... ¦ 3 ¦ 
+-------------------------------+---+

A query like you are trying to write is going to return ...

+------+---+ 
¦ This ¦ 1 ¦ 
¦ That ¦ 2 ¦ 
¦ This ¦ 3 ¦ 
+------+---+

Three unique rows, see?

[stardoc]

Thanks a ton for explaining it so beautifully. I tried it on database and it worked well. Now I am faced with another problem (sorry for asking simple and stupid questions, i am still a newbie to mysql and php). I have been trying to code this query in php and it is not showing any results. No errors are being displayed to give me a clue also. Please have a look at my code and correct my mistakes

<?php
global $dbi
$sql = "SELECT DISTINCT SUBSTRING_INDEX(sentence_field, '' '', 1 ) AS first1 from sentence_table ORDER BY first1', $dbi";
$result = $sql;
list( $sentence_field ) = sql_fetch_row( $result, $dbi );
echo "$sentence_field";
?> 

I know I have coded it in a silly way, but I have been trying hard to learn and understand php. Maybe one day I'll be able to help other newbies like myself on the forum.