CODEWITHSUNDEEP
phpmysql
SHT
SHT

Reputation: 720

PHP MYSQL How to use Substring

I have this application where I want to display various albums from musicartists. My issue though is that the track_duration-field gives me for example 00:04:16 but what I want is 04:16 - so I tried to integrate the MySQL SUBSTRING-function but without luck.

My original MySQL-string looks like this:

SELECT products.title AS album_title, products.genre AS album_genre,
    products.ccws_pro_id AS product_upc, track_title, track_duration 
    FROM products
INNER JOIN track_albumn_information ON products.ccws_pro_upc = track_albumn_information.product_upc 
AND track_albumn_information.artist_id =  '".$artist_id."'  LIMIT 0 , 30

So, I tried to do track_duration, SUBSTRING(track_duration, 3) FROM products...

But that didnt work. It returned the same value 00:04:16.

How can i do this ?

Upvotes: 1

Views: 87

Answers (2)

JaMaBing
JaMaBing

Reputation: 1041

You should check if there are realy leading zeros, otherwise you can do a "replace" by

SELECT TRIM(LEADING '00:' FROM '00:04:16');

Upvotes: 2

MrCode
MrCode

Reputation: 64526

You are selecting both the original and the modified version.

Because you didn't give the substring version an alias, your PHP will be seeing a field that is literally called SUBSTRING(track_duration, 3), plus the original as track_duration.

Looks like you just want the modified version, which can be done by giving it an alias:

SELECT products.title AS album_title, products.genre AS album_genre, products.ccws_pro_id AS product_upc, track_title, SUBSTRING(track_duration, 3) AS track_duration FROM products

Now in your PHP if you output the track_duration field for the rows, it will contain the modified data.

Upvotes: 3

Related Questions