insert/update/delete

[dannyd]

Can anyone steer me in a direction to insert/update/delete fields in a table in 1 script.

 

Thanks.

[Anzeo]

Use $_GET variables to distinguish which action you want.

[dannyd]

Is there a script example that can show add update delete with functions.

[wildteen88]

Here is a template. Code is not complete, all you need to do is put the necessary code bits in the switch statement (add, edit and update)

<?php

$host     = 'localhost';
$user     = 'username';
$pass     = 'password';
$db_name  = 'database';
$tbl_name = 'database_table';

// connect to mysql and select database
mysql_connect($host, $user, $pass);
mysql_select_db($db_name);

// get the required action
if(isset($_GET['action']) && !empty($_GET['action']))
{
    // call code based on action
    switch(strtolower($_GET['action']))
    {
        case 'edit':
            // code here to update data in table
        break;

        case 'delete':
            // code here to delete data from a table
        break;

        case 'insert':
            // code here to insert data into a table
        break;

        default:
            echo 'Invalid action permitted';
    }
}
else
{
    // get all data from the table
    $sql    = 'SELECT * FROM ' . $tbl_name;
    $result = mysql_query($sql);

    // get the number of field from the table
    $num_fields = mysql_num_fields($result);

    // display results with table fieldnames and "add" link
    echo '<a href="?action=add">Add Record</a>
<table border="1" cellspacing="2" cellpadding="5">
  <tr>'."\n";

    // first the field names from table
    for($i = 0; $i < $num_fields; $i++)
    {
        echo '    <th>' . ucwords(mysql_field_name($result, $i)) . "</th>\n";
    }
    // append the Action column
    echo "    <th>Action</th>\n";

    echo "  </tr>\n";

    // now display all content to screen
    while($row = mysql_fetch_assoc($result))
    {
        // use implode to create new cell for each item in the row
        echo "  <tr>\n    <td>" , implode("</td>\n    <td>", $row) . "</td>\n    ";

        // display our action links (edit and delete)
        echo '<td><a href="?action=edit&id='.$row['id'].'">EDIT</a> | <a href="?action=delete&id='.$row['id'].'">DELETE</a></td>'."\n  </tr>\n";
    }
    // close table
    echo "</table>\n";
}
?>

[dannyd]

Awesome, this is exactly what I was looking for. I got the delete to work because its just passing in the id. Can you give me a 2 field example of how an update or insert would work within this code if possible ?

[dannyd]

would the edit form be inside the case statement by calling the row by id ? How would the submit to update the edit work ?

[CcXD]

That was really useful, thanks a lot!

 

i have a question though:

