Displaying a schedule

gazzab

Hi, I am currently building a site for a radio website. On the main page I want there to be a line of text saying which is show is currently on air. I have set up a database with the full schedule which consists of a number of different shows throughout the day.

My database fields are:
id
showname
showdetails
day
timestart (time, set as start time of a particular show)
timeend (time, set as end time of a particular show)
showdate (date, default 0000-00-00)

this is what I have so far.. it shows the current time, but does not show what show corresponds to the current time:

$hour = date('H'); 
$minutes = date('i'); 
$newtime = "$hour:$minutes"; 
$day = date('D'); 
echo $newtime; // I used these to make sure i was getting the right time. 
echo "<br><Br>"; 
echo $day; // again, another debug line for the right day. 

$link = mysql_connect('localhost', 'username', 'password'); 
	mysql_select_db("my_databasename", $link);


$query = "SELECT * FROM schedule WHERE showdate = '".$day."' AND 'timestart' <= '".$newtime."' ORDER BY 'timeend' ASC";
$result = mysql_query($query, $link) or die(mysql_error()); 
$row=mysql_num_rows($result); 
for ($j = 0 ; $j < $row ; ++$j)

		{
		echo '<br/>';
		echo '<b>' . mysql_result($result,$j,'showname') . ' - ' . mysql_result($result,$j,'timestart') .'</b><br/>';
		echo '' . mysql_result($result,$j,'timeend') . '<br/><br/>';
		}

mysql_close($Link);

Would really appreciate some help with this. Many thanks.

ixalmida

You've got a field name enclosed in single-quotes in your query, but also your date/time comparisons are flawed. You need to make a conversion, either on the PHP side or on the MySQL side so that the values you are comparing are the same.

Assuming that you want your show schedules to change on a weekly basis (otherwise, you could just compare the numeric representation of the day of week), you probably want the following:

$now = date('H:i:s'); 
$today = date('Y-m-d'); 

$query = "SELECT * FROM schedule WHERE showdate = '$today' ".
  "AND timestart  > '$now' ORDER BY 'timeend' ASC";

If the schedule was the same from week to week, you want the following:

$now = date('H:i:s');
$day = date('w') + 1;          // Note: PHP and MySQL use different day numbers; PHP uses 0-6, MySQL uses 1-7

$query = "SELECT * FROM schedule WHERE DAYOFWEEK(showdate)= '$day' ".
  "AND timestart > '$now' ORDER BY 'timeend' ASC";

gazzab

Thanks for your reply.

The schedule will be the same for each week. I tried both of what you have said and I still do not get any result displayed?

Many thanks

ixalmida

Hmm...are there any errors? Can you insert the following die() statement just to make sure you're getting a handle to the result?

$result = mysql_query($query, $link) or die(mysql_error()); 
die('$result');     // Should give you a numeric output

Something else you could stand to change is how you retrieve your data. You're code is more complicated and inefficient than necessary.

while($row=mysql_fetch_assoc($result))
  {
  extract($row);    // Dump row contents into like-named variables
  echo '<br/>'; 
  echo '<b>$showname - $timestart</b><br/>';
  echo '$timeend<br/><br/>'; 
  }

Note: If there is any possibility of users injecting SQL, then extract() is not safe.

gazzab

I adapted my code to what you have given me, this is what I currently have:

<?php 
$now = date('H:i:s'); 
$day = date('w') + 1;

$link = mysql_connect('localhost', 'CHRdb', 'c1h2r3'); 
	mysql_select_db("CanterburyHospitalRadio", $link);

$query = "SELECT * FROM schedule WHERE DAYOFWEEK(showdate)= '$day' ". 
  "AND timestart > '$now' ORDER BY 'timeend' ASC";

$result = mysql_query($query, $link) or die(mysql_error()); 
die('$result');     // Should give you a numeric output 

while($row=mysql_fetch_assoc($result)) 
  { 
  extract($row);    // Dump row contents into like-named variables 
  echo '<br/>'; 
  echo '<b>$showname - $timestart</b><br/>'; 
  echo '$timeend<br/><br/>'; 
  }

mysql_close($link); 
?>

