[SOLVED] one dropdown populated from multiple rows ..is it possible ?

[chrisduff]

Hi There ...

 

Here I am, Sunday nearly midnight and I am stuck !!

 

 

I am creating a form where users can choose three differents areas of Expertise.

 

So in my database, I have three rows "expertise", "expertise2" and "expertise3". The user has three different drop down menu ( they hold the same data ).. Everything works fine, the users select one expertise per drop down, submit and the data is written in the three differents rows mentionned above.

 

The point where I am stuck is that I am trying to a search engine. I would like to have one drop down menu listing listing the distinct content of the values which have been written in the three rows ( if that make sens) .. to summarize the user can enter three different area of expertise and i want to be able to search then from a drop down menu which centralize this data.

 

Here is my recordset

 

mysql_select_db($database_my_connection, $my_connection);$query_rs_searchExpertise = "SELECT DISTINCT user_expertise FROM users_info ORDER BY user_expertise ASC";$rs_searchExpertise = mysql_query($query_rs_searchExpertise, $my_connection) or die(mysql_error());$row_rs_searchExpertise = mysql_fetch_assoc($rs_searchExpertise);$totalRows_rs_searchExpertise = mysql_num_rows($rs_searchExpertise);

I tried to do :

SELECT DISTINCT user_expertise AND user_expertise1 AND user_expertise  FROM users_info 

but it doesn't seem to work.

 

Here is also the drop down menu:

 

<select name="select_expertise" id="select_expertise">                      <option value="" <?php if (!(strcmp("", "Expertise"))) {echo "selected=\"selected\"";} ?>></option><?phpdo {  ?><option value="<?php echo $row_rs_searchExpertise['user_expertise']?>"<?php if (!(strcmp($row_rs_searchExpertise['user_expertise'], "Expertise"))) {echo "selected=\"selected\"";} ?>><?php echo $row_rs_searchExpertise['user_expertise']?></option>                      <?php} while ($row_rs_searchExpertise = mysql_fetch_assoc($rs_searchExpertise));  $rows = mysql_num_rows($rs_searchExpertise);  if($rows > 0) {      mysql_data_seek($rs_searchExpertise, 0);	  $row_rs_searchExpertise = mysql_fetch_assoc($rs_searchExpertise);  }?>                  </select>

 

Is what I want to do possible ?

 

Cheers

 

Christophe

[teng84]

this is my function

function dropmenu($arrvalue,$selected){

foreach($arrvalue as $key=>$value){

    $selected_value = ($selected == $key)?'selected':'';

$stroption .= "<option value='".$key."' $selected_value>".$value."</option>\n";

  }

  return $stroption;

}

[Barand]

If you can't be bothered to format your code then I can't be bothered to struggle to read it. Good luck.

[teng84]

lol thats why I only give the functions i made without reading and knowing what this tread starter really wants but it works though

[chrisduff]

Not too sure what happend but I thought the code was formatted. sorry about that.

 

The recordset:

 

mysql_select_db($database_my_connection, $my_connection);
$query_rs_searchExpertise = "SELECT DISTINCT user_expertise FROM users_info ORDER BY user_expertise ASC";
$rs_searchExpertise = mysql_query($query_rs_searchExpertise, $my_connection) or die(mysql_error());
$row_rs_searchExpertise = mysql_fetch_assoc($rs_searchExpertise);
$totalRows_rs_searchExpertise = mysql_num_rows($rs_searchExpertise);

 

What I tried to do:

 

SELECT DISTINCT user_expertise AND user_expertise1 AND user_expertise FROM users_info

 

My Drop down menu:

 

<select name="select_expertise" id="select_expertise">
<option value="" <?php if (!(strcmp("", "Expertise"))) {echo "selected=\"selected\"";} ?>></option>
<?php
do {
?><option value="<?php echo $row_rs_searchExpertise['user_expertise']?>"
<?php if (!(strcmp($row_rs_searchExpertise['user_expertise'], "Expertise"))) {echo "selected=\"selected\"";} ?>>
<?php echo $row_rs_searchExpertise['user_expertise']?></option>
<?php
} while ($row_rs_searchExpertise = mysql_fetch_assoc($rs_searchExpertise));
$rows = mysql_num_rows($rs_searchExpertise);
if($rows > 0) {
mysql_data_seek($rs_searchExpertise, 0);
$row_rs_searchExpertise = mysql_fetch_assoc($rs_searchExpertise);
}
?>
</select>

 

Thank you

 

Christophe

[Barand]

