30 mysql queries in one page?

[inter]

Hello all! Im very new to php, but I've been thrown into the deep end at work.

 

I have a question regarding how many mysql queries you can/should have per php page.

 

For example, I am making a web calendar where people can add/edit/view events. On one of the view options, there will be a typical monthly calendar style view. The events will then appear inside the little calendar boxes, with a link to a more detailed event view. The only way I know how to do this is:

 

- make a seperate web page for each month, manually copying a 2008 calendar.

- mysql query for each daily box in the calendar. The query would search the events database for any event on 2008-01-01 and then list them. The next box would then do the same for 2008-01-02, etc. Resulting in around 30 queries per page.

 

Is it possible to simply do 1 query per month, and then filter the results into each daily box in the calendar view?

 

Thanks for any responses!

 

 

PS: I realise it's stupid to manually code each month in a page, but I dont know how to make a calendar style view any other way. I have however, made simple list views using just one page and using "monthlist.php?client=bob&month=01".

[cooldude832]

yes you can do calendars in php I have built one its very easy with 1 (one) query

you have a date/time field in your table and you query all events that are between date/time of the month statr and month end.

 

very simple to do with 1 query

[inter]

Thanks for the reply Cooldude.

 

How would I turn that list of events into a calendar though?

 

At the moment I have a month page, but it just lists the events in order of date.

 

eg: it queries the database for records with the DateMonth field equal to 01 (for Jan), and lists those records Ascending by DateDay field.

[cooldude832]

you have to think outta the box here

<?php
$date_start = ("u", mktime());
$date_end = ("u", mktime());
$q = "Select ".$fields." from `events` where Date >= ".$month_start." && Date <= ".$month_end." order by Date ASC";
$r = mysql_query($q) or die(mysql_error());
if(mysql_num_rows($r) >0){
   while($row = mysql_fetch_array($r)){
              foreach($row as $key=> $value){ 
                    $data[$row['date']][$key] = $value;
              }
    }
    //Now output a calendar and then based on what day of the month its echoing out display that node of the $data
}
?>

That is some psedeo code to think about.

[inter]

Cheers for that code.

 

I tried to follow it as I read through it, but a lot of those commands are a bit beyond me.

 

I think I'll have to learn what an array is before I'm able to understand what the following means:

 

while($row = mysql_fetch_array($r)){
              foreach($row as $key=> $value){ 
                    $data[$row['date']][$key] = $value;
              }

 

So far Ive only ever used a few select funtions to do what I needed (turning a mysql query into variables and then using echo to display stuff on the page).

 

I'll go googling for some basic php introductions I guess.

[btherl]

inter, have you used arrays?  Multi-level arrays?  String-indexed arrays?  They are very powerful.

 

Arrays let you store large quantities of data in one place.  They also let you sort that data, and then display it in the sorted order.

 

String indexed arrays let you look up data by a key of some sort.  In cooldude's example, he uses date as the key.  This allows you to instantly find all data for a date just by accessing $data['2008-01-01'], for example.

 

So if you're not familiar with arrays, it's time to start

[cooldude832]

that section you are unfamilar with is the powerhouse behind it.

Its the logic that makes it go.

 

As you build your calendar the easiest way to do it is start with a month say Febuary 1st of 2008.  Then you go and say well if its not a sunday lets find the sunday before it and start counting there. (As you calendar will be in a table with headers Sunday, Monday, Tuesday----> Saturday)

You will be using the date function that will populate the days of the month that correspond to the days of the week.  Then you simply use that date you are currently on in a while or for loop and echo out the array nodes corresponding to that day.

 

This is a very simple calendar program I have built that will get you started

<?php
//arrays used for verification
$months = array("January", "Febuary", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");
$years = array("2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015", "2016");
$days=  array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31");

//Verification of data being presented
if(in_array($_GET['year'], $years) && $_GET['month'] >0 && $_GET['month'] <13 && intval($_GET['month']) && $t_date_2 > $t_date){
$date = mktime(0,0,0,$_GET['month'],1,$_GET['year']);
$month = $_GET['month'];
$year = $_GET['year'];
}
else{
$date = mktime(0,0,0,date("m"),1,date("y"));
$month = date("m");
$year = date("Y");
}
$temp = strtotime("Last Sunday", $date);
$temp = strtotime("-1 Day", $temp);
echo "<table border=\"1\">";
echo "
<tr>
<td>Sunday</td>
<td>Monday</td>
<td>Tuesday</td>
<td>Wednesday</td>
<td>Thursday</td>
<td>Friday</td>
<td>Saturday</td>
";
//Limiter on $i is to prevent spilling into next month as it is not checked
$i = 0;
while($i <52){
//If its a saturday we need a new row
	if(date("w",$temp) == 6){
	//Start of a new row
		echo "\n</tr><tr>\n";
	}
	//Creation of the next day
	$temp = strtotime("+1 Day", $temp);
	$d_check = date("Y-m-d",$temp);
//Outputting of td with the day number in the box, this is where you also include your events data.
	echo "<td>".date("j", $temp)."</td>";
	$i++;
}
echo "</tr></table>";
?>

[alecks]

Check out http://keithdevens.com/software/php_calendar

A nice (albeit a little bit messy) calendar generating function.

[cooldude832]

there are literally a million ways to build a calendar, you just need to find the method that best suites you, this method i used was for looking at a hotel room's reservation so I had used the while loop so in the future the $i value could be dynamic so that you could view a larger or smaller date range.