...

View Full Version : Generating link list from database? OOPS NOT RESOLVED



Angrysafer
09-07-2009, 04:17 PM
I am extremely new to PHP and don't know where to begin with this, but it seems simple enough. I want to dynamically generate a list of links from a database. So for example I would have some sort of database or file on my website that was just a single column on numbers:

125375
193745
058214

Like that and it would insert the numbers into two links:

1. site.com/link?id=125375 site.com/link2?id=125375
2. site.com/link?id=193745 site.com/link2?id=193745
3. site.com/link?id=058214 site.com/link2?id=058214

Also, I want to format the output into an ordered list. I don't really know what I am doing, so any help is appreciated. Thanks.

whizard
09-07-2009, 04:32 PM
First of all, you need your database setup like this:

| ID | link |
|---|-----|
| 1 | 125375|

etc


Then, with your PHP, try this:



<?php

$query = "SELECT `link` FROM `TABLENAMEHERE`";
$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_assoc($result))
{
?>
<a href="site.com/link2?id="<?php print $row['link']; ?>">LINK TEXT HERE</a>
<
<?php
}
?>


I hope that helps a little, please post again with any questions.

Dan

Angrysafer
09-07-2009, 04:58 PM
Wow, thanks! I wasn't expecting a response this quickly. I think I can use your code to do what I want, but I'm not experienced with databases either. I have cpanel access, and I just created a mySQL database, but all I can seem to do is add users, which I did and gave all privilleges to, but I don't see how to edit the database. Or am I doing something completely wrong?

I hope it is ok to ask this here. I might need more help with the php side once I figure out my database.

PappaJohn
09-07-2009, 05:08 PM
Your cpanel should give you access to phpmyadmin - which will allow you to edit the database

Angrysafer
09-07-2009, 05:34 PM
Alright, I created a table inside my database with a field with a single int value. Is this correct? In any case I can't seem to edit the actual database. Thank you for holding my hand through this, haha.

Zangeel
09-07-2009, 05:42 PM
You want to set the ID row as the primary key, and AUTO_INCREMENT then simply add the linkid row.

Angrysafer
09-07-2009, 05:55 PM
Ok, I made the changes to my table, but how do I add a row. I swear I am actually poking around in this thing on my own, but I'm not having much luck, and I am pretty intimidated. I already had to remake my table a few times because I think I messed something up.

whizard
09-07-2009, 06:25 PM
You should have one row in your table set like this:

name: id
type: int
extra: auto_increment

and another like this:

name: link
type: int


That will work with the code I gave.

:thumbsup:

Dan

Angrysafer
09-07-2009, 06:41 PM
You should have one row in your table set like this:

name: id
type: int
extra: auto_increment

and another like this:

name: link
type: int


That will work with the code I gave.

:thumbsup:

Dan

Are rows the same thing as fields then?

I've added two fields named id and link. I tried inserting one of my ids, I'm not really sure if it worked or not. I'm hopelessly confused.

I've tried reading a few tutorials, but they all seem to have a slightly different phpMyAdmin version or something. I'm having trouble finding a tutorial for someone who has never looked at a database in their life.

Also, I tried plugging in your php code into Amaya and it is giving me an error of "not well-formed (invalid token)" for this line:


<a href="site.com/link2?id="<?php print $row['link']; ?>">LINK TEXT HERE</a>

It doesn't seem to like the second quotation mark. I'm not sure what the problem is.

whizard
09-07-2009, 06:47 PM
Sorry, I made a couple mistakes.

First, yes, by row I meant field. My bad.

Second, the second quote shouldn't be there.
It should be like this instead:
<a href="site.com/link2?id=<?php print $row['link']; ?>">LINK TEXT HERE</a>

OK, the ID is auto increment, which means that you don't have to put anything in the field, and the database will automatically insert the number in ID.

Dan

Angrysafer
09-07-2009, 09:00 PM
Well Amaya is still giving an error, even with the code modified. Still no idea what's up with that. Sometimes those errors just go away. What should I be using to insert data into my database? Is there a query command I should be using, or is there an easier way, say importing a plain text file?

Another thing that is bothering me is that your code doesn't seem to reference a database, just a table. Am I missing something?

Haha, also Amaya doesn't seem to support php? I am figuring out notepad++ right now.

whizard
09-07-2009, 09:18 PM
Sorry, gotta go for class, but as far as referencing a db, good point, check out

http://us2.php.net/manual/en/function.mysql-select-db.php

Dan

Angrysafer
09-07-2009, 09:31 PM
I looked around in there, so do I need to create a user with say just view permissions to access the database but not put my user and password right into the php code?

Also, this isn't a very complicated set of data, is there anyway to avoid mySQL entirely, it's giving me a real headache. Could I have the php just read through a text file, either with hard breaks, or comma separated, or something?