-Is there a way of sending 2 types different $_POST['action'] (i.e. "edit" or "accept) using one single form? i have multiple data that needs to be send back to the same page, and depending on which button i click, different parts of the site are displayed.

       echo "<form action=\"AddProjDoc.php\" method=\"post\">\r\n";

// Hidden Values to Pass to Next page

echo "<p><input type=\"hidden\" name=\"action\" value=\"EDITDOC\"></p>\r\n";
echo "<p><input type=\"hidden\" name=\"action\" value=\"ACCEPTDOC\"></p>\r\n";

// Submit Button
echo "<tr><td></td><td><input type=\"submit\" name=\"submit\" value=\"Edit\"></td></tr>\r\n ";
        echo "<tr><td></td><td><input type=\"submit\" name=\"submit\" value=\"Submit\"></td></tr>";

        echo "</form>\r\n";

 

this is not a working code, as the browser does not know how to choose the 'action' value ("EDITDOC" or "ACCEPTDOC").  How can i make it so that the browser knows which one i choose and then assigns the correct 'action' to $_POST['action'] ???

[eddierosenthal]

i guess what you are asking for is that the form and the php work together in the same script.

your file would be called something.php and the form and html could be at the bottom or top of the page.

the action of the form would refer to itself. so when the submit button is pressed the form is read by the script in the page.

 

[CcXD]

yes, i have a whole bunch of php files scattered everywhere (each for every 'page'  like edit, etc), and i want to make just one nice php file which contains them all.  So, yes i need the script and html to work in the same file. And yes u understood my question, i would like to know how can i make it so that this one form has 2 buttons, and that each one assigns a different value for 'action'.

 

Heres the actual code:

echo "<form action=\"AddProjDoc.php\" method=\"post\">\r\n";
		echo "";

		// Hidden Values to Pass to Next page
		echo "<p><input type=\"hidden\" name=\"doc_type\" value=\"$doc_type\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_num\" value=\"$doc_num\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_desc\" value=\"$doc_desc\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_rel_date\" value=\"$doc_rel_date\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_orig_index\" value=\"$doc_orig_index\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_prefix\" value=\"$doc_prefix\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_proj\" value=\"$project\"></p>\r\n";	
		echo "<p><input type=\"hidden\" name=\"notes\" value=\"$notes\"></p>\r\n";

		// Hidden Action Var
		echo "<p><input type=\"hidden\" name=\"action\" value=\"ACCEPTDOC\"></p>\r\n";

		// Submit Button
		echo "<tr><td></td><td><input type=\"submit\" name=\"submit\" value=\"Submit\"></td></tr>";

		echo "</form>\r\n";

		echo "<form action=\"AddProjDoc.php\" method=\"post\">\r\n";
		// Hidden Values to Pass to Next page
		echo "<p><input type=\"hidden\" name=\"doc_type\" value=\"$doc_type\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_num\" value=\"$doc_num\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_desc\" value=\"$doc_desc\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_rel_date\" value=\"$doc_rel_date\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_orig_index\" value=\"$doc_orig_index\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_prefix\" value=\"$doc_prefix\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"doc_proj\" value=\"$project\"></p>\r\n";
		echo "<p><input type=\"hidden\" name=\"notes\" value=\"$notes\"></p>\r\n";
		// Hidden Action Var
		echo "<p><input type=\"hidden\" name=\"action\" value=\"EDITDOC\"></p>\r\n";


		// Submit Button
		echo "<tr><td></td><td><input type=\"submit\" name=\"submit\" value=\"Edit\"></td></tr>\r\n ";
		echo "</table>\r\n";

		echo "<hr>";

 

as you can see both have the same information to post to the next instance of this page, but i have 2 forms.  Is there a way to put this info in just one form with 2 buttons, and depending on which button i press i load a different part of my php script??

[discomatt]

Watch that your single file doesn't become overly cluttered and huge. Many files is good for efficiency.

 

A common approach is to have all $_GET parsing on a single page, and all the functions, classes and 'grunt work' in separate files, included as needed. This keeps the engine from parsing and storing a bunch of code it never ends up executing.

[CcXD]

yes that is true, that is why im trying to compress the above code.

[dannyd]

So in the case edit I would put my form like below, i know this is wrong how would I pass the edit form tag to properly pass the edited field to edit.php where i guess i would perform the mysql update ?

 

case 'edit':

 

            // code here to update data in table

       

                $attribute_id = $_GET['attribute_id'];

$attribute_name = $_GET['attribute_name'];

 

echo "<form action=\"edit.php?&attribute_id=".$attribute_id."&attribute_name=attribute_name\" method=\"post\">";

 

echo '<input type="hidden" name="attribute_id" value="' . $attribute_id . '">';

  echo '<input type="text" name="attribute_name" value="' . $attribute_name . '">';

echo '<input name="update" type="submit" value="Update">';

    echo '</form>';

break;

[discomatt]

Looks fine to me

[dannyd]

The url that the form submits to ends up looking like below:

 

http://www.domain.com/test/edit.php?&attribute_id=53&attribute_name=attribute_name

 

the  id is fine because it shouldnt change.

 

problem is the attribute name doesnt pass the newly edited value.

[dannyd]

got it!

 

echo "<form action=\"edit.php?&attribute_id=".$attribute_id."&attribute_name=".$_POST['attribute_name']."

[dannyd]

i know you mentioned its bad coding practice to keep things in the same file ... but for the sake of getting something working .. can i create a case called update and have it handle the mysql statement coming from another case ?

 

//update database

case 'update':

 

        $attribute_id = $_GET['attribute_id'];

$attribute_name = $_GET['attribute_name'];

 

echo $attribute_name;

 

$sql = 'UPDATE product_attributes SET attribute_id="' . $attribute_id . '", attribute_name="' . $attribute_name . '" WHERE attribute_id="' . $attribute_id . '"';

        $result = mysql_query($sql);

 

    break;

 

 

//update form

case 'edit':

            // code here to update data in table

            $attribute_id = $_GET['attribute_id'];

    $attribute_name = $_GET['attribute_name'];

echo "<form action=\"?action=update&attribute_id=".$attribute_id."&attribute_name=".$_POST['attribute_name']." method=\"post\">";

echo '<input type="hidden" name="attribute_id" value="' . $attribute_id . '">';

echo '<input type="text" name="attribute_name" value="' . $attribute_name . '">';

echo '<input name="update" type="submit" value="Update">';

echo '</form>';

 

break;

 

 

is this even possible ?

[wildteen88]

You wouldn't need to submit the form to edit.php, just submit the form to it's self. Then in the edit section for the switch/case statement you'll have this:

        case 'edit':
            // check that form has been submitted:
            if(isset($_POST['submit']) && $_POST['submit'] == 'Apply Changes')
            {
                $id = $_POST['id'];

                foreach($_POST as $field => $value)
                {
                    if($field != 'submit' && $field != 'id')
                    {
                        $fields_list[] = "`$field`='" . mysql_real_escape_string($value) . "'";
                    }
                }

                $sql = 'UPDATE ' . $tbl_name . ' SET ' . implode(', ', $fields_list) . ' WHERE id=' . $id;

                echo "<pre>$sql</pre>";
            }
            // form has not been submitted, display form
            elseif(isset($_GET['id']) && is_numeric($_GET['id']))
            {
                // get the id from the url
                // url example: ?action=edit&id=1
                $id    = $_GET['id'];
                // get data from table based on id
                $sql   = 'SELECT * FROM '.$tbl_name.' WHERE id='.$id;
                // perform query
                $result = mysql_query($sql);

                // check that the quewry return one result
                if(mysql_num_rows($result) == 1)
                {
                    $row = mysql_fetch_assoc($result);

                    $form = '<form action="?action=edit" method="post">';

                    foreach($row as $field_name => $field_value)
                    {
                        $form .= '<b>' . ucwords($field_name) . '</b>: <input type="text" name="'. $field_name .'" value="' . $field_value . '" /><br />';
                    }

                    $form .= '<input type="submit" name="submit" value="Apply Changes" /></form>';

                    echo $form;
                }
            }
        break;

[dannyd]

Thank you so much! You just made my life easier. Now to understand your logic. Works Great!

[dannyd]

Is there a way I can ignore fields when I output them in the edit code you just pasted when the form is generated ?

 

Like:

foreach($row as $field_name => $field_value)

    {

  if(!$fieldname != id){

 

  //echo fields to be edited

 

  }

         

}

 

I dont want to be able to edit ids.

[dannyd]

figured it out..

 

if($field_name != 'attribute_id' && $field_name != 'clid')

[dannyd]

Every time i post it inserts a new row .. how can i trap that from happening ?

[BlueSkyIS]

see if it already exists. if it does, don't insert it again.

[eddierosenthal]

you can also let the database reject rows when they are based on the primary key, and trap the error.

 

[dannyd]

Im using the template for a script however I pass in an id through the url so the add/update/delete works for a specific product attributes. How can I make the id global thats passed into the script. For the cases it doesnt recognize the passed in variable.