Avatar of Lennart Ericson
Lennart Ericson
Flag for Sweden asked on

How to print database content.

A database contains among other things the html code for pages. Some of the pages have hyperlinks. I want to print the various hyperlinks. I have tried this:

<?php
$host = 'localhost';
$usr = 'user name';
$pwd = 'password';
$db = 'dbs';
@mysql_connect($host,$usr,$pwd)
or die("<p>Kan ej ansluta till databasservern.</p>");
@mysql_select_db($db)
or die("<p>Kan ej ansluta till databasen " . $db . ".</p>");

$SQL = " SELECT * FROM jbs ";
$retid = mysql_query($SQL);

if (!$retid) { echo( mysql_error()); }

else {
        $antal = mysql_num_rows($retid);
        while ($row = mysql_fetch_array($retid)) {
            $text         = $row["text"];
            $kategori  = $row["kategori"];
            $titel         = $row["titel"];
            $sida        = $row["sida"];

$input = @file_get_contents($text) or die("Kunde inte läsa: $kategori - $titel");

$regexp = "/<a\s[^>]*href=(\"??)([^\" >]*?)\\1[^>]*>(.*)<\/a>/";
  if(preg_match_all("$regexp/siU", $input, $matches, PREG_SET_ORDER)) {
    foreach($matches as $match) {

if (!empty($regexp))       {
if (!empty($text))       {

for ($nr=0; $nr<$antal;)

echo "<tr><td>".$match[$nr][$nr+1]."</td></tr>";
$nr++;
        }}}}}}
?>

It doesn't work. And I don't know where I go wrong. Is it at "$input = @file_get_contents($text)"?
Could your experts, please, help me out?
Web DevelopmentPHPScripting Languages

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
Terry Woods

Do you want to print the hyperlinks, or the pages at the URLs of the hyperlinks?
Lennart Ericson

ASKER
I only want to print the hyperlinks.
SOLUTION
Ray Paseur

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Terry Woods

This line is either trying to fetch a website from a given URL, or trying to fetch the contents of a given filename:
$input = @file_get_contents($text) or die("Kunde inte läsa: $kategori - $titel");

Open in new window


As Ray says, we need to have a better understanding of what kind of data you're dealing with. I'd like to see the output of:

print "Text: '$text'<br>\n"; 

Open in new window

Run this just before the file_get_contents.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Lennart Ericson

ASKER
I added echo "<tr><td>$text</td></tr><tr><td>--- --- ---</td></tr>";
Got all the pages in the database I wanted. So far so good.
I took the code of the html page created and attached it to the "Attach file". Please see the file 'page.txt'.

-------------------------------------------

This might be irrelevant, however, when running the file without this added command (echo "<tr><td>$text</td></tr><tr><td>--- --- ---</td></tr>";) , I get the message "Kunde inte läsa: $kategori - $titel" comming from "$input = @file_get_contents($text) or die("Kunde inte läsa: $kategori - $titel");"
That's the reason I asked if you think this command is wrong.
page.txt
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Lennart Ericson

ASKER
TerryAtOpus and Ray_Passeur, thanks for your replies.
With your help I managed to get the output I want:

<?php
//error_reporting(E_ALL|E_STRICT);
//ini_set('display_errors', '1');
date_default_timezone_set('Europe/Paris');
include "../dbconfig/dbconfig.php";

@mysql_connect($host,$usr,$pwd)
or die("<p>Kan ej ansluta till databasservern.</p>");
@mysql_select_db($db)
or die("<p>Kan ej ansluta till databasen " . $db . ".</p>");
$nr = 0;
$SQL = " SELECT text, kategori, titel, sida FROM jbs ";
$retid = mysql_query($SQL);
if (!$retid) { echo( mysql_error()); }
            else {


    $numrows = mysql_numrows($retid);
    $backcolour = "FFFFFF";
    for ($i=0; $i <$numrows; $i  )
    {
        $rs = mysql_fetch_array($retid);
        if ($backcolour == "FFFFFF")
            {
            $backcolour = "e8e8e8";
            }
        else
            {
            $backcolour = "FFFFFF";
            }

            while ($row = mysql_fetch_array($retid)) {
            $text = $row["text"];
            $kategori = $row["kategori"];
            $titel = $row["titel"];
            $sida = $row["sida"];
$nr++;
$regexp = "/<a\s[^>]*href=(\"??)([^\" >]*?)\\1[^>]*>(.*)<\/a>/";

if (preg_match("$regexp", "$text")) {
  echo "<tr><td>&nbsp;</td><td bgcolor=\"$backcolour\">$nr</td><td bgcolor=\"$backcolour\">&nbsp;</td><td bgcolor=\"$backcolour\"><strong>$kategori - $titel:</strong> <a href=\"http://www.jbs.nu/$sida\" target=\"_blank\" >$sida </a></td><td>&nbsp;</td></tr>
        <tr><td>&nbsp;</td></tr>";
}}}}
?>

This code takes ages to finish. Any suggestions on how to speed it up?
Terry Woods

How many rows of data are you working with, and what's the typical size of each value in the text column?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Lennart Ericson

ASKER
There are in all 93 rows of data. To give you an understanding of the size of each value I attach a file.
jbs-tabell.csv
Terry Woods

Making the wildcards non-greedy may help a little:
$regexp = "/<a\s[^>]*?href=(\"??)([^\" >]*?)\\1[^>]*>(.*?)<\/a>/";

Also, if you want to print just the link (and not the text that is linked), then this should suffice:
$regexp = "/<a\s[^>]*?href=(\"??)([^\" >]*?)\\1[^>]*>/";
Ray Paseur

Thanks for the points and thanks for using EE, ~Ray
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck