Inserting data into a mysql database

PHP
1

I’m very new to this and I’m following the examples in the book “Build you own database driven website using php and mysql” and I’m working on the content management system, I’m trying to add and edit users in the database; the form comes up alright and it doesn’t bring up any error messages but when editing nothing comes up in the boxes to edit and when you type the information in it doesn’t change anything and when you go to add a new user nothing happens I’m wondering if I’ve missed something out at some point this is the code for the index.php:

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”>
<html xmlns=“http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=utf-8” />
<title>Untitled Document</title>
</head>

<body>
<?php
include $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/db.inc.php’;
$result = mysqli_query($link, ‘SELECT id, firstname, surname, email, pword, address, telno FROM cakeuser’);
if (!$result)
{
$error = ‘Error fetching users from database!’;
include ‘error.html.php’;
exit();
}

while ($row = mysqli_fetch_array($result))
{
$users = array(‘id’ => $row[‘id’], ‘firstname’ => $row[‘firstname’], ‘surname’ => $row[‘surname’], ‘email’ => $row[‘email’], ‘pword’ => $row[‘pword’], ‘address’ => $row[‘address’], ‘telno’ => $row[‘telno’]);
}
include ‘users.html.php’;

if (isset($_POST[‘action’]) and $_POST[‘action’] == ‘Delete’)
{
include $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/db.inc.php’;
$id = mysqli_real_escape_string($link, $_POST[‘id’]);

// Delete the author
$sql = "DELETE FROM cakeuser WHERE id='$id'";
if (!mysqli_query($link, $sql))
{
	$error = 'Error deleting user.';
	include 'error.html.php';
	exit();
}
header('Location: .');
exit();

}

include_once $_SERVER[‘DOCUMENT_ROOT’] .
‘/includes/magicquotes.inc.php’;

if (isset($_GET[‘add’]))
{
$pagetitle = ‘New User’;
$action = ‘addform’;
$firstname = ‘’;
$surname = ‘’;
$email = ‘’;
$pword = ‘’;
$address = ‘’;
$telno = ‘’;
$id = ‘’;
$button = ‘Add user’;

include 'form.html.php';
exit();

}

if (isset($_GET[‘addform’]))
{
include $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/db.inc.php’;

$firstname = mysqli_real_escape_string($link, $_POST['firstname']);
$surname = mysqli_real_escape_string($link, $_POST['surname']);
$email = mysqli_real_escape_string($link, $_POST['email']);
$pword = mysqli_real_escape_string($link, $_POST['pword']);
$address = mysqli_real_escape_string($link, $_POST['address']);
$telno = mysqli_real_escape_string($link, $_POST['telno']);
$sql = "INSERT INTO cakeuser SET
		firstname='$firstname',
		surname='$surname',
		email='$email'
		pword='$pword',
		address='$address',
		telno='$telno'";
if (!mysqli_query($link, $sql))
{
	$error = 'Error adding submitted user.';
	include 'error.html.php';
	exit();
}
header('Location: .');
exit();

}

if (isset($_POST[‘action’]) and $_POST[‘action’] == ‘Edit’)
{
include $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/db.inc.php’;

$id = mysqli_real_escape_string($link, $_POST['id']);
$sql = "SELECT id, firstname, surname, email, pword, address, telno FROM cakeuser WHERE id='$id'";
$result = mysqli_query($link, $sql);
if (!$result)
{
	$error = 'Error fetching user details.';
	include 'error.html.php';
	exit();
}
$row = mysqli_fetch_array($result);

$pagetitle = 'Edit User';
$action = 'editform';
$name = $row['firstname'];
$name = $row['surname'];
$email = $row['email'];
$name = $row['pword'];
$name = $row['address'];
$name = $row['telno'];
$id = $row['id'];
$button = 'Update user';

include 'form.html.php';
exit();

}

if (isset($_GET[‘editform’]))
{
include $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/db.inc.php’;

$id = mysqli_real_escape_string($link, $_POST['id']);
$firstname = mysqli_real_escape_string($link, $_POST['firstname']);
$surname = mysqli_real_escape_string($link, $_POST['surname']);
$email = mysqli_real_escape_string($link, $_POST['email']);
$pword = mysqli_real_escape_string($link, $_POST['pword']);
$address = mysqli_real_escape_string($link, $_POST['address']);
$telno = mysqli_real_escape_string($link, $_POST['telno']);
$sql = "UPDATE cakeuser SET
		firstname='$firstname',
		surname='$surname',
		email='$email'
		pword='$pword',
		address='$address',
		telno='$telno'
		WHERE id='$id'";
if (!mysqli_query($link, $sql))
{
	$error = 'Error updating submitted user.';
	include 'error.html.php';
	exit();
}
header('Location: .');
exit();

}

?>

</body>
</html>

Thankyou for any help

2

Are your database settings are correct? It seems you have no connection to the database

3

it says its using this : “include $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/db.inc.php’;” to connect in the book that file looks like this:

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=“http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=iso-8859-1” />
<title>Untitled Document</title>
</head>

