获取MySql行的一部分？

Question

Is there any way to get just a part from MySql cell and ignore everything after predefined character? For example, I have row with cell's "ID, LINK, PHONE". Inside LINK cell DB wrights links like mysite.com/mypicture.jpeg. Is there any way, to get just "mysite.com" from this cell and ignore everything after slash, so I can echo it inside anchor tag?

Edit: To be absolutely clearly I'm posting the existing code:

<?PHP
if(mysql_connect($db_host,$db_user,$db_pass)) {
    mysql_select_db($db_name);
    mysql_query("CREATE TABLE IF NOT EXISTS smsads(id bigint unsigned primary key auto_increment, link varchar(255), fromnum varchar(60))");
    $res = mysql_query("SELECT * FROM smsads ORDER BY id DESC LIMIT 3");
    while($row = mysql_fetch_object($res)) {
        $http_link = $row->link;
        if(strstr($http_link, 'http') === FALSE) $http_link = 'http://'.$http_link;
        echo "<div id=\"banner\"><a href=\"{$http_link}\" target=\"_blank\"><img src=\"{$http_link}\" /></a></div>";
    }
}
?>

Answer 1

A solution, on the PHP side, might be to use strpos and substr, to :

• find the position of the character
• and extract what comes before/after.

A bit like this, for instance :

$str = 'mysite.com/mypicture.jpeg';

$position = strpos($str, '/');
if ($position !== false) {
    $before = substr($str, 0, $position);
    $after = substr($str, $position+1);
    var_dump($before, $after);
}

Which will get you :

string 'mysite.com' (length=10)
string 'mypicture.jpeg' (length=14)

If you know there will always be one (and only one) slash in your data, you can also use explode and list :

list($before, $after) = explode('/', $str);
var_dump($before, $after);

Which will give you the same output :

string 'mysite.com' (length=10)
string 'mypicture.jpeg' (length=14)

Another idea would be to do that on the SQL side -- if you need both fields, though, doing it on the PHP side is not a bad idea (both sides, PHP and SQL, are valid, actually).

---

EDIT after the comments.

What about something like this, for your loop :

while($row = mysql_fetch_object($res)) {
    $http_link = $row->link;
    $position = strpos($http_link, '/');
    if ($position !== false) {
        $before = substr($http_link, 0, $position);
        if(strstr($http_link, 'http') === FALSE) {
            $http_link = 'http://'.$http_link;
            $before = 'http://' . $before;
        }
        echo "<div id=\"banner\"><a href=\"{$before}\" target=\"_blank\"><img src=\"{$http_link}\" /></a></div>";
    }
}

For each line, you are getting the $row->link to the $http_link variable, like you did before.

Then :

• you extract the part that comes before the '/' ; and use it for the a href tag, to link to the root of the website
• you use the full URL from the DB, for the img src tag, to display the image

And you don't forget to add 'http://' if necessary to both URLs, like you did at the first place when you only had one.

Note : that code is not tested, but should give you a hint of a possible solution.

Hope this helps :-)