PHP Coding Help

[CurtisKenny]

Ok before i begin, im very new at php and dont flame me if i've done something stupidly wrong...


Basically, im in charge of the website for new community radio station Bolton FM and need a php script to say the on air DJ (before i go any further, please DO NOT suggest a dj panel to me).

I want to hook this php script up to a database with the tables "Mon", "Tue", etc with the columns "Showbegin", "Showend" and "Presenter" to pull up the current presenter.

Here is my code:

<?php 

$query = "SELECT showbegin, showend, presenter 
FROM date('D') 
WHERE showbegin < curtime('G:i') 
AND showend > curtime('G;i')"; 

$result = mysql_query($query); 
if (!$result) { 
    //do error processing (log error, etc) 
    echo "query failure!".mysql_error(); 
    die(); 
} 

$showInfo = mysql_fetch_assoc($result); ?>

 
<div class="show"> 
<p>Currently airing: <?php echo $showInfo['presenter'] ?></p>

(i have my connection info at the top of the page, so no, it isnt missing)

I get the error

query failure!You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '() WHERE showbegin < time(G:i) AND showend > time(G:i)' at line 2

Could anyone please suggest something?
As i said, please be patient with me, im a bit inexperienced in php...

Moved by ReviewDude (Forum Moderator) from "Web Design", as this is an issue related to PHP code, not its use within design.

[JAK06]

What version of MySQL are you using ?

[CurtisKenny]

PHP version 5.2.5
MySQL version 5.0.67-community

[wsg14]

$query = "SELECT showbegin, showend, presenter 
FROM date('D') 
WHERE showbegin < curtime('G:i') 
AND showend > curtime('G;i')"; 

$result = mysql_query($query);  

There's no need to make two variables for a mysql_query, you can do it all in one and it gets rid of unneeded code:

$result = mysql_query("SELECT * FROM date('D') WHERE showbegin < curtime('G:i'') AND showend > curtime ('G:i')");  

-------

if (!$result) { 
    //do error processing (log error, etc) 
    echo "query failure!".mysql_error(); 
    die(); 
}  

I don't think your viwers would want to see a mysql error, so just make a stupid phrase saying "There is a problem" or something like that. Why are you using die()? There's no need for it to be there as you're not trying to exit the script. You're also going to want to do an else{} as if you don't even if there is an error in your mysql the page is still going to try to display the currently on air text. Your new code:

if(!$result){  
    echo "onoes! There is a problem!"; 
} else {  

-------

$showInfo = mysql_fetch_assoc($result);  

For what you're doing mysql_fetch_assoc would work, as you haven't given any specific details on what to fetch from the database, just two times.

-------

<div class="show"> 
<p>Currently airing: <?php echo $showInfo['presenter'] ?></p>

There's nothing wrong with that.

-------

So here's your complete new code:

<?php $result = mysql_query("SELECT * FROM date('D') WHERE showbegin < curtime('G:i'') AND showend > curtime ('G:i')");

if(!$result){  
    echo "onoes! There is a problem!"; 
} else {
    $showInfo = mysql_fetch_assoc($result);
?>
<div class="show"> 
<p>Currently airing: <?php echo $showInfo['presenter'] ?></p>
<?php } ?>

I haven't tested it but that should work, let me know if you get any errors.

[CurtisKenny]

Thank you very much wsg14 for your detailed explanation, i understand it a bit more now.

Here is my current code:

<?php 
$day = date('D');
$result = mysql_query("SELECT * FROM `$day` WHERE showbegin < date('G:i') AND showend > date('G:i')");

if(!$result){  
    echo "There has been an error in retrieving the current presenter. "; 
} else {
    $showInfo = mysql_fetch_assoc($result);
?>


<div class="show"> 
<p>Currently airing: <?php echo $showInfo['presenter'] ?></p>
<?php } ?>

I had to add the date into the $day variable to get it to work, and change curtime to date(G:i)



At the moment it does not display the presenter. All i get back is:
Currently airing: (blank space)

Would this have something to do with my database?

At the moment i have 7 tables each named Mon Tue Wed Thu Fri Sat & Sun
Each table has 6 collumns:

ID (int,auto_increment_
showbegin (time)
showend (time)
presenter (text)
photo (text)
profile (text)

With photo and profile being added into the script later on...

[wsg14]

Get rid of: AND showend > date('G:i') in your mysql_query.

[CurtisKenny]

I've got it working now!

I figured out i had to use curtime() instead of date(G:i) and it works perfectly now.

Here is my final code:

<?php 
$day = date('D');
$result = mysql_query("SELECT * FROM `$day` WHERE showbegin < curtime() AND showend > curtime()");

if(!$result){  
    echo "There has been an error in retrieving the current presenter. "; 
} else {
    $showInfo = mysql_fetch_assoc($result);
?>


<div class="show"> 
<p>Current Presenter: <?php echo $showInfo['presenter'] ?></p>
<?php } ?>
</div>

Thanks for all your help!

[wsg14]

lad you figured it out.

[CurtisKenny]

lad you figured it out.

I did

Ive just tired adding a different entry in the database for 18:50 and it changed it perfectly.

I think im going to have to get Bolton FM to send me on a php course -facepalm- haha


Thanks again.