Scriptet
09-07-2009, 10:24 PM
Here's how you connect to your MySQL database, you do need to put your user and pass inside the code.



<?php

//Connect to MySQL Database
$user = 'your username goes here';
$pass = 'your password goes here';
$db = 'your database name goes here';

mysql_connect(localhost, $user, $pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());


echo "<h1>We have successfully connected to the MySQL database now we can play around with our tables, e.g insert and retrieve data via queries</h1>";
?>


Oh and of course you can do it by a file aswell, read here (http://www.tizag.com/phpT/fileread.php), you just simply read the file and the echo out the contents line by line.

Angrysafer
09-07-2009, 10:50 PM
Scriptet, I just read the tutorial on that php site. I think this is how I want to proceed. It just seems simpler. I decided to make a few files to test things, but it didn't work out.

I have phptest.php:

<html>
<body>
<?php
$myFile = "links.txt";
$fh = fopen($myFile, 'r');
$theData = fgets($fh);
fclose($fh);
echo $theData;
?>
</body>
</html>

and links.txt:

link1
link2
link3
link4
link5

I uploaded both files to my server, but my output looks like this.


link1

It is failing to read the rest of the lines. I didn't alter the code from his tutorial, so I am a little perplexed.

fifth
09-07-2009, 11:05 PM
You need to loop through each line in your text file. try;



$fh=fopen('links.txt', 'r');
while(!feof($fh)) {
$line=fgets($fh);
echo $line;
}
fclose($fh);

Scriptet
09-07-2009, 11:30 PM
Yeah it is reading one line of text at a time so you need to loop through

Angrysafer
09-08-2009, 01:24 AM
Ok, thanks, that did the trick. I've started to format the output now, and I'm stuck on another problem.



<html>
<body>
<?php
print '<ol><li>';
$fh=fopen('links.txt', 'r');
while(!feof($fh)) {
$line=fgets($fh);
echo $line;
print '<br>';
}
print '</li></ol>';
fclose($fh);
?>
</body>
</html>


Is producing a list, but only the first entry is numbered. Like this:



1. Link1
Link2
Link3


Any tips?

Scriptet
09-08-2009, 01:57 AM
Ok, thanks, that did the trick. I've started to format the output now, and I'm stuck on another problem.



<html>
<body>
<?php
print '<ol><li>';
$fh=fopen('links.txt', 'r');
while(!feof($fh)) {
$line=fgets($fh);
echo $line;
print '<br>';
}
print '</li></ol>';
fclose($fh);
?>
</body>
</html>


Is producing a list, but only the first entry is numbered. Like this:



1. Link1
Link2
Link3


Any tips?

Yup each list item is within the <li> </li> tags and the <ol></ol> is meant to wrap around them all. For more info: http://www.w3schools.com/HTML/html_lists.asp

So infact you want:



<html>
<body>
<?php
print '<ol>';
$fh=fopen('links.txt', 'r');
while(!feof($fh)) {
$line=fgets($fh);
echo "<li>".$line."</li>";
print '<br>';
}
print '</ol>';
fclose($fh);
?>
</body>
</html>


Note how I added the <li></li> within the loop so it gets added round each individual item:thumbsup:

Angrysafer
09-08-2009, 03:06 AM
Alright! I've got it working perfectly now! Thank you so much for you help. Now that I'm looking at a list of over 500 links, I was wondering if I could paginate it somehow. I looked at a few tutorials, but once again, they've completely confused me. There must be some sort of code I can just add to mine.

Also, i find it amusing that print and echo are nearly identical. I switched all instances of print to echo. That shouldn't cause any problems later, right?

Scriptet
09-08-2009, 03:18 AM
Nope but most people use echo for minor reasons. Why have you switched it?

Anyway for pagination you could do the links like:

<a href="currentpage.php?page=2">Page 2</a>
<a href="currentpage.php?page=3">Page 3</a>
<a href="currentpage.php?page=4">Page 4</a>

Then you can do:



if($_GET['page']){

//We are on a page

$page = $_GET['page'] //store the page number in the variable

$amountOfRecordsPerPage = 25;
$recordStart = $page * $amountOfRecordsPerPage;

//Then loop through the records from $recordStart for $amountOfRecordsPerPage
}


Sorry for the poor explanation, I have to go now, but that is the basic idea (one of many ways to do it). If you are still struggling i'll provide an example of some sort tommorow.

Scriptet
09-08-2009, 06:31 PM
Hi,

I decided to try and make a pagination script, just to try and test myself really. It should work as expected:



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php

//Set The Name of The Text File Containing the List Below
$fileName = "url-list.txt";
//Set the Amount of Records You Want To Show Per Page Below
$perPage = 25;

//End of User Config - You Shouldn't Need to Modify Anything Below This Line//


//Count Amount of URL's (lines) present in file
$count = count(file($fileName));

//Calculate Amount of pages required
$pagesReq = round($count / $perPage);


if($_GET['page']){
//Then We Are Given a Page Through the URL
if($_GET['page'] >= 1 && $_GET['page'] <= $pagesReq){
//The Page is within Valid Range
$page = $_GET['page'];
$endRecord = $perPage * $page;
$startRecord = ($endRecord - $perPage) + 1;
displayPages($pagesReq, $page);
displayRecords($fileName, $startRecord, $endRecord);
}
else echo "<h1>Error: The Page Supplied Is Not Valid!</h1>";
}
else{
//No Page Given Through the URL so Display the First 25 Records
$page = 1;
$startRecord = 1;
$endRecord = $perPage;
displayPages($pagesReq, $page);
displayRecords($fileName, $startRecord, $endRecord);
}

displayPages($pagesReq, $page);


//Function to Display the Records for the Current Page
function displayRecords($file, $startRec, $endRec){
$handle = fopen($file, 'r') or die("Cannot Open File!!");
$olNum = $startRec + 1;
echo "<ol start=\"$startRec\">";
while(!feof($handle)){
$z++;
$line = fgets($handle);
if(($z >= $startRec) && ($z <= $endRec)){
//The line is between the required range so display it
echo "<li>".$line."</li>";
}
}
echo "</ol>";
fclose($handle);
}

//Function To Display Links to Pages
function displayPages($totalPages, $currentPage){
for($x = 1; $x <= $totalPages; $x++){
if($x != $currentPage)echo "<a href=\"index.php?page=$x\">$x</a> | ";
else echo "$x | "; //Do not highlight the current page
}
}
?>
</body>
</html>


The only things you need to modify is the name of the .txt file and the amount of records you want to show per page, and it should run like clockwork hopefully.

I'd be greatful for any feedback/improvements because i'm still learning myself :)

