Page 1 of 2

Insert Records Code - FROM_UNIXTIME Error

Posted: Thu Feb 05, 2009 3:21 pm
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;
 
}
?>
 

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Thu Feb 05, 2009 4:58 pm
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.

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Fri Feb 06, 2009 4:19 am
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?

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Fri Feb 06, 2009 10:13 am
by RobertGonzalez
What version of MySQL are you running this query against?

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Fri Feb 06, 2009 4:02 pm
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

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Sat Feb 07, 2009 9:02 am
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?

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Sat Feb 07, 2009 12:27 pm
by RobertGonzalez
How are you checking for errors?

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Sat Feb 07, 2009 2:46 pm
by millsy007
Currently the only error checking I have is on the actual sql query - or die ( mysql_error () );
Should I add something else?

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Sat Feb 07, 2009 8:33 pm
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.

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Sun Feb 08, 2009 5:09 am
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

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Sun Feb 08, 2009 4:22 pm
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.

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Sun Feb 08, 2009 4:27 pm
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.

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Sun Feb 08, 2009 4:35 pm
by RobertGonzalez
Right, but when the queries are sent are they sent in one long string or is each query sent?

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Sun Feb 08, 2009 4:46 pm
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?

Re: Insert Records Code - FROM_UNIXTIME Error

Posted: Sun Feb 08, 2009 7:16 pm
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.