PHP Update Table

[bobbyD]

Hi! I'm trying to create a form that recalls certain records from a table and then updates a column in that table based on user input.

I have been able to recall the appropriate information, but no luck in updating and actually writing back to the db.

I have the following code:

<?php
// connect to the database and select the correct database
mysql_connect("localhost","","");
mysql_select_db("") or die("Unable to select database");

if ( isset( $_POST['approve'])) {
	echo "<pre>";
	print_r($_POST);
	echo "</pre>";
	foreach($_POST['Index'] AS $id) {


	$approve = $_POST['Approval'][$id];
	$update = "UPDATE TimeOff SET Approval='" . $approve . "' WHERE Index='" . $id . "'";

mysql_query($update) or die (mysql_error());
	}
}


$sql = "SELECT * FROM TimeOff WHERE Approval=''";
$res = mysql_query($sql);

if (mysql_num_rows ($res) > 0) {
		echo "<form method='post'>";
		while ($row = mysql_fetch_assoc($res))
		{
		echo "ID: " . $row['Index']  . " <br>\n";
		echo "Name: " . $row['Name']  . " <br>\n";
		echo "sDate: " . $row['StartDate']  . " <br>\n";
		echo "eDate: " . $row['EndDate']  . " <br>\n";
		echo "Reason: " . $row['Reason']  . " <br>\n";
		echo "Approval: <select name='approval[".$row['Index']."]' value='" .$row['Approval']."'><option value=''></option><option value='Yes'>Yes</option><option value='No'>No</option></select><br>\n";

	echo "<hr>\n";
	}

	echo "<input type='submit' name='approve' value ='submit'>";
	echo "</form>";

}

?>

I'm sure I'm doing something completely wrong, any help/suggestions are greatly appreciated!

Thanks!

[laserlight]

The first thing you need to do is to stop using the legacy MySQL extension, especially when you are not taking steps to protect against SQL injection by properly escaping your string input to the SQL statement. Use the MySQLi extension or the PDO extension instead. Both of these allow you to use prepared statements, which can be used as an alternative to a more manual method of escaping string input or otherwise rendering non-string input safe.

When you have made the changes, post your updated code and tell us how does it not work.

[bobbyD]

Thanks for the response laserlight.

I guess I didn't mention that I'm teaching myself how to use PHP and SQL and am only on day 3 of even trying.

I wish your suggestions were more helpful, but I got this far by watching tutorials on youtube/reading through forums such as this, and would have no idea how to modify my code to use your suggestions.

[cretaceous]

Move this to the top after the database connect - that will show you what the form has sent in the POST array :

echo "<pre>";
	print_r($_POST);
	echo "</pre>";

<select.. > should not have a value
so this is meaningless:
<select name='approval[".$row['Index']."]' value='" .$row['Approval']."'>
the value part goes in the <option> item, as you have done

not only that but your SQL query ask for results where Approval='' - in other words where it's blank
so $row['Approval'] will always be empty

[bobbyD]

Thank You cretaceous!

Made the changes you suggested and am getting closer to understanding what the code is trying to do.

Now when the page loads it shows the actual value for Approval in the drop down list and allows me to select a different one (I initially set the SQL WHERE Approval='' so only those that have not yet been approved Yes or No would load).

Where can I learn about the output for print_r and what each piece means?

I am getting this when the form submits (assuming I selected Yes in the dropdown for record #1:

Array
(
    [approval] => Array
        (
            [1] => Yes
            [2] => 
            [3] => 
            [4] => 
            [5] => 
            [6] => 
            [7] => 
            [8] => 
            [9] => 
            [10] => 
            [11] => 
            [12] => 
        )

[Index] => 12
[approve] => Approve
)

...however I'm not sure what this is telling me.

I know the numbers inside [] are the index values from my table because when I had the SQL only bringing blank approval values, only those ID numbers showed in the print.

On the right, I know the Yes comes from what I selected, but that's it.

I know I'm getting closer, but haven't yet got the update query to write to my db.

Thanks again for your help thus far - I'm a total beginner and am just trying to learn a new skill.

[cretaceous]

Array
(
    [approval] => Array
        (
            [1] => Yes
            [2] => 
            [3] => 
            [4] => 
            [5] => 
            [6] => 
            [7] => 
            [8] => 
            [9] => 
            [10] => 
            [11] => 
            [12] => 
        )

so you have 12 rows in your drop down - they are all in there because you are using this style:
name='approval[".$row['Index']."]'
that creates an array of approval[0] , approval[1] etc (but the actual numbers will be as your data output)
so you can see that "approval" with index value of '1' is the entry that was chosen

    [approve] => Approve
)

this is the submit button - its name is 'approve' and its text value is 'Approve' (so you changed that from the word 'submit')

not sure what 'Index' is from..