I have read through so many posts on this subject my head is ready to explode.
I am trying to create a little form that will update a database of prospective customers for a client, as well as list out all of the entries in the database. I am not a backend developer which I have mentioned to my client repeatedly, but my client doesn't seem to care... So I'm trying to work this out on the fly.
I have created all of the following columns in the table "prospects": "id", "fname", "lname", "tickler", and "info". So I know that they are there. Also, they are working just fine for the list of prospects I have below the update form (which works just fine).
But when I try to update the database, I get a "Could not update data: Unknown column '$id' in 'where clause'" error. If I remove the WHERE clause, it switches to "Could not update data: Unknown column '$fname' in 'field list'". At the moment I'm just trying to get it to work with the First Name using the ID # as the condition. Eventually it will be able ot update the other fields, too.
I don't know if anyone can see the page (I think my client password protected the page and I'm still logged in), but you can try viewing it at http://www.vlist.info.
Here is the code:
<body>
<h2>Update Prospect</h2>
<?php
if(isset($_POST['update']))
{
$dbhost = 'host';
$dbuser = 'dbo558532591';
$dbpass = 'pass';
$dbname = 'db558532591';
$tablename = 'prospects';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$id = $_POST['id'];
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$tickler = $_POST['tickler'];
$info = $_POST['info'];
mysql_select_db( $dbname ) or die('Could not find table:' . mysql_error());
$sql = 'UPDATE prospects SET fname = $fname WHERE id = $id';
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully
";
mysql_close($conn);
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table>
<tr>
<th>ID:</th>
<th>First Name:</th>
<th>Last Name:</th>
<th>Tickler:</th>
<th>Info:</th>
<th></th>
</tr>
<tr>
<td><input type="text" name="id" /></td>
<td><input type="text" name="fname" /></td>
<td><input type="text" name="lname" /></td>
<td><input type="text" name="tickler" /></td>
<td><input type="text" name="info" /></td>
<td><input type="submit" value="Update" name="update" /></td>
</tr>
</table>
</form>
<?php
}
?>
<h2>Prospects</h2>
<?php $localhost="host";$username="dbo558532591";$password="pass";$database="db558532591";
mysql_connect($localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database.");
$query="SELECT * FROM prospects";$result=mysql_query($query);
$num=mysql_numrows($result);mysql_close();?>
<table>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Tickler</th>
<th>Info</th>
</tr>
<?php $i=0;while ($i < $num) {
$id=mysql_result($result,$i,"id");
$fname=mysql_result($result,$i,"fname");
$lname=mysql_result($result,$i,"lname");
$tickler=mysql_result($result,$i,"tickler");
$info=mysql_result($result,$i,"info");
?>
<tr>
<td><?php echo $id; ?></td>
<td><?php echo $fname; ?></td>
<td><?php echo $lname; ?></td>
<td><?php echo $tickler; ?></td>
<td><?php echo $info; ?></td>
</tr>
</table>
<?php $i++;}?>