Validate if Data Exists

[xynah]

[FONT="Arial"]Can anybody help me out with this?

I want to validate if the data really exists in the database.

The form requires the user to simply input a 6-character length number (name in form field is invNum) and I'd like to validate if the number (invNum=ID) exists in the database. If the number doesn't exist, I'd like to echo a message and not just give the user a blank form.

Here's my form:

[FONT="Verdana"]<form method="post" action="query.php" name="frmBox">
<table width="5%" border="0" cellspacing="1" cellpadding="1">
<tr>
<td>
<input name="invNum" maxlength="6" tabindex="6"/>
</td>
</tr>
<tr>
<td>
<input type="submit" name="submit" value="Track it!" class="button" tabindex="6"/>
</td>
</tr>
</table>
</form>[/FONT]

Here's my query.php file code:[/FONT]

[FONT="Verdana"]<?php
// get values from input form
$id = $_REQUEST['invNum'];

$username = "username";
$password = "password";
$database = "database";

#CONNECT TO MYSQL
$dbcnx = @mysql_connect('localhost',$username,$password) or die("Unable to connect to MySQL");

#CONNECT TO DATABASE
@mysql_select_db($database) or die( "Unable to select database");

#CREATE THE QUERY
$sql = ("SELECT * FROM TrackBox WHERE ID = '$id' ORDER BY date Limit 0, 30");

