Query code doesn't give result

[xynah]

Can anybody help me with this code? I've been working on this for a week. Please help me...

It's for a query form where anyone can put a six-digit number (name in the form is invNum) which is actually the ('ID') in the database's table ('TrackBox'). The result should show the date ('DATE') and the location ('LOCATION').

The validation of the invNum is working. But I'm lost 😕 ... What's wrong with this code?

[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]

[sheephat]

Not changed

<?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>.'; 
} 
?>

[sheephat]

do you get an error, or just no results?

[shaneH]

$result = mysql_query($query, $conn);

I think this might be your error the $conn will reconnect to the database.

I think if you just change it to

$result = mysql_query($query);

it will work

[xynah]

I queried an existing data --- I don't get any error. I don't get any result either. 🙁 What shows is: "This number cannot be found in our records. Please contact us. "

I tried shaneH's suggestion... but it's still the same. Any other suggestion?

[NogDog]

shaneH wrote:

$result = mysql_query($query, $conn);

I think this might be your error the $conn will reconnect to the database.

I think if you just change it to

$result = mysql_query($query);

it will work

The 2nd arg to mysql_query() should not be the problem. (It's not necessary if you currently have only one active database connection, but it doesn't hurt anything, and you may need to use it if you have multiple database connections.)

[NogDog]

Never assume that your query worked. Try something like the following to see if there are any SQL errors:

$query = "SELECT `DATE`, `LOCATION` FROM TrackBox WHERE ID = '$id'";
$result = mysql_query($query, $conn) or die("Query failed: ($query): " . mysql_error());

[xynah]

[INDENT]$query = "SELECT DATE, LOCATION FROM TrackBox WHERE ID = '$id'";
$result = mysql_query($query, $conn) or die("Query failed: ($query): " . mysql_error());[/INDENT]

I tried this, too... but no luck at all... :bemused:

I think this part is the problem because its text color is black:

[INDENT]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]

Geez, I've been haunted by codes in my sleep the whole week... :xbones:

[NogDog]

I don't think it should matter with MySQL, but you might want to try removing the quotes around '$id' in the query string, since it's an integer value and not a string. Assuming that doesn't help, all I can suggest is to output the query string if no rows are found so that you can double-check it:

   else 
   { 
      echo 'This number cannot be found in our records. Please contact us.';
      // DEBUG
      echo "<pre>The query was:\n$query</pre>";
      // END DEBUG
   } 

[xynah]

Ok... I did it too:

// 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) or die("Query failed: ($query): " . mysql_error());
// 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.'; 
      // DEBUG 
      echo "<pre>The query was:\n$query</pre>"; 
      // END DEBUG 
   } 
mysql_close($conn); 
} 
else 
{ 
echo 'You did not enter 6 characters. Please go <a href="javascript:history.back(-1);">back</a>.'; 
} 

The result was:
[RIGHT][LEFT][INDENT]This number cannot be found in our records. Please contact us.
The query was:
SELECT DATE, LOCATION FROM TrackBox WHERE ID = '1'[/INDENT][/LEFT][/RIGHT]

[NogDog]

Is '1' an existing ID value in the TrackBox table? If so, iss TrackBox an INTEGER type of field, or a CHAR or VARCHAR type? If a character type, do the values have leading zeros in the data? (You are casting the input value to (int), so you'll lose any leading zeros that were input in the form.)

Did you try it yet without the quotes around the value in the query?

$query = "SELECT `DATE`, `LOCATION` FROM TrackBox WHERE ID = $id";

[xynah]

My code problem isn't solved yet but I am really thankful to see a lot of people trying to help me out... Thank you, guys... I really appreciate it!

Now, back to the code... The ID that is existing in TrackBox table is "000001" and I wanted to see the result showing the DATE and the LOCATION.

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>"; 
} 

Originally, the code I was using gave me results... If I queried the ID "000001", it shows:

<font face='verdana' size='-1'><table border='1' width='40%'>
<tr>
<th
<b>Date</b></th>
<th
<b>Location</b></th>
</tr><h5>This is your current status...</h5><tr><td align=center>2007-01-30</td><td align=center>Boston</td></tr></table></font> 

The original code looked like this...

<?php
// Database connection details. 
$username = "username";
$password = "password";
$database = "database";
$id = $_REQUEST['invNum'];

#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 `DATE`, `LOCATION` FROM TrackBox WHERE ID = '$id'"); 

#EXECUTE QUERY
$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>";
}
?> 

But I wanted the data to undergo validation, so I tried to ask help here and thankfully, Laserlight came to the rescue. The validation worked, but the results were not showing...

This is how my code looks like now:

<?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) or die("Query failed: ($query): " . mysql_error());
// 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.'; 
      // DEBUG 
      echo "<pre>The query was:\n$query</pre>"; 
      // END DEBUG 
   } 
mysql_close($conn); 
} 
else 
{ 
echo 'You did not enter 6 characters. Please go <a href="javascript:history.back(-1);">back</a>.'; 
} 
?> 

Any changes I should do?

[NogDog]

Look at these two lines:

