Simple Link Database

[john-formby]

Hi everyone,

I am very new to PHP and have a question I hope you can help me with. I set up a static HTML site a while back which was basically a very simple directory of links. I have since modified it and the pages now include PHP includes for header, footer & navigation. However, the links are still static HTML. What I want to do is set up a small database that will store all the links. I want to be able to set the number of links per page and at some point in the future be able to search. Only problem is that I don't have the first idea how to go about this and was hoping someone could explain.

I am going to be adding Google Adsense adverts to the pages so I am not sure if this makes a difference.

Thanks,

John

[DaveLinger]

well first obviously you'd have to set up the database. You'd probably want a row for the URL, a row for the database, and a row for the time the link was submitted. Then you can retrieve the first five results like so:

[code]
//connect to your database here

$query="SELECT * FROM your_table ORDER BY time_submitted_row DESC LIMIT 5,1";
$result=mysql_query($query);

mysql_close();

$i=0;
while ($i < 5) {

$url=mysql_result($result,$i,"url_row");
$description=mysql_result($result,$i,"description_row");
$newdes = stripslashes($description);

echo "<a href=\"$url\">$url</a><br>$newdes<br><br>";

$i++;
}
[/code]

[john-formby]

EDIT: I have created my database and here is my table:

[code]CREATE TABLE `links` (
  `link_id` int(11) NOT NULL auto_increment,
  `category_code` varchar(10) NOT NULL default '0',
  `link_text` varchar(254) NOT NULL default '',
  `url` varchar(254) NOT NULL default '',
  `description` text NOT NULL,
  `time_added` timestamp(14) NOT NULL,
  `new_window` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`link_id`)
) TYPE=MyISAM AUTO_INCREMENT=226;[/code]


Not really sure how to modify the code to display the results. If you could offer any advice I would be really grateful.

Thanks

[DaveLinger]

what is the function of the "new_window" row?

[code]<?php

if (!$link = mysql_connect('YOUR HOST', 'YOUR USER', 'YOUR PW')) {
   echo 'Could not connect to mysql';
   exit;
}

if (!mysql_select_db('YOUR DB', $link)) {
   echo 'Could not select database';
   exit;
}

$query="SELECT * FROM links ORDER BY time_added DESC LIMIT 5,1";
$result=mysql_query($query);

mysql_close();

$i=0;
while ($i < 5) {

$url=mysql_result($result,$i,"url_row");
$link_id=mysql_result($result,$i,"link_id");
$link_text=mysql_result($result,$i,"link_text");
$category_code=mysql_result($result,$i,"category_code");
$description=mysql_result($result,$i,"description");
$newdes = stripslashes($description);

echo "<a href=\"$url\" title=\"Link ID $link_id - Category $category_code\">$link_text</a><br>$newdes<br><br>";

$i++;
}

?>[/code]

that code is leaving out the new_window row.

[john-formby]

Hi,

Ok, here is the code as it stands:

[code]<?php
$hostname="localhost";
$dbname="pagelinks";
$user="********";
$pass="********";

$db = mysql_connect($hostname, $user, $pass);
mysql_select_db($dbname, $db);

$pageSize = 5;

$pageNumber = isset($_REQUEST['page']) ? intval($_REQUEST['page']) : 1;

$query="SELECT * FROM links WHERE category_code = 20 ORDER BY link_text ASC LIMIT ".(($page-1) * $pageSize).','.$pageSize;
$result=mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {

$link_text=$row['link_text'];
$description=$row['description'];
$url=$row['url'];
$popup = ($row['new_window'] == 1);
$newdes = stripslashes($description);

if($popup) {
  $link = '<a href="'.$url.'" onclick="window.open(this.href);return false">';
} else {
  $link = '<a href="'.$url.'" >';
}
echo "$link_text<br>$newdes<br/>".$link."$url</a><br><br>";
}

$numberOfRecords = mysql_result(mysql_query('SELECT count(*) FROM links WHERE category_code=20'),0,0);
for($i = 0; $i < $numberOfRecords; $i += $pageSize) {
  $page = 1 + floor($i/$pageSize);
  echo '<a href="'.$_SERVER['PHP_SELF'].'?page='.$page.'">'.$page.'</a>';
}

mysql_close();
?>[/code]

It seems to work ok. It queries the db and returns the correct results. It now opens the links in a new window and the pagination is working.

There is just one very small problem left to solve. When I click a link from the navigation bar it loads the page with the page numbers, but not the rows from the db. I have to click n one of the page numbers for it to query the db and show the results. I am sure this is a very simple thing but I can't figure it out, can anyone help?

Thanks

[john-formby]

Anyone got any ideas?

Thanks

[DaveLinger]

if I understand correctly you could just change the link in your navigation bar to whatever is contained in the url for the first page

[john-formby]

Cheers, I hadn't thought of that

Thanks for your help