Is there a better way?

[stackumhi]

Hello,

The code below works fine but I was wanted to know if there was a better(shorter) way of doing this?

 

Thanks for any input.

 

<?
$result1 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 1");
$total_num_rows1 = mysql_num_rows($result1);

$result2 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 2");
$total_num_rows2 = mysql_num_rows($result2);

$result3 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 3");
$total_num_rows3 = mysql_num_rows($result3);

$result4 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 4");
$total_num_rows4 = mysql_num_rows($result4);

$result5 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 5");
$total_num_rows5 = mysql_num_rows($result5);

$result6 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 6");
$total_num_rows6 = mysql_num_rows($result6);

$result7 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 7");
$total_num_rows7 = mysql_num_rows($result7);

$result8 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 8");
$total_num_rows8 = mysql_num_rows($result8);

$result9 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 9");
$total_num_rows9 = mysql_num_rows($result9);

$result10 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 10");
$total_num_rows10 = mysql_num_rows($result10);

$result11 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 11");
$total_num_rows11 = mysql_num_rows($result11);

$result12 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 12");
$total_num_rows12 = mysql_num_rows($result12);

$result13 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 13");
$total_num_rows13 = mysql_num_rows($result13);

$result14 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 14");
$total_num_rows14 = mysql_num_rows($result14);

$result15 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 15");
$total_num_rows15 = mysql_num_rows($result15);

$result16 = mysql_query("SELECT * FROM vendors WHERE vendor_type_id = 16");
$total_num_rows16 = mysql_num_rows($result16);

// Get total Vendors
$result = mysql_query("SELECT * FROM vendors");
$total_num_rows = mysql_num_rows($result);

?>    
      
      <p>AV / Tech / Production - <? echo $total_num_rows1 ?><br />
        Bakeries / Cakes - <? echo $total_num_rows2 ?><br />
        Bridal Services - <? echo $total_num_rows3 ?><br />
        Caterers / Food Service - <? echo $total_num_rows4 ?><br />
        Destination Management - <? echo $total_num_rows5 ?><br />
        DJs / Entertainment - <? echo $total_num_rows6 ?><br />
        Event Management - <? echo $total_num_rows7 ?><br />
        Event Rentals / Tents - <? echo $total_num_rows8 ?><br />
        Florists / Flowers - <? echo $total_num_rows9 ?><br />
        Health / Beauty - <? echo $total_num_rows10 ?><br />
        Printing / Marketing - <? echo $total_num_rows11 ?><br />
        Photographers - <? echo $total_num_rows12 ?><br />
        Transportation / Valet - <? echo $total_num_rows13 ?><br />
        Tuxes / Formal Wear - <? echo $total_num_rows14 ?><br />
        Videographers - <? echo $total_num_rows15 ?><br />
        Wedding Officiants - <? echo $total_num_rows16 ?></p>
      <p><strong>Total Vendors - <? echo $total_num_rows ?></strong></p>

[EmlynK]

You could try something like this, but I'm not sure if it would work:

 

function num_rows($id) {
if($id <= 0) {
	$vendors = ("SELECT * FROM vendors");
}
else {
	$vendors = ("SELECT * FROM vendors WHERE vendor_type_id = ". abs(@intval($id)) ."");
}
$num_vendors = mysql_num_rows($vendors);
print $num_vendors;
}
?>