So in my database, I have three rows "expertise", "expertise2" and "expertise3". The user has three different drop down menu ( they hold the same data ).. Everything works fine, the users select one expertise per drop down, submit and the data is written in the three differents rows mentionned above.

 

If the three dropdowns are the same then you just need the same dropdown for the search.

 

I have three rows "expertise", "expertise2" and "expertise3".

 

Do you mean rows, or columns? They should be in separate rows but your middle (attempted) piece of code, and the fact they have names, suggests columns

[chrisduff]

Hi Barand

 

expertise, expertise2 and expertise3 are different columns from one table.

 

the way my table is organised is like that:

 

userID

userName

userSurname

expertise

expertise2

expertise3

 

If the three dropdowns are the same then you just need the same dropdown for the search.

 

You are right.. the thing is that it's quite easy for this one drop down to check against one column (expertise) but how can I make it check against three columns (expertise or expertise2 or expertise3 ) ?

[Barand]

If $expertise is the user-selected value you want to search for,

$sql = "SELECT userID, userName, userSurname
          FROM user_info
          WHERE (expertise = '$expertise') 
                OR (expertise2 = '$expertise') 
                OR(expertise3 = '$expertise')";

 

A better design for the table is

[pre]

user_info        userexpertise          expertise

---------        -----------            ----------

userID    ---+    id            +----  expID

userName    +--< userID        |      expertise

userSurname      expID    >----+

[/pre]

 

All the expertise values are now in a single column. Users can can have as many or as few as you want without the preset arbitrary limit of three. The SQL becomes simpler as you don't have to triplicate the WHERE conditions.

 

The expertise table is the source from which to populate the dropdowns

[chrisduff]

Thanks for your advise I'll give it a try.

 

 

[Barand]

Also, instead of 3 dropdowns, which again sets an arbitrary limit, consider a single, multiple-select dropdown or checkboxes.

[chrisduff]

It now works.. Barand I followed your advice and my dropdown is populated from the independand Expertise table as opposed to trying to have it populated by distinct entries in the from the 3 expertise columns from the user_info table.

 

so the recordset looks like that on the search page:

 

mysql_select_db($database_my_connection, $my_connection);
$query_rs_searchExpertise = "SELECT * FROM tbl_expertise ORDER BY col_expertise ASC";
$rs_searchExpertise = mysql_query($query_rs_searchExpertise, $my_connection) or die(mysql_error());
$row_rs_searchExpertise = mysql_fetch_assoc($rs_searchExpertise);
$totalRows_rs_searchExpertise = mysql_num_rows($rs_searchExpertise);

 

The Drop down looks like that:

 

<select name="select_expertise" id="select_expertise">
  <option value="" <?php if (!(strcmp("", "Expertise"))) {echo "selected=\"selected\"";} ?>></option>
  <?php
do {  
?>
  <option value="<?php echo $row_rs_searchExpertise['col_expertise']?>"
<?php if (!(strcmp($row_rs_searchExpertise['col_expertise'], "Expertise"))) {echo "selected=\"selected\"";} ?>>
<?php echo $row_rs_searchExpertise['col_expertise']?></option>
  <?php
} while ($row_rs_searchExpertise = mysql_fetch_assoc($rs_searchExpertise));
  $rows = mysql_num_rows($rs_searchExpertise);
  if($rows > 0) {
      mysql_data_seek($rs_searchExpertise, 0);
  $row_rs_searchExpertise = mysql_fetch_assoc($rs_searchExpertise);
  }
?>
</select>

 

The result page:

 

<?php
mysql_select_db($database_my_connection, $my_connection);
$sql = 'SELECT * FROM users_info WHERE ';

if (!empty($_GET['select_expertise'])) {
$sql .= 'user_expertise LIKE \'%'. $_GET['select_expertise'] .'%\'OR ';
}

if (!empty($_GET['select_expertise'])) {
$sql .= 'user_expertise2 LIKE \'%'. $_GET['select_expertise'] .'%\' OR ';
}

if (!empty($_GET['select_expertise'])) {
$sql .= 'user_expertise3 LIKE \'%'. $_GET['select_expertise'] .'%\' AND ';
}

if (substr($sql, strlen($sql) - strlen('WHERE ')) == 'WHERE ') {
$sql = substr($sql, 0, strlen($sql) - strlen('WHERE '));
}
if (substr($sql, strlen($sql) - strlen('AND ')) == 'AND ') {
$sql = substr($sql, 0, strlen($sql) - strlen('AND '));
}

$sql .= ' ORDER BY user_name ASC';

 

Voila .. thanks again

 

Chris