[SOLVED] Calender - SQL and while loop

[Drainy]

Hi,

 

First post here but i have read through topics in the past.

Hope someone can give me some advice really.

I am trying to develop a calender function to help people book dates, reserve dates, keep track of bookings etc. It is in the very very early stages and at the moment I am dealing with a few of the conceptual ideas in my mind.

One view to developing this is to use PHP to dynamically generate the calender each time based on the date of the server itself and to then grab the data required from an SQL server.

 

For example;

SQL database with tables consisting of users, bookings and then a table for each month. Inside each month it will have a unique ID (PK) and then a date, year and bid (foreign key from booking) - the year field is to allow the same table to be used for many years of january bookings.

 

My issue, if I dynamically generate the table then within the while loop I will have to use a variable,  say $currentDay, to query the SQL database with (assuming ive already set the month as january and identified it as the table to search). Will running a query like this around 28-31 times as the page is loaded realistically far far too in-efficient.

I already understand that its not going to be the most effecient way to query the database but I dont know to what extent.

 

Thanks for any help or suggestions.

[dvd420]

Hi,

 

I am unable to get a clear picture of the problem, but I think you should implement the logic in SQL rather than PHP.

 

Some better answers can be given if some more details are available.

[Drainy]

Hi,

 

Well im trying to decide if it will be a problem.

Basically I am planning a while loop with a variable set for the day of the month.

It will start at 1 and inside the while loop it will print a table box with the day of the month in it.

This will execute until it reaches the last day of the month.

My issue is that inside this loop I want to put a query that makes PHP run and query my SQL database each time to check if that day of the month has an entry in the month table and if it does it would change the colour or do something - that part isnt so important. What concerns me is that it will be running 28-31 queries on the database in very quick succession.

Is this neccessarily bad or is there a more effecient way I could do this which Im missing completely?

 

[dvd420]

Hi,

 

So as far as I can get the issue, each day is having different records, so anyhow you have to query the database for each day. However, you can write the query without any day constrain and you can fetch all the records fro all the days in single query. The result will be an array. Run a while loop for this array.

 

Hope the following stuff can help you

 

//Connect to db server
$link = mysql_connect('SERVER', 'USER', 'PASSWORD');


//Select the db
$db_selected = mysql_select_db('DB_NAME', $link);


//This is the query
$query = "SELECT firstname, lastname, address, age FROM friends;";

//execute the query once, $result is an array
$result = mysql_query($query, $link);


//Loop for the results
while ($row = mysql_fetch_assoc($result))
{
    echo $row['firstname'];
    echo $row['lastname'];
    echo $row['address'];
    echo $row['age'];
}

mysql_free_result($result);

[dvd420]

If you are using some other database, you have to look for the corresponding functions. I have never worked with SQL Server.

 

If you are using Oracle databse with OCI then it'll look like:

 

$conn = oci_connect("username","password","host_string");

$query = "SELECT firstname, lastname, address, age FROM friends";

//Prepare the qry to be executed
$stid = oci_parse($conn, $query);


$rerult = oci_execute($stid, OCI_DEFAULT);	// Execs the stmt
while ($row = oci_fetch_assoc($stid))
{
foreach ($row as $item)
{
	echo "$item\t";
	echo "\n";
}
}

 

foreach is yet another construct to access arrays. WHILE loop can also be used.

[Drainy]

Thanks for that, think its helped me think through another way to go about this.