<p>AV / Tech / Production - <? num_rows(1); ?><br />
Bakeries / Cakes - <? num_rows(2); ?><br />
Bridal Services - <? num_rows(3); ?><br />
Caterers / Food Service - <? num_rows(4); ?><br />
Destination Management - <? num_rows(5); ?><br />
DJs / Entertainment - <? num_rows(6); ?><br />
Event Management - <? num_rows(7); ?><br />
Event Rentals / Tents - <? num_rows(; ?><br />
Florists / Flowers - <? num_rows(9); ?><br />
Health / Beauty - <? num_rows(10); ?><br />
Printing / Marketing - <? num_rows(1); ?><br />
Photographers - <? num_rows(11); ?><br />
Transportation / Valet - <? num_rows(12); ?><br />
Tuxes / Formal Wear - <? num_rows(1); ?><br />
Videographers - <? num_rows(13); ?><br />
Wedding Officiants - <? num_rows(14); ?>
</p>
<p><strong>Total Vendors - <? num_rows(0); ?></strong></p>

[samoht]

I'm sure there are lots of ways to do this that could use much less lines of code.

 

You could try using a for loop with and int and maybe COUNT instead of mysql_num_rows()

for ($i=1; $i<=15; $i++)
{
   $myresult[$i] = mysql_query("SELECT COUNT(*) FROM vendors WHERE vendor_type_id = $i");
   
}

 

Then maybe put your buisness names (or whatever those are) in an array to and loop through them assigning $myresult[$i] for the total.

For that matter you could do one foreach loop instead.

 

[BlueSkyIS]

FYI: don't use short tags as they are deprecated. always use full tag and echo:

 

<p>AV / Tech / Production - <?php echo num_rows(1); ?><br />

[ChemicalBliss]

Why yes, there is indeed .

 

A) always use <?php ?> - its my pet hate to see a shorthand.

B) The less mysql calls (and also the smaller the manipulated data) the better.

 

Here's how i would do it:

<?php

// Get all rows with all vendors

// Query - Note: only select the vendor_type_id field as thats all we really need to seperate the data.
$query = "SELECT vendor_type_id FROM vendors 
WHERE vendor_type_id = 1 
	OR vendor_type_id = 2 
	OR vendor_type_id = 3 
	OR vendor_type_id = 4 
	OR vendor_type_id = 5 
	OR vendor_type_id = 6
GROUP BY vendor_type_id"; // Note: GROUP BY - will group vendor ids together in blocks.

$result = mysql_query($query);

// Setup an empty array we can use to count the different vendor ids, change keys here to reflect what vendor type ids your using
$vendor_counts = array(
1 => 0,
2 => 0,
3 => 0,
4 => 0,
5 => 0,
6 => 0
);

// Now we count each vendor id;
while($row = mysql_fetch_assoc($result)){
$vendor_counts[$row['vendor_type_id']]++;
}

// Now print results:
print_r($vendor_counts);

?>

 

One mysql query. Counts stored in an array for easy and quick access. Could easily be made dynamic b incorporating variable vendor type ids.

 

hope this helps

[stackumhi]

Thanks everyone for your input.

 

I will try your suggestions.

[EmlynK]

I'm sure there are lots of ways to do this that could use much less lines of code.

 

You could try using a for loop with and int and maybe COUNT instead of mysql_num_rows()

for ($i=1; $i<=15; $i++)
{
   $myresult[$i] = mysql_query("SELECT COUNT(*) FROM vendors WHERE vendor_type_id = $i");
   
}

 

Then maybe put your buisness names (or whatever those are) in an array to and loop through them assigning $myresult[$i] for the total.

For that matter you could do one foreach loop instead.

 

Ahh, this is much better than my suggestion. A lot less lines of code needed for the selection of vendors. I didn't think about using a loop. doh.

[ChemicalBliss]

I'm sure there are lots of ways to do this that could use much less lines of code.

 

You could try using a for loop with and int and maybe COUNT instead of mysql_num_rows()

for ($i=1; $i<=15; $i++)
{
   $myresult[$i] = mysql_query("SELECT COUNT(*) FROM vendors WHERE vendor_type_id = $i");
   
}

 

Then maybe put your buisness names (or whatever those are) in an array to and loop through them assigning $myresult[$i] for the total.

For that matter you could do one foreach loop instead.

 

Ahh, this is much better than my suggestion. A lot less lines of code needed for the selection of vendors. I didn't think about using a loop. doh.

 

Yes but too many mysql queries imo

[samoht]

The more I think about it the more I don't like the vendor id being a simple 1,2,3 ... to match the vendor names(?) that you already have on your page. This really seems like you need a better query that COUNT's the number of records for each vendor and then lists the name of the vendor that is it. But I would need to see your table structure to understand if that would work for you.

 

IF you still want to use the static numbering here is an example of the foreach loop that I mentioned:

