Shortest way to return results based on two values

[CoffeeAddict]

To use an example, let's say I have a list of 50 cars, with their make and model and mileage in a database.

 

I want to return results that list all the fords fusions with the first and second highest mileage, all the honda elements with the first and second highest mileage, etc. etc. and make both of those results (the first and second) a variable so I can put them neatly into a table on the website.

 

Is there any way of doing that without writing 50 different queries for each make and model? If I have a lot of different cars that could get time consuming and be a lot of code, correct?

 

 

 

[WebStyles]

something like order by mileage desc limit 2

[CoffeeAddict]

That would work, but then it would return the highest and second highest mileage for all the cars and not just for each make and model, right?

 

I can select the make and model of the car, and order that by mileage and desc limit 2, however I then need to do that for EVERY make and model, and if if got 50 different types of cars that's a lot of code. Just looking for a shorter way to cycle through all the different cars and return each individual results without writing 50 queries.

[AbraCadaver]

Well if you have select boxes on your form that allows multiple selections, and you define them as an array like (name="make[]") then something like this:

 

$makes  = "'" . implode("','", array_map('mysql_real_escape_string', $_POST['make'])) . "'";
$models = "'" . implode("','", array_map('mysql_real_escape_string', $_POST['model'])) . "'";

// SELECT * FROM `cars` WHERE `make` IN ($makes) AND `model` IN ($models) ORDER BY `mileage` DESC LIMIT 2

 

You can also create a function that does this to reuse.

[CoffeeAddict]

There is no form to define the make or model beforehand, I just want to fetch the data and then display it.

[Cineex]

well you could get all the makes with one sql and then loop throw them with a query for each like this:

 

$getMakesSQL = mysql_query("SELECT id FROM makes");

while($row = mysql_fetch_array($getMakesSQL))
{
$id = $row['id'];
$modleSQL = mysql_query("SELECT * FROM model WHERE makeID='$id' DESC LIMIT 2");
//create your table with thte output
}

[CoffeeAddict]

That would work except it doesn't account for ordering each make by it's mileage.

 

The results should look something like this:

 

Honda

Civic - mileage 21000

Civic - mileage 12000

 

Ford

Fusion - mileage 145000

Fusion - mileage 4000

 

and so on...

[Cineex]

the add "ORDER MY milage" to the sql so it would look like this:

 

SELECT * FROM model WHERE makeID='$id' DESC LIMIT 2 ORDER BY mileage

[CoffeeAddict]

I will give that a shot, thank you!

 

Looking it over it seems like it would only return a total of two results, instead of two results for each model. But I could be wrong.

[Cineex]

I will give that a shot, thank you!

 

Looking it over it seems like it would only return a total of two results, instead of two results for each model. But I could be wrong.

 

No, it should return all the models. Don't forget that if you are putting it into a varibal that you have to write

$foo .= "<tr>something here asdkasdjk</tr>";

//NOT
$foo = "<tr>something here asdkasdjk</tr>";

 

Don't miss the DOT "." .

[Zane]

What does your table schema look like?

 

The answer is all in the query you perform, once you run mysql_fetch_array through a loop, you can have that populate a multidimensional array with all the results.