<body>
<?php
$link = mysqli_connect(‘localhost’, ‘root’, ‘******’);
if (!$link)
{
$error = ‘Unable to connect to the database server.’;
include ‘error.html.php’;
exit();
}

if (!mysqli_set_charset($link, ‘utf8’))
{
$output = ‘Unable to set database connection encoding.’;
include ‘output.html.php’;
exit();
}

if (!mysqli_select_db($link, ‘cake’))
{
$error = ‘Unable to locate the cake database.’;
include ‘error.html.php’;
exit();
}

?>
</body>
</html>

thankyou

4

Can you post form.html.php?

5

<?php include_once $_SERVER[‘DOCUMENT_ROOT’] .
‘/includes/helpers.inc.php’; ?>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN”
“http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”>
<html xmlns=“http://www.w3.org/1999/xhtml” xml:lang=“en” lang=“en”>
<head>
<title><?php htmlout($pagetitle); ?></title>
<meta http-equiv=“content-type”
content=“text/html; charset=utf-8”/>
</head>
<body>
<h1><?php htmlout($pagetitle); ?></h1>
<form action=“” method=“post”>
<div>
<label for=“firstname”>First Name: <input type=“text” name=“firstname”
id=“firstname” value=“<?php htmlout($firstname); ?>”/></label>
</div>
<div>
<label for=“surname”>Surname: <input type=“text” name=“surname”
id=“surname” value=“<?php htmlout($surname); ?>”/></label>
</div>
<div>
<label for=“email”>Email: <input type=“text” name=“email”
id=“email” value=“<?php htmlout($email); ?>”/></label>
</div>
<div>
<label for=“pword”>Password: <input type=“text” name=“pword”
id=“pword” value=“<?php htmlout($pword); ?>”/></label>
</div>
<div>
<label for=“address”>Address: <input type=“text” name=“address”
id=“address” value=“<?php htmlout($address); ?>”/></label>
</div>
<div>
<label for=“telno”>Telephone: <input type=“text” name=“telno”
id=“telno” value=“<?php htmlout($email); ?>”/></label>
</div>
<div>
<input type=“hidden” name=“id” value=“<?php
htmlout($id); ?>”/>
<input type=“submit” value=“<?php htmlout($button); ?>”/>
</div>
</form>
</body>
</html>

thankyou

6

Please use the appropriate code tags (instead of quote), that way the code will stay indented and will be easier to read.

First of all (and OT): do you really have doctype, header and body tags in all files?

Second: I don’t know what htmlout() does, but I guess it echo’s the string you’re passing?

Third: try to debug index.php by putting some echo’s here and there to follow the flow (= to see what parts are being executed), and to check the value of some variables.

4:

$[B][COLOR="Red"]name [/COLOR][/B]= $row['firstname'];
$[B][COLOR="red"]name [/COLOR][/B]= $row['surname'];
$email = $row['email'];
$[B][COLOR="red"]name [/COLOR][/B]= $row['pword'];
$[B][COLOR="red"]name [/COLOR][/B]= $row['address'];
$[B][COLOR="red"]name [/COLOR][/B]= $row['telno'];

Notice something strange here? :slight_smile:

7

  1. Yes I do should I not? I’ve never done this before and it has it on the examples in the book and its put there automatically by dreamweaver so i’ve just been leaving it, do i not need it?

  2. its a function that was defined in a file so it could be used with each page i think

<?php 
function html($text)
{
return htmlspecialchars($text, ENT_QUOTES, 'UTF-8');
}

function htmlout($text)
{
echo html($text);
}
?>

  1. not to sound like an even bigger idiot but how do i use echo in these files?

  2. how silly am i haha
    with changing the fourth thing the name is displaying but none of the other fields and it’s still not actually changing anything in the database

thankyou very much

8

  1. The resulting html page must have only one doctype, one head and one body. So if you have those in the index.php, then you should eliminate them from all files that are included.

  2. ok

3 and 4)
Please add the red line to your code in index.php, and see what shows up:


if (isset($_POST['action']) and $_POST['action'] == 'Edit')
{
  include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

  $id = mysqli_real_escape_string($link, $_POST['id']);
  $sql = "SELECT id, firstname, surname, email, pword, address, telno FROM cakeuser WHERE id='$id'";
  $result = mysqli_query($link, $sql);
  if (!$result)
  {
    $error = 'Error fetching user details.';
    include 'error.html.php';
    exit();
  }
  $row = mysqli_fetch_array($result);

  [B][COLOR="Red"][SIZE="2"]echo "let's see what $row contains: "; print_r($row); echo "<br />";[/SIZE][/COLOR][/B] 

  $pagetitle = 'Edit User';
  $action = 'editform';
  $name = $row['firstname'];
  $name = $row['surname'];
  $email = $row['email'];
  $name = $row['pword'];
  $name = $row['address'];
  $name = $row['telno'];
  $id = $row['id'];
  $button = 'Update user';

  include 'form.html.php';
  exit();
}