How to assign and output string "name" to the $query in PHP, if url matches in database

Question

I have this working query that outputs URL and click counts from database.

<?php
$query = "SELECT *,count(*) FROM db_data WHERE ip GROUP BY data_url";
    $sampleCount = mysql_query($query);
    $result = mysql_query($query);
    if($sampleCount > 0) {
        while ($row =mysql_fetch_array($result)) {
            $dataList_br .= '<div class="info_table">
                                 <div class="info_table1">
                                     <p>' .$row['data_url']. '</p>
                                 </div>
                                 <div class="info_table2">
                                     <p>' .$row['count(*)']. '</p>
                                 </div>
                             </div>';
        }
    } else {
        $dataList_br .= '<p class="warning">No data found in database. Please contact admin.</p>';
    }

This will output: "/somepath/index.php" and total count for example "24", and other sets of urls with total hit counts.

Question: How do I change URL output "/somepath/index.php" to the string name like "Page Name", instead of displaying URL.

Any help is greatly appreciated.

Just add another column in the database with the name of that link and display it. — ksg91, Jun 27 '12 at 16:40
Yes, that would be to easy then :) I have script that collects visitor data on the certain pages. And Im trying to create table that displays that data. What I need is to assign names only to those pages, instead of displaying url's. So, today I have maybe 24 records $row's, and tomorrow there will be maybe 1000 more record $row's. — crashtestxxx, Jun 27 '12 at 16:46
what do you call page name? is it the content of the `
` element? I guess your script which collect this data can also collect this information — jolivier, Jun 27 '12 at 16:50
That would be awesome. Collect Some Name from the page. Any hints how to do that? — crashtestxxx, Jun 27 '12 at 16:52
The mysql extension is outdated and on its way to deprecation. New code should use mysqli or PDO, both of which have important advantages, such as support for prepared statements. Don't use [`SELECT *`](http://stackoverflow.com/q/321299/) unless you're writing a DB administration program; select only the columns you need. — outis, Jun 27 '12 at 16:56
Consider picking a [meaningful username](http://tinyurl.com/so-hints). One advantage to this is others can use [at-replies](http://meta.stackexchange.com/questions/43019/how-do-comment-replies-work) and you'll get a notification that someone has addressed you in a comment. — outis, Jun 27 '12 at 16:58

score 1 · Accepted Answer · edited Jun 27 '12 at 17:40

Using this you will be able to show Page Title instead of URL

//let a url be so 
$url = 'http://google.com';

function file_get_contents_curl($url){
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_HEADER, 0);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

    $data = curl_exec($ch);
    $info = curl_getinfo($ch, CURLINFO_CONTENT_TYPE);
    return $data;
}

//fetching url data via curl
$html = file_get_contents_curl($url);

if($html) {
    //parsing begins here
    $doc = new DOMDocument();
    @$doc->loadHTML($html);
    $nodes = $doc->getElementsByTagName('title');
    $title = $nodes->item(0)->nodeValue;
    echo $title;
}

Note you can [format lines as code](http://meta.stackexchange.com/questions/22186/) by indenting them four spaces. The "{}" button in the editor toolbar does this for you. Edit your answer and try it out. Click the orange question mark in the editor toolbar for more information and tips on formatting. — outis, Jun 27 '12 at 16:59

score 1 · Answer 2 · answered Jun 27 '12 at 17:08

There is three ways to do this :

You can put one more column in the table that will contain the page name for a url. You can simply get that use that.
You can put a mapping in a constants file as associative array. From where you can get the page name value for that url.

You can simply put this case in your code here like this :

$query = "SELECT *,count(*) FROM db_data WHERE ip GROUP BY data_url";
$sampleCount = mysql_query($query);
$result = mysql_query($query);
if($sampleCount > 0) {
    while ($row =mysql_fetch_array($result)) {
        $dataList_br .= '<div class="info_table">
                             <div class="info_table1">
                                 <p>' .
if($row['data_url'] == "/somepath/index.php"){
     $html = file($row['data_url']);
     //or file_get_contents() method can be used.
     print_r($test['title']); // put index as per the html
}else{
    echo $row['data_url']
}. '</p>
                                 </div>
                                 <div class="info_table2">
                                     <p>' .$row['count(*)']. '</p>
                                 </div>
                             </div>';
        }
    } else {
        $dataList_br .= '<p class="warning">No data found in database. Please contact admin.</p>';
    }

You can either use file() or file_get_contents. Difference is that file() returns array, while other one returns string.

Note that remote file reading via `file_get_contents`, `file` &c will only work if [allow_url_fopen](http://www.php.net/manual/en/filesystem.configuration.php#ini.allow-url-fopen) is [enabled](http://php.net/ini_get). — outis, Jun 27 '12 at 17:42

How to assign and output string "name" to the $query in PHP, if url matches in database

2 Answers2