PHP MYSQL How to use Substring

Question

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 ?

Define didn't work. Was there an error? Was the result other that the expected one? If yes, what? — Andrius Naruševičius
– Andrius Naruševičius, Commented Mar 19, 2014 at 9:11
@AndriusNaruševičius I updated my question, it returned the same value.. — SHT
– SHT, Commented Mar 19, 2014 at 9:14

MrCode · Accepted Answer · 2014-03-19 09:17:42Z

3

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.

answered Mar 19, 2014 at 9:17

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SHT Over a year ago

Hmm I use BackboneJS for my frontend, so I dont output the track_duration with echo $track_duration. The query returns the data as JSON, so somehow this does not work...

JaMaBing · Accepted Answer · 2014-03-19 09:20:00Z

2

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

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

answered Mar 19, 2014 at 9:20

JaMaBing

1,04115 silver badges32 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL How to use Substring

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related