PHP/MySQL Errors

gmrstudios

I created a simple php/mysql phonebook from one of the many tutorials floating around the net and seem to be running into some errors. When I do the queries within MySQL they work like a charm and I don't encounter errors. When I add or delete a record via the php form things work just fine as well. However, when I try to update a record with a form I get the following error:

Notice: Undefined index: Submit in C:\Inetpub\wwwroot\intranet_database\update.php on line 9

Here is the code for the update page:

<?
// START PHP CODES. THIS PART MUST ON THE TOP OF THIS PAGE. 

// Connect database. 
include("connectdb.php");

// ***** This part will process when you Click on "Submit" button ***** 
// Check, if you clicked "Submit" button 
if($_POST["Submit"]){

// Get parameters from form. 
$id=$_POST['id'];
$name=$_POST['name'];
$email=$_POST['email'];
$tel=$_POST['tel'];
$location=$_POST['location'];

// Do update statement. 
mysql_query("update phonebook set name='$name', email='$email', tel='$tel' where id='$id'");

// Re-direct this page to select.php.
header("location:select.php");
exit;
}
// ************* End update part *************

// *** Select data to show on text fields in form. ***

// Get id parameter (GET method) from select.php 
$id=$_GET['id'];

// Get records in all columns from table where column id equal in $id and put it in $result.
$result=mysql_query("select * from phonebook where id='$id'");

// Split records in $result by table rows and put them in $row. 
$row=mysql_fetch_assoc($result);

// Close database connection. 
mysql_close();
?>

<!-- END OF PHP CODES AND START HTML TAGS -->

<html>
<body>
<!-- set this form to POST method and target this form to itself ($PHP_SELF;)--> 
<form id="form1" name="form1" method="post" action="<? echo $PHP_SELF; ?>">
<p>Name : 
<!-- name of this text field is "name" -->
<input name="name" type="text" id="name" value="<? echo $row['name']; ?>"/>
<br />
Email :
<!-- name of this text field is "email" -->
<input name="email" type="text" id="email" value="<? echo $row['email']; ?>"/>
<br />
Tel : 
<!-- name of this text field is "tel" -->
<input name="tel" type="text" id="tel" value="<? echo $row['tel']; ?>"/>
<br />
Location: 
<!-- name of this text field is "location" -->
<input name="location" type="text" id="location" value="<? echo $row['location']; ?>"/>
</p>
<p>
<input type="submit" name="Submit" value="Submit" />
</p>
</form>
</body>
</html>

I tried using ' rather than " to without luck.

Any assitance you could provide would be greatly appreciated.

I can get my way around most scripts but I am a novice at best. This is a Win2K3/IIS/PHP install . I apologize in advance if this is something easily fixed or if I missed something obvious.

bradgrafelman

Some of the following items are suggestions to improve the code (e.g. remove deprecated coding habits) while others may help debug the issue:

Don't use '<?' in PHP scripts and instead use the full '<?php' tag; short_tags is disabled by default (and should be left that way, IMHO).
Using code such as
```
if($_POST["Submit"])
```
will cause the "undefined index" messages if the form hasn't been POST'ed yet. Instead, you should use functions such as [man]isset/man or [man]empty/man that were designed to test if a variable exists/is empty.

The same might be said above for code such as:

$id=$_POST['id'];
$name=$_POST['name'];
$email=$_POST['email'];
$tel=$_POST['tel'];
$location=$_POST['location'];

Often, I define incoming variables like so:

$name = (isset($_POST['name']) ? $_POST['name'] : '');
// etc. for all variables

Your MySQL query may be failing; in a development environment where you're debugging scripts that aren't working correctly, it's often helpful to use code such as this:
```
mysql_query('SQL query here') or die('Error: ' . mysql_error());
```
That way, you can see if MySQL is trying to tell you what's wrong with the query when an error occurs.
Your script appears to be vulnerable to SQL injection attacks; user-supplied data should never be placed directly inside a SQL query. Instead, you should first sanitize it with a function such as [man]mysql_real_escape_string/man. If $id is an integer type, for example, you could cast it to an int to prevent any errors in the query.
In the second part of your script, you assume $GET['id'] to have some value; what if it does not?
Normally, you should never do a 'SELECT *' query and instead only SELECT the columns that you actually need data from. Even if you present use data from all columns, naming all of the columns will keep the query optimized if you later add more columns (whereas a "SELECT " query will incorporate these new fields and use more memory than what might be necessary).
Where is $PHP_SELF ever defined? You should check to make sure that the register_globals directive is disabled. If it is not, disable it now and start using the superglobal arrays (see: [man]variables.superglobals) such as $_SERVER (e.g. $_SERVER['PHP_SELF'], in this case). Why? The register_globals directive has been declared a security risk for some time now and as such has been deprecated/removed from future versions of PHP. More information about this directive's deprecation can be found here: [man]security.globals[/man].
To protect the integrity of the HTML code, you might want to use [man]htmlentities/man function on all of the data coming from $row.

gmrstudios

bradgrafelman,

Thank you for the comprehensive and timely response. I am implemented the large majority of your suggestions into the script to great success. However, I hit another bump in the road after resolving the Undefined index notice. Here is the updated code per your suggestions:

<?php
// START PHP CODES. THIS PART MUST ON THE TOP OF THIS PAGE. 

// Connect database. 
include("connectdb.php");

