Connecting to MySQL Database using select statment and the while loop

[jknapp]

Hi:

 

I built a quick script that allows the user to enter a zip code into a text box so we can tell them where are locations are. The next page I connected to a MySQL database using the select statement.

 

I will get the correct amount of how many zip codes there are in the databse using num rows but when the locations print to the page it continues to duplicate locations and show several locations multiple times. I am not sure why this is. My code is below. Any help would be greatly appreciated.

 

Thanks

 

 

 

// Connect to server and select database.

$connection = @mysql_connect("$host", "$username", "$password")or die("cannot connect");

$db = @mysql_select_db($db_name, $connection)or die(mysql_error());

 

$sql = "select id, name, address, citystate, zipcode from tablename WHERE zipcode='$zipcode'";

 

$result = @mysql_query($sql, $connection) or die(mysql_error());

 

 

 

// get the number of rows in the result set

$num = mysql_num_rows($result);

 

// print a message

if ($num !=0) {

$msg = "<p><center><font size='4' color='000000' face='Arial, Helvetica, sans-serif'><b>There are <font color='FF0000'>$num</font> search results for <font color='FF0000'>$zipcode</font> zip code.</b></font><br>";

echo "<p><center>$msg<table width='610' bgcolor='ffffff' border='1' bordercolor='000000'>

<tr bgcolor='003366'><td>Name</td><td>Address</td><td> City State</td><td>Zip Code</td></tr></table></center></p>";

while ($row = mysql_fetch_array($result)) {

 

$id1 = $row['id'];

$name1 = $row['name'];

$address1 = $row['address'];

$citystate1 = $row['citystate'];

$zipcode1 = $row['zipcode'];

 

$display_block .= "<table width='610' bgcolor='ffffff' border='1' bordercolor='000000'><tr bgcolor='ffffff'><td>$name1</td><td>$address1</td><td>$citystate1</td><td>$zipcode1</td></tr></table>";

echo "$display_block";

}

mysql_close ($connection);

} else {

$msg = "<p><center><font size='4' color='000000' face='Arial, Helvetica, sans-serif'><b>There are <font color='FF0000'>$num</font> search results for <font color='FF0000'>$zipcode</font> zip code.</b></font></center></p>";

echo "<p><center>$msg</center></p>";

} 

?>

[lemmin]

I don't see any obvious errors in your code. The way I see it, it should print out exactly $num different locations. If it is printing the same location more than one, my guess is that it is in your database like that. Take a look and make sure the database is set up like you would expect.