up vote 1 down vote favorite

I'm trying to get a drop down list to display multiple columns from a table, and for the selected row's primary id to be stored in the variable name.

I get a list of rows if I drop the CONCAT function and SELECT a single column, but I can't figure out how to select more than one. What am I doing wrong?

<li>
    <?php 
    $sql="SELECT CONCAT(county, ' ',municipality, ' ',park), id FROM mtmg.locality";
    $result=mysql_query($sql, $connection);

    echo '<label for="county_municipality_park">County, Municipality, Park</label>';
    echo '<select  id="county_municipality_park" name="county_municipality_park">';

    while ($row = mysql_fetch_assoc($result)) {echo '<option value="'.$row['county,municipality,park'].'">'.$row['county,municipality,park'].'</option>';}
    echo mysql_error();

    echo '</select>';
    ?>
</li>
share|improve this question
are they all string values? – Amirshk May 23 '11 at 21:03

2 Answers

up vote 1 down vote accepted

You need to give your CONCAT() function an alias, something like

SELECT CONCAT(county, ' ',municipality, ' ',park) as county_municipality_park, id FROM ...

and then reference it as such in the $row array, i.e. $row['county_municipality_park'].

share|improve this answer
@SabeenMalik @spanky Great explanation! One more thing, this only works if all values are non-null. Is there any way to show partial results? – user81997 May 23 '11 at 22:09
1  
I might select county, municipality, and park separately, and then reference them all together in PHP. Once it gets put into the $row array, PHP will treate NULL and blank values the same, so instead of echoing $row['county_municipality_park'] you could echo $row['county'].' '.$row['municipality'].' '.$row['park']. Is that what you're looking for? – spanky May 23 '11 at 22:17
Fantastic! This is exactly what I was looking for. One last thing, I haven't been able to test this yet, but based on the current query: $sql="SELECT county, municipality, park, id FROM mtmg.locality";, is everything but the last value after SELECT displayed, the final value (id) to be stored in $_POST['county_municipality_park']? I have to learn PHP/MySQL as I go along with my job, so this isn't apparent to me yet. – user81997 May 23 '11 at 22:32
1  
You can display any of the SELECTed fields. You've used mysql_fetch_assoc($result) to turn them into an array called $row, so any of them can be called from there, like $row['id'], $row['municipality'], etc. Things get stored in $_POST when you use them as values for input items in HTML and then post the data using a <form> with a post method. This is really too much to write about in a comment. I suggest you look into some tutorials! – spanky May 23 '11 at 22:35
1  
OK thanks! I've figured it all out with your help. I should be putting: echo '<option value="'.$row['id'].'">'.$row['county'].', '.$row['municipality'].', '.$row['park'].'</option>' to get what I want. I should have known that, but it wasn't apparent to me until now. I initially wrote the form in openoffice base, and I thought it was something in the SQL syntax that differentiated displayed and stored data. – user81997 May 23 '11 at 22:48
up vote 1 down vote

try this

$sql="SELECT CONCAT(county, ' ',municipality, ' ',park) as location , id FROM mtmg.locality";

and then use $row['location']

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.