Reducing the amount of code needed

[richrock]

Argh!  I need to figure out a way to stop myself copying the same code all the time...

 

I'm learning fast, but not sure how to do this properly.  I have 6 mysql queries :

 

$query_loc = "SELECT DISTINCT location FROM ".$table."";
$result_loc = mysql_query($query_loc) or die(mysql_error($err1));
$row_loc = mysql_fetch_assoc($result_loc);

 

But if I do :

 

$query_loc = "SELECT DISTINCT location, sector, industry, type, position, placed FROM ".$table."";
$result_loc = mysql_query($query_loc) or die(mysql_error($err1));
$row_loc = mysql_fetch_assoc($result_loc);

 

Then the form doesn't work.  So I'm trying to figure out if I can set up something that does the first example, but repeats for each of the fields in the second example.

 

I'm slowly getting the hang of this, and know this one in principle - just my newbie status can't do it  :'(

 

Thanks

 

Rich

[GingerRobot]

What do you mean by "the form doesn't work"? You should only be using one query if you're just trying to select different fields. We need a bit more information and, most likely, a bit more code.

[richrock]

Sorry!  When I meant the form doesn't work, it is not returning distinct results for each drop down :

 

	$query_loc = "SELECT DISTINCT location, sector, industry, type, position, placed FROM ".$table."";
$result_loc = mysql_query($query_loc) or die(mysql_error($err1));
$row_loc = mysql_fetch_assoc($result_loc);

 

So in the dropdown for industry, my list goes :

Select Industry

Logistics

Industrial

Warehousing

Logistics

etc..

 

The list is just a list.  Here's a code for the form element itself :

//Select Job By Industry
echo "<select name='sel_jobindustry' style='width: 125px;'>\n";
	echo "<option>Select Industry</option>";
		do {  

		$s_industry = @$_POST['sel_jobindustry'] == $row_ind['industry'] ? "selected" : "";

		echo "<option value='".$row_ind['industry']."' $s_industry>".$row_ind['industry']."</option>\n";

		} while ($row_ind = mysql_fetch_assoc($result_ind));
			$rows = mysql_num_rows($result_ind);
			if($rows > 0) {
			mysql_data_seek($result_ind, 0);
			$row_ind = mysql_fetch_assoc($result_ind);
		}

echo "</select>\n";

echo "<br />";

 

It's just really strange that if I write a single table query, it finds distinct values and displays them in the list, but if I combine them into all the tables, it doesn't...  :-\

 

Edit : Just thought, If the table is like this :

IndustrySectorType

WarehousingTransportManager

RetailLogisticsConsultant

WarehousingManufacturingCEO

 

Then would everything be displayed?  For example : Row 1 - Warehousing > Transport > Manager is different from Warehousing > Manufacturing > CEO?

 

Hope this helps

 

Rich

 

[unkwntech]

This should work.

<?php
//Select Job By Industry
echo "<select name='sel_jobindustry' style='width: 125px;'>\n";
	echo "<option>Select Industry</option>";
		$row_ind = mysql_fetch_assoc($result_ind);
		while($row_ind = mysql_fetch_assoc($result_ind))
		 {
			echo "<option value='".$row_ind['industry']."' $s_industry>".$row_ind['industry']."</option>\n";
		 }

echo "</select>\n";

echo "<br />";

[samshel]

you mean you want to select distinct columns from different tables in single query? OR is it distinct values for different columns from one table?

[richrock]

This should work.

<?php
//Select Job By Industry
echo "<select name='sel_jobindustry' style='width: 125px;'>\n";
	echo "<option>Select Industry</option>";
		$row_ind = mysql_fetch_assoc($result_ind);
		while($row_ind = mysql_fetch_assoc($result_ind))
		 {
			echo "<option value='".$row_ind['industry']."' $s_industry>".$row_ind['industry']."</option>\n";
		 }

echo "</select>\n";

echo "<br />";

 

Hi,

 

Thanks a lot, it does work, but I've lost the retention of the selected value.  So now instead of having :

Transport, Logistics, Commerce, Logistics, Production

I now have :

Transport, Logistics, Commerce, Production.

 

But when I now select a value, say, Transport, submit the form, I lose the selected value in the dropdown... :S

 

Rich