<?php
$vendors = array('AV / Tech / Production','Bakeries / Cakes','Bridal Services','Caterers / Food Service','Destination Management','DJs / Entertainment','Event Management','Event Rentals / Tents','Florists / Flowers','Health / Beauty','Printing / Marketing','Photographers','Transportation / Valet','Tuxes / Formal Wear','Videographers','Wedding Officiants');
$i = 1;
foreach ($vendors as $vendor){
$myresult[$i] = mysql_query("SELECT COUNT(*) FROM vendors WHERE vendor_type_id = $i");
echo $vendor . ":" $myresult[$i];
$i++;
}
?>

[ChemicalBliss]

Just a couple things to note;

 

1. External resources (mysql) is really expensive in PHP resources. Every call costs a lot. Benchmark our scripts to find which is fastest .

2. Also code readability is very important is reusable code (which you should eb aiming for ).

 

[stackumhi]

Thanks ChemicalBliss.

 

Can you provide a short example of the array you mentioned above..I am very much a newbie.

 

Thanks.

 

// Setup an empty array we can use to count the different vendor ids, change keys here to reflect what vendor type ids your using
$vendor_counts = array(
1 => 0,
2 => 0,
3 => 0,
4 => 0,
5 => 0,
6 => 0
);

[ChemicalBliss]

That is the empty array .

 

The numbers on the left are the "KEYS" of each "item" of the array, whereas the numbers on the right are the "VALUE"s of each item in the array.

 

So, we are using the array "KEYS" as the "vendor_type_ids", this way we can more easily "increment" each value as neccessary in the loop.

 

for instance, if you only wanted to count the vendor type ids 3,5 and 6 then;

 

Query:

// Query - Note: only select the vendor_type_id field as thats all we really need to seperate the data.
$query = "SELECT vendor_type_id FROM vendors 
WHERE  vendor_type_id = 3 
	OR vendor_type_id = 5 
	OR vendor_type_id = 6
GROUP BY vendor_type_id"; // Note: GROUP BY - will group vendor ids together in blocks.

 

Empty Array:

// Setup an empty array we can use to count the different vendor ids, change keys here to reflect what vendor type ids your using
$vendor_counts = array(
3 => 0,
5 => 0,
6 => 0
);

 

 

hope this clears things up

 

PS:

The code I originally gave you should be able to replace the code you originally gave us . - though the variable names etc will be different and the print_r() is there to show you the results - look in the "source Code" of the html result.

[samoht]

ChemicalBliss,

 

I agree, I still think a single query is the answer.  There should be no need for arrays or functions if all this data is stored in the db already.

[ChemicalBliss]

ChemicalBliss,

 

I agree, I still think a single query is the answer.  There should be no need for arrays or functions if all this data is stored in the db already.

 

Sometimes you can "over simplify" problems, looks cool - but in the long run won't do you any favors . Still, I cannot think of a single mysql query that could get those 6 seperate counts into a variable that isn't an array and is on 1 line .

[samoht]

without the table structures I can't tell

 

stackumhi,

 

if you will show us your table structure and some sample data I'll bet we can figure out a query that will work for you that is not resource intensive and allows for your data to be flexible (i.e. the names and numbers can be changed if needed)

[samoht]

I'm guessing that table "vendors" is a lookup table and vendor_type_id is a foreign key?

 

if so, wouldn't this query

"SELECT vendor_type_id, COUNT(*) FROM vnedors GROUP BY vendor_type_id";

Return the proper count for each vendor type id listed in order?

 

IF this query did return the wanted values and the vendors tables also stored the vendor name then this

"SELECT vendor_type_id, vendor_name, COUNT(*) FROM vnedors GROUP BY vendor_type_id" ORDER BY vendor_type_id;

 

Should be all you need 

 

Then your print out would simply be a foreach loop

 

If the vendor name is not in the vendor table - you could either use the array method I posted before (not flexible, since you have to list the vendors in order) or make a more complex query that retrieves the vendor name (need a inner join).

[stackumhi]

Here is the table column fields:

 

vendor_id

date_entered

vendor_type_id

vendor_name

vendor_address

 

Please let me know if you need any other info.

[stackumhi]

Here is a screen shot of the vendors table...might work better

 

[attachment deleted by admin]

[samoht]

Well it looks like the query I suggested will work then. You could try running that query (in phpmyAdmin or whatever) and see if the results give you what you want.

 

If it does, we can use the output to calculate the grand total instead of using another query.

 

 

 

 

[stackumhi]

Thanks to all, for your input.