current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
    
use mysql_fetch_assoc(); or change to mysql_fetch_array($result_form, MYSQL_ASSOC); –  kjy112 Feb 25 '11 at 16:53

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.