Should I be using functions here or is it no appropriate? Also I know there is a possible issue in that the file.php and the file.php?page=1 will display the same data, possible duplicate content problem? Is there a way to redirect file.php to file.php?page=1 someway, or how else could I go about it?
Also there may be a better way to find a record on a specific line in a file, I am just doing it by counting the lines as we loop through the file, and seeing if it matches startRecord and then displaying it if it does.

Angrysafer
09-08-2009, 11:07 PM
I uploaded you code, and it seems to do nearly exactly what I want, so I can handle the modifications. But, just a few thoughts.

1. The pagination should probably be a relative link instead of an absolute one. I uploaded your code as phptest.php on my server and it displayed the first page fine, but clicking on the next page took me back to my index.

2.Also I think navigation at the bottom is enough. I don't think it needs to be above and below the page.

Great work though, do you have a paypal? I'd like to pay you a few bucks, Scriptet, if that's alright.

Edit: I finished modifying the script for my needs except for changing the link to a relative one. Do I need to assign a variable that reads the file name of the current page, or is there is an easier way? Thanks.

Scriptet
09-08-2009, 11:34 PM
I uploaded you code, and it seems to do nearly exactly what I want, so I can handle the modifications. But, just a few thoughts.

1. The pagination should probably be a relative link instead of an absolute one. I uploaded your code as phptest.php on my server and it displayed the first page fine, but clicking on the next page took me back to my index.

2.Also I think navigation at the bottom is enough. I don't think it needs to be above and below the page.

Great work though, do you have a paypal? I'd like to pay you a few bucks, Scriptet, if that's alright.

Edit: I finished modifying the script for my needs except for changing the link to a relative one. Do I need to assign a variable that reads the file name of the current page, or is there is an easier way? Thanks.

1. When you say relative aren't the links relative already?

Absolute would be like http://www.mysite.com/phptest.php?page=1
Relative would be like phptest.php?page=1

Anyway the link is defined here:



//Function To Display Links to Pages
function displayPages($totalPages, $currentPage){
for($x = 1; $x <= $totalPages; $x++){
if($x != $currentPage)echo "<a href=\"index.php?page=$x\">$x</a> | ";
else echo "$x | "; //Do not highlight the current page
}
}


2. Sounds like you solved it already but just remove displayPages($pagesReq, $page); from both the if and else statement. not the one at the bottom becuase this is for the bottom of the page.

EDIT:

I see what you meant about the relative links now, find:


//Function To Display Links to Pages
function displayPages($totalPages, $currentPage){
for($x = 1; $x <= $totalPages; $x++){
if($x != $currentPage)echo "<a href=\"index.php?page=$x\">$x</a> | ";
else echo "$x | "; //Do not highlight the current page
}
}


