Half PHP and SQL question ??

[Anon]

This is my code i got it from a book.

while ($row = mysql_fetch_array($result)) {
$username = $row["username"];
$fname = $row["fname"];
$lname = $row["lname"];
$city = $row["city"];
$state = $row["state"];
$zip = $row["zip"];

What it will do is display the info i have stored in my sql database for basic user info. what i need it to do is search by zip and display all the info listed name, city, state, zip. What do i do next? Currently this code displays just the most recent single entry to the sql database.

[fitzbean]

SELECT name, city, state, zip FROM tablename ORDER BY zip DESC

This will return the list starting with the greatest zip code numerically.

[[deleted]]

What you do is not in PHP, but in SQL.
Make a new query that does what you want.
I suggest you start by reading up on SQL syntax to get an idea of what it can do.

You should use SQL to find the results, and use PHP only to print the results that the SQL found.

[Anon]

Thankyou so much, that code you gave works perfectly, but it only displays ONE entry, the most recent added. Could my sql data base be set up wrong ??

This is my code i'm working with..

$sql = "SELECT username, fname, lname, city, state, zip, resume FROM $table_name ORDER BY zip DESC

";

$result = @($sql,$connection) or die("Couldn't execute query.");

while ($row = mysql_fetch_array($result)) {
$username = $row["username"];
$fname = $row["fname"];
$lname = $row["lname"];
$city = $row["city"];
$state = $row["state"];
$zip = $row["zip"];
$resume = $row["resume"];	
		}


}

?>

<// bunch of code.. //>

<?php
echo "<table border=2>";
echo "<tr>
<td><b>UserName</b></td>
<td><b>First Name</b></td>
<td><b>Last Name</b></td>
<td><b>City</b></td>
<td><b>State</b></td>
<td><b>Zip</b></td>
<td><b>Resume</b></td>
</tr>";

	echo "<tr>
		<td>$username</td>
		<td>$fname</td>
		<td>$lname</td>
		<td>$city</td>
		<td>$state</td>
		<td>$zip</td>
		<td>$resume</td>
	     </tr>";

	echo "</table>";

?>

[fitzbean]

Try this:

$sql = "SELECT username, fname, lname, city, state, zip, resume FROM $table_name ORDER BY zip DESC ";

$result = mysql_query($sql, $connection);
$rows = mysql_num_rows($result);

$i = 0;
while ($i < $rows)
{
$username = mysql_result($result, $i, username);
$fname = mysql_result($result, $i, fname);
$lname = mysql_result($result, $i, lname);
$city = mysql_result($result, $i, city);
$state = mysql_result($result, $i, state);

     <// bunch of code.. //> 

echo "<table border=2>";
echo "<tr>
<td>UserName</td>
<td>First Name</td>
<td>Last Name</td>
<td>City</td>
<td>State</td>
<td>Zip</td>
<td>Resume</td>
</tr>";

echo "<tr>
<td>$username</td>
<td>$fname</td>
<td>$lname</td>
<td>$city</td>
<td>$state</td>
<td>$zip</td>
<td>$resume</td>
</tr>";

echo "</table>";

    $i++;
    }

[Anon]

Thankyou so much for responding to my question. So quickly. After using what you last suggested it still gives me some error.. on line 21. That is the line.. that says "<b>$rows = mysql_num_rows($result); </b>"
To me that means that it's not finding any results ? But how can that be my db is full of test entries. I've created several ones ordered by Zip code starting at 11111 to 99999 just for testing this.. I've also shorted it down to just showing First and Last name to be searchable by zip code. I really would appricate any help you can offer. Thankyou

This is what i'm working with now...

$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");

$sql = "SELECT fname, lname, zip, FROM $table_name ORDER BY zip DESC ";

$result = mysql_query($sql, $connection);
$rows = mysql_num_rows($result) or die("Couldn't find records");

$i = 0;
while ($i < $rows)
{
$fname = mysql_result($result, $i, fname);
$lname = mysql_result($result, $i, lname);
$zip = mysql_result($result, $i, zip);
}

}

echo "<table border=2>";
echo "<tr>
<td>First Name</td>
<td>Last Name</td>
<td>Zip</td>
</tr>";

echo "<tr>
<td>$fname</td>
<td>$lname</td>
<td>$zip</td>
</tr>";

echo "</table>";

    $i++;

?>

[fitzbean]

You have a simple error, see how you have 2 "}" after the while loop? You only need one, AND you must put the close bracket AFTER
the $i++; or you will catch your script in an infinite loop, and if its not after the readout, it will only print the last record pulled from the DB. So do this:

$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");

$sql = "SELECT fname, lname, zip, FROM $table_name ORDER BY zip DESC ";

$result = mysql_query($sql, $connection);
$rows = mysql_num_rows($result) or die("Couldn't find records");

$i = 0;
while ($i < $rows)
{
$fname = mysql_result($result, $i, fname);
$lname = mysql_result($result, $i, lname);
$zip = mysql_result($result, $i, zip);

echo "<table border=2>";
echo "<tr>
<td>First Name</td>
<td>Last Name</td>
<td>Zip</td>
</tr>";

echo "<tr>
<td>$fname</td>
<td>$lname</td>
<td>$zip</td>
</tr>";

echo "</table>";

$i++;
}

That should do the trick.. email me at [e-mail] if you have any further troubles with this; this thread is getting lost in the mix 😉

Fitz

?>