1. This website uses cookies. By continuing to use this website you are giving consent to cookies being used.
    For information on cookies and how you can disable them visit our Cookie Usage page.
    Dismiss Notice

PHP how to update your links with a sql database

Discussion in 'Web Development' started by bmcoll3278, Aug 11, 2009.

  1. bmcoll3278

    bmcoll3278 New Member

    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

    Code:
    $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.
     

    Attached Files:

  2. bmcoll3278

    bmcoll3278 New Member

    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.
    Code:
    <?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.
     
    ayyaz005 likes this.
  3. bmcoll3278

    bmcoll3278 New Member

    create a css menu from links

    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
    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
    Code:
    <?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
    Code:
    $thelinks= "<a href='$linky[1]'> $linky[0]</a> <br>";
    to this

    Code:
    $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

    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
     
    ayyaz005 likes this.
  4. ishkey

    ishkey Moderator, Logos, Sports Crests Staff Member Verified Member

    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
     
  5. bmcoll3278

    bmcoll3278 New Member

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

    navyfalcon Well-Known Member Verified Member

    Make sure to post it to other sites with your signature (your URL)
    Great tutorial -
    falcon
     
    ayyaz005 likes this.
  7. hippo

    hippo New Member

    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.
     
    ayyaz005 likes this.
  8. bmcoll3278

    bmcoll3278 New Member

    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
    Code:
    $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


    Code:
    $result = mysql_query("SELECT * FROM dyn_menu where cat='your cat name'limit 20 OFFSET 20");
    nextpage
    Code:
    $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.
     
    ayyaz005 likes this.