how to update your links with a sql database

[bmcoll3278]

This post is about using php and sql to update all links on your site from a admin page.

I am including the zip file with the files you will need. so here is how it works.

This will allow you to create a data base to store all links for your website and the next post will show you how to display them on your pages.

You can ad or remove the links from the data base and that will update all pages on your site where the links are displayed

Step1 use PHPadmin to create a new database on your server and assign it a user id and password.

Step2 Download the attached zip file and unzip it

step 3 use PHPadmin again to goto your new data base click on import and import the file called sql.sql

step 4 open each of the files index.php, delete.php, linkchanger.php

At the top of each file you will see this

$mysqluser = "your database user id"; 

$mysqlpass = "your database password";

$mysqldb = "your database name";

change each to your database info.

Create a new file folder on you server and password protect it

Upload the files index.php, delete.php, linkchanger.php

Now point your browser at yoursite.com/new-folder-name

And you are ready to start inserting links be sure you have set the permissions on the files to 755

See the next post to see how to display the links to your site.

[bmcoll3278]

Part 2
ok now you have a great new tool to insert or remove links on your site.
Here is how to insert the links into your site.
Remember from the last post you changed some info in each file to match your data base.
do it again in this code. also change (your cat name) to the category of links you want to display.
and update the database info.

<?php
$mysqluser = "your sql user"; ///change to your userid
$mysqlpass = "your sql password"; /// change to your password
$mysqldb = "your database name";  /// change to your database name
mysql_connect(localhost,$mysqluser,$mysqlpass);
@mysql_select_db($mysqldb) or die( "Unable to select database");

$result = mysql_query("SELECT * FROM dyn_menu where cat='your cat name'");
while($row = mysql_fetch_array($result))
  {

$linky[0] = "$row[links]";
$linky[1] = "$row[link_url]";
$thelinks= "<a href='$linky[1]'>  $linky[0]</a> <br>";
  echo"$thelinks"; 
 }
?>

Now to the nuts and bolts
step 1 for whatever page you want to use this in you must change the file extension to php
so insted of index.html you will change it to index.php
Oh no now all the links to this page wont work. Don't worry I will show you how to fix that next
Step 2 Take the code above (that you just changed the info in) and paste it into the webpage with the .php extension where you want the links to appear . This code will print the links in a straight list. In future posts I will show you how to insert it into a table or make a nice css menu like you will see at the top of http://www.bmcoll.com
For now lets show you how to fix that whole html/php thing
copy this code to notepad or mac equivalent

RewriteEngine on
RewriteRule ^index\.html$ index.php

If the name of your page is something other than index change the code above from index to your page name
duplicate the code for each page name.
Now on your server in the root you should have a file named .htaccess open it and paste the code into it.
If the file does not exist create it.
now even though the file is called index.php when someone enters index.html it will still goto the right page.

For any of the php files that are in a sub folder You may need to copy the .htaccess file into that sub folder this depends on your server.

[bmcoll3278]

Ok now to make a css menu using the code above

You will need to add to the header section on your page some css code

<style type ="text/css">
body {  
 font: 75% Arial,sans-serif}
  text-align: center;
ul#minitabs{list-style: none;margin: 0;padding: 7px 0;
  border-bottom: 1px solid #CCC;font-weight: bold;
  text-align: center;white-space: nowrap}
ul#minitabs li{display: inline;margin: 0 2px}
ul#minitabs a{text-decoration: none;padding: 0 0 3px;
  border-bottom: 4px solid #FFF;color: #999}
ul#minitabs a#current{border-color: #F60;color: #06F}
ul#minitabs a:hover{border-color: #F60;color: #666}
  }
</style>

You can play with this code to change colors and text size

Now using the sql code we have been working with

<?php
$mysqluser = "your sql user"; ///change to your userid
$mysqlpass = "your sql password"; /// change to your password
$mysqldb = "your database name"; /// change to your database name
mysql_connect(localhost,$mysqluser,$mysqlpass);
@mysql_select_db($mysqldb) or die( "Unable to select database");

$result = mysql_query("SELECT * FROM dyn_menu where cat='your cat name'");
while($row = mysql_fetch_array($result))
{

$linky[0] = "$row[links]";
$linky[1] = "$row[link_url]";
$thelinks= "<a href='$linky[1]'> $linky[0]</a> <br>";
echo"$thelinks"; 
}
?>

change this line

$thelinks= "<a href='$linky[1]'> $linky[0]</a> <br>";

to this

$thelinks= "<li><a href='$linky[1]'>  $linky[0]</a></li>";

then you will add before the<?php
<ul id="minitabs">
and after the ?>
</div>
so here is the full code

<ul id="minitabs">
<?php
$mysqluser = "your sql user"; ///change to your userid
$mysqlpass = "your sql password"; /// change to your password
$mysqldb = "your database name"; /// change to your database name
mysql_connect(localhost,$mysqluser,$mysqlpass);
@mysql_select_db($mysqldb) or die( "Unable to select database");

$result = mysql_query("SELECT * FROM dyn_menu where cat='your cat name'");
while($row = mysql_fetch_array($result))
{

$linky[0] = "$row[links]";
$linky[1] = "$row[link_url]";
$thelinks= "<li><a href='$linky[1]'>  $linky[0]</a></li>";
echo"$thelinks"; 
}
?> 
</div>

If you want to see this menu in action click the link in my signature it is at the top of the page

[ishkey]

Excellent !!!
Great piece of writing. To the point, easy to follow.
I've downloaded the files and going to try it out.
Again thanks for a great tutorial.
Dave

[bmcoll3278]

This is the same code I use pasted from my pages, So please let me know if there is any problem making it work

[navyfalcon]

Make sure to post it to other sites with your signature (your URL)
Great tutorial -
falcon

[hippo]

I'm a newbie to php and I found your tutorial doing a search on making link indexes.

I tried all your code and got it to work - thank you very much.

I would however like to ask you for assistance in adding pagination to the results. Be nice to limit results to 20 or so links per page.

Is this something that can easily be added to your current code?

Thanks you very much again.

[bmcoll3278]

when you look at the code you can see that you assign categories to the links. Just create a new cat for each group of 20 then thats all that will display.
Then link to the next page with the new cat for the next 20

That would be the fast way.

Or this

$result = mysql_query("SELECT * FROM dyn_menu where cat='your cat name'limit 20 OFFSET 0");

then on the next page of links it would be

$result = mysql_query("SELECT * FROM dyn_menu where cat='your cat name'limit 20 OFFSET 20");

nextpage

$result = mysql_query("SELECT * FROM dyn_menu where cat='your cat name'limit 20 OFFSET 30");

and so on.

the LIMIT is number of records displayed and OFFSET is where the records start.