Jump to content


Photo

Simple Link Database


  • Please log in to reply
7 replies to this topic

#1 john-formby

john-formby
  • Members
  • PipPipPip
  • Advanced Member
  • 91 posts
  • LocationMahe, Seychelles

Posted 27 June 2006 - 01:24 PM

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
"I think computer viruses should count as life. I think it says something about human nature that the only form of life we have created so far is purely destructive. We've created life in our own image." ~ Stephen Hawking

#2 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 27 June 2006 - 02:49 PM

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:

//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++;
}

http://www.DaveLinger.com
dave at linger dot com

#3 john-formby

john-formby
  • Members
  • PipPipPip
  • Advanced Member
  • 91 posts
  • LocationMahe, Seychelles

Posted 27 June 2006 - 03:10 PM

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

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;


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

Thanks
"I think computer viruses should count as life. I think it says something about human nature that the only form of life we have created so far is purely destructive. We've created life in our own image." ~ Stephen Hawking

#4 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 27 June 2006 - 05:27 PM

what is the function of the "new_window" row?

<?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++;
}

?>

that code is leaving out the new_window row.
http://www.DaveLinger.com
dave at linger dot com

#5 john-formby

john-formby
  • Members
  • PipPipPip
  • Advanced Member
  • 91 posts
  • LocationMahe, Seychelles

Posted 27 June 2006 - 05:38 PM

Hi,

Ok, here is the code as it stands:

<?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();
?>

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
"I think computer viruses should count as life. I think it says something about human nature that the only form of life we have created so far is purely destructive. We've created life in our own image." ~ Stephen Hawking

#6 john-formby

john-formby
  • Members
  • PipPipPip
  • Advanced Member
  • 91 posts
  • LocationMahe, Seychelles

Posted 28 June 2006 - 12:05 PM

Anyone got any ideas?

Thanks
"I think computer viruses should count as life. I think it says something about human nature that the only form of life we have created so far is purely destructive. We've created life in our own image." ~ Stephen Hawking

#7 DaveLinger

DaveLinger
  • Members
  • PipPipPip
  • Advanced Member
  • 268 posts
  • LocationWV, USA

Posted 28 June 2006 - 12:24 PM

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
http://www.DaveLinger.com
dave at linger dot com

#8 john-formby

john-formby
  • Members
  • PipPipPip
  • Advanced Member
  • 91 posts
  • LocationMahe, Seychelles

Posted 28 June 2006 - 04:23 PM

Cheers, I hadn't thought of that

Thanks for your help
"I think computer viruses should count as life. I think it says something about human nature that the only form of life we have created so far is purely destructive. We've created life in our own image." ~ Stephen Hawking




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users