What happened to the Messenger Plus! forums on msghelp.net?
Shoutbox » MsgHelp Archive » Skype & Technology » Tech Talk » Perform Function on SQL Query Lookup?

Perform Function on SQL Query Lookup?
Author: Message:
Scripting Contest Winner


Posts: 2395
Reputation: 53
37 / Male / Flag
Joined: Jul 2003
O.P. Undecided  Perform Function on SQL Query Lookup?
Yea I know the title's confusing so I'll try and explain what I mean.

In the MPSounds.net DB the soundpack names are stored as 'The Simpsons Movie' etc, but I am now making the urls to pack like /soundpacks/the-simpsons-movie.

One solution would be to have a 2nd field in the table with the url friendly version of the name(the-simpsons-movie instead of The Simpsons Movie) but I'm just wondering if it's possible to do something along the lines of:

SELECT FROM blah WHERE MyFunction(title) = 'the-simpsons-movie'

where MyFunction does the lowercasing, removing non alphanumerics etc.  Just wondering if this is possible at all, or do I need to add a new field?

Thanks for any replied :)
SoundPacks   -   Scripts   -   Skins

that's not a bug, thats an unexpected feature
08-21-2007 12:48 PM
Profile E-Mail PM Web Find Quote Report
Full Member


Posts: 415
Reputation: 21
37 / Male / Flag
Joined: Sep 2004
RE: Perform Function on SQL Query Lookup?
cant you just use php for the function? convert to lower case and string replace the spaces, then use the output in the SQL select query.
08-21-2007 01:41 PM
Profile E-Mail PM Web Find Quote Report
Former Admin


Posts: 2365
Reputation: 81
Joined: Mar 2002
RE: Perform Function on SQL Query Lookup?
I'll save you the trouble.

I use this in my CMS which is floating around on the MyBB site, MyBB ideas, new MyBB Mods site, and all of my client sites.

This function is directly from the CMS module which builds the friendly page URLs, checking for duplicates, appending numbers etc.

Modify as needed:
function build_friendly_title($title, $page_id=0, $parent_id=0)
    global $db;
    $friendly_title = strtolower($title);
    $friendly_title = preg_replace("#\s#", "-", $friendly_title);
    $friendly_title = preg_replace("#([^a-zA-Z0-9-_])#", "", $friendly_title);
    $test_title = $friendly_title;
    $i = 1;
    if($page_id > 0)
        $page_id_add = " AND page_id != '$page_id'";
        $query = $db->query("SELECT friendly_title FROM pages WHERE friendly_title='$test_title' $page_id_add AND parent_id='".intval($parent_id)."' ORDER BY page_id ASC LIMIT ".($i-1).", 1");
        $existing_page = $db->fetch_array($query);
            $test_title = $friendly_title."-".$i;
    return $test_title;

You can, however, also define functions in MySQL - but they won't work with PHP callbacks etc.
08-21-2007 01:42 PM
Profile PM Find Quote Report
« Next Oldest Return to Top Next Newest »

Threaded Mode | Linear Mode
View a Printable Version
Send this Thread to a Friend
Subscribe | Add to Favorites
Rate This Thread:

Forum Jump:

Forum Rules:
You cannot post new threads
You cannot post replies
You cannot post attachments
You can edit your posts
HTML is Off
myCode is On
Smilies are On
[img] Code is On