How to generate a query that uses only the filled out fields on a form

[rogerzebra]

Hi fellas,
I'm trying to make a form that uses only the filled out fields in a single database query.For example i have up to 3 fields and if just 2 of them are filled out by the user, i want to use only these 2 in the query. How can i generate a query according to filled fields? I know my problem is related to faulty defined $ClassCode variables, the database has only one ClassCode row and I'm not sure how to use the $result variable. As usual I appreciate any help I can get.

///////Html//////////      
<input type="text" name="ClassCode" size="10" maxlength="4">
      <input type="text" name="ClassCode2" size="10" maxlength="4">
      <input type="text" name="ClassCode3" size="10" maxlength="4"><br><br>
///////End Html////////////////////

$ClassCode = $_POST['ClassCode'];
$ClassCode2 = $_POST['ClassCode2'];
$ClassCode3 = $_POST['ClassCode3'];


$query = "SELECT classcode_rate.ClassCode, classcode_rate.Classification, classcode_rate.Rate, carriernameid.CarrierName
FROM classcode_rate INNER JOIN carriernameid
ON classcode_rate.CarrierID=carriernameid.CarrierID
WHERE carriernameid.CarrierName='$CarrierName' AND classcode_rate.ClassCode='$ClassCode'";

if($_POST['ClassCode2']) {
    $query2 .= "AND classcode_rate.ClassCode = '" . $_POST['$ClassCode2'] ."' ";
	}
if($_POST['ClassCode3']) {
    $query3 .= "AND classcode_rate.ClassCode = '" . $_POST['$ClassCode3'] ."' ";
	} 

$result = mysql_query($query) or die (mysql_error());

This is what I get when I echo the queries.

SELECT classcode_rate.ClassCode, classcode_rate.Classification, classcode_rate.Rate, carriernameid.CarrierName FROM classcode_rate INNER JOIN carriernameid ON classcode_rate.CarrierID=carriernameid.CarrierID WHERE carriernameid.CarrierName='Redwood' AND classcode_rate.ClassCode='5'AND classcode_rate.ClassCode = '' AND classcode_rate.ClassCode = ''

The query shows only the result from the first classcode variable, it's supposed to generate the value result from 3 different classcodes. Any ideas?

 

[KarveR]

if(!empty($_POST['ClassCode2'])){
// add it to the pile, its not empty
}

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[rogerzebra]

Thanks for your respond Karver.
I'm not quite sure though if you wanted me to use the same sql between the brackets? like so...

if(!empty($_POST['ClassCode2'])){
$query2 .= "AND classcode_rate.ClassCode = '" . $_POST['$ClassCode2'] ."' ";
}
if so, when all three field filled in it's only process the result from one classcode which is the last filled field from the list? any ideas?

 

[KarveR]

$query = "
SELECT
classcode_rate.ClassCode,
classcode_rate.Classification,
classcode_rate.Rate,
carriernameid.CarrierName
FROM classcode_rate
INNER JOIN carriernameid
ON classcode_rate.CarrierID=carriernameid.CarrierID
WHERE
carriernameid.CarrierName='$CarrierName'
AND classcode_rate.ClassCode='$ClassCode'";

if(!empty($_POST['ClassCode2'])) {
    $query .= " AND classcode_rate.ClassCode = '" . $_POST['ClassCode2'] ."' ";
}
if(!empty($_POST['ClassCode3'])) {
    $query .= " AND classcode_rate.ClassCode = '" . $_POST['ClassCode3'] ."' ";
}

This will now append the separate AND .. if the $_POST variables are populated, so you could have 1, 2 or 3 in the query.



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[rogerzebra]

Karver,
The last piece at least generate the query as it should, thanks alot I really apprecite it, but I still can't get this *#@^ to flow. I'm still not sure about the html and how to defined the classcode variables.
So I wonder first, <input type="text" name="ClassCode2" <input type="text" name="ClassCode3" generates the variable $ClassCode2 and 3 right?
And I defined the variables in php with
$ClassCode2 = $_POST['ClassCode2'];
$ClassCode3 = $_POST['ClassCode3'];
I'm not sure if this part is correct though? Doesn't $_POST variables in this case trying to pull data from row ClassCode2 and row ClassCode3 from the database? What I'm trying to accomplish is to query the same row "ClassCode" three times, as the query you helped me with.
I though it would be best to post the while loop to.

$result = mysql_query($query) or die (mysql_error());
$num=mysql_num_rows($result);
$i=0;
	while ($i < $num) 
	{	
		$ClassCode=mysql_result($result,$i,"ClassCode");
		$ClassCode2=mysql_result($result2,$i,"ClassCode");
		$ClassCode3=mysql_result($result3,$i,"ClassCode");

I'm sorry I'm confused, if you could shed some light over this would be great.

The echo statement of the query looks good
SELECT classcode_rate.ClassCode, classcode_rate.Classification, classcode_rate.Rate, carriernameid.CarrierName FROM classcode_rate INNER JOIN carriernameid ON classcode_rate.CarrierID=carriernameid.CarrierID WHERE carriernameid.CarrierName='Redwood' AND classcode_rate.ClassCode='5'AND classcode_rate.ClassCode = '16' AND classcode_rate.ClassCode = '38'

I'm sorry to bugging you with this again /rz

 

[KarveR]

you could use OR instead of AND, so your query would be . where classcode = 'blah' OR classcode = 'blah' or ...

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.