?
Solved

editing table data and saving it it mysql with php

Posted on 2005-03-16
7
Medium Priority
?
449 Views
Last Modified: 2013-12-12
Hi,
with php and mysql, how, having edited the contents of some table which I have created and displayed in my webpage, do I detect that that data in a particular cell
has been edited and then save it to disk.
thanks
0
Comment
Question by:dm14011
7 Comments
 
LVL 32

Expert Comment

by:ldbkutty
ID: 13561631
can you show your code ?
0
 
LVL 2

Expert Comment

by:Lance_Frisbee
ID: 13570013
Code would help - but I would imagine that you could try to save this information when the data is actually edited. I'm assuming you're using forms to acheive this. When you click submit and it sends a query to the database to INSERT or UPDATE, you can have a 2nd query that actually gets that specific record id, and appends all of its info to a file using fwrite()...

Just an idea.

Lance
0
 

Author Comment

by:dm14011
ID: 13570465
<?php
/*heres the whole of the file
it contains 2 functions - one to connect to the db and one to show the table. There is also
a bit of code after the first function that is meant to listen and recieve calls to save.
It is first meant to recieve the primary key of the row being saved, but I can only get it to repond to row number one being saved
the function is first suppose to display any sql table after connecting to the database (just enter the table name
as the parameter.
Perhaps you can test it with your own db.Just call the functions with the appropriate parameters and try and get the cells when edited to save the data back in the table.
thanks
*/
?>
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>

<?php

#variables
$tablename;
$primary_key_name;

?> <?php
function Connect($host, $user_name, $password, $database_name)
{


#connect to database
#establish connection with server
$link = mysql_connect($host, $user_name, $password);
            if (!$link) {
                     die('Could not connect: ' . mysql_error());
               }
               else echo "Successful Connection";
               
#select required database
$db_selected = mysql_select_db($database_name);
if (!$db_selected) {
   die ('Can\'t use database : ' . mysql_error());
}
}
      
?>

<?php
  #listens for submit calls to save?
  if (!empty($_POST["save"]))  
{
echo "save button pressed";

 # start of code when save button has been pressed
    $primary_key=$_POST["primary_key"];  
   
echo "primary key value = ".$primary_key;
   
   /* #SELECT row to be saved from table
    $query="SELECT * FROM $tablename WHERE $primary_key_name = $primary_key";  // find pet that has been altered
    $query_result = @ mysql_query($query);  // run query outlined in above line.
      if (!$query_result) {
                           die ('update failed: ' . mysql_error());
            }

    for($count = 0;$row = mysql_fetch_row($query_result); $count++)
     {//
            #$names = mysql_field_name($table_data, 0);            
            $count3 = 0;
            #print_r($names);
            print_r($row);
            foreach($row as $key=>$value)
            {
            echo $count3;
            $name = mysql_field_name($table_data, $count3);      
            echo $name;
            $saved_name = $name.'_save';
            
            $update_name_query="UPDATE $tablename SET $name = '".$_POST[$saved_name]."' WHERE $primary_key_name = $primary_key";
            $update_result = mysql_query($update_name_query); // runs update of pet field query
       
        if (!$update_result) {
                           die ('update failed: ' . mysql_error());
            }
            $count3++;
            }


    } */ // end of code for record retrieval of the pet query

} // end of code when save button has been pressed




#need function to print out sql tables

