MySQL data into HTML table

[adrian_]

Hi all,

I'm new to php/myslq and I'm going crazy trying to figure this one out

 

I'm building a personal calendar and I want to display the data into a HTML table.

 

//query the database
$query = "SELECT * FROM tbl_events WHERE event_day=$day AND event_month=$month AND event_year=$year";
$query = mysql_query($query);

//build the table
echo '<table>';
for ($y = 0; $y < 6; $y++){
   echo '<tr><td>';
        //insert the data here
   echo '</td></tr>';
}
echo '</table>';

 

When I store the events into the database, I assign a slot for each one depending on the hour. I don't want to use more than 6 events daily, hence the for loop. The problem I have is how to I insert the data into the designed <td>? In a particular day I could have only 2 events: event 1 - slot 2, event 2 - slot 6. I want to be able to enter each event into its own cell

 

I hope I'm making myself clear enough. Sorry for any English mistakes if any.

 

Thank you

[adrian_]

OK. So I can do it if I query the database at every single line

 

after the creation of the table cell I entered the following code:

$query = "SELECT * FROM tbl_events WHERE event_day=$day AND event_month=$month AND event_year=$year AND event_slot=$y";
$query = mysql_query($query);

//echo $row['event_image'];

if(mysql_num_rows($query) > 0){
	$row = mysql_fetch_array($query);
	echo 'event taken';	//here I'll insert the code to display the event
}
else {
	echo 'empty event';
}

 

Is this the correct way in doing it? What will happen if I get let say 100 visitors in the same time and I generate a calendar of events for 2 weeks?

2weeks * 6 events/per day * 100 visitors = 1200 queries to the database...

Isn't it much? Can it take it?

 

Many thanks

[Amit20]

Databases are designed to handle large amount of data. So need not worry about the size of data..

 

If size of your data increases still your database will handle it,  just take care that you have a normalized table so your database responds efficiently.

 

Also when data increases,  your RAM and processor must be well capable on your local machine.

[Amit20]

Hope this code explains you how you can fit your events in their td's

 

while($row=mysql_fetch_array($result))
{
echo "<tr>";									
echo "<td>";
echo $row['Event1'];
echo "</td>";
echo "<td>";
echo $row['Event2'];
echo "</td>";
echo "</tr>";
}

 

 

[adrian_]

Hi,

Thanks for both replies. I was concerned if I generated heavy traffic, how my event calendar will handle. It's good to know I shouldn't really be concerned about it as long as I have a good server

 

For the second part, the idea was to get the event from the database and insert it into its slot: events from 21.00 to 24.00 go into slot 4 (as example). Maybe there aren't any other events during that day so, slots 0 - 3 and 5 will appear empty. The second solution is not a valid one because the above event will be insert into the first position (wrong place) compared to the 4th position (correct place).

 

Thanks again for the replies

 

[Psycho]

NEVER RUN QUERIES IN LOOPS. Database transactions are one of the most expensive operations you can have. I have seen an entire site slow to a crawl because someone tried to generate a report that was built using looping queries.

 

Here is the process you should follow:

 

1. Query ALL the records for the time period you are going to display. Let's say a month. Order the results by date (ascending).

2. Get the current (i.e. first) record from the result set.

2. Build logic in PHP to build the calendar for the entire month

3. In the logic to create each "day" in the calendar, check if the current record retrieved from the result set is for that current day. If so, use it in the output for that day and get the next record. Also, check if the next record is for the current day and repeat until either there are no more records from the result set or the current record is not for the current day.

[adrian_]

Thank you. I think I got what you said and it makes sense as well. That's kinda what I was looking for, as I thought it's not a good practice to query the database for every event.

I'll put some hours into it and I'm pretty sure I can make it work.

 

The second problem I'm facing is passing data from one page to another. I know I cannot display enough information about a particular event into the table, so I want to include a link into the cell which will point to a page in which all the information about that particular event will be displayed.

 

echo '<td><a href="link_to_page_location">Event 21</a></td>'

 

How do I pass all the data, which I already have, from this page to the one I'm point to, in order to display all the necessary information?

 

One way would be to add an id to every cell of the table. This id would be the id of the event from the database. Use javascript to pass it to the next page and then interrogate the database again for that specific id. This would solve my problem but again I think it's not the best solution ever.

I know I could use session variables to pass data from one point to another, but this is not a good practice in this case as I can't uniquely identify a table cell, thus the data being passed from the calendar to the next page. Session variables would be good if I had just one event, but for 10 events?

 

Any ideas are greatly appreciated.

Tanks

[Psycho]

How do I pass all the data, which I already have, from this page to the one I'm point to, in order to display all the necessary information?

 

One way would be to add an id to every cell of the table. This id would be the id of the event from the database. Use javascript to pass it to the next page and then interrogate the database again for that specific id. This would solve my problem but again I think it's not the best solution ever.

I know I could use session variables to pass data from one point to another, but this is not a good practice in this case as I can't uniquely identify a table cell, thus the data being passed from the calendar to the next page. Session variables would be good if I had just one event, but for 10 events?

 

You almost had it right. You would use the record ID, but you don't need JavaScript or anything fancy like that. YOu don't have to pass the event data at all - only the ID. In the code that creates the calendar, you need to create logic for the events to provide a link. You can get fancy and put a link on the TD cells for the days with events, but if you have multiple events for a single day you wouldn't know which one to open. I would simply display the text description for the event in the TD cell as a hyperlink. The URL should look something like this

<a href="show_event.php?id=5">Party at the Hard Rock</a>

 

For each event you create the same link, but using the id of that event as the parameter for the URL. Then you just need to build that page (show_event.php) to access that value on the $_GET array, do a DB query to get the event details and display the info.

[adrian_]

Thanks for everything. It works like charm....for now at least. Still struggling with the calendar though, but the link is wonderful.

Thanks agian.

[Psycho]

Still struggling with the calendar though,...

 

And, what is the problem?