CODEWITHSUNDEEP
phpmysql
Spoonk
Spoonk

Reputation: 703

Getting part of MySql row?

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>";
    }
}
?>

Upvotes: 1

Views: 289

Answers (4)

Pascal MARTIN
Pascal MARTIN

Reputation: 400932

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.

Upvotes: 1

Omry Yadan
Omry Yadan

Reputation: 33616

Something like this:

SELECT substring_index(substring_index('http://google.com/path?blah=1&2.1','/',3),'/',-1)

will work or full urls.

edit: added example for partial url:

select substring_index('google.com/path?blah=1&2.1','/',1);

In your case, replace

$res = mysql_query("SELECT * FROM smsads ORDER BY id DESC LIMIT 3");

with 

$res = mysql_query("SELECT link,substring_index(link,'/',1) host FROM smsads ORDER BY id DESC LIMIT 3");

This should work assuming your urls are always without the http:// part. (if they are with it, use the other example I provided).

Upvotes: 1

RC.
RC.

Reputation: 28197

Using substring on the LINK column on the database side or on the data once you have on the client side is an option, but depending on what you're doing here and how many rows you can expect in this table, I would consider breaking that field out into a separate column in your table so you only have to calculate it once at insert time. Then you can index it, easily block certain ones from being inserted, etc.

Going from there, depending on your needs, you may want to further normalize the LINK in order to store host and domain portions only once and having a key back to the path and filename. You can do all this, and then for convenience you can make a view to pull them all together for your application and users to use.

Upvotes: 0

rf_circ
rf_circ

Reputation: 1915

SELECT SUBSTRING_INDEX(link,'/',1);

should do the trick. It will return everything before the first slash character

Upvotes: 3

Related Questions