UNIXTIME Error

[millsy007]

I am trying to run a script to populate my tables:

 

<?php

$date_start = '01/30/2009';

$date_end = '12/31/2009';

$runs_per_day = array ( 9, 12, 16, 22 );

$routes = array ( 1, 2, 3, 4, 5 );

$dh = 'localhost';
$dn = 'coach';
$du = 'user';
$dp = 'pass';



mysql_connect ( $dh, $du, $dp ) or die ( mysql_error () );



mysql_select_db ( $dn );




$date_start = strtotime ( $date_start );



$date_end = strtotime ( $date_end );



$id = 1;



while ( $date_start <= $date_end )



{





foreach ( $runs_per_day AS $departure_time )





{







mysql_query ( "INSERT INTO Shuttle VALUES ( " . $id . ", FROM_UNIXTIME ( " . ( ( $departure_time * 3600 ) + $date_start ) . " ) );" ) or die ( mysql_error () );







foreach ( $routes AS $route )







{









mysql_query ( "INSERT INTO journey VALUES ( " . $id . ", " . $route . ", 0 );" );







}







$id += 1;





}





$date_start += 86400;



}
?>

 

However I get the error: FUNCTION beachh_shuttle.FROM_UNIXTIME does not exist

[Mark Baker]

I'd double check the line that includes:

"INSERT INTO Shuttle VALUES ( " . $id . ", FROM_UNIXTIME 

and check that you do have a comma and not a dot before FROM_UNIXTIME

[millsy007]

Thanks but I definately have a comma. I tried doing an echo:

 

$sql="INSERT INTO shuttle VALUES ( " . $id . ", FROM_UNIXTIME ( " . ( ( $departure_time * 3600 ) + $date_start ) . " ) )";
echo $sql;
mysql_query ($sql) or ;

 

And I got:

INSERT INTO shuttle VALUES ( 1, FROM_UNIXTIME ( 1233324000 ) )INSERT INTO shuttle VALUES ( 2, FROM_UNIXTIME ( 1233334800 ) )INSERT INTO shuttle VALUES ( 3, FROM_UNIXTIME ( 1233349200 ) ) ..... etc

 

I am a little confused by the date formatting what I need is there to be a record of each shuttle trip, of which there are 4 daily at 9:00 12:00 16:00 and 22:00 How would I get these times into the correct format?

[Mark Baker]

So next question.

 

What version of MySQL are you running?

[millsy007]

MySQL client version: 4.1.22

Server version: 5.0.51a-community

 

Is that what you mean?

[PFMaBiSmAd]

I recommend removing the space that is between the function name and the parentheses -

 

change this:  FROM_UNIXTIME (

to this: FROM_UNIXTIME(

 

White space should not matter, but perhaps you found a bug in the sql parser.

[PFMaBiSmAd]

There is a white-space problem. I removed all the excess white-space and the ; on the end of the sql statement and the following query recognizes the FROM_UNIXTIME function -

 

mysql_query ("INSERT INTO Shuttle VALUES (" . $id . ", FROM_UNIXTIME(" . ( ( $departure_time * 3600 ) + $date_start ) . "))") or die (mysql_error());

 

[Mark Baker]

Well spotted PFMaBiSmAd

[millsy007]

Hi this now runs, thanks for the help 

However part of the query isnt doing what it should so any advice would be great. What I expected to happen was for each shuttle 5 journeys take place, I wanted to create a record for each of these jouneys by insertiting the 'shuttle_id', 'route_id' and 'occupancy' in a 'journey' table, however nothing is going in and I get no error?

 

the journey table has

id

shuttle_id

route_id

occupancy

 

should i specify this in the insert statement?