Can't edit mySQL

Beginners - Learning PHP
#1

Hi, I’ve made a website where you can enter data into a database using a form. I’ve also made a page where you can view the data called listEmployees.php
I’ve echoed “update” buttons next to each entry in listEmployees where a page called editEmployees.php is supposed to change them but it’s not working.
When I press “update” it just comes up with “Error”
Any ideas what I’m doing wrong? Here is my code for editEmployees

[php]<?php
function valid($employeeID, $monthsWorked, $paidHoliday, $reasons)
{

?>

Employee ID

How many months have you been here

Do have you paid holiday left? CHOOSE ONE Yes No

What is your reason for absense?

Illness - doctors note
Illness - no doctors note
Holiday
Family/personal problems
Unable to get into work (weather etc)
<?php } $host="localhost"; // Host name $username="root"; // Mysql username $password=""; // Mysql password $db_name="pay"; // Database name $tbl_name="sickdays"; // Table name

// Connect to server and select database.
mysql_connect("$host", “$username”, “$password”)or die(“cannot connect”);
mysql_select_db(“pay”)or die(“cannot select DB”);

if (isset($_POST[‘submit’]))
{

if (is_numeric($_POST[‘id’]))
{

$id = $_POST[‘id’];
$employeeID = mysql_real_escape_string(htmlspecialchars($_POST[‘employeeID’]));
$monthsWorked = mysql_real_escape_string(htmlspecialchars($_POST[‘monthsWorked’]));
$paidHoliday = mysql_real_escape_string(htmlspecialchars($_POST[‘paidHoliday’]));
$reasons = mysql_real_escape_string(htmlspecialchars($_POST[‘reasons’]));

if ($employeeID == ‘’ || $monthsWorked == ‘’ || $paidHoliday == ‘’)
{

$error = ‘You havent filled out the required fields!’;

valid($employeeID, $monthsWorked, $paidHoliday, $reasons)

}
else
{

mysql_query(“UPDATE sickdays SET employeeID=’$employeeID’, monthsWorked=’$monthsWorked’, paidHoliday=’$paidHoliday’, reasons=’$reasons’ WHERE id=’$id’”)
or die(mysql_error());

header(“Location: listEmployees.php”);
}
}
else
{

echo ‘Error1!’;
}
}
else
{

if (isset($_GET[‘id’]) && is_numeric($_GET[‘id’]) && $_GET[‘id’] > 0)
{

$id = $_GET[‘id’];
$result = mysql_query(“SELECT * FROM sickdays WHERE id=$id”)
or die(mysql_error());
$row = mysql_fetch_array($result);

if($row)
{
$employeeID = $row[‘employeeID’];
$monthsWorked = $row[‘monthsWorked’];
$paidHoliday = $row[‘paidHoliday’];
$reasons = $row[‘reasons’];

valid($employeeID, $monthsWorked, $paidHoliday, $reasons);

}
else
{
echo “No results!”;
}
}
else

{
echo ‘Error’;
}
}
?>[/php]

#2

First I would suggest to concentrate on getting you php code to work first then worry about spiffing it up with it up with html/csss.

Secondly throw away mysql and use mysqli for mysql is depreciated.

here is some code to get that hopefully will get you started.
How to connect to it
[php]<?php
$db = new mysqli(“localhost”, “root”, “*****”, “pay”);
/* check connection */
if (mysqli_connect_errno()) {
printf(“Connect failed: %s\n”, mysqli_connect_error());
exit();
}[/php]

how to update a mysqli (My_SQL) database
[php] function update_content($id, $page, $user_date) {

	global $db;
		
	// define sensible test values
	
	// create a prepared statement
	$update_stmt = $db->prepare('
	    UPDATE
		   Sickdays
	    SET
		   employeeID =?,			   
		   monthWorked = ?,
		   paidHoliday= ?,
                       reasons = ?
	    WHERE
		   id = ?
	');
	
	/* bind parameters for markers */
	$update_stmt->bind_param('sssss', employeeID, monthWorked, paidHoliday, reasons);
	
	/* execute query */
	$update_stmt->execute();
    
	/* close statement */
	$update_stmt->close();
}	[/php]

and finally a better way of escaping you code

[php] function html_escape($raw_input) {
return htmlspecialchars($raw_input, ENT_QUOTES | ENT_HTML401, ‘UTF-8’); // important! don’t forget to specify ENT_QUOTES and the correct encoding
} [/php]

[php]$employeeID =html_escape($_POST[‘employeeID’]);[/php]

Now I can’t guarantee any of the code will work, but it’s up to you to figure that out. If you get really stuck then consult www[dot].php[dot]net

#3

a little fix

[php]/* bind parameters for markers */
$update_stmt->bind_param(‘sssss’, $employeeID, $monthWorked, $paidHoliday, $reasons, $id);

//This binds your variables to the query (notice the ? marks) [/php]

#4

Hi, i’ve done it with sqli like you’ve said (with some changes) but all variables i echo-ed in my html were not recognised. so i tried to define them as follows and i get the following error

“Fatal error: Call to undefined method mysqli_stmt::bindParam() in C:\wamp\www\website2\edittutor.php on line 37”

Here is my code I’ve added after yours
[php]try {
$query = “select employeeID, monthsWorked, paidHoliday, reasons where id = ? limit 0,1”;
$stmt = $db->prepare ($query);

	$stmt->bindParam('1', $_REQUEST['id']);
	$stmt->execute();
	
	$row = $stmt->fetch(PDO::FETCH_ASSOC);
	
	$employeeID = $row['employeeID'];
	$monthsWorked = $row['monthsWorked'];
	$paidHoliday= $row['paidHoliday'];
	$reasons = $row['reasons'];
	$id = $row['id'];
	
}catch(PDOExcepton $exception) {
	echo "Error: " . $exception->getMessage();
}[/php]

Can anyone help me on this? I’ve sick of this edit page D:

#5

I haven’t really looked at your code in depth, but from what I have it looks like you are confusing mysqli or PDO. Either one offers greater security than mysql, some people prefer PDO for it offers greater flexibility where they might not always be using a MySQL database and some people know that they will always be using mysqli (I fall in that category). Pick one and stick to that one. My suggestion for you is to go to www.php.net and check the mysqli (or PDO) statements that you want to work with and check out their examples (They even give you a working database “World” that you can try out the examples out yourself). This is what I do, if I need to fetch some data from the database and put it in an associative array. So I do a search like “fetch associative” and then click on mysql_fetch_assoc. Then I notice it is depreciated and click on the appropriate function (mysqli or PDO - In my case I would choose mysqli) that they will give you. I check it out to see if it will do what I want it to do or if I think it won’t I click on the other options they usually give. Sometimes I find out that it won’t do what I want it do by trying the examples out, but that’s no biggie for I will just move on to something that will. I hope this helps.

#6

You were right, I was getting mixed up between the two. I decided to stick with PDO for now, I get no errors and it says “updated successfully” but it doesn’t actually change anything. My insertData.php is in mysqli, do you think they somehow conflict or does it not matter? I know it’s messy to have both in one project but just in the meantime I’d like to know why it’s not doing anything. I may make a seperate post.

Sponsor our Newsletter | Privacy Policy | Terms of Service