Creating a form from mysql_fetch_array query

问题:

I have a field in a MySQL database called 'location' - it serves up North, South, East, West only.

I have used this code to get only the 4 results that are distinct:

$query_form = "SELECT DISTINCT location FROM hotel ORDER BY location ASC";
$result_form = mysqli_query($dbc, $query_form) or die('die query error');
$row_form = mysql_fetch_array($result_form, MYSQLI_NUM);

I wanted to use the four results from this query to populate a table such that:

<option value='1'>North</option>
<option value='2'>South</option>
<option value='3'>East</option>
<option value='4'>West</option>

I have used this:

foreach ($row_form['location'] as $k => $v) {
    echo '<option value="' . $k . '">' . $v . '</option>\n';
    }

but I fear that my approach will not work - with regret, I am a noob and unable to work out what is wrong!

Thanks

回答1:

mysql_fetch_array will only (quoting the manual, emphasis mine) :

Fetch a result row as an associative array, a numeric array, or both

So, you'll have to call this function several times -- actually, once per row (so, here, 4 times).

This is normally done with a loop, looping while you get results.
In your case, you'll probably want to use something that looks like this :

$lineCounter = 1;
while ($row = mysql_fetch_array($result_form, MYSQL_ASSOC)) {
    // Use your row, here.
    // For instance : $row['location']
    // And you can use the $lineCounter variable, to keep track of the current line number.
    $lineCounter++;
}

Notes :

• I used MYSQL_ASSOC, to get an associative array for each row : I think it's easier to work this way.
• In the body of the while loop, you can use the $row associative array, indexed by column name.

As a sidenote, when injecting data (such as strings) in some HTML code, you should escape the output properly, to avoid HTML injections.

See, for a solution, the htmlspecialchars function.

回答2:

MYSQLI_NUM actually returns the fields with numeric indices. As per your requirement, I believe, it wont really help in having <option value="x">..

[Check this link about what MYSQLI_NUM actually does: What does MYSQLI_NUM mean and do?

Now, in your code, you need to modify as follows:

$i=1;
while ($row_form=mysql_fetch_array($result_form, MYSQLI_NUM)) {
    echo '<option value="' . $i . '">' . $row_form['location'] . '</option>\n';
    $i++;
}

EDIT: As pointed out in another answer here, you really need to have a loop construct for retrieving each row out from the database. One single mysql_fetch_array(..) will give you only one row. You need to iterate in a loop, till the function returns a valid row.

Check how the function works: http://php.net/manual/en/function.mysql-fetch-array.php