// ***** This part will process when you Click on "Submit" button ***** 
// Check, if you clicked "Submit" button ****Correct use of Submit Button****
if(isset($_POST['submit'])){
// Get parameters from form. 
$id = (isset($_POST['id']) ? $_POST['id'] : ''); 
$name = (isset($_POST['name']) ? $_POST['name'] : ''); 
$email = (isset($_POST['email']) ? $_POST['email'] : ''); 
$tel = (isset($_POST['tel']) ? $_POST['tel'] : ''); 
$location = (isset($_POST['location']) ? $_POST['location'] : ''); 

// Do update statement. 
mysql_query("update phonebook set name='$name', email='$email', tel='$tel', location='$location', where id='$id'"); 

// Re-direct this page to select.php.
header("location:select.php");
exit;
}
// ************* End update part *************

// *** Select data to show on text fields in form. ***

// Get id parameter (GET method) from select.php 
$id=$_GET['id'];

// Get records in all columns from table where column id equal in $id and put it in $result.
$result=mysql_query("select * from phonebook where id='$id'");

// Split records in $result by table rows and put them in $row. 
$row=mysql_fetch_assoc($result);

// Close database connection. 
mysql_close();
?>

<!-- END OF PHP CODES AND START HTML TAGS -->

<html>
<body>
<!-- set this form to POST method and target this form to itself ($_SERVER['PHP_SELF'];)-->
<form id="form1" name="form1" method="post" action="<? echo $_SERVER['PHP_SELF']; ?>">
<p>Name : 
<!-- name of this text field is "name" -->
<input name="name" type="text" id="name" value="<? echo $row['name']; ?>"/>
<br />
Email :
<!-- name of this text field is "email" -->
<input name="email" type="text" id="email" value="<? echo $row['email']; ?>"/>
<br />
Tel : 
<!-- name of this text field is "tel" -->
<input name="tel" type="text" id="tel" value="<? echo $row['tel']; ?>"/>
<br />
Location: 
<!-- name of this text field is "location" -->
<input name="location" type="text" id="location" value="<? echo $row['location']; ?>"/>
</p>
<p>
<input type="submit" name="Submit" value="Submit" />
</p>
</form>
</body>
</html>

This is the error message I now receive. I assume this has to do with $id=$_GET['id']; not pulling a value of some sort, but have no idea how to go about fixing it.

Notice: Undefined index: id in C:\Inetpub\wwwroot\intranet_database\update.php on line 29

Thanks again for your help it is greatly appreciated.

bradgrafelman

As I mentioned above, if $_GET['id'] is not set (which it apparently is not) then the second part of that PHP code doesn't make sense. Not sure what you're after, so you'll have to work out the logic on it (e.g. first check if 'id' was passed in the URL and, if so, run the query).

gmrstudios

I am attempting to get this script in working order I suppose. With delete and insert working properly via the form and delete, insert, and update all working when I run the querries in phpMyAdmin, the last portion of the script has given me fits.

I believe the script pulls $GET['id'] from the previous page or select.php. Since I defined id as

$id = (isset($_POST['id']) ? $_POST['id'] : '');

is that causing the get function to bomb out?

I also noticed that the submit $_SERVER['PHP_SELF'] calls the page name but not the id.

bradgrafelman

$GET['id'] will return the value of the 'id' parameter in the URL of the current page, if it exists. Otherwise, the array key won't even exist and your script will throw notices about undefined indexes. That's why I suggested testing the value of $GET['id'] and only executing the second MySQL query if it exists.

gmrstudios wrote:
I also noticed that the submit $SERVER['PHP_SELF'] calls the page name but not the id.

As per the manual for $SERVER (see [man]variables.server]), $SERVER['PHP_SELF'] contains "the filename of the currently executing script, relative to the document root." So, if you're referring to something such as "http://mysite.com/foo.php?id=1", then yes, of course $SERVER['PHP_SELF'] would excuse the 'id' portion since it has nothing to do with the 'filename of the currently executing script.'

If you wanted to include the query string as well, you'd have to do something like:

$_SERVER['PHP_SELF'] . (!empty($_SERVER['QUERY_STRING']) ? "?$_SERVER[QUERY_STRING]" : '');

Also, forgot to mention this earlier: Your script is vulnerable to SQL injection attacks. User-supplied data should never be placed directly into a SQL query. Instead, you must first sanitize it with a function such as [man]mysql_real_escape_string/man.

Note that since the purpose of sanitization is to prevent unwanted data from entering the SQL query, there are other methods of data sanitization as well, depending upon the expected data type. For example, if $GET['id'] is expected to be an integer, all you would need to do in order to sanitize it would be to cast the data to an integer:

$id = (isset($_GET['id']) ? (int)$_GET['id'] : NULL); // you can use whatever you'd like for a default value

gmrstudios

On the update page select.php the user is presented with the ability to update or delete. When you click delete the query processes as it should. When you click update you are brought to update.php and a URL similar to this:

http://happyfuntime.com/_database/update.php?id=6

It presents you with four field forms autofilled with the information from the database. Name, Email, Tel, Location. After changing a field and clicking submit I don't receive errors back from MySQL I added the error checking you suggested via mysql_query('SQL query here') or die('Error: ' . mysql_error()); and it don't receive error prompts. Its supposed to go back to select.php after processing the form.

I would assume that regardless of whether or not the next page gives me an undefined variable error the query should still process when I click submit.

Thanks again for your help.

gmrstudios

Do I need to supply more information in order to get this resolved? I am a novice and really have no idea how to fix this. Thanks again for reading.