Newbie's problems with MySQL and php_问答_开发者

I'm a real newbie with php and MySQL. Now I'm working on the following code which should search the database for eg. all the Lennons living in Liverpool.

1) How should I modify "get.php" to get the text "no results" to appear if there are no search results.

2) How should I modify "index.php" to get the option values (city and lastname) straight from the database instead of having to type them one by one?

3) Am I using mysql_real_escape_string the right way?

4) Any other mistakes in the code?

index.php:

<form action="get.php" method="post">
    <p>
        <select name="city">
            <option value="Birmingham">Birmingham</option>
            <option value="Liverpool">Liverpool</option>
            <option value="London">London</option>
        </select>
    </p>
    <p>
        <select name="lastname">
            <option value="Lennon">Lennon</option>
            <option value="McCartney">McCartney</option>
  开发者_Python百科          <option value="Osbourne">Osbourne</option>
        </select>
    </p>
    <p>
        <input value="Search" type="submit">
    </p>
</form>

get.php:

<?php
$city = $_POST['city'];
$lastname = $_POST['lastname'];

$conn = mysql_connect('localhost', 'user', 'password');
mysql_select_db("database", $conn) or die("connection failed");
$query = "SELECT * FROM users WHERE city = '$city' AND lastname = '$lastname'";
$result = mysql_query($query, $conn);

$city = mysql_real_escape_string($_POST['city']);
$lastname = mysql_real_escape_string($_POST['lastname']);

echo $rowcount;
while ($row = mysql_fetch_row($result)) 
{
    if ($rowcount == '0') 
        echo 'no results';
    else
    {
        echo '<b>City: </b>'.htmlspecialchars($row[0]).'<br />';
        echo '<b>Last name: </b>'.htmlspecialchars($row[1]).'<br />';
        echo '<b>Information: </b>'.htmlspecialchars($row[2]);
    }
}

mysql_close($conn);
?>

You have to use mysql_real_escape string before you send a query to the database.

1) the $rowcount variable is not defined. Do something like this instead:

$zerorows=true;
while ($row = mysql_fetch_row($result)) 
{
  $zerorows=false;
  echo '<b>City: </b>'.htmlspecialchars($row[0]).'<br />';
  echo '<b>Last name: </b>'.htmlspecialchars($row[1]).'<br />';
  echo '<b>Information: </b>'.htmlspecialchars($row[2]);
}
if($zerorows) echo "no results";

2) Select them from the db and write them in the HTML

<select><?php
$q=mysql_query(...);
while($row=mysql_fetch_row($q))
echo "<option>".$row[0]."</option>";
?></select>

3) You have to use the function before the query:

$query = "SELECT * FROM users WHERE city = '".mysql_real_escape_string($_POST['city'])."' AND lastname = '".mysql_real_escape_string($_POST['lastname'])."'";

4) Try the code. It's hard to find errors without testing.

You've got a few errors in get.php. Here's a modified correct version.

<?php
$city = mysql_real_escape_string($_POST['city']);
$lastname = mysql_real_escape_string($_POST['lastname']);

$conn = mysql_connect('localhost', 'user', 'password') or die("Connection failed");
mysql_select_db("database", $conn) or die("Switch database failed");
$query = "SELECT * FROM users WHERE city = '$city' AND lastname = '$lastname'";
$result = mysql_query($query, $conn) or die("Query failed");
$rowcount = mysql_num_rows($result);

if ($rowcount == 0) 
{
    echo 'no results';
}
else
    while (($row = mysql_fetch_row($result)) !== false)
    {
        echo '<b>City: </b>'.htmlspecialchars($row[0]).'<br />';
        echo '<b>Last name: </b>'.htmlspecialchars($row[1]).'<br />';
        echo '<b>Information: </b>'.htmlspecialchars($row[2]);
    }
}

mysql_close($conn);
?>

$rowcount was undefined - I've made this pull the number of rows from your result-set
You weren't escaping your POST variables until AFTER you'd done the query - you must do them before
You were checking the $rowcount inside the while query - if there weren't any rows returned, the while loop wouldn't run so this check wouldn't occur
You weren't checking the result of the mysql_fetch_row call to correctly stop execution if no more records are retrieved
You weren't checking that the connection and query was actually successful
You were comparing an integer ($rowcount) with a string ('0') - this would have worked due to the way PHP data-types are handled, but it reads better if you get your data-types right

Another suggestion is to use mysql_fetch_assoc in place of mysql_fetch_row - it will allow you to reference your result fields by name - e.g. $row['city'] so if you re-order the columns in your table, it won't break your application.

Since your user submitted the City and Lastname, it's a bit of a waste to echo that out on each loop iteration. So the better way to go would be to have the sql query return "no results" if there are no results, and to just print the city/lastname above the loop:

<?php
$city = mysql_real_escape_string($_POST['city']);
$lastname = mysql_real_escape_string($_POST['lastname']);

$conn = mysql_connect('localhost', 'user', 'password');
mysql_select_db("database", $conn) or die("connection failed");
$query = "SELECT COALESCE(information,"No Results") AS information FROM users 
          WHERE city = '$city' AND lastname = '$lastname'";
$result = mysql_query($query, $conn);

while ($row = mysql_fetch_row($result)) 
{
echo '<b>Information: </b>'.htmlspecialchars($row[0]);
}
mysql_close($conn);
?>