[SOLVED] MySQL display data relevant to time of day?

[Highland3r]

not sure if I'm trying to be too adventures but what i would like to do is have an area of my site which displays a different id in the same mySQL table at different times of the day, IE say a breakfast show had a DJ on at 9o'clock then at 10o'clock the DJ changed could i create a code that would automatically update the now playing DJ for me ?

[ngreenwood6]

yes you can. I would look into the date() function. do you know the times that the dj are going to be playing or how would you like it to update?

[Highland3r]

this is a part of mySQL and php im not too sure on the time() date() functions i know the times of the day the djs will be playing but the djs will change depending the day of the week but the djs will be the same time every week could you offer a tutorial script for me to see or something ?

[ngreenwood6]

If you can elaborate more on your set up or show me some code I can help you with the date function in the meantime check this out: http://www.tizag.com/phpT/phpdate.php

[Highland3r]

thanks for the suggested site i have looked their has been great help i think im getting the hang of php and mySQL thanks to you ngreenwood6

[Highland3r]

i thought i had solved the problem but appears not it works in one sense when you add the times etc it displays them in order soonest time first so arranges them like 10:00, 10:15, 10:30, ETC... but when 10:15 come it leaves the time 10:00 still showing here's the code i have so far

 

<?php

// Require the database class

require_once('includes/DbConnector.php');

// Create an object (instance) of the DbConnector

$connector = new DbConnector();

// Execute the query to retrieve articles

$result = $connector->query('SELECT ID,name,photo,time,date FROM cmsschedule order by time ASC LIMIT 5');

// Get an array containing the results.

// Loop for each item in that array

while ($row = $connector->fetchArray($result)){

echo $row['name'];

echo $row['photo'];

echo '<br>on at ';

echo $row['time'];

echo '<hr /></a> </p>';

}

?>

[ngreenwood6]

I think that you are missing a where clause:

 

SELECT * FROM table WHERE time < '$time'

 

that will pick the time that is less that a variable time.

[Highland3r]

i have entered the code suggested but returns an error message of

"Parse error: syntax error, unexpected T_VARIABLE in /home/sites/mydomain.co.uk/public_html/index.php on line 136"

 

<?php
// Require the database class
require_once('includes/DbConnector.php');
// Create an object (instance) of the DbConnector
$connector = new DbConnector();
// Execute the query to retrieve articles
$time = $_GET["time"];

$result = $connector->query('SELECT * FROM cmsschedule WHERE time < '$time');
// Get an array containing the results.
// Loop for each item in that array
while ($row = $connector->fetchArray($result)){
echo $row['name'];
echo $row['photo'];
echo '<br>on at ';
echo $row['time'];
echo '<hr /></a> </p>';
}
?>

 

[ngreenwood6]

sorry I think you misunderstood, that was just an example. what are you storing in the time field? is it a timestamp or what?

[Highland3r]

in the time field i m storing the dj start time ?

[Highland3r]

anyone?

[ngreenwood6]

This is a little more confusing than you think it is. What do you store in the time field? Is it a timestamp (looks like this 130949832) or an actual time(10:00)? Also where does the time for the dj's get put into the database?

[Highland3r]

at the moment i have been manually entering the time into the time field on the database entered as the appropriate time IE time would equal 10:00

[Highland3r]

Anybody?

[ngreenwood6]

You could try this:

 

<?php
// Require the database class
require_once('includes/DbConnector.php');

// Create an object (instance) of the DbConnector
$connector = new DbConnector();

// Set the time
$start_time = time();
$time = date("H:i",$start_time) //this makes it 24 hour time (1 o'clock will be 13 and disply like 13:00) 

// Execute the query to retrieve articles
$result = $connector->query('SELECT ID,name,photo,time,date FROM cmsschedule WHERE time >= '$time' order by time ASC LIMIT 5');

// Get an array containing the results.
// Loop for each item in that array
while ($row = $connector->fetchArray($result)){
echo $row['name'];
echo $row['photo'];
echo '<br>on at ';
echo $row['time'];
echo '<hr /></a> </p>';
}

?> 

 

I cannot guarantee that this will work because you are not storing the time with the correct values. But in order for this to work you will need to use 24 hour time when you store it in the database. For example if you wanted something to show after noon it will start with 13 for 1 o'clock pm. When you put them in the database make sure you use 24 hour time. I did this so that this example might actually work with your set-up (not sure though). Please let me know the results.

[Highland3r]

i have used the code provided i can slightly understand what it is doing but i am given this error

 

Parse error: syntax error, unexpected T_VARIABLE in /home/sites/mydomain.co.uk/public_html/index.php on line 140

 

the line in question is

 

$result = $connector->query('SELECT ID,name,photo,time,date FROM cmsschedule WHERE time >= '$time' order by time ASC LIMIT 5');

 

any help appreciated

[ngreenwood6]

try this:

 

$result = $connector->query("SELECT ID,name,photo,time,date FROM cmsschedule WHERE time >= 'time' order by time ASC LIMIT 5");

[Highland3r]

have tried the new code but still getting the same error ? any suggestions ?

[ngreenwood6]

Sorry I forgot the $ in time:

 

$result = $connector->query("SELECT ID,name,photo,time,date FROM cmsschedule WHERE time >= '$time' order by time ASC LIMIT 5");

[Highland3r]

nah wasnt that tried that already canna seem to see why its not working it should be :S any ideas?

the error i get is

Parse error: syntax error, unexpected T_VARIABLE in /home/sites/mydomain.co.uk/public_html/index.php on line 140

 

this reffers to the bit of code above

 

$result = $connector->query("SELECT ID,name,photo,time,date FROM cmsschedule WHERE time >= '$time' order by time ASC LIMIT 5");

[ngreenwood6]

Can you post all of your code from this page?

[Highland3r]

<?php
// Require the database class
require_once('includes/DbConnector.php');

// Create an object (instance) of the DbConnector
$connector = new DbConnector();

// Set the time
$start_time = time();
$time = date("H:i",$start_time) //this makes it 24 hour time (1 o'clock will be 13 and disply like 13:00) 

// Execute the query to retrieve articles
$result = $connector->query("SELECT ID,name,photo,time,date FROM cmsschedule WHERE time >= '$time' order by time ASC LIMIT 5"));


// Get an array containing the results.
// Loop for each item in that array
while ($row = $connector->fetchArray($result)){
echo $row['name'];
echo $row['photo'];
echo '<br>on at ';
echo $row['time'];
echo '<hr /></a> </p>';
}

?>

[ngreenwood6]

Forgot a ; on the line before it

 

try this

<?php
// Require the database class
require_once('includes/DbConnector.php');

// Create an object (instance) of the DbConnector
$connector = new DbConnector();

// Set the time
$start_time = time();
$time = date("H:i",$start_time); //this makes it 24 hour time (1 o'clock will be 13 and disply like 13:00) 

// Execute the query to retrieve articles
$result = $connector->query("SELECT ID,name,photo,time,date FROM cmsschedule WHERE time >= '$time' order by time ASC LIMIT 5"));


// Get an array containing the results.
// Loop for each item in that array
while ($row = $connector->fetchArray($result)){
echo $row['name'];
echo $row['photo'];
echo '<br>on at ';
echo $row['time'];
echo '<hr /></a> </p>';
}

?>

[Highland3r]

thanks m8 once again i ow you one thanks for all your help its appreciated

[ngreenwood6]

No problem. Most of the time when you get errors look on the line that it is referring to and up 2 lines and down 2 lines and you will find the problem more than likely. Did this work for you?