function PrintTable($tablename_)
{

$tablename = $tablename_;

      #get column headings
      $fresult = mysql_query("SHOW COLUMNS FROM $tablename");
      if(!$fresult)
      {
      echo 'Could not run query: ' .mysql_error();
      exit;
      }
      
      #get the whole table
      $table_query="SELECT * FROM $tablename";
      $table_data = mysql_query($table_query);
      if (!$table_data) {
               die ('Can\'t complete query : ' . mysql_error());
      }      
      
      #print rows as coulmns
      #note:- when printing html varibale strings in a print call, use '
      #            instead of ".
      print("<H1>Pet Information Table</H1>");
      print("<TABLE SUMMARY='CO1018 Example Pet Table Form' border='1' width='100%'>");

  print("<thead>");
   print("<COLGROUP>");
   
   if(mysql_num_rows($fresult) > 0)
   {
   for($count = 0;$row = mysql_fetch_assoc($fresult); $count++)
        {
        #store primary key name if found
        if($row['Key'] == "PRI") $primary_key_name = $row['Field'];
        
      print("<col width='20%'>");
      #echo mysql_num_rows($fresult);
     
      }
    }
   
 print("  
</colgroup>
    <tr>
    ");
   
    $fresult = mysql_query("SHOW COLUMNS FROM $tablename");
      if(!$fresult)
      {
      echo 'Could not run query: ' .mysql_error();
      exit;
      }
      
     if(mysql_num_rows($fresult) > 0)
   {
   for($count = 0;$row = mysql_fetch_assoc($fresult); $count++)
        {
        #note teh ". either side to ensure variable is formatted correctly
      print("<TH SCOPE=col>".$row["Field"]."</TH>");          
      }
    }
      
      #the table
      #$table = my_field_table($table_data, 0);
      
       $fresult = mysql_query("SHOW COLUMNS FROM $tablename");
      if(!$fresult)
      {
      echo 'Could not run query: ' .mysql_error();
      exit;
      }
print("
      <TH SCOPE=col>&nbsp;</TH>
    </tr>
  </thead>
 
  <tbody>
  ");
 
       /**set posting allowed*******************************************/
       
        $primary_key_col_no;
        
        for($count = 0;$field_info = mysql_fetch_assoc($fresult); $count++)
        {
        if ($field_info['Key'] == "PRI") $primary_key_col_no = $count;
}
      echo $primary_key_col_no;
        
    echo "<form name='petform' method='post'>";
  for($count = 0;$row = mysql_fetch_row($table_data); $count++)
{


      $row2 = $row;
      
      #start new row
      print("<TR>");
      $count2 = 0;
      
      #holds lengths of each result column stored in THE LAST ROW RETURNED
      #BY mysql_fetch_row(), mysql_fetch_assoc(), mysql_fetch_array(),
      #mysql_fetch_robject()
      $lengths = mysql_fetch_lengths($table_data);
      
      #for each cell
      foreach($row as $key=>$value)
      {
      $type = mysql_field_type($table_data, $count2);
      $name = mysql_field_name($table_data, $count2);
      
      print(" <TD SCOPE=row> <INPUT TYPE= $type NAME = $name SIZE=$lengths[$count2] VALUE=$value></TD>");      
       $count2++;
       }
       
       $count2 = 0;
       
       #do inputs
       #for each cell
      foreach($row2 as $key=>$value)
      {
      
      #to get primary key
      
      $name = mysql_field_name($table_data, $count2);
      
      #print_r($field_info);
      #for saving data
       $save_name = $name.'_save';      

       #if primary key then make special call
       if ($count2 == $primary_key_col_no)
       {
       print( "<INPUT type='hidden' value=$value name='primary_key'>");
       echo "primary key = ".$value;
       }

       print("<INPUT type='hidden' value=$value name=$save_name>");      
       $count2++;
       }
       
       #submit means it is going to be a box that submits when pressed
        print("<TD SCOPE=row><INPUT type='submit' value='Save' name='save' size='20'></TD>");    
     
       #end row
       print("</TR>");
       
       echo "</form>"; // end of record form
       
       
       
       
       }
       

}

?>



</body>

</html>
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 32

Expert Comment

by:ldbkutty
ID: 13573219
>> Perhaps you can test it with your own db.Just call the functions with the appropriate parameters and try and get the cells when edited to save the data back in the table.

Can you give the table structure + some data's in Sql format so I can try testing here ?
0
 

Author Comment

by:dm14011
ID: 13575348
<?php
/*Here is a function and some code to create the database to test with and a table to test on.
Connect first using your own method, or the connection function int the code section above, then run the
coe here to create the database and table
thanks
*/
function Create_Database($name) {

       $result = mysql_query("CREATE DATABASE $name");
       
            if (!$result)
            {
                     die('Invalid query: ' . mysql_error());
            }
   }

Create_Database("petdb");

$db_selected = mysql_select_db("petdb");
if (!$db_selected) {
   die ('Can\'t use database : ' . mysql_error());
}

$result =  mysql_query("CREATE TABLE petinfo
                              (
                        petid smallint(5) NOT NULL auto_increment,pet VARCHAR(20),
                        sex CHAR(1), birth DATE, price DECIMAL(5,2), PRIMARY KEY (petid))");
                                 if (!$result) {
                                     die('pet table error:' . mysql_error());
                                    }

$result =  mysql_query("
Insert into petinfo values (null,'Siamese Cat','f','1993-03-04','560.00'),
(null,'Persian Cat','m','1994-03-17','200.00'),
(null,'American Bull Dog','f','1989-05-13','250.50'),
(null,'Australian Koolie','m','1990-08-27','300.00'),
(null,'Basset Hound','m','1998-08-31','260.00'),
(null,'Budgy','f','1998-09-11','290.00'),
(null,'Parrot','f','1997-12-09','300.00'),
(null,'Python','m','1996-04-29','499.95'),
(null,'Bengal Cat','f','1999-03-30','50.00')");

if (!$result) {
                                       die('insert data error:' . mysql_error()
                                       );
                                       }

?>
0
 
LVL 10

Expert Comment

by:Kshitij Ahuja
ID: 15659625
No comment has been added to this question in more than 21 days,so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:
[PAQ - Refund]

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

Kshitij Ahuja
EE Cleanup Volunteer
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 15700191
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question