SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP editing data in html table generated from mysql query which includes field names

    I need to be able to put data from a mysql table into a dynamic html table - that's not difficult but I need to also retrieve the column names, so for the data I can't use <?php echo $field; ?>. I can get all the data into a table using the following code:
    Code PHP:
    PHP Code:
     
    <?php
     
    function display_db_query($query_string, $header_bool) {
        // perform the database query
        $result_id = mysql_query($query_string)
        or die("display_db_query:" . mysql_error());
        // find out the number of columns in result
        $column_count = mysql_num_fields($result_id)
        or die("display_db_query:" . mysql_error());
     
        if($header_bool) {
            echo "<table width='850' border='0' align='center' cellpadding='5' cellspacing='1' class='entryTable'>";
            echo "<tr class='entryTableHeader'>";
            for($column_num = 0; $column_num < $column_count; $column_num++) {
                $field_name = mysql_field_name($result_id, $column_num);
                print("<td><center><b>$field_name</b></center></td>");
            }
            print("</tr>\n");
        }
        // print the body of the table
        while($row = mysql_fetch_row($result_id)) {
            print("<tr>");
            for($column_num = 0; $column_num < $column_count; $column_num++) {
                print("<td class='content' align='center'>$row[$column_num]</td>\n");         
            }
            print("<td class='content' align='center'>Edit</td>\n");
            print("</tr>\n");
        }
        print("</table>\n");
    }
     
    function display_db_table($tablename, $header_bool) {
        $query_string = "SELECT * FROM products_orderable";
        display_db_query($query_string, $header_bool);
    }
    ?>
    <HTML><HEAD><TITLE>Products Orderable table</TITLE></HEAD>
    <BODY>
    <TABLE><TR><TD>
    <?php
    $table = "table1";
     
    display_db_table($table, //$global_dbh,
    TRUE, "border='2'");
    ?>
    </TD></TR></TABLE></BODY></HTML>

    But then I need to be able to edit the items in the table or to delete any of them or add another. I am not sure how to do this without any reference to the item since I won't know what the fields are. Can anyone help please?
    thanks
    P.S. I'm not allowed (by the spec) to use javascript

  2. #2
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    before you run this query: select * from $table

    create a function which collects field names and key types from your table:
    run a query: SHOW FIELDS FROM database.table and you will see how this query works.

    and if KEY index is PRI, you can use that to create modify link, delete link.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have used that query - SHOW FIELDS FROM - but I am not quite sure how that will help me. The first field in the table is the primary key but how do I identify that for editing purposes? A user might want to edit only one of the fields with that primary key so how would I identify that and how would I make the mysql query to update thta in the table? This particular table is a list of products with description, price etc etc so a user may only want to change the descripton or the price, for instance.
    Sorry if I am being dense but I am learning PHP as I go along.
    thanks for any help

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Lets learn the very basics how you can make fields for update.

    First, list the table, build update link based on the primary key.

    Watch the url, and check if $_GET["id"] has value,
    build a MYSQL select link with a condition.

    You can find tutorial videos, articles around the internet.

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do know how to insert mysql data into a html table and create the links to do updates and have done that loads of times but never when I did not know the field names first. I can get the primary id and all the other attributes of the field names but I still do not know how to make the link in the data rows to select a row to update or delete and in the case of update how would I do an update query when I do not know the names of the fields to update?
    If you could post a code sample to explain that would help.
    thanks

  6. #6
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Too many tasks here.
    Updating a row and building a table are different tasks. Please choose one.

    If you want to update a row, you have to build an HTML form. And fill it with row data. And after submit you can build an update query and execute it.

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I should not have got carried away! If you see the code at the top of the thread, I can execute a query to retrieve both the field names and data from a table and insert it into a html table. What I cannot get my head around is how to assign variables in order to make a form to submit. I have got a potential edit link at the end of each row and would like to link that with the primary id which is always the first field in any of our tables. How do I select what to edit??

  8. #8
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need another script for editing.
    You don't have to assign anything in this table. It will be pulled from database and assigned to input fields in that editing script.
    So, you ought to go for it. For editing script. You have to done it first.

    Forget that table for a while

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right I have retrieved both the field names and the data and sent it to a html table but since I do not know the names of the fields before this and essentially never know (in terms of writing the query) how can I make an input field for the edit? Any of the data in the rows, apart from the primary id, can potentially be edited.

  10. #10
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A dynamic updater is looks like this. Its creating input fields.
    See, its not as easy as you think, but there are alternate ways for doing updaters. Learn the basics, write the field names into an array, and in a foreach loop you can make input fields. The shorter way is a bit simplier.
    From this code the pager, delete(batch delete) order , batch edit , search and insert features are missing.

    And its not always true that 'id is always the first field'...

    PHP Code:
    <?php

    function stripslashes2$string )
    {
        if ( 
    get_magic_quotes_gpc() ) {
            return 
    stripslashes$string );
        } else {
            return 
    $string;
        } 


    function 
    display_db_query$tablename$header_bool $border )

        
    // find out the number of columns in result
        
    $result mysql_query"SHOW FIELDS FROM $tablename);
        while ( 
    $row mysql_fetch_assoc$result ) ) {
            if ( 
    $row['Key'] == "PRI" )
                
    $primarykey $row['Field'];
            else
                
    $field[$row['Field']] = array( $row['Type'] );
        } 

        if ( isset( 
    $_POST["update$tablename"] ) ) {
            
    $sql sprintf"update $tablename SET " );
            
    $sqlfields = array();

            foreach( 
    $field AS $k => $v ) {
                if ( !empty( 
    $_POST["edit"][$k] ) )
                    
    $sqlfields[] = "$k='" mysql_real_escape_stringstripslashes2$_POST["edit"][$k] ) ) . "'";
                else
                    
    $sqlfields[] = "$k = NULL ";
            } 
            if ( 
    count$sqlfields ) > ) {
                
    $sql .= implode" , " $sqlfields ) . " WHERE $primarykey=" intval$_POST["updateid"] ) ;

            
    mysql_query$sql ) OR DIE( mysql_error() );
            if ( 
    mysql_affected_rows() > )
                print 
    "Updated succesfully<br />";
            } 
            else
            echo 
    "No change<br />";
        } 
        
    // perform the database query
        
    $result_id mysql_query"SELECT * from $tablename)
        or die( 
    "display_db_query:" mysql_error() );

        if ( 
    $header_bool ) {
            echo 
    "<table width='850' $border align='center' cellpadding='5' cellspacing='1' class='entryTable'>";
            echo 
    "<tr class='entryTableHeader'>";
            foreach( 
    $field AS $k => $v )
            print( 
    "<td><center><b>$k</b></center></td>" );

            print( 
    "<td><center><b>Edit</b></center></td>
            </tr>\n" 
    );
        } else
            echo 
    "<table width='850' $border align='center' cellpadding='5' cellspacing='1' class='entryTable'>
    "
    ;

        while ( 
    $row mysql_fetch_assoc$result_id ) ) {
            print( 
    "<tr>" );
            if ( isset( 
    $_GET["editmode"] ) AND $_GET["editmode"] == $row[$primarykey] ) {
                
    $editmodeison true;
                echo 
    "<form method=\"post\" action=\"{$_SERVER["PHP_SELF"]}\">";
            } else
                
    $editmodeison false;

            foreach( 
    $field AS $k => $v ) {
                if ( 
    $editmodeison )
                    print( 
    "<td class='content' align='center'><input type=\"text\" name=\"edit[$k]\" value=\"" . ( !empty( $row[$k] )?htmlspecialchars$row[$k] ) : htmlspecialchars'' ) ) . "\" /></td>\n" );
                else
                    print( 
    "<td class='content' align='center'>" . ( !empty( $row[$k] )?htmlspecialchars$row[$k] ) : htmlspecialchars'-' ) ) . "</td>\n" );
            } 

            if ( 
    $editmodeison )
                print( 
    "<td class='content' align='center'><input type=\"hidden\" name=\"updateid\" value=\"{$row[$primarykey]}\"><input type=\"submit\" name=\"update$tablename\" value=\"update\"></form></td>\n" );
            else
                print( 
    "<td class='content' align='center'><a href=\"{$_SERVER["PHP_SELF"]}?editmode=" $row[$primarykey] . "\">Edit</a></td>\n" );

            print( 
    "</tr>\n" );
        } 
        print( 
    "</table>\n" );


    ?>
    <HTML><HEAD><TITLE>Products Orderable table</TITLE></HEAD>
    <BODY>
    <TABLE><TR><TD>
    <?php

    /* For the following details, ask your server vendor  */
    $dbhost "xxxxxxx";
    $dbuser "xxxxxxx";
    $dbpass "xxxxxxx";
    $dbname "xxxxxxx";
    mysql_connect$dbhost$dbuser$dbpass ) or die ( "Unable to connect to MySQL server" );
    mysql_select_db"$dbname);
    mysql_query"SET NAMES utf8" );

    $table "table1";

    display_db_query$table// $global_dbh,
        
    true"border='2'" );

    ?>
    </TD></TR></TABLE></BODY></HTML>
    Code PHP:
     

  11. #11
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, I never thought it was easy! That's why I was having such a problem understanding how I should approach it. Thank-you so much for showing me, that code does what I want to do for the editing, now I can add the delete and add parts. Thank-you again, my php knowledge is growing!

  12. #12
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you finish with the code, search for: Ajax table editor for mysql That makes these tasks.

    The problem with these programs is if you need more complex table structure.

  13. #13
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am using the code you gave me to do the updates on my table and have incorporated it into my system, everything works fine. However, try as I might I cannot work out how to do a delete on a whole row. This can either be done in the same file as the update or in a separate one i.e the delete and update do not have to be there at the same time. I have tried both ways but I just can't get it to work. I also need to be able to ask the user to confirm that they want to delete a row before the mysql query is actioned. And re your last remark about Ajax - I wish I could use it but the spec I've been given is that NO javascript can be used - just to make things more difficult!
    Thanks for any help, hints you can give

  14. #14
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i don't really like taking evry features into one big function, however
    deleting an entry with a confirmation is not a big deal.

    You know the primary key field, you can make checkboxes with array field names.
    PHP Code:
    <input type="checkbox" name="delete[]" value="<?php echo $_row["primarykey"]; ?>">

  15. #15
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand how to do that but I am not sure how to incorporate it with the rest of the php I am using for the update - or would this go into a separate file for deleting? If so, the question again is how to incorporate it with the code to display all the field names and products with their primary IDs as in the code above. I have tried but failed miserably so far!

  16. #16
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    better to go for separate file.
    later you can join files, but while learning it is much better to have separate files.

    Also, it is much better for learning purpose to update and delete by one entry, not multiple.
    Or you'll stick for someone's help forever. You'll get it, yeah. But if you really want to learn, things must be learned distinctly. Step by step. From easy steps to more complicated. Multiple item forms are complicated.

  17. #17
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree learning in small steps is the best way but I seem to make big leaps forward in some directions and in others a tiny step seems to be a huge problem!
    As I said I used the code you suggested for the update, which works fine (the update I mean) but try as I might the best I can manage so far for adapting it for a delete is to get the Field names into a table but no field values and obviously no way of deleting what I cannot retrieve! It makes it very difficult not knowing the names of the fields and also not being able to use javascript for the confirmation of delete action. Can you help please?
    Code PHP:
    <?php
    require_once 'include/config.php';
     
     
    function stripslashes2( $string )
     
    {
     
        if ( get_magic_quotes_gpc() ) {
     
            return stripslashes( $string );
     
        } else {
     
            return $string;
     
        } 
     
    } 
     
     
     
    function display_db_query( $tablename, $header_bool , $border )
     
    { 
     
        // find out the number of columns in result
     
        $result = mysql_query( "SHOW FIELDS FROM products_orderable" );
     
        while ( $row = mysql_fetch_assoc( $result ) ) {
     
            if ( $row['Key'] == "PRI" )
     
                $primarykey = $row['Field'];
     
            else
     
                $field[$row['Field']] = array( $row['Type'] );
     
        } 
     
     
     
        if ( isset( $_POST["delete$row[$primarykey]"] ) ) {
     
    		$sql = sprintf( "delete FROM products_orderable WHERE" );
     
    //       $sql = sprintf( "delete FROM products_orderable WHERE prodord_id = $row[$primarykey] " );
     
            $sqlfields = array();
     
     
     
           foreach( $field AS $k => $v ) {
     
                if ( !empty( $_POST["delete"][$k] ) )
     
                    $sqlfields[] = "$k='" . mysql_real_escape_string( stripslashes2( $_POST["delete"][$k] ) ) . "'";
     
                else
     
                    $sqlfields[] = "$k = NULL ";
     
            } 
     
            if ( count( $sqlfields ) > 0 ) {
     
                $sql .= implode( " , " , $sqlfields ) . " WHERE $primarykey=" . intval( $_POST["updateid"] ) ;
     
     
     
            mysql_query( $sql ) OR DIE( mysql_error() );
     
            if ( mysql_affected_rows() > 0 )
     
                print "Deleted succesfully. Are you sure you want to make these changes? </p>";
     
            } 
     
            else
     
            echo "No change<br />";
     
        } 
     
        // perform the database query
     
        $result = mysql_query( "DELETE FROM products_orderable WHERE prodord_id = '$primarykey'" )
     
        or die( "display_db_query:" . mysql_error() );
     
     
    */
        if ( $header_bool ) {
     
            echo "<table width='850' $border= '0' align='center' cellpadding='5' cellspacing='1' class='entryTable'>";
     
            echo "<tr class='topcontent'>";
     
            foreach( $field AS $k => $v )
     
            print( "<td><center><b>$k</b></center></td>" );
     
     
     
            print( "<td><center><b><input type='checkbox' name='delete[]' value='<?php echo $primarykey; ?>'></b></center></td>
     
            </tr>\n" );
     
        } else
     
            echo "<table width='850' $border='0' align='center' cellpadding='5' cellspacing='1' class='entryTable'>
     
    ";
     
     
     
        while ( $row[$primarykey] = mysql_query( $result ) ) {
     
            print( "<tr>" );
     
            if ( isset( $_GET["deletemode"] ) AND $_GET["deletemode"] == $row[$primarykey] ) {
     
                $deletemodeison = true;
     
                echo "<form method=\"post\" action=\"{$_SERVER["PHP_SELF"]}\">";
     
            } else
     
                $deletemodeison = false;
     
     
     
            foreach( $field AS $k => $v ) {
     
                if ( $deletemodeison )
     
                    print( "<td class='content' align='center'><input type=\"text\" name=\"delete[$row]\" value=\"" . ( !empty( $row[$k] )?htmlspecialchars( $row[$k] ) : htmlspecialchars( '' ) ) . "\" /></td>\n" );
     
                else
     
                    print( "<td class='content' align='center'>" . ( !empty( $row[$k] )?htmlspecialchars( $row[$k] ) : htmlspecialchars( '-' ) ) . "</td>\n" );
     
            } 
     
            if ( $deletemodeison )
     
              print( "<td class='content' align='center'><input type=\"hidden\" name=\"deleteid\" value=\"{$row[$primarykey]}\"><input type=\"submit\"  name=\"delete$row[primarykey]\" value=\"delete\"></form></td>\n" );
     
     
     else
    */
                print( "<td class='content' align='center'><a href=\"{$_SERVER["PHP_SELF"]}?deletemode=" . $row[$primarykey] . "\">Delete</a></td>\n" );
     
     
     
            print( "</tr>\n" );
     
        } 
     
        print( "</table>\n" );
     
    } 
     
     
     
    ?>
     
    <HTML><HEAD><TITLE>Products Orderable table</TITLE></HEAD>
     
    <BODY>
    <h1><center>Products Orderable</center></h1>
    <TABLE><TR><TD>
     
    <?php
     
     
     
    $table = "table";
     
     
     
    display_db_query( $table, 
     
        true, "border='0'" );
     
     
     
    ?>
     
    </TD></TR></TABLE>
     
    </BODY></HTML>

  18. #18
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    offtopic. the way sitepoint displaying code is just terrible.

  19. #19
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    offtopic. the way sitepoint displaying code is just terrible.
    couldn't agree more

  20. #20
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does that have any relevance to what I asked?

  21. #21
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If this is not a homework project, then you should start how to make it with small steps.
    As you can see, this code is becoming into robust.
    The update + delete + confirmation script should take into new functions.
    A tutorial how to create functions

  22. #22
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    offtopic. the way sitepoint displaying code is just terrible.
    That's a result of using the [PHP] tag.

    If [HIGHLIGHT="PHP"] is used instead, the code seems to come out better.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript


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
  •