john-formby Posted June 27, 2006 Share Posted June 27, 2006 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 Quote Link to comment Share on other sites More sharing options...
DaveLinger Posted June 27, 2006 Share Posted June 27, 2006 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] Quote Link to comment Share on other sites More sharing options...
john-formby Posted June 27, 2006 Author Share Posted June 27, 2006 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 Quote Link to comment Share on other sites More sharing options...
DaveLinger Posted June 27, 2006 Share Posted June 27, 2006 what is the function of the "new_window" row?[code]<?phpif (!$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. Quote Link to comment Share on other sites More sharing options...
john-formby Posted June 27, 2006 Author Share Posted June 27, 2006 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 Quote Link to comment Share on other sites More sharing options...
john-formby Posted June 28, 2006 Author Share Posted June 28, 2006 Anyone got any ideas?Thanks Quote Link to comment Share on other sites More sharing options...
DaveLinger Posted June 28, 2006 Share Posted June 28, 2006 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 Quote Link to comment Share on other sites More sharing options...
john-formby Posted June 28, 2006 Author Share Posted June 28, 2006 Cheers, I hadn't thought of that Thanks for your help Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.