Jump to content

Archived

This topic is now archived and is closed to further replies.

john-formby

Simple Link Database

Recommended Posts

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.