Get data from database, only once?

[bschwarz]

Alright, my subject was confusing, I hope the question isn't.

Say I have two columns in a database, make and model.

When make is selected from a drop down, I want it to get all the model numbers that exist for that make and spit them back out, but I do not want to get duplicates (ie if there are two entries with G550 as the model number) I'd only want to see G550 once in the list.

how?? 🙂

[LordShryku]

SELECT DISTINCT model
FROM table

[bschwarz]

thanks dude.
I'd just found it.. RIGHT at the bottom of the SELECT documentation

cheers

[bschwarz]

okay, how about that... AND I don't want it to get null fields (fields with nothing in them)

[PurpleMonkey]

Use JScript (I'll supply one if you want) or

alter table cars modify field_name datatype(length) MULL;

but the JScript is better becasue then the user can't enter a null field in the first case.

function check() {
if(!document.form.field.value) {
alert("Please fill in this field!");
return false;

}
}

[bubblenut]

Javascript can be switched off, I'd always suggest doing important validation server side. If it's not imperetive that the fields are filled, you just want to make sure they're not in the query if they're empty then you want something like this.

<?php
$sql='SELECT DISTINCT model FROM table WHERE';
$where='';
//array of fields
$fields=array('field1','field2','field3');
for($i=0;$i<count($fields);$i++) {
  if(!empty($_GET[$fields[$i]]))
    $where.=($where==''?'':' && ').$fields[$i]."='".$_GET[$fields[$i]]."'";
}

HTH
Bubble

[LordShryku]

SELECT DISTINCT model
FROM table
WHERE model IS NOT NULL

if you were talking about the sql, not the input

[bschwarz]

Originally posted by LordShryku
SELECT DISTINCT model
FROM table
WHERE model IS NOT NULL

if you were talking about the sql, not the input

ahh yes, thats right.
wasn't talking about the input dudes, thanks though.

[bschwarz]

so basically this is all for some search forms on a web site.

how can I make it search for fields only if a value has been given?
is that along the lines of what bubblenut was doing?

[LordShryku]

Yep. His code is just checking to make sure that the field was filled in, then adding it to the sql query.

[bschwarz]

Originally posted by LordShryku
Yep. His code is just checking to make sure that the field was filled in, then adding it to the sql query.

I don't understand how its actually added to the query?

Do you have to run 2 queries?

[bschwarz]

okay, woah, my brain has finally switched back to "ON".

How can I also make the query ignore "Select" or something like that?

[bschwarz]

<?
	$make = $_POST['search_make'];
	print $make."<br />\n\r<br />\n\r";
	$where='';
	//array of fields
	$fields=array('make', 'model', 'calibre', 'action_type', 'rrp', 'rrp', 'make', 'model', 'calibre', 'action_type');
	$post_fields=array('make','model','calibre', 'action_type', 'prc_rng_from', 'prc_rng_to', 'keywords');
	for($i=0; $i< count($fields); $i++) {
	  	if(!empty($_POST[$fields[$i]])){
			$where.=($where==''?'':' && ').$fields[$i]." LIKE '%".$_POST[$post_fields[$i]]."%'";
		}
	}
	include("docs/include.php");
	$query = "SELECT DISTINCT make, model, calibre, action_type, rrp FROM stock WHERE ".$where;
	print "<b>QUERY: </b>".$query."<br />\n\r<br />\n\r";
	print "<b>WHERE: </b>".$where."<br />\n\r<br />\n\r";
	$result = MYSQL_QUERY($query, $conn);
	if(!$result){
		die(MYSQL_ERROR());
	}
	$count = count($result);
	if($count == 0){
		print "no results!";
		exit;
	}
	while(list($make, $model, $calibre, $action_type, $rrp) = MYSQL_FETCH_ARRAY($result)){
		print "MAKE: ".$make." MODEL: ".$model." CALIBRE: ".$calibre." ACTION TYPE: ".$action_type." RRP: ".$rrp."<br />\n\r";
	}

MYSQL_CLOSE($conn);
?>

okay currently I'm running that...
but for some reason this is my output:

QUERY: SELECT DISTINCT make, model, calibre, action_type, rrp FROM stock WHERE

WHERE:

You have an error in your SQL syntax near '' at line 1

I can't seem to workout the error at all?
Any ideas?

[bubblenut]

You're gonna kick yourself 😃

 for($i=0; $i< count($fields); $i++) {
          -->>if(!empty($_POST[$fields[$i]])){<<--
            $where.=($where==''?'':' && ').$fields[$i]." LIKE '%".$_POST[$post_fields[$i]]."%'";
        }
    } 

You're checking the $_POST array with the wrong set of vars.

Also, the post_fields array has a different number of elements to the fields array, this doesn't make sense.

As a side note, you may want to put in a check which throws an error if none of the fields were filled.

HTH
Bubble

[bschwarz]

I think that using a loop to contruct the WHERE clause is causing me too much trouble, I really need to get this done today and I can see that my boss is going to start riding me soon. (lets not go there though! 🙂)

I currently have something like this going on, only problems are : empty fields, and I need to make it check for price range to and from, and if those values are empty and yadda yadda, getting hard, anyone have ideas I might be able to borrow? 🙂

thanks for the help up until now also, you've all been awesome!

$fields=array('make', 'model', 'calibre', 'action_type');
	for($i=0; $i< count($fields); $i++) {
	 	if(!empty($_POST[$fields[$i]])){
			$where.=($where==''?'':' && ').$fields[$i]." LIKE '%".$_POST[$fields[$i]]."%'";
		}
	}
	$where .= " && rrp => '$prc_rng_from' && rrp =< '$prc_rng_to'";

include("docs/include.php");
$query = "SELECT DISTINCT make, model, calibre, action_type, rrp FROM stock WHERE $where";
print "<b>QUERY: </b>".$query."<br />\n\r<br />\n\r";
print "<b>WHERE: </b>$where<br />\n\r<br />\n\r";
$result = MYSQL_QUERY($query, $conn);
if(!$result){
	die(MYSQL_ERROR());
}

edit: edited code, went back to using the loop for the regular fields, can apply other things like less or more for price range later... right? eep