Create multiple arrays from query data

Please show us the CREATE TABLE statement and the query.  I think with PDO you would be able fetch an array of objects that was already ordered in a useful way, but I would need to look at the data to know that.

Also, never use the "fetch_array()" function -- they retrieve twice as much data as you need, resulting in a lot of unnecessary overhead.

@ray

CREATE TABLE `tbl_vehicles` (
  `vehicle_id` int(11) NOT NULL AUTO_INCREMENT,
  `variant` varchar(45) DEFAULT NULL,
  `fueltype` varchar(6) DEFAULT NULL,
  `doors` int(11) DEFAULT NULL,
  `seats` int(11) DEFAULT NULL,
  `intro` varchar(10) DEFAULT NULL,
  `make` varchar(20) DEFAULT NULL,
  `mmcode` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`vehicle_id`)
) ENGINE=MyISAM AUTO_INCREMENT=17454 DEFAULT CHARSET=latin1$$

@gr8gonzo
I knew there must be a better way!

Thank you, it is actually so simple, can't believe I did not think of sorting the query.
I realised with this current project that I really don' have my head around the php array structure yet.

Wow, with all of this in the same table, it's going to be quite a challenge to come up with an accurate picture of the vehicle configurations - in a single query.  Here's where I am stumped.

Let's say you have a Mercedes Diesel 4 Door, and a Mercedes Gasoline 4 Door and a Mercedes Gasoline 2 Door.  Now let's say you also have a Ferrari Gasoline 2 Door.  If a client selects Mercedes, there should still be options available to choose the fueltype and the number of doors.  But if the client selects Ferrari, those options should no longer be a part of the selection.  Similarly, if the client selects 2 Door, both Mercedes and Ferrari should still be available, but if the client selects 4 Door, the Ferrari should not be an option any more.

I think the general design pattern for this sort of thing, if I am understanding your application, would use multiple queries and an AJAX front end.  It's conceivable that you could do all of it in JavaScript, based off of a single query, but my vision of that concept lacks much consolidation of thought.

As the client makes successive selections, these selections become part of the WHERE clause in your query, enabling the client to refine and redefine selections.

@ray

what I did here was to build a query dynamically each time the form is posted.

On the first page the user chooses a vehicle make for example Mercedes and click on select.  This project is to get to the vehicle code so the person would already know what the make is. there wot be searches like "Get me all vehicles with 4 doors" but rather "Show me all BMW's with 4 doors"

This takes them to a second where I run a query that updates all the drop downs with all the
options under mercedes. (If the user want to use another make they go back to the main menu) Each time one of the filter dropdowns are changed a JS script posts the form to the server where I dynamically create a query based on the dropdowns that have values selected. If nothing in a drop down changed it is ignored.

Each time all the info come back and all dropdowns are populated with the info. This project is to get to the vehicle code so the person would already know what the make is.

Here is the current code - still working on it but so far it does the job.

$vehicleMake = $_POST['make'];
$vehicleFuel = $_POST['fuel'];
$vehicleDoors = $_POST['doors'];
$vehicleSeats = $_POST['seats'];
$vehicleIntroDate = $_POST['intro'];
$vehicleVariant = $_POST['variant'];

//this is just for testing
echo "<h2>Your Current filters</h2>";
echo "Make: " .$vehicleMake;
echo "<br/>";
echo "Variant: ".$vehicleVariant;
echo "<br/>";
echo "Doors: " .$vehicleDoors;
echo "<br/>";
echo "Seats:" .$vehicleSeats;
echo "<br/>";
echo "Fuel type: " .$vehicleFuel;
echo "<br/>";
echo "Intro Date: " .$vehicleIntroDate;
echo "<br/>";
echo "<br/>";

//function to remove duplicates and sort arrays
function processArray($process){
	$process = array_unique($process);
	asort($process);
	return $process;	
}


//create an empty array to be used for building the query based on data sent
$queryArray = array();

//generate a query based on above received info
if($vehicleVariant != "None Selected"){
	$queryArray['variant'] = "variant = '".$vehicleVariant."'";
}


if($vehicleIntroDate != "None Selected"){
	$queryArray['intro'] = "intro = '".$vehicleIntroDate."'";
}

if($vehicleFuel != "None Selected"){
	if($vehicleFuel == "Diesel"){
		$queryArray['fuel'] = "fueltype = 'D'";	
	}else{
		$queryArray['fuel'] = "fueltype = 'P'";
	}
}


if($vehicleSeats != "None Selected"){
	$queryArray['seats'] = "seats = '".$vehicleSeats."'";
}

if($vehicleDoors != "None Selected"){
	$queryArray['doors'] = "doors = '".$vehicleDoors."'";
}



//if no imformation is posted then generate query to retrive all info of make
if($vehicleFuel == "None Selected" && $vehicleDoors == "None Selected" && $vehicleSeats == "None Selected" && $vehicleIntroDate == "None Selected" &&
$vehicleVariant == "None Selected"){
	$sql = "SELECT * FROM tbl_vehicles WHERE make='".$vehicleMake."'";
}else{
	//otherwise build a new query
$sql = "SELECT * FROM tbl_vehicles WHERE make='".$vehicleMake."' ";
$sql .= "AND ".implode(" AND ",$queryArray);
}

Select allOpen in new window

@ray

the scenario you sketched above is exactly what I have been mulling over the last 2 days. I cold not figure out how that would all be done with one query untill I realised this morning that
a)The make of vehicle would already be known
b) I could create queries on the fly with the array as done in the code.

My problem after that was the way I handled the data coming back. I needed to generate individual dropdowns form the data and while the way I did it worked (loading individual arrays and runnign a foreach to create the drop-downs) I felt that surely there must be a more streamlined way of doing it - hence this question.

I looked at gr8gonzo's suggestion above and I think that will work fine.

Suit yourself -- I don't have a dog in the fight, and gr8gonzo's advice is always spot-on.  I'm slightly concerned that the design would force me to choose the vehicle make in order to get to the things I really want - like whether there is a red convertible available, or the number of doors.  So instead of being able to ask one question (eg: Show me the vehicles with 4 doors) I would have to ask a series of questions: Show me the Mercedes with 4 doors, show me the BMW with 4 doors, show me the Ferrari with 4 doors, etc.

You might want to look at the CarMax search for inspiration.

:) thank you for the help and advice. I will have a look at that site.

This little project is just a proof of concept for in house use. We will use it in our workflow here just to speed up things. Basically we get one car at a time and need to get the book code for it.