// Validate invNum.
if (isset($_REQUEST['invNum']) && strlen($_REQUEST['invNum']) == 6)
{
// Cast invNum to int to prevent SQL injection.
$id = (int)$_REQUEST['invNum'];

Part of the if condition is checking to see if $_REQUEST['invNum'] is 6 characters long (the strlen() comparison). But then you convert $id to an integer by casting it via "(int)". This means that if the form input was "000001", $id will now have a value of integer 1 (no leading zeros). If you are comparing that number to the string "000001" in the database, they will not match, unless you cast the database value to an integer, too.

Assuming all the above is a correct analysis, you could change the line where you assign the value to $id to:

// Cast invNum to int to prevent SQL injection.
$id = sprintf("%06d", (int)$_REQUEST['invNum']);

[xynah]

Hi again... I've been working around the code and it now looks like this:

<?php
// Database connection details. 
$username = "username";
$password = "password";
$database = "database";
$id = $_REQUEST['invNum'];

#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 `DATE`, `LOCATION` FROM TrackBox WHERE ID = '$id'"); 

#EXECUTE QUERY
$rs = mysql_query($sql,$dbcnx) or die(mysql_error());
// Validation
// Validation
if (strlen($invNum) !=6)
{
die("<p align='center'><font face='Verdana' size='2' color='#000000'>Please enter a valid 

number</font></p>");
}
 echo("<p align='center'><font face='Verdana' size='2' color='#000000'>This is your current 

status...<br><table border='1' 

width='40%'><tr><th\n><b>Date</b></th><th\n><b>Location</b></th></tr><tr>$row['DATE'] 

</tr><tr>$row['LOCATION']</tr></table></font></p>");
?>

At least I don't see text in black anymore... but this is what I get all the time...

[INDENT]Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING[/INDENT]

The error is in this line:

 echo("<p align='center'><font face='Verdana' size='2' color='#000000'>This is your current status...<br><table border='1' width='40%'><tr><th\n><b>Date</b></th><th\n><b>Location</b></th></tr><tr>$row['DATE'] </tr><tr>$row['LOCATION']</tr></table></font></p>");

[xynah]

Hi NogDog!

I tried doing your suggestion... I don't get any error but it still doesn't publish results. I see the page now and it's not blank.

Hmmm...

[NogDog]

The PHP parser does not like associative array elements with quoted indexes within a double-quoted string. You can help it out by using complex variable notation (curly braces):

echo("<p align='center'><font face='Verdana' size='2' color='#000000'>This is your current status...<br>
<table border='1' width='40%'><tr><th\n><b>Date</b></th><th\n><b>Location</b></th></tr>
<tr>{$row['DATE']} </tr><tr>{$row['LOCATION']}</tr></table></font></p>");

[xynah]

I tried it, too. My code looks like this now...

#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 `DATE`, `LOCATION` FROM TrackBox WHERE ID = '$id'"); 

#Validation
if (strlen($invNum) !=6)
{
die("<p align='left'><font face='Verdana' size='2' color='#000000'>Please enter a valid 
number</font></p>");

#EXECUTE QUERY
$rs = mysql_query($sql,$dbcnx) or die(mysql_error());

echo("<p align='center'><font face='Verdana' size='2' color='#000000'>This is your current 
status...<br> 
<table border='1' width='40%'><tr><th\n><b>Date</b></th><th\n><b>Location</b></th></tr> 
<tr>{$row['DATE']} </tr><tr>{$row['LOCATION']}</tr></table></font></p>"); 
}
?>

It only tells me, even when the ID number actually exists: Please enter a valid number

The part of the code that seems to be wrong (coz the text aren't gray):

</p>");

#EXECUTE QUERY
$rs = mysql_query($sql,$dbcnx) or die(mysql_error());

echo("<p align='center'><font face='Verdana' size='2' color='#000000'>This is your current 
status...<br> 
<table border='1' width='40%'><tr><th\n><b>Date</b></th><th\n><b>Location</b></th></tr> 
<tr>{$row['DATE']} </tr><tr>{$row['LOCATION']}</tr></table></font></p>"); 
}
?>

Adviiiiiiil!!!!

[tams]

Ok so I tried the following and I get two error messages

Warning: mysql_query() expects parameter 1 to be mysql, string given
Warning: mysql_error() expects exactly 1 parameter, 0 given

try changing this

$rs = mysql_query($sql,$dbcnx) or die(mysql_error());

to

$rs = mysql_query($dbcnx,$sql) or die ("Couldn't execute query.".mysqli_error($dbcnx));

[xynah]

It doesn't work either... For the meantime, I'm just settling for this... At least this code gives me results... It doesn't work the way it should be, but it's better than getting a blank page... or getting an "invalid number" message when the number is actually valid.

The code goes like this:

#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 `DATE`, `LOCATION` 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($invNum) !=6)
{
die("<p align='left'><font face='Verdana' size='2' color='#000000'>Please enter a valid number</font></p>");
}
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);
}
?> 

If anyone has any ideas to make my validation ("This number cannot be found in our records..." and "Please enter a valid number...") work, please share. I appreciate any sort of help. :glare:

[quandrie]

Strange tag that: "<th\n>". Could the backslash be creating a problem?