MySQL quandry

[bdl]

I've got a table setup something like this (an example):

vehicles_table:
id | vehicle_type | manf | vin | price | timestamp

Let's say on each record you have a seperate vehicle_type, like 'truck', 'car', 'suv', 'compact', etc. What I need to do is run a count for each specific type of vehicle_type, and generate tables based on that count. Now, I've tried the COUNT() function and can't figure how to run it to do what I want. Let's say I use the following query:

SELECT id, vehicle_type, manf, vin, price,
UNIX_TIMESTAMP(timestamp) AS timestamp,
COUNT(vehicle_type) AS each_type
FROM vehicles_table
GROUP BY vehicle_type;

This only groups the vehicle_type all in one result, not what I want. By default, you MUST use the GROUP BY command to group the output when you use COUNT(). I don't know another way around that...

I need to dynamically find the count for each vehicle_type, create a new table with X rows based on that value. So each vehicle_type will have it's own table, and each table will have X rows based on how many entries of that specific vehicle_type there are. Hope this makes sense. An example would be if there are 20 'suv' types and 10 'compact' types. It would generate two tables, one with 20 rows and another with 10 rows.

Any insights would be appreciated. =)

[Admin]

hmmmm.... that is a tough one.... ill try to put some time into this when i get back from lunch...

you should check out my PHP question

[bdl]

Originally posted by Admin
hmmmm.... that is a tough one.... ill try to put some time into this when i get back from lunch...

you should check out my PHP question

Thanks =)

In thinking more about it, I'm leaning towards having to do two queries, one which will pull the count of each type out, let's say to the array $each_type and so you'll end up with something like $each_type['compact']['10'] that you can plug into the next query, which will pull everything down. Do a 'foreach()' loop or something with the $each_type array and have it execute the table with the other query's data.

PHP Code:

// get count of each vehicle_type from db
$each_query = "SELECT vehicle_type, COUNT(vehicle_type) AS count_type FROM vehicles_table";
$each_result = mysql_query($each_query);
$each_type = mysql_fetch_array($each_result);
// get all other data from db
$data_query = "SELECT * FROM vehicles_table ORDER BY id";
$data_result = mysql_query($data_query);
// do something here, no idea what as of yet :heh: 


Yes, I had a look at your PHP problem the other day, not familiar enough to answer it, I'm afraid.

[sde]

well not sure if this is efficient.. but perhaps it will help:

PHP Code:

<?
$result=mysql_query("select distinct vehicle_type from vehicles_table");
while($row = mysql_fetch_row($result))
{
  $type_array[]=$row['vehicle_type'];
}

foreach($type_array as $each)
{
  $result=mysql_query("select count(id) as count from vehicles_table where vehicle_type='$each'");
  while($row=mysql_fetch_row($result))
  {
    echo "Total " . $each . ":" . $row['count'] . "<br>\n";
   }
}
?>

now you'de probably have to use the $type_array again to query the specifics for each product id .. perhaps you could do this in the last foreach loop.

hope this helps.

[pngwyn]

This worked for me in one of my similar tables, should work for you. You _cannot_ add columns, unless they are all similar within the group.

---
SELECT count( id ) as count, vehicle_type FROM vehicles GROUP BY vehicle_type
---

Good luck, have fun!

[pngwyn]

I just re-read your question and determined that my response probably wasn't the answer you were looking for. Lets try that again.

If you want to display all the rows in the table in separate HTML tables and only do it with one query then first you would need to grab all the records in the table.

$SqlTxt = "SELECT * FROM table;
$SqlQry = mysql_query($SqlTxt);

Then we want to loop through those rows and assign them to an array.

// $count will start from one so that the count will match the 
// number given by count() in the for() loop
$count =  '1';
while($rows = mysql_fetch_array($SqlQry)) { 
   $vechicle_type = $rows[vehicle_type;
   $vehicles[$vehicle_type][$count] = $rows;
   $count++;
}

// Debugging ... So you can see the array ... 
print("<pre>");
print_r($vehicles);
print("</pre>");

That should build a multidemensional array placing all the vechicles of the 'suv' type under, $vehicles[suv], 'trucks' under, $vehicles[trucks], etc...

Now just print those rows.

// Print SUV's
$currType = "suv";
print("<table>");
print("<tr><td>Vehicle: SUV</td><tr>");
for($i = 0; $i = count($vehicles[$currType]); $i++) {
  $currentVehicle = $vehicles[$currType][$i];
  print("<tr><td>$currentVehicle[manf], $currentVehicle[vin] $ $currentVehicle[price]</td></tr>");
}
print("</table>");

You will have to change the $currType for each vechicle type or you can build another array with all available types and nest this loop in a loop that moves through all the different types. It should be noted that only if a row exists for that vehicle_type will a print out be available otherwise that part of the array would be absent.

I hope this example helps. I haven't actually tried this particular peice of code, but it is a method that I use quite often. Syntactically it may have errors, but the logic should work. *crossing fingers* ;-)

If you have any problems or questions just post it back on to here and I will try to help...

[bdl]

Thanks for the replies and the ideas; I'm still working with various code to see if I can get it to do what I want. Something of a mix of sde's solution and pyngwyn's solution is what I'm working on.

[bdl]

Going in a completely different direction, I came up with this, and it works the way I want it to. This code creates a table based on each vehicle type from the array $vehTypes.

PHP Code:

<?php
// vehicles_test.php

// create an array with the hard-coded vehicle types
$vehTypes = array(
                "compact",
                "luxury",
                "truck",
                "van",
                "suv"
                );
include("./mysql/dbconn.php");
// loops through the vehTypes array, creates a table based on
// each type
foreach ($vehTypes as $each_type) {
  $sqlQuery = "SELECT
               vehicle_type, manf, model, vin, price,
               UNIX_TIMESTAMP(timestamp) AS timestamp
               FROM test.vehicles
               WHERE vehicle_type = '$each_type'
               ORDER BY timestamp";
  if (!$sqlResult = mysql_query($sqlQuery))
     die("Unable to return resultset: ".mysql_error());

  echo '<table border=1 cellpadding=3 cellspacing=1>';
  while ($row = mysql_fetch_array($sqlResult, MYSQL_ASSOC)) {
    extract($row);
    echo '<tr>';
    echo '<td>'.$vehicle_type.'</td>';
    echo '<td>'.$manf.'</td>';
    echo '<td>'.$model.'</td>';
    echo '<td>'.$vin.'</td>';
    echo '<td>'.$price.'</td>';
    echo '<td>'.date("m/d/y", $timestamp).'</td>';
    echo '</tr>';
  }
  echo '</table>';
  echo '<br>';
}
?>

Comments?

[Antagony]

I couldn't really tell what he wanted, but it looks like bdl got it right.

[sde]

i learned a lot about grouping this weekend because of this post .. sql is fun .. woohoo