and change to:


//Function To Display Links to Pages
function displayPages($totalPages, $currentPage){
for($x = 1; $x <= $totalPages; $x++){
if($x != $currentPage)echo "<a href=\"phptest.php?page=$x\">$x</a> | ";
else echo "$x | "; //Do not highlight the current page
}
}

or if you want it to work regardless of the filename change it to:



//Function To Display Links to Pages
function displayPages($totalPages, $currentPage){
for($x = 1; $x <= $totalPages; $x++){
if($x != $currentPage)echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$x\">$x</a> | ";
else echo "$x | "; //Do not highlight the current page
}
}
$_SERVER['PHP_SELF'] gets the document name :)

Angrysafer
09-08-2009, 11:55 PM
Excellent! I did not know about the PHP_SELF command. Thank you.

Angrysafer
09-09-2009, 02:20 AM
Actually, it seems to be dropping a page unless it is completely full. For example, with 11-19 entries total and set to display 10 per page it will only display page 1. Any idea why? Also, since I had to reopen this anyway. I probably only want it to display 10 or so pages, like the one before your current page, the first page, and the next 6 and then the last page. Something like that.

Actually, more like this:

1-5-[6]-7-8-9-23

Scriptet
09-09-2009, 07:21 PM
Actually, it seems to be dropping a page unless it is completely full. For example, with 11-19 entries total and set to display 10 per page it will only display page 1. Any idea why?

Do you mean 11-14 entries say caus 15-20 and the 2nd page appears right?

Anyway I realised what the problem was. I was using round() to calculate the required page numbers.
So say there was 14 records, then it was coming back as 1.4 pages are required, and then rounding it down to 1 page. Instead it's meant to round up to 2 pages, and this is done using the ceil() function :)


Also, since I had to reopen this anyway. I probably only want it to display 10 or so pages, like the one before your current page, the first page, and the next 6 and then the last page. Something like that.
Actually, more like this:

1-5-[6]-7-8-9-23

Yup this is certainly possible, just needed to modify the displayPages() function a bit.

Anyway here is the rounding fixed, and the pagination adjusted according to exactly what you said (first page, last page, previous page, current page next 3 pages)



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php

//Set The Name of The Text File Containing the List Below
$fileName = "url-list.txt";
//Set the Amount of Records You Want To Show Per Page Below
$perPage = 10;

//End of User Config - You Shouldn't Need to Modify Anything Below This Line//


//Count Amount of URL's (lines) present in file
$count = count(file($fileName));

//Calculate Amount of pages required
$pagesReq = ceil($count / $perPage);


if($_GET['page']){
//Then We Are Given a Page Through the URL
if($_GET['page'] >= 1 && $_GET['page'] <= $pagesReq){
//The Page is within Valid Range
$page = $_GET['page'];
$endRecord = $perPage * $page;
$startRecord = ($endRecord - $perPage) + 1;
displayPages($pagesReq, $page);
displayRecords($fileName, $startRecord, $endRecord);
}
else echo "<h1>Error: The Page Supplied Is Not Valid!</h1>";
}
else{
//No Page Given Through the URL so Display the First 25 Records
$page = 1;
$startRecord = 1;
$endRecord = $perPage;
displayPages($pagesReq, $page);
displayRecords($fileName, $startRecord, $endRecord);
}

displayPages($pagesReq, $page);


//Function to Display the Records for the Current Page
function displayRecords($file, $startRec, $endRec){
$handle = fopen($file, 'r') or die("Cannot Open File!!");
$olNum = $startRec + 1;
echo "<ol start=\"$startRec\">";
while(!feof($handle)){
$z++;
$line = fgets($handle);
if(($z >= $startRec) && ($z <= $endRec)){
//The line is between the required range so display it
echo "<li>".$line."</li>";
}
}
echo "</ol>";
fclose($handle);
}

//Function To Display Links to Pages
function displayPages($totalPages, $currentPage){
//Display Link to First Page, Last Page, Current Page, Previous Page, Next 3 Pages
for($x = 1; $x <= $totalPages; $x++){
if($x == 1 || $x == $currentPage-1 || $x == $currentPage || $x == $currentPage+1 || $x == $currentPage+2 || $x==$currentPage+3 || $x == $totalPages){
if($x != $currentPage)echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$x\">$x</a> | ";
else echo "$x | "; //Do not highlight the current page
}
}
}
?>
</body>
</html>


I'm sure were pretty close to a working script now :)

Angrysafer
09-10-2009, 01:41 AM
Looks great, this is exactly what I wanted. I think this would be a pretty useful script for a lot of other people too. Perhaps code in an option to turn pagination on or off with a variable in the configuration part of the script so that people could use it in a wider variety of situations.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum