field into array based on value and then count()

[peterbrowne]

Hi

 

I have a mysql query where I want to put the fields into an array based on their value and then count the number of items in the array. 

 

The query is:

 

$query_workshop_sem1_venue2_series1 = "SELECT * FROM workshop, venue WHERE workshop.venue_id = venue.venue_id AND workshop.type ='1' AND workshop.semester = '1' AND series = '1' AND workshop.venue_id = '2' AND workshop.cancelled != '2'";
$result_workshop_sem1_venue2_series1 = mysql_query($query_workshop_sem1_venue2_series1, $connection) or die(mysql_error());

 

I then want to put $row_workshop_sem1_venue2_series1[cutoff_date] into an array based on:

 

$row_workshop_sem1_venue2_series1[cutoff_date] < date() (ie current date)

 

Sounds simple, I can do this with a seperate query, but I should already have the data from the original query above. 

 

Any help appreciated!

 

Thanks,

 

Peter

[R0bb0b]

I don't understand what you mean, but this should get you started

 

<?php
$query_workshop_sem1_venue2_series1 = "SELECT * FROM workshop, venue WHERE workshop.venue_id = venue.venue_id AND workshop.type ='1' AND workshop.semester = '1' AND series = '1' AND workshop.venue_id = '2' AND workshop.cancelled != '2'";
$result_workshop_sem1_venue2_series1 = mysql_query($query_workshop_sem1_venue2_series1, $connection) or die(mysql_error());

//loop through each row
while($row = mysql_fetch_assoc($result_workshop_sem1_venue2_series1))
{
    //loop through each column
foreach($row as $fieldname=>$value)
{
	// do what ever you want with the column names and values at this point
}
}
?>

[peterbrowne]

Thanks R0bb0b for the reply.

 

I'm not sure what to do with your code.  What I want to do is just count the number of rows returned where $row_workshop_sem1_venue2_series1[cutoff_date] equals a specific value, in this case is less than the current date.

 

[R0bb0b]

it would actually be easier if you just did it in its own query:

<?php
$query = "SELECT count(workshop.venue_id) as total FROM workshop, venue WHERE workshop.venue_id = venue.venue_id AND cutoff_date < CURDATE()";
$result = mysql_query($query);
$totalrows = mysql_result($result, 0, "total");
?>

[peterbrowne]

Yes, it does work with its own query...just though that there might be a better way of doing this rather than adding another query (there are already about 30 queries on the the page...)

 

 

Thanks,

 

Peter

[R0bb0b]

well, its either that or loop through all of the results with a loop like the one that I posted previously and compare all of the results with the current date or just make another query.  On the other hand, think about it this way, the database does this process of looping and calculating much faster than php so I wouldn't be surprised if it was actually faster, especially if you index that column.