When I view it all that is displayed is: $result ?

gazzab

Or maybe you know of a completely different way of doing this than what I have?

All I need is one line of text saying the current show that corresponds to the current day and time and the database item of that?

ixalmida

Oops...try something like this:

die("Result link: ".$result);

Again, we're looking for a number for $result. If it isn't displaying a number, then the query isn't returning any results. The next thing to try is:

die($query);

Then copy the query (displayed on the page output) to your MySQL Administrator (or whatever query tool your ISP provides) and try running it directly. If it gives you no results, then you need to start troubleshooting.

When I'm troubleshooting a query, I'll usually just delete one of the conditions and try again until I get the results I'm looking for. When I get some results, I know which condition was hindering the query.

gazzab

Thanks for your reply, I really appreciate this.

I updated my code with:

die("Result link: ".$result);

and I got the result of:

Result link: Resource id#4

What does this mean?

Thanks

bradgrafelman

It means that [man]mysql_query/man returned a resource and not boolean FALSE (indicating that an error occured while executing the query).

Your problem is here:

  extract($row);    // Dump row contents into like-named variables 
  echo '<br/>'; 
  echo '<b>$showname - $timestart</b><br/>'; 
  echo '$timeend<br/><br/>';

Variable interpolation does not occur within single-quoted strings - only double-quoted strings. See this section in the manual for more information.

gazzab

Thanks for your reply. I looked up in the manual, and am now a little confused. Is that for getting data from an array? As the variables I am trying to echo are the field names in my table?

Could you please advise me on how I could do this?

Many thanks

bradgrafelman

Not sure what you're asking... did you read the manual section I linked to? Variables are not parsed in single-quoted strings - only double-quoted strings. Simple example:

$foo = 'Hello world!';

echo 'The message is: $foo'; // The message is: $foo
echo "The message is: $foo"; // The message is: Hello world!

gazzab

Yes I did read it. But basically the variables are the fields in my database table. So how would I define these first?

these are the table fields:
showname
timestart
timeend

does that help?

Many thanks

bradgrafelman

Presumably the variables are already defined after you use [man]extract/man.

gazzab

This is my current code:

$now = date('H:i:s'); 
$day = date('w') + 1;

$link = mysql_connect('localhost', 'user', 'pass'); 
	mysql_select_db("db_name", $link);

$query = "SELECT * FROM schedule WHERE showdate = '$day' ". 
  "AND timestart > '$now' ORDER BY 'timeend' ASC";

$result = mysql_query($query, $link) or die(mysql_error()); 
die("Result link: ".$result);     // Should give you a numeric output 

while($row=mysql_fetch_assoc($result)) 
  { 
  extract($row);    // Dump row contents into like-named variables 
  echo "<br/>"; 
  echo "<b> $showname - $timestart </b><br/>"; 
  echo "$timeend<br/><br/>"; 
  }


mysql_close($link);

And when run all I am still getting displayed is: Result link: Resource id #4

I really am grateful for your replys, but I just dont understand where I am going wrong...?

Thanks

bradgrafelman

You still haven't removed the [man]die/man statement ixmalmida had you insert for debugging purposes.

gazzab

When i remove the die statement nothing is displayed at all?

bradgrafelman

Inside if the while() loop, add this:

print_r($row);

and let us know what it displays.

gazzab

still no output 🙁

bradgrafelman

Show us another fresh copy of the code you have now.

gazzab

$now = date('H:i:s'); 
$day = date('w') + 1;

$link = mysql_connect('localhost', 'user', 'pass'); 
	mysql_select_db("db_name", $link);

$query = "SELECT * FROM schedule WHERE showdate = '$day' ". 
  "AND timestart > '$now' ORDER BY 'timeend' ASC";

$result = mysql_query($query, $link) or die(mysql_error()); 
//die("Result link: ".$result);     // Should give you a numeric output 

while($row=mysql_fetch_assoc($result)) 
  { 
  extract($row);    // Dump row contents into like-named variables 
  print_r($row); 
  echo "<br/>"; 
  echo "<b> $showname - $timestart </b><br/>"; 
  echo "$timeend<br/><br/>";   

  }

mysql_close($link);