Insert Records Code - FROM_UNIXTIME Error

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Insert Records Code - FROM_UNIXTIME Error

Post by millsy007 »

I have written some code to insert data into my database that will manage a coach schedule. But I am getting the error:
FUNCTION beachh_shuttle.FROM_UNIXTIME does not exist

Code: Select all

<?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 = 'local';
$dn = 'beachh_shuttle';
$du = 'root';
$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;
 
}
?>
 
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Insert Records Code - FROM_UNIXTIME Error

Post by RobertGonzalez »

You are going to have modify your query a little bit. Right now it looks like you are taking a 9, 12, 16 or 22 and multiplying it times 3600 and trying to take that number and add it to a date string. Echo the query out in each loop and you should see something inconsistent with what you really want. FROM_UNIXTIME expects a unix timestamp and will translate that into a regular full data string for you on the database.
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Re: Insert Records Code - FROM_UNIXTIME Error

Post by millsy007 »

I tried doing an echo:

Code: Select all

 
$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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Insert Records Code - FROM_UNIXTIME Error

Post by RobertGonzalez »

What version of MySQL are you running this query against?
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Re: Insert Records Code - FROM_UNIXTIME Error

Post by millsy007 »

I discovered the problem was a missing space in the
mysql_query ("INSERT INTO shuttle VALUES (" . $id . ", FROM_UNIXTIME(" . ( ( $departure_time * 3600 ) + $date_start ) . "))") or die (mysql_error());
line, thanks for the advice
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Re: Insert Records Code - FROM_UNIXTIME Error

Post by millsy007 »

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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Insert Records Code - FROM_UNIXTIME Error

Post by RobertGonzalez »

How are you checking for errors?
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Re: Insert Records Code - FROM_UNIXTIME Error

Post by millsy007 »

Currently the only error checking I have is on the actual sql query - or die ( mysql_error () );
Should I add something else?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Insert Records Code - FROM_UNIXTIME Error

Post by RobertGonzalez »

For development what you have is fine. You never really want mysql_error() being used in production though. Remember that insert, update, delete and a few other queries return a row count when successful or a 0 when not. When you are checking success on those types of queries make sure you are checking the result correctly.
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Re: Insert Records Code - FROM_UNIXTIME Error

Post by millsy007 »

Okay thanks I will add some error checking, but I know that the second sql query is not inserting any rows as I ran a query on the table
mysql_query ( "INSERT INTO journey VALUES ( " . $id . ", " . $route . ", 0 );" );
is not doing anything yet the first query does?

Is there something wrong with the insert statement as it seems to work the same way as the previous
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Insert Records Code - FROM_UNIXTIME Error

Post by RobertGonzalez »

Is each query being sent individually or are you sending them all at once? If you are sending them all at once you will need to buffer your queries or wrap them in a transaction. Otherwise the DB server will respond after the first query and none of the others will get executed.
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Re: Insert Records Code - FROM_UNIXTIME Error

Post by millsy007 »

Currently I am running the whole code at once by saving the webpage as filldatabase.php then opening it online to run the queries.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Insert Records Code - FROM_UNIXTIME Error

Post by RobertGonzalez »

Right, but when the queries are sent are they sent in one long string or is each query sent?
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Re: Insert Records Code - FROM_UNIXTIME Error

Post by millsy007 »

My understanding is that each time

Code: Select all

mysql_query ( "INSERT INTO shuttle VALUES ( " . $id . ", FROM_UNIXTIME ( " . ( ( $departure_time * 3600 ) + $date_start ) . " ) );" ) or die ( mysql_error     () );
Is called the query is run sperately?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Insert Records Code - FROM_UNIXTIME Error

Post by RobertGonzalez »

You're right. One thing to look at also is how many iterations are running. A good debugger would come in real handy in a case like this.
Post Reply