Query code doesn't give result

xynah · Jul 25, 2007

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 · Jul 25, 2007

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 · Jul 25, 2007

do you get an error, or just no results?

shaneH · Jul 25, 2007

$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 · Jul 25, 2007

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 · Jul 26, 2007

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 · Jul 26, 2007

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 · Jul 26, 2007

[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 · Jul 26, 2007

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 · Jul 26, 2007

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 · Jul 26, 2007

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 · Jul 26, 2007

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 · Jul 26, 2007

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 · Jul 26, 2007

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 · Jul 26, 2007

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 · Jul 26, 2007

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 · Jul 26, 2007

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 · Jul 26, 2007

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 · Jul 27, 2007

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 · Jul 28, 2007

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

Query code doesn't give result

Xxynah

Ssheephat

Ssheephat

SshaneH

Xxynah

NogDog

NogDog

Xxynah

NogDog

Xxynah

NogDog

Xxynah

NogDog

Xxynah

Xxynah

NogDog

Xxynah

Ttams

Xxynah

Qquandrie