0

After i execute this code the two tables with 2 relations is not made or ignored.

Tables trips and stop_times is the once that is ignored. This is what shows in phpmyadmin: enter image description here

Any ideas people??

Here is my PHP code.

 $dbName = $dbname;

 // SQL Queries to create all needed tables to fill with data!
 // Don't change any of this unless you know what you're doing.

 $createDB = "
 CREATE SCHEMA `$dbName` ;
 ";

 $sqlagency="CREATE  TABLE agency (
   agency_id INT(11) NOT NULL DEFAULT '0' ,
   agency_name VARCHAR(255) NULL DEFAULT NULL ,
   agency_url VARCHAR(255) NULL DEFAULT NULL ,
   agency_timezone VARCHAR(255) NULL DEFAULT NULL ,
   agency_lang VARCHAR(255) NULL DEFAULT NULL ,
   agency_phone VARCHAR(255) NULL DEFAULT NULL ,
   PRIMARY KEY (`agency_id`) )
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = latin1;";

 $sqlcalendar="CREATE  TABLE calendar (
   service_id VARCHAR(25) NULL DEFAULT NULL ,
   monday TINYINT(1) NULL DEFAULT NULL ,
   tuesday TINYINT(1) NULL DEFAULT NULL ,
   `wednesday` TINYINT(1) NULL DEFAULT NULL ,
   `thursday` TINYINT(1) NULL DEFAULT NULL ,
   `friday` TINYINT(1) NULL DEFAULT NULL ,
   `saturday` TINYINT(1) NULL DEFAULT NULL ,
   `sunday` TINYINT(1) NULL DEFAULT NULL ,
   `start_date` VARCHAR(255) NULL DEFAULT NULL ,
   `end_date` VARCHAR(255) NULL DEFAULT NULL ,
   `start_date_timestamp` INT(11) NULL DEFAULT NULL ,
   `end_date_timestamp` INT(11) NULL DEFAULT NULL )
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = latin1;";

 $sqlcalendar_dates="CREATE  TABLE calendar_dates (
   `service_id` VARCHAR(25) NULL DEFAULT NULL ,
   `date` VARCHAR(255) NULL DEFAULT NULL ,
   `date_timestamp` INT(11) NULL DEFAULT NULL ,
   `exception_type` INT(2) NULL DEFAULT NULL )
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = latin1;";

 $sqlroutes="CREATE  TABLE routes (
   `route_id` INT(11) NOT NULL DEFAULT '0' ,
   `agency_id` INT(11) NULL DEFAULT NULL ,
   `route_short_name` VARCHAR(255) NULL DEFAULT NULL ,
   `route_long_name` VARCHAR(255) NULL DEFAULT NULL ,
   `route_type` INT(2) NULL DEFAULT NULL ,
   `route_text_color` VARCHAR(255) NULL DEFAULT NULL ,
   `route_color` VARCHAR(255) NULL DEFAULT NULL ,
   `route_url` VARCHAR(255) NULL DEFAULT NULL ,
   `route_desc` VARCHAR(255) NULL DEFAULT NULL ,
   PRIMARY KEY (`route_id`) ,
   INDEX `fk_routes_agency1` (`agency_id` ASC) ,
   CONSTRAINT `fk_routes_agency1`
     FOREIGN KEY (`agency_id` )
     REFERENCES `agency` (`agency_id` )
     ON DELETE NO ACTION
     ON UPDATE NO ACTION)
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = latin1;";

 $sqlshapes="CREATE  TABLE shapes (
   `shape_id` INT(15) NOT NULL DEFAULT '0' ,
   `shape_pt_lat` DECIMAL(8,6) NULL DEFAULT NULL ,
   `shape_pt_lon` DECIMAL(8,6) NULL DEFAULT NULL ,
   `shape_pt_sequence` INT(11) NULL DEFAULT NULL ,
   PRIMARY KEY (`shape_id`) )
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = latin1;";

 $sqltrips="CREATE  TABLE trips (
   `route_id` INT(11) NULL DEFAULT NULL ,
   `service_id` VARCHAR(25) NULL DEFAULT NULL ,
   `trip_id` INT(11) NOT NULL DEFAULT '0' ,
   `trip_headsign` VARCHAR(255) NULL DEFAULT NULL ,
   `trip_short_name` VARCHAR(255) NULL DEFAULT NULL ,
   `direction_id` TINYINT(1) NULL DEFAULT NULL ,
   `block_id` INT(11) NULL DEFAULT NULL ,
   `shape_id` INT(11) NULL DEFAULT NULL ,
   PRIMARY KEY (trip_id) ,
   INDEX fk_trips_routes1 (route_id ASC) ,
   INDEX fk_trips_calendar_dates1 (service_id ASC) ,
   CONSTRAINT fk_trips_routes1
     FOREIGN KEY (route_id)
     REFERENCES routes (route_id)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION,
   CONSTRAINT fk_trips_calendar_dates1
     FOREIGN KEY (service_id)
     REFERENCES calendar_dates(service_id)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION)
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = latin1;";

 $sqlstops="CREATE  TABLE stops (
   `stop_id` INT(11) NOT NULL DEFAULT '0' ,
   `stop_code` VARCHAR(255) NULL DEFAULT NULL ,
   `stop_name` VARCHAR(255) NULL DEFAULT NULL ,
   `stop_desc` VARCHAR(255) NULL DEFAULT NULL ,
   `stop_lat` DECIMAL(8,6) NULL DEFAULT NULL ,
   `stop_lon` DECIMAL(8,6) NULL DEFAULT NULL ,
   `zone_id` INT(11) NULL DEFAULT NULL ,
   `stop_url` VARCHAR(255) NULL DEFAULT NULL ,
   `location_type` INT(2) NULL DEFAULT NULL ,
   `parent_station` INT(11) NULL DEFAULT NULL ,
   PRIMARY KEY (`stop_id`) )
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = latin1;";

 $sqlstop_times="CREATE  TABLE stop_times (
   `trip_id` INT(11) NULL DEFAULT NULL ,
   `arrival_time` TIME NULL DEFAULT NULL ,
   `arrival_time_seconds` INT(11) NULL DEFAULT NULL ,
   `departure_time` TIME NULL DEFAULT NULL ,
   `departure_time_seconds` INT(11) NULL DEFAULT NULL ,
   `stop_id` INT(11) NULL DEFAULT NULL ,
   `stop_sequence` INT(11) NULL DEFAULT NULL ,
   `stop_headsign` VARCHAR(255) NULL DEFAULT NULL ,
   `pickup_type` INT(2) NULL DEFAULT NULL ,
   `drop_off_type` INT(2) NULL DEFAULT NULL ,
   `shape_dist_traveled` VARCHAR(255) NULL DEFAULT NULL ,
   INDEX `fk_stop_times_trips` (`trip_id` ASC) ,
   INDEX `fk_stop_times_stops1` (`stop_id` ASC) ,
   CONSTRAINT `fk_stop_times_trips`
     FOREIGN KEY (`trip_id` )
     REFERENCES trips (`trip_id` )
     ON DELETE NO ACTION
     ON UPDATE NO ACTION,
   CONSTRAINT `fk_stop_times_stops1`
     FOREIGN KEY (`stop_id` )
     REFERENCES stops (`stop_id` )
     ON DELETE NO ACTION
     ON UPDATE NO ACTION)
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = latin1;";

     // Creates and selects DB
     mysql_query($createDB);
     mysql_select_db($dbName);

     // Create the tables for GTFS data

     mysql_query($sqlagency);
     mysql_query($sqlcalendar);
     mysql_query($sqlcalendar_dates);
     mysql_query($sqlroutes);
     mysql_query($sqlshapes);
     mysql_query($sqltrips);
     mysql_query($sqlstops);
     mysql_query($sqlstop_times);
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
Andre Mesel
  • 113
  • 1
  • 9
  • Is it possible that i need to add this: SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; ? – Andre Mesel Apr 19 '13 at 14:32
  • When I tried your MySQL code I got the following error on the `trips` table: `ERROR 1005 (HY000): Can't create table 'test.trips' (errno: 150)`. There's a StackOverflow post about `errno 150` [here](http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150). Check the top answer for a list of conditions that can cause this error. – Ed Gibbs Apr 19 '13 at 14:41
  • thank you very much, i will check it out. iam getting no errors when i execute it through my webpage it's just ignored. – Andre Mesel Apr 19 '13 at 14:44
  • That will depend on your page and how it handles errors. I've seen lots of pages that ignore errors or log them to some obscure location. With so much MySQL code, I'd recommend testing it out in Workbench or the MySQL command line first if you can - you'll get great feedback and probably save a lot of time. Good luck! – Ed Gibbs Apr 19 '13 at 14:47
  • good idea, iam running it in workbench now and your right iam getting errors:P ty – Andre Mesel Apr 19 '13 at 14:55

0 Answers0