Complicated query

[vigour]

Dear forum!

My mysql database have the following filenames in the picture row:

XXX 00001.jpg
XXX 00002.jpg
SES 00001.jpg
XHB 00001.jpg
XHB 00002.jpg
XHB 00003.jpg

I need only the first three characters from the filenames with no duplicates like this:

XXX
SES
XHB

This is my code.

$result = mysql_query("SELECT picture FROM products");
while($row = mysql_fetch_array($result))
 	{	
	$myArray[] = substr($row['picture'], 0, 3);
	}
$myArray = array_unique($myArray);

My question is, can this be done only with a query to the database?

[dagon]

SUBSTR()

[vigour]

Thanks :-)

The following fixed everything.

$result = mysql_query("SELECT DISTINCT SUBSTRING(picture,1,3) as picture FROM products ORDER BY picture");

dagon;10917470 wrote:

SUBSTR()

[bradgrafelman]

Note that if this query will be executed often, and if the filename format is strictly maintained throughout the data, you might consider splitting the first three letters into a separate column (and indexing it).