ed
Member
Registered: 10th Sep 03
User status: Offline
|
Not done this before, but I want to get videos, audio or images from my database using regex from a mime column. The data in the column would be something like video/quicktime so I would think the regex for that would be video/(.*) to get all videos?
So, why doesn't this work?
SELECT * FROM media_attachments WHERE mime REGEXP '/video/.*/'
Anyone know? It keeps on returning 0 rows, even though there is a video/quicktime row in there.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
couldn't you use LIKE?
'^video' will match anything with video in it. If you want to match multiple strings then use '^(video|audio)' (you get the idea)
[Edited on 11-08-2010 by Dom]
|
ed
Member
Registered: 10th Sep 03
User status: Offline
|
Sorted it with RegEx. Forgot that a / doesn't mean anything without at \ in front of it. So the query is:
SELECT * FROM media_attachments WHERE mime REGEXP '/video(.*)/'
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
I think LIKE is less expensive?
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by Ian
I think LIKE is less expensive?
LIKE is usually around 5/6 times quicker (from what i've experienced in the pasted using JMeter) than REGXP matching similar patterns. Unless you need to do funky pattern matching then i'd stick with LIKE.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Although if you're bothered about speed I'm sure you could normalise the MIME string and query from the junction table. That would be even quicker as you're not doing substring matches anywhere then.
Should join nice as well because you're using foreign keys which are therefore primary in the other tables and therefore indexed.
|
ed
Member
Registered: 10th Sep 03
User status: Offline
|
I guess what I could do is only add the content part of the mime. I'm actually not interested in the type after the file has been encoded because it can only be an mp4, ogv or webm if it's a video or an mp3 or ogg if it's audio. No RegEx or like at all then 
|
ed
Member
Registered: 10th Sep 03
User status: Offline
|
But I think I'll use like for now seeing as it seems more suited 
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Slightly off topic, you server siding the encoding of files? How you going about that?
|
ed
Member
Registered: 10th Sep 03
User status: Offline
|
I've got FFMPEG installed server side and I'm running Linux commands from a php script that gets file info from the MySQL database. Have problems with permissions at the moment though and can't seem to use chmod in php. I think I can run cron jobs as root though which might work...
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by ed
I've got FFMPEG installed server side and I'm running Linux commands from a php script that gets file info from the MySQL database. Have problems with permissions at the moment though and can't seem to use chmod in php. I think I can run cron jobs as root though which might work...
hate dealing with permissions and PHP under linux, complete ballache most of the time. Although it's usually because the PHP script doesn't have the right permissions, so it might be worth temporarily chmod 777/rwxrwxrwx the php script.
By the way, you seen the PHPVideoToolkit? It's basically a wrapper/class for ffmpeg and FLVTools. It's what i've used in the past for php video encoding stuff and it works a treat - http://phpvideotoolkit.sourceforge.net/index.html
|
ed
Member
Registered: 10th Sep 03
User status: Offline
|
Not seen that. The only related thing I've seen is ffmpeg-php which doesn't do anything re endcoding, will have to take a look...
It seems that ffmpeg will only encode if you've got 0777 set on the file and directory. Easy enough to achieve if I could get the chmod function to work in php. Keeps throwing an error even though the php user is the owner of the file. I guess I know what my Sunday is going to involve 
|