#EXECUTE QUERY
if($id == "")
{
// no number entered
{
die('You did not enter anything. Please go
<a href="javascript:history.back(-1);">back</a>.');
}
if (isset($id['ID']) AND
(strlen($id['ID']) >= 6) AND
(strlen($id['ID']) <= 5) )
// checking the length
{
die('Please enter 6 characters.');
}
if(!filter_var($id=='ID'))
echo "This number cannot be found in our records. Please contact us.";
}
else
{
$rs = mysql_query($sql,$dbcnx) or die(mysql_error());
echo "<font face='verdana' size='-1'><table border='1' width='40%'>
<tr>
<th\n><b>Date</b></th>
<th\n><b>Location</b></th>
</tr>";while($row = mysql_fetch_array($rs))
{
echo "<h5>" . "This is your current status..." . "</h5>";
echo "<tr>";
echo "<td align=center>" . $row['DATE'] . "</td>";
echo "<td align=center>" . $row['LOCATION'] . "</td>";
echo "</tr>";
}
echo "</table></font>";
mysql_close($dbcnx);
}
?> [/FONT]

[laserlight]

When posting PHP code, kindly indent it properly and place them within php bbcode tags. This makes the code easier to read.

The first problem I see is that you do not check if $REQUEST['invNum'] exists before assigning it to $id. Rather, use isset to check that $REQUEST['invNum'] exists, and also check if its length is equal to 6. If so, assign it to $id, casting it to an int to prevent a possible SQL injection. Only then should you use $id to construct the SQL statement and subsequently execute the query.

Your SQL statement could be as simple as:

SELECT COUNT(ID) FROM TrackBox WHERE ID = '$id'

Since all you want to know is whether the number exists (so you check if the count is non-zero).

I notice that you wrote:

(strlen($id['ID']) >= 6) AND (strlen($id['ID']) <= 5)

This can be more simply expressed as:

strlen($id['ID']) == 6

That said, you probably want to use $id since $id['ID'] does not exist.

[xynah]

Ok... I don't know what you meant by using isset (I am new to php). But this is what I did so far:

[INDENT]<?php
// get values from input form
$id = $_REQUEST['invNum'];

$username = "username";
$password = "password";
$database = "database";

#CONNECT TO MYSQL
$dbcnx = @mysql_connect('localhost',$username,$password) or die("Unable to connect to MySQL");

#CONNECT TO DATABASE
@mysql_select_db($database) or die( "Unable to select database");

#CREATE THE QUERY
$sql = ("SELECT COUNT(ID) FROM TrackBox WHERE ID = '$id'");

#EXECUTE QUERY
if($id == "")
{
// no number entered
{
die('You did not enter anything. Please go
<a href="javascript:history.back(-1);">back</a>.');
}
if (strlen($id['ID']) == 6)
// checking the length
{
echo "Please enter 6 characters.";
}
if(!filter_var($id=='ID'))
echo "This number cannot be found in our records. Please contact us.";
}
else
{
$rs = mysql_query($sql,$dbcnx) or die(mysql_error());
echo "<font face='verdana' size='-1'><table border='1' width='40%'>
<tr>
<th\n><b>Date</b></th>
<th\n><b>Location</b></th>
</tr>";while($row = mysql_fetch_array($rs))
{
echo "<h5>" . "This is your current status..." . "</h5>";
echo "<tr>";
echo "<td align=center>" . $row['DATE'] . "</td>";
echo "<td align=center>" . $row['LOCATION'] . "</td>";
echo "</tr>";
}
echo "</table></font>";
mysql_close($dbcnx);
}
?> [/INDENT]

I still can't figure out why it doesn't work 😕 . Please help me.

[laserlight]

I would expect it to be along the lines of:

// Database connection details.
$username = "username";
$password = "password";
$database = "database";

// Connect to the database.
$conn = @mysql_connect('localhost', $username, $password) or die("Unable to connect to MySQL");
@mysql_select_db($database, $conn) or die( "Unable to select database");

// Validate invNum.
if (isset($_REQUEST['invNum']) && strlen($_REQUEST['invNum']) == 6)
{
    // Cast invNum to int to prevent SQL injection.
    $id = (int)$_REQUEST['invNum'];
    // Select the status.
    $query = "SELECT `DATE`, `LOCATION` FROM TrackBox WHERE ID = '$id'";
    $result = mysql_query($query, $conn);
    // Display status if there is at least one row.
    if (mysql_num_rows($result) > 0)
    {
        // print table header
        while ($row = mysql_fetch_assoc($result))
        {
            // print current status
        }
        // print table footer
    }
    else
    {
        echo 'This number cannot be found in our records. Please contact us.';
    }
    mysql_close($conn);
}
else
{
    echo 'You did not enter 6 characters. Please go <a href="javascript:history.back(-1);">back</a>.';
}

Of course, this is an unpolished example, but it does show how the validation logic would work.

[xynah]

Thanks, laserlight. Your code does validate the data. But now... my problem is: after validation, it doesn't give the query result at all.

[xynah]

Can somebody help me please? Laserlight helped me validate my data, but the problem is that, after validation, my query doesn't display any result. Please help...

[xynah]

Ok... this is the code... It is now able to validate the data (thanks to laserlight!), but it doesn't produce my query result...

[INDENT]<?php
// Database connection details.
$username = "username";
$password = "password";
$database = "database";

// Connect to the database.
$conn = @mysql_connect('localhost', $username, $password) or die("Unable to connect to MySQL");
@mysql_select_db($database, $conn) or die( "Unable to select database");

// Validate invNum.
if (isset($REQUEST['invNum']) && strlen($REQUEST['invNum']) == 6)
{
// Cast invNum to int to prevent SQL injection.
$id = (int)$_REQUEST['invNum'];
// Select the status.
$query = "SELECT DATE, LOCATION FROM TrackBox WHERE ID = '$id'";
$result = mysql_query($query, $conn);
// Display status if there is at least one row.
if (mysql_num_rows($result) > 0)
{
// print table header
while ($row = mysql_fetch_assoc($result))
{
// print current status
if (mysql_num_rows($result)> 1)
{
echo "<h5>" . "This is your current status..." . "</h5>";
echo "<tr>";
echo "<td align=center>" . $row['DATE'] . "</td>";
echo "<td align=center>" . $row['LOCATION'] . "</td>";
echo "</tr>";
}
echo "</table></font>";
}
// print table footer
}
else
{
echo 'This number cannot be found in our records. Please contact us.';
}
mysql_close($conn);
}
else
{
echo 'You did not enter 6 characters. Please go <a href="javascript:history.back(-1);">back</a>.';
}
?>[/INDENT]

[bradgrafelman]

When posting PHP code, please use the board's [PHP][/PHP] bbcode tags - they make the code much, much easier to read (as well as aiding the diagnosis of syntax errors). You should edit your post now and add these bbcode tags to help others in reading/analyzing your code.

[xynah]

Ok. Thanks for the reminder.