SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is there a better way to do this?

    Hey

    On my page www.thornworx.com/weirdbeard/toptunes.php the contents are generated by a simple MySQL table.

    I am trying to design a DBMS so that I can easily update it by a web based script (rather than having to go through PHPMyAdmin on my host provider). I will also add other components as and when they arrive on my site.

    It is pretty obvious how it works - there is a select statement that queries by a position field. Values 1-5 appear in the table, then any tune that has position value 6 appear in the "recent top tunes list" (limited to 5).

    I was just wondering is there is any better way to do this (for example serving it with a text file), and advice on the DBMS and the best way of doing it...

    Thanks a lot
    Last edited by weirdbeardmt; Aug 24, 2001 at 14:13.
    I swear to drunk I'm not God.
    » Matt's debating is not a crime «
    Hint: Don't buy a stupid dwarf ö Clicky

  2. #2
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just realised that wasn't too clear --

    What I mean is as and when I want to add a new tune to the list, I have to renumber one of them to position 6, then do an insert statement.

    Hope that is better
    I swear to drunk I'm not God.
    » Matt's debating is not a crime «
    Hint: Don't buy a stupid dwarf ö Clicky

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds like what you are wanting to do is develop a content management system - right? A customised web page interface using forms to update your database.

    Well, I made a back-end content management system for a shoe wholesaler a while ago (my first PHP project actually). The user could view a list of all the shoes in a range (summer range, winter range, etc) and rank their order in the catalog. This then determined where the shoes appeared in the catalog pages (six shoes per catalog page). Here is a little from the code. Note that this code is in a while loop which loops through all the rows in the result set from a select query:
    PHP Code:
    echo "<input type='hidden' name='itemID[]' value='$anItem->id'></p>\n";
    echo
    "<p><input type='text' name='newRank[]' value='$anItem->rank' size='5'></p></td>"
    Then when I process the form data, I update all the rankings in the database
    PHP Code:
    if ($rank) {
       
    $x 0;
       foreach (
    $itemID as $item) {
          
    $sql "UPDATE item_category
                  SET rank = " 
    $newRank[$x++] . "
                  WHERE item_id = 
    $item";
          
    mysql_query($sql)
             or die( 
    mysql_errno() . " : " mysql_error() );
       }


  4. #4
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freakysid
    Sounds like what you are wanting to do is develop a content management system - right? A customised web page interface using forms to update your database.
    Exactly right.

    I am using this, to get the entries in the database:

    PHP Code:
    $findtunes "SELECT * from toptunes ORDER BY position LIMIT 5";
    $foundtunes mysql_query($findtunes) or die ("DOH");

    while (
    $tunes mysql_fetch_array($foundtunes)) {

    $id $tunes["id"];
    $position $tunes["position"];
    $tunetitle $tunes["tunetitle"];
    $artist $tunes["artist"];

    echo (
    "<TR><TD CLASS='mainbodytext'>$position</TD><TD CLASS='mainbodytext'>$tunetitle</TD><TD CLASS='mainbodytext'>$artist</TD></TR>");

    }

    $getprevioushits "SELECT * from toptunes where position like '6'ORDER BY id DESC LIMIT 5";
    $gotprevioushits mysql_query($getprevioushits) or die ("DOH");

    echo (
    "<TR><TD CLASS='mainbodytext' COLSPAN='3'>Previous top tunes include ");

    while (
    $previoushits mysql_fetch_array($gotprevioushits)) {

    $artist $previoushits["artist"];
    $title $previoushits["tunetitle"];
    echo (
    "$title by $artist...");


    In your code, did the input box appear next to the shoe they were trying to edit and automatically take the id of that item? Sounds like that is what I' m after.

    Cheeky, I know, but, any chance you could do that to my code...
    I swear to drunk I'm not God.
    » Matt's debating is not a crime «
    Hint: Don't buy a stupid dwarf ö Clicky

  5. #5
    SitePoint Member
    Join Date
    Aug 2001
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The content management system shouldn't be too hard, I have one I wrote that works on any table with a primary key. It can add, delete, and modify records. I can post some of it if you want...
    Microsoft’s Motto: Resistance is futile, you will be assimilated…

  6. #6
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeh, that would be good.

    It's really odd, because I previously designed one, which was a login site, and allowed them to alter their preferences/usage history etc. but for some reason it doesn't want to work with this...!
    I swear to drunk I'm not God.
    » Matt's debating is not a crime «
    Hint: Don't buy a stupid dwarf ö Clicky

  7. #7
    SitePoint Member
    Join Date
    Aug 2001
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now I removed a litle bit of the security I have in my own script but this should still work. This is my first script (literally the first I have written) so I know it could use some clean up, I look forward to any feedback (oh btw I know it all works I use it every day!)

    PHP Code:
    <?
    session_start
    ();
    include(
    "funcs.php"); // the only function used here is connecttodb

    // If user wishes to logout send them back to the login page
        
    if ($lgout=="yes"){
            
    // Clear all important vars
            
    session_unregister("uname");
            
    session_unregister("userpass");
            
    session_unregister("dbselect");
            
    $uname="";
            
    $userpass="";
            
    $dbselect="";
            
    // redirect them to the login page
            
    header ("Location: index.php");
            print(
    "<html> \n".
                    
    "<head> \n".
                    
    "<title>I am sorry...</title> \n".
                    
    "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'> \n".
                    
    "</head> \n".
                    
    "<body bgcolor='#003366' text='#000000'> \n".
                    
    "<p>You have been logged-off, click<a href='index.php'> here <a>to login again.</P> \n".
                    
    "</body> \n".
                    
    "</html> \n");
        }
    // End Log-out section

    // Open the requested database and do a general query
        
    connecttodb$uname$userpass$dbselect);
        if (isset(
    $viewtable)){
            
    $result = @mysql_query ("select * from $viewtable") or die ("Sorry, I died while refreshing the table.");
        }
    // If a record was set to be added add it.
        
    if ($doadd != ""){
            
    $addresult $result;
            for (
    $i 0$i < @mysql_num_fields ($addresult); $i++) {            
                
    $meta = @mysql_fetch_field ($addresult);
                
    $colnames["$i"] = $meta->name;
            }
            
    $icolnames implode(","$colnames);
            
    $idataval implode("','"$f);
            
    $ndoad = @mysql_query ("INSERT INTO $viewtable ($icolnames) VALUES('"."$idataval"."')");
            
    $doadd "";        
            
    $result = @mysql_query ("select * from $viewtable") or die ("Sorry, I died while refreshing the table.");
        }
    // End record adder
    // Start Update Section
        
    if ($domodd != ""){
            for (
    $i 0$i < @mysql_num_fields ($result); $i++) {
                
    $meta = @mysql_fetch_field ($result);
                
    $metaname["$i"] = $meta->name;
                if (
    $meta->primary_key == 1){
                    
    $pricolnum $i;
                }
            }

            
    $rcount 0;
            while (
    $row = @mysql_fetch_array($result)) {
                
    $rowpri $row[$pricolnum];
                for (
    $i 0$i < @mysql_num_fields ($result); $i++) {
                    
    $colname $metaname["$i"];
                    
    $fnamernum $colname."_".$rcount ;
                    if (
    $trown["$rcount"]){
                        
    $allvars get_defined_vars();
                        
    $newval $allvars["$fnamernum"];
                        
    mysql_query ("UPDATE $viewtable SET $colname='$newval' WHERE $pricol='$rowpri'");
                    }
                }
                
    $rcount++;
            }
            
    $result = @mysql_query ("select * from $viewtable") or die ("Sorry, I died while refreshing the table.");
        }
    // End Update Section
    // If records have been flagged for deletion, delete them
        
    if ($delr != ""){
                
    $i=0;
                
    $drown = @implode(","$trown);
                
    $trown = @explode(","$drown);
                while(
    $i <= count($trown)){
                    
    $someval $trown["$i"];
                    
    $someval5["$i"] = $someval;
                    @
    mysql_query("DELETE FROM $viewtable WHERE $pricol = $someval");
                    
    $i++;
                }
        
    $result = @mysql_query ("select * from $viewtable") or die ("Sorry, I died while refreshing the table.");
        }
    // End record deleter
        
    $laster mysql_error();
        print(
    "<html> \n".
            
    "<head> \n".
            
    "<title>Database Administer pages</title> \n".
            
    "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'> \n".
            
    "</head> \n".
            
    "<body bgcolor='#330099' text='#FFFFFF'> \n".
            
    "<table width='799' border='1' bgcolor='#006699' height='49' align='center'> \n".
              
    "<tr> \n".
                  
    "<td width='216'><font color='#000000'><b>Hello and welcome, $uname.</b></font></td> \n".
                  
    "<td width='268' height='20'><font color='#000000'><b>Currently working in the $dbselect database.</b></font></td> \n".
                  
    "<td width='293' height='20'><a href='$PHP_SELF?lgout=yes'><font color='#000000'><i>To logout or change the database click here</i></font></a></td> \n".
              
    "</tr> \n".
              
    "<tr> \n".
                  
    "<td width='216'><font color='#000000'><b>The last error I encounterd was:</b></font></td> \n".
                  
    "<td colspan='2'><font color='#000000'> $laster </font></td> \n".
              
    "</tr> \n".
             
    "</table>".
               
    "<FORM METHOD=POST ACTION='$PHP_SELF'>");

    // Start Table viewing script
        
    if (isset($viewtable)) {
            print(
    "<FONT SIZE='4'> Current table is <I><B>$viewtable</B></I> in $dbselect.</FONT>\n <p>&nbsp;</p> \n".
                    
    "<TABLE border width=100%> \n <tr bgcolor=#9999ff> \n");
    // Start Check for Primary Key & Collect Column Names
            
    for ($i 0$i mysql_num_fields ($result); $i++) {
                
    $meta = @mysql_fetch_field ($result);
                
    $metaname["$i"] = $meta->name;
                if (
    $meta->primary_key == 1){
                    
    $pricol $meta->name;
                    
    $prikey 1;
                    
    $primcolnum $i;
                }
            }

    // End Check for Primary Key
    // Begin Labling of columns
            
    if ($prikey == 1){
                echo(
    "<TD>&nbsp;</TD> \n");
            }

            for (
    $i 0$i mysql_num_fields ($result); $i++) {
                
    $metaname2 $metaname["$i"];
                echo (
    "<TD><FONT COLOR='#000000'> $metaname2 </FONT></TD> \n");
            }
            echo(
    "</TR> \n");

    // End Labling Section
    // Start Data fill

            
    $rcount 0;
            while (
    $row = @mysql_fetch_array($result)) {
                echo (
    "<TR> \n");
                if (
    $prikey == 1){
                    echo (
    "<TD><INPUT TYPE='checkbox' NAME='trown[$rcount]' value='$row[$primcolnum]'></TD> \n");
                }
                for (
    $i 0$i < @mysql_num_fields ($result); $i++) {
                    
    $dname $row[$i];
                    if (
    $modds != "" && $trown[$rcount] == $row["$primcolnum"]){
                        
    $incolname $metaname["$i"];
                        echo(
    "<TD> <INPUT TYPE='text' NAME='"."$incolname"."_"."$rcount"."' VALUE='$dname'> </TD> \n");
                    } else {
                        echo(
    "<TD> $dname </TD> \n");
                    }
                }
                echo (
    "</TR> \n");
                
    $rcount++;
            }
    // End Data Fill
          
    print("<input type='hidden' name=viewtable value='"."$viewtable"."'>".     // Pass viewtable on...
                
    "<input type='hidden' name=pricol value='"."$pricol"."'> \n");      // Pass the primary column name on...
    // Start Mod butt add section
            
    if ($modds != ""){
                print (
    "</TABLE> \n".
                        
    "<input type='submit' name=domodd value='Save Updates'> \n".
                        
    "<input type='submit' name=cancelmodd value='Cancel'> \n");
            }
    // End Mod butt add section
    // Start Add Section
            
    if ($add != "") {
                echo(
    "<TD>&nbsp;</TD>");
                for (
    $i 0$i < @mysql_num_fields ($result); $i++) {
                    echo(
    "<TD><INPUT TYPE='text' NAME='f["."$i"."]'></TD> \n");
                }
                print (
    "</TABLE> \n".
                        
    "<input type='submit' name=doadd value='Add it'> \n".
                        
    "<input type='submit' name=canceladd value='Cancel'> \n");
            }
            echo (
    "</TABLE> \n");
    // End Add Section
    // Start Options (to add records etc...)
        // If a Primary Key was found display the buttons etc....    
        
    if ($prikey == 1){
            if (
    $add == "" && $modds == ""){
                print(
    "<BR><BR> \n <table width='100%' border='0'> \n <tr> \n".
                        
    "<td><input type='submit' name='add' value='Add a Record'></td> \n".
                        
    "<td><div align='center'><input type='submit' name='modds' value='Modify Selected Records'></div></td> \n".
                        
    "<td><div align='right'><input type='submit' name='delr' value='Delete Selected Records'></div></td> \n".
                        
    "</tr></table> \n");
            }
         
    // If a Primary Key was not found display the error
        
    } else {
            echo (
    "<b>I am sorry but data in this table cannot be edited because it has no primary key.</b>");
        }
        
    // End Options

        

    // End of the view table script
    // Start Listing Availible Tables
            
    $tablesmysql_list_tables($dbselect);
            echo(
    "<BR><BR> \n");
            while ( list(
    $tnames) = mysql_fetch_array ($tables) ) { 
                echo (
    "<BR><a href='$PHP_SELF?viewtable=""$tnames"."'><FONT COLOR='#FFFFFF'>$tnames</FONT></a> \n");
            }
    // End Table list script

        
    print("</FORM> \n");


    include(
    "footer.php"); // My custom footer (copyright notice, last mod date etc....)
        
    print("</body> \n </html>");
    ?>
    Microsoft’s Motto: Resistance is futile, you will be assimilated…


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •