Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 37
  1. #1
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Administration of a database can be a pain in the arse if you have to delete dozens or hundreds of items one by one by one. If you have an interface like this, you may want to make one where you can delete multiple rows from a MySQL table all in one shot. This tutorial will show you how to do exactly that and provide you a script by which you can easily set up such an interface for yourself using a checkbox list.

    The concept is based upon two principles.
    1. In SQL, multiple items can be deleted from a table using the WHERE IN selection criterion as follows:
      Code:
      DELETE FROM table WHERE integer_value IN (1, 2, 3);
      DELETE FROM table WHERE varchar_value IN ('A', 'B', 'C');
    2. In HTML, a series of checkboxes with the same "name" attribute value will submit a string value of "1, 2, 3", or an array of strings, such as ["1", "2", "3"] to the next page.
      Code:
      <input type="checkbox" name="deleted_items[]" value="1" />
      <input type="checkbox" name="deleted_items[]" value="2" />
      <input type="checkbox" name="deleted_items[]" value="3" />

    With those concepts in place, one should realize that concatenating a single DELETE query to handle multiple rows of data should be a snap. All we have to do is check the boxes corresponding to the rows we want to delete and concatenate the comma-delimited post value into the DELETE query and execute the query.

    The following script does just that (the rest of my comments regarding this tutorial are in the script itself):
    PHP Code:
    <?php
    /**
      * Title:  PHP Multi-Delete Administration Script
      * Author: Steven Moseley (stevenmoseley.com)
      * Source: Web Design Forums (webdesignforums.net/thread10043.html)
      */

    /**
      * You will need to change some values in this section to set up the
      * script to work with your database and table.  Follow the directions
      * below and change the necessary values (there aren't many) as
      * indicated by the comments (//)
      */

    // Set your database connection information
    $serverurl "localhost";
    $username "admin";
    $password "";

    // Set this to your database name
    $database "db_transio";

    // Set this to the name of the table you want to delete from
    $table_name "transios";

    // Set this to the name of the primary key column of that table
    $table_pk "transio_id";

    // Set this to the name of the descriptive column of that table
    $table_desc "transio_name";

    /** 
      * That's all you have to do!  You have now set up your script.
      * If you want, you can add a header and footer or put some
      * HTML after this PHP code block, but the script will work
      * just like this!
      */

    // Establish a connection with the database
    mysql_connect($serverurl$username $password);
    mysql_select_db($database);

    // If the form was submitted, delete the items selected from the database
    if ($_POST["deleted_items"]) {
        
    $deleted_items join(', '$_POST["deleted_items"]);
        
    $query "DELETE FROM $table_name WHERE $table_pk IN ($deleted_items)";
        
    $result mysql_query($query);
        
    header("Location: ".$PHP_SELF);
    }
    ?>
    <!--
    Title:  PHP Multi-Delete Administration Script
    Author: Steven Moseley (stevenmoseley.com)
    Source: Web Design Forums (webdesignforums.net/thread10043.html)

    This free script allows a user to delete multiple columns from a single table with a 
    single-column integer primary key.  The script is free for use or modification. 
    Please leave this header in place to credit authors.
    -->

    <html>
        <head>
            <title>PHP Multi-Delete Administration Script</title>
        </head>
        <body>

            <form action="<?php echo $PHP_SELF?>" method="post">
            <table cellpadding="5" cellspacing="0" border="1">
                <tr>
                    <th><?php echo $table_desc?></th>
                    <th>Delete?</th>
                </tr>
    <?php
    // Get a list of items in the table
    $query "SELECT $table_pk$table_desc FROM $table_name ORDER BY $table_desc ASC";
    $result mysql_query($query);

    // Display the form so the user can delete one or more items from the table
    while ($row mysql_fetch_assoc($result)) {
        echo 
    "\t\t\t<tr>";
        echo 
    "<td>".$row[$table_desc]."</td>";
        echo 
    "<td><input type=\"checkbox\" name=\"deleted_items[]\" ";
        echo 
    "value=\"".$row[$table_pk]."\" /></td>";
        echo 
    "</tr>\n";
    }
    ?>
                </table>
                <input type="submit" value="Delete Selected" />
            </form>
        </body>
    </html>
    That's about it! You can apply this script to just about any table with a single-column integer primary key (won't work for varchar id's because of the single-quote requirement for generating a SQL query with varchars). Try it out and post feedback and comments !!!

    Enjoy ;-)

  2.  

  3. #2
    Senior Member ericbusch's Avatar
    Join Date
    Aug 2003
    Location
    Daejeon, South Korea
    Posts
    241
    Member #
    2779
    Transio,

    Beautiful code, but so far, no luck! I double and triple checked my username, password, database and table spellings. I have even looked at the rest of the code for missing ";" and things like that but I can't see where the problem is happening...

    Any ideas?

    Eric
    Blogs For Sale - Each blog is packed with 1000s of signatures and ads are highly integrated into the site. Great CTR! Completely customizable from Admin CP. On Sale!
    Complete Websites For Sale - Fantastic looking sites rich with content. Come complete with domain names. I only have a couple of these.

  4. #3
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    It's working for me, but only deleting the last record of the selected group. I am making some updates and will post a new version soon.

    EDIT: did some tinkering and got it working flawlessly. I have updated the example in the original tutorial post.

    EDIT: there is a slightly altered version of the original temporarily available for you guys to look at here: http://www.advancedwebcontent.com

    EDIT: took down the online example. no need for it anymore.

  5. #4
    Senior Member ericbusch's Avatar
    Join Date
    Aug 2003
    Location
    Daejeon, South Korea
    Posts
    241
    Member #
    2779
    Transio,

    I can't get it! Do you have any suggestions as to what i might be doing wrong? It almost seems as if it isn't even connecting to the database because when I just try to echo a variable, it won't.

    Any ideas?
    Eric
    Blogs For Sale - Each blog is packed with 1000s of signatures and ads are highly integrated into the site. Great CTR! Completely customizable from Admin CP. On Sale!
    Complete Websites For Sale - Fantastic looking sites rich with content. Come complete with domain names. I only have a couple of these.

  6. #5
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    Hi Eric.

    It would probably help if you list the error codes (you most probably are generating).

    "It's not working" isn't much to go on.

    On my system its working perfectly, so the code does work.

    Good luck sorting it out, gotta run now.
    S. Rosland

  7. #6
    Senior Member ericbusch's Avatar
    Join Date
    Aug 2003
    Location
    Daejeon, South Korea
    Posts
    241
    Member #
    2779
    That's a good idea. "Not working" doesn't tell you too much does it?
    I will try and generate some error codes. They aren't in the code right now. I'll let you know how it goes.

    Eric
    Blogs For Sale - Each blog is packed with 1000s of signatures and ads are highly integrated into the site. Great CTR! Completely customizable from Admin CP. On Sale!
    Complete Websites For Sale - Fantastic looking sites rich with content. Come complete with domain names. I only have a couple of these.

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Did you get it working yet?

  9. #8
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Duuuudeee.... nice code! Umm... I'm gonna steal it, m'kay?

    To make it better, you could pop up a pull down options window where you could select the DB/table that you want to delete from. I believe I already have the code for it somewhere.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  10. #9
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Why don't you add that in? we can make that version 2.0 !!!

    EDIT: Actually, I thought about that for a second... you would have to do some DB reflection to discover the PK and Primary Description fields. Not as easy as you might think, and you'd have to make some assumptions on the Description stuff.

  11. #10
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Not that hard actually. PK's gonna be numeric, right? Only display numeric ones, then let user select PK.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com


Page 1 of 4 1 2 3 ... LastLast

Remove Ads

Posting Permissions

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

Search tags for this page

getting admincp from information schema

Click on a term to search for related topics.
All times are GMT -6. The time now is 09:58 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com