Jump to content

Simple Link Database


john-formby

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.