How to display multiple urls stored in same column using php?

Question

I'm trying to display social media urls stored in same column like so :

https://twitter.com/username,
https://www.facebook.com/username,
https://username.tumblr.com/,

Here is my displaying code :

$stmt = $pdo->prepare("SELECT * FROM profiles");
$stmt->execute();
$row = $stmt->fetch();

$temp = array();
$row['ik_social'] = trim($row['ik_social'], ',');
$temp   = explode(',', $row['ik_social']);
$temp   = array_filter($temp);

$item = array();
foreach($temp as $k => $value){
    $item[]= trim(str_replace(array('[',']'),"",$value ));
}

foreach($item as $k=> $val){
    If (stristr($val, "codepen.io")){
        echo '<a target="_blank" rel="noopener" href="'.$val.'" title="codepen">codepen</a>';
    }else if(stristr($val, "github.com")){
        echo '<a target="_blank" rel="noopener" href="'.$val.'" title="github">github</a>';
    }else if(stristr($val, "aycan.net")){
        echo '<a target="_blank" rel="noopener" href="'.$val.'" title="facebook">facebook</a>';
    }else if(stristr($val, "youtube.com")){
        echo '<a target="_blank" rel="noopener" href="'.$val.'" title="youtube">youtube</a>';
    }
}

It works fine, but it seems like I dont need that much code, is there a short way to do it ?

Just store the name of the social media site with the URL. Then you can output the link with the name in a simple loop. — John Conde, Dec 24 '20 at 03:26
Urls comes from auto added inputs, not specified inputs. see this please https://stackoverflow.com/q/65386167/12232340 — , Dec 24 '20 at 03:28
Normalize the schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad). — sticky bit, Dec 24 '20 at 03:29
You can still determine the site when you save it to the database. That way you only do it once and not on every page load. — John Conde, Dec 24 '20 at 03:30
You already have the code. But instead of using it to build the link, use it to save the name into the database when you save the URL. (There's a better way to do this but this is a good place for you to start). — John Conde, Dec 24 '20 at 03:33
Oh I got you, how would I know which value is going in which column ? and I dont even know how many inputs filled by user because they are auto inputs. — , Dec 24 '20 at 03:36

score 0 · Accepted Answer · 2020-12-24T04:34:59.193

If you have stored urls comma separated you can try this in one line, if you get a result from DB:

$url_array = array_unique(explode(',', preg_replace('/\s+/', '', $row['ik_social'])));

preg_replace would remove all white space, tab and line break.

explode would create the array list.

array_unique would clean duplicated.

You can try to use parser of url and rebuild like you need, also the url must be validate to work fine...

Example:

<?php

$url_array=[
    'https://twitter.com/username',
    'https://www.facebook.com/username',
    'https://username.tumblr.com/',
    ];
    
    foreach($url_array as $k=> $url){
        if (filter_var($url, FILTER_VALIDATE_URL) && $url != '') {
            $comp_url = parse_url($url);
            $main_url = $comp_url['scheme'].'://'.$comp_url['host'].'/';
            $host_url = $comp_url['host'];
            echo '<a target="_blank" rel="noopener" href="'.$main_url.'" title="'.$host_url.'">'.$host_url.'</a><br>';
        } else {
        echo("$url is not a valid URL");
        }
    }

result:

<a target="_blank" rel="noopener" href="https://twitter.com/" title="twitter.com">twitter.com</a><br><a target="_blank" rel="noopener" href="https://www.facebook.com/" title="www.facebook.com">www.facebook.com</a><br><a target="_blank" rel="noopener" href="https://username.tumblr.com/" title="username.tumblr.com">username.tumblr.com</a><br>

Documentation:

https://www.php.net/manual/en/function.parse-url.php

i have use and array has example; but you need iterate your database return result.

note: this only work with natural Urls and not with app url shortcut like twitter or skype.

How to pass urls to array ? they are in one column `ik_social` — , Dec 24 '20 at 04:13
I'm having error `Undefined index: host in D:\wamp\www\html\modules\test.php on line 12` — , Dec 24 '20 at 04:16
yes, latest code throwing this error. it show urls with that error is well. this line `$main_url = $comp_url['scheme'].'://'.$comp_url['host'].'/';` — , Dec 24 '20 at 04:18
@Dlk I don't know what that url is; but already add another update that supports url validation — , Dec 24 '20 at 04:21
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/226368/discussion-between-francisco-nunez-and-dlk). — , Dec 24 '20 at 04:21

How to display multiple urls stored in same column using php?

1 Answers1