[hall882006]

Im having trouble with my database for my program im trying to implement a Bus Schedule and i have no idea how to go about linking these tables let alone the information for each table. All i know is that i have to have a Bus Id, Bus Name, day of the week for the schedule Ex: weekend weekday, also whether is going inbound or outbound, and last the streets that it stops at with its times throughout the day. My problem is that this is alot of information to link together as one and i dont know how to go about doing this. I have started a little bit but i didnt get far heres what i have.....

CREATE TABLE IF NOT EXISTS Bus_Schedule (
	_id VARCHAR(5) PRIMARY KEY , 
	BusName VARCHAR(35))

CREATE TABLE IF NOT EXISTS time_table (
	_id VARCHAR(5), 
	dayOfWeek VARCHAR(10),
	streetName VARCHAR(35),
	direction VARCHAR(35),
	timesList VARCHAR(300))

The problem i have most concerned with is that say i have a record for any bus how can i link everything so i can retreive it later fast and easy once i input data for the database i think my setup is all wrong that's where my problem lies... EX:

INSERT INTO Bus_Schedule VALUES('1','Freeport Road')

INSERT INTO time_table VALUES('1','Weekend','Borrow Rd','Towards town','8 830 845 900 915 930 945')

[baavgai]

Do NOT put lists in fields. Ever, ever, ever. You make another table, if you have to.

CREATE TABLE IF NOT EXISTS BusSchedule (
	-- why would this by varchar
	-- also, auto increment ( varies according to db )
	-- and what's with an underscore?
	-- _id VARCHAR(5) PRIMARY KEY , 
	BusId integer primary key,
	BusName VARCHAR(35) NOT NULL
)

CREATE TABLE IF NOT EXISTS TimeTable (
	-- is this just two values?
	-- perhaps you want some kind of boolean representation?
	BusId integer,
	dayOfWeek VARCHAR(10), 
	time integer,
	streetName VARCHAR(35),
	direction VARCHAR(35),
)

INSERT INTO BusSchedule VALUES(1,'Freeport Road')
INSERT INTO TimeTable VALUES(1,'Weekend',0800,'Borrow Rd','Towards town')
INSERT INTO TimeTable VALUES(1,'Weekend',0830,'Borrow Rd','Towards town')
INSERT INTO TimeTable VALUES(1,'Weekend',0845,'Borrow Rd','Towards town')
INSERT INTO TimeTable VALUES(1,'Weekend',0900,'Borrow Rd','Towards town')
INSERT INTO TimeTable VALUES(1,'Weekend',0915,'Borrow Rd','Towards town')
INSERT INTO TimeTable VALUES(1,'Weekend',0930,'Borrow Rd','Towards town')
INSERT INTO TimeTable VALUES(1,'Weekend',0945,'Borrow Rd','Towards town')

Now you ask questions like, where will bus 1 be between 8 and 9. When is the next time the bus will pass X road, etc.

Direction is kind of odd. You probably don't need it. You need to know the next stop...

I might do something like have a table of stops.

CREATE TABLE IF NOT EXISTS BusStop (
	BusStopId integer primary key,
	streetName VARCHAR(35)
)

CREATE TABLE IF NOT EXISTS TimeTable (
	BusId integer,
	dayOfWeek VARCHAR(10), 
	StopTime integer,
	BusStopId integer
)

INSERT INTO BusStop VALUES(1,'Borrow Rd')
INSERT INTO BusStop VALUES(2,'Main St')
INSERT INTO BusStop VALUES(3,'Park Ave')

INSERT INTO TimeTable VALUES(1,'Weekend',0800, 1)
INSERT INTO TimeTable VALUES(1,'Weekend',0805, 2)
INSERT INTO TimeTable VALUES(1,'Weekend',0820, 3)
INSERT INTO TimeTable VALUES(1,'Weekend',0830, 1)

You can figure out prior and next stops from this data.

Hope this helps.

[hall882006]

yes the direction would have just two values it would either be going towards town or away from town, and the _id for bus schedule was varchar because on one of the buses i have to put _id is 'BLSV' and the TIME is what was messing my code up thanks alot i didnt know you couldnt put them in a list like that. For direction would i just have a separate table or it would be a boolean. I didnt understand what you meant by that.

[baavgai]

Simply, if you know the current stop, you should be able to figure out the next one.

-- Current Stop
select *
   from TimeTable
   where BusId=1 and dayOfWeek='Weekend' and StopTime=0805;

BusId dayOfWeek StopTime BusStopId
1     'Weekend'     0805         2

-- Next Stop Time
select min(StopTime) as NextStopTime
   from TimeTable
   where BusId=1 and dayOfWeek='Weekend'  and StopTime>0805;

NextStopTime
0820

-- Next Stop
select *
   from TimeTable
   where BusId=1 and dayOfWeek='Weekend' and StopTime=0820;

BusId dayOfWeek StopTime BusStopId
1     'Weekend'     0820         3

If you like, you could have a "NextStop" column in the table and chain them together before hand, to make easier selects. In which case, you'd want a single numeric primary key.

This post has been edited by baavgai: 02 August 2011 - 06:12 AM

[hall882006]

I having trouble figuring out why my times wont display in my last list i have no errors but once i hit my button to go to my times it wont display anything. I thought it was because i was running out of memory, but now i have that issue fixed it still displays nothing. I'm trying to display the information for my sql statements and the first few list seems to work fine i think. Its just once i get to my last list it doesn't do anything. Here is what im trying to accomplish: Theres two tables one table has info for the bus and next table has info for the times. First screen displays the list of the buses. then i pass the _id variable to the next xml view.

  cursor = db.rawQuery("SELECT _id, busId, BusName FROM Bus_Schedule", new String[]{});   

        intent.putExtra("BUS_ID", cursor.getInt(cursor.getColumnIndex("_id")));

Then i use this extra to match up the _id of the bus with the _id of the time table

Cursor cursor = db.rawQuery
        (
        "SELECT A2._id, A2.dayOfWeek " +
        "FROM TimeTable A2 " + 
        "WHERE A2._id = ? " +
        "GROUP BY A2.dayOfWeek", 
				
        
        new String[]{""+idNum}); 

Then pass this _id of this dayOfWeek to next xml. I do this for the day, direction, and street. All this displays fine. Now i want the time to correspond to each button push from before so the time that displays should be the time for the Bus pushed, the day pushed, the direction, pushed, and the street pushed, and then display the times for this. Maybe what im getting wrong is that for the last extra i used for times does this tie everything back to each other button pushed or just the last button. here is my times statement.

 "SELECT A2._id, A2.time " +
        "FROM TimeTable A2 " + 
        "WHERE A2._id = ? " ,
     

This post has been edited by hall882006: 18 September 2011 - 05:37 PM

[hall882006]

Now i tried to change my code around and it gives me errors now:

09-18 23:31:26.846: ERROR/AndroidRuntime(28776): FATAL EXCEPTION: main
09-18 23:31:26.846: ERROR/AndroidRuntime(28776): java.lang.IllegalStateException: get field slot from row 0 col -1 failed
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at android.database.Cursorwindow.getLong_native(Native Method)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at android.database.Cursorwindow.getInt(Cursorwindow.java:466)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at android.database.AbstractWindowedCursor.getInt(AbstractWindowedCursor.java:93)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at com.bus.schedule.Street.onListItemClick(Street.java:76)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at android.app.ListActivity$2.onItemClick(ListActivity.java:321)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at android.widget.AdapterView.performItemClick(AdapterView.java:284)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at android.widget.ListView.performItemClick(ListView.java:3688)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at android.widget.AbsListView$PerformClick.run(AbsListView.java:1808)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at android.os.Handler.handleCallback(Handler.java:587)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at android.os.Handler.dispatchMessage(Handler.java:92)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at android.os.Looper.loop(Looper.java:123)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at android.app.ActivityThread.main(ActivityThread.java:4627)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at java.lang.reflect.Method.invokeNative(Native Method)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at java.lang.reflect.Method.invoke(Method.java:521)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:858)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)
09-18 23:31:26.846: ERROR/AndroidRuntime(28776):     at dalvik.system.NativeStart.main(Native Method)
09-18 23:31:57.197: ERROR/SensorManager(2487): unregisterListener: com.android.server.PowerManagerService$12@47b7f650
09-18 23:31:57.420: ERROR/SensorManager(13873): unregisterListener: com.cloudspeed.hot.apps.AppService$2@47d27f48 SMB380
09-18 23:32:02.568: ERROR/LockPatternKeyguardView(2487): Phone is boot completed. so can send broadcast message for music player

      Cursor cursor = db.rawQuery
        (
        "SELECT _id, time " +
        "FROM TimeTable  " +
        "WHERE _id = ? " ,
       
     

        new String[]{""+id});        

       
           
        	
        	adapter = new SimpleCursorAdapter(
                    this, 
                    R.layout.time_list_item, 
                    cursor, 
                    new String[] {"time"}, 
                    new int[] {R.id.timeList});
    setListAdapter(adapter);
    
            
	        
    }	

[macosxnerd101]

Moved to Android.

Post your table schema, and Log your query. What are they looking like? Does your query work when you run them outside of Android?

[hall882006]

My query works fine until it gets to my times and i only have two tables here they are...

<statement>
CREATE TABLE IF NOT EXISTS Bus_Schedule (
	 _id INTEGER,
	 busId VARCHAR(6),
	 BusName VARCHAR(45)) 
	
</statement>

<statement>
CREATE TABLE IF NOT EXISTS TimeTable (
	_id INTEGER, 
	busId VARCHAR(6),
	dayOfWeek VARCHAR(10),
	time integer,
	direction VARCHAR(40),
	streetName VARCHAR(60)) 	
</statement>

<statement>INSERT INTO Bus_Schedule VALUES(1,'1','Freeport Road')</statement>
<statement>INSERT INTO Bus_Schedule VALUES(2,'2','Mount Royal')</statement>
<statement>INSERT INTO Bus_Schedule VALUES(3,'4','Troy Hill')</statement>

<statement>INSERT INTO TimeTable VALUES(1,'1','Weekday',0413,'To Downtown Pittsburgh','Wilkinsburg Wilkinsburg Station Stop B')</statement>

The timetable has alot more like that.
Here what im doing with the statements.

	        cursor = db.rawQuery("SELECT _id, busId, BusName FROM Bus_Schedule", 
	    						new String[]{});   
	        
	      
	          adapter = new SimpleCursorAdapter(
	                                    this, 
	                                    R.layout.bus_list_item, 
	                                    cursor, 
	                                    new String[] {"busId", "BusName"}, 
	                                    new int[] {R.id.busId, R.id.BusName});
	                    setListAdapter(adapter);
	                    
	                    
	                 
	        
	         }     
	    
	    	public void onListItemClick(ListView parent, View view, int position, long id) {
	    	Intent intent = new Intent(this, daysOfWeek.class );
	    	Cursor cursor = (Cursor) adapter.getItem(position);
	    	
	    	intent.putExtra("BUS_ID", cursor.getInt(cursor.getColumnIndex("_id")));
	    	
	        startActivity(intent);
	         
	     
	    }

        idNum = getIntent().getIntExtra("BUS_ID", 0);
        
        
        SQLiteDatabase db = (new DatabaseHelper(this)).getWritableDatabase();
      
     
        
        Cursor cursor = db.rawQuery
        (
        "SELECT A2._id, A2.dayOfWeek, A2.direction, A2.streetName, A2.time " +
        "FROM TimeTable A2 " + 
        "WHERE A2._id = ? " +
        "GROUP BY A2.dayOfWeek", 
				
        
        new String[]{""+idNum});        
         
        	
        	adapter = new SimpleCursorAdapter(
                    this, 
                    R.layout.week_list_item, 
                    cursor, 
                    new String[] {"dayOfWeek"}, 
                    new int[] {R.id.dayOfWeek});
    setListAdapter(adapter);
    
            
	        
    }	
    
    
 	public void onListItemClick(ListView parent, View view, int position, long id) {
    	Intent intent = new Intent(this, Direction.class );
    	Cursor cursor = (Cursor) adapter.getItem(position);
    	
    	intent.putExtra("Day_ID", cursor.getInt(cursor.getColumnIndex("_id")));
    	


         startActivity(intent);
         
     
    }

  idDay = getIntent().getIntExtra("Day_ID", 0);
        

        
        
        
        
        
        SQLiteDatabase db = (new DatabaseHelper(this)).getWritableDatabase();
      
     
        
        Cursor cursor = db.rawQuery
        (
        
         "SELECT A2._id, A2.dayOfWeek, A2.direction, A2.streetName, A2.time " +
         "FROM TimeTable A2 " + 
         "WHERE A2._id = ? " +
         "GROUP BY A2.direction", 
				
        

        new String[]{""+idDay});        

       
           
        	
        	adapter = new SimpleCursorAdapter(
                    this, 
                    R.layout.direction_list_item, 
                    cursor, 
                    new String[] {"direction"}, 
                    new int[] {R.id.direction});
    setListAdapter(adapter);
    
            
	        
    }	
    
	public void onListItemClick(ListView parent, View view, int position, long id) {
    	Intent intent = new Intent(this, Street.class );
    	Cursor cursor = (Cursor) adapter.getItem(position);
    	
    	intent.putExtra("Street_ID", cursor.getInt(cursor.getColumnIndex("_id")));
    	
        startActivity(intent);
         
     
    }

  
        idStreet = getIntent().getIntExtra("Street_ID", 0);
        

        
        
        
        
        
        SQLiteDatabase db = (new DatabaseHelper(this)).getWritableDatabase();
      
     
        
        Cursor cursor = db.rawQuery
        (
       
        		  "SELECT A2._id, A2.streetName " +
        	      "FROM TimeTable A2 " + 
        	      "WHERE A2._id = ? " +
        	      "GROUP BY A2.streetName", 
        					
        

        new String[]{""+idStreet});        

       
           
        	
        	adapter = new SimpleCursorAdapter(
                    this, 
                    R.layout.street_list_item, 
                    cursor, 
                    new String[] {"streetName"}, 
                    new int[] {R.id.street});
    setListAdapter(adapter);
    
            
	        
    }	
    
    public void onListItemClick(ListView parent, View view, int position, long id) {
    	Intent intent = new Intent(this, Times.class );
    	Cursor cursor = (Cursor) adapter.getItem(position);
    	
    	intent.putExtra("BUS_ID", cursor.getInt(cursor.getColumnIndex("_id")));
    	intent.putExtra("BUS_Day", cursor.getInt(cursor.getColumnIndex("dayOfWeek")));
    	intent.putExtra("BUS_Direction", cursor.getInt(cursor.getColumnIndex("direction")));
    	intent.putExtra("BUS_Street", cursor.getInt(cursor.getColumnIndex("streetName")));

    	
        startActivity(intent);
         
     
    }

        SQLiteDatabase db = (new DatabaseHelper(this)).getWritableDatabase();
      
     
        
        Cursor cursor = db.rawQuery
        (
        "SELECT _id, time " +
        "FROM TimeTable  " +
        "WHERE _id = ? " ,
       
     

        new String[]{""+id});        

       
           
        	
        	adapter = new SimpleCursorAdapter(
                    this, 
                    R.layout.time_list_item, 
                    cursor, 
                    new String[] {"time"}, 
                    new int[] {R.id.timeList});
    setListAdapter(adapter);
    
            
	        
    }	

Do i need more sql tables and to be able to connect the timetable with the times. Cause each time has four different variables to determine what time to display: The bus of the times, the day whether its weekday or weekend, the direction of where it going, and the street that its passing.

[macosxnerd101]

The TIME keyword is a datatype for MySQL. You shouldn't name your column time. Also, you should have a primary key for each of your tables.

[hall882006]

       
         "SELECT A2._id, A2.dayOfWeek, A2.direction " +
         "FROM TimeTable A2 " + 
         "WHERE A2._id = ? AND A2.dayOfWeek = ? " +
         "GROUP BY A2.direction", 
				
        

        new String[]{""+idNum+idDay}); 

Am i allowed to do this im trying to pass two intents with the put extra to this activity and use them in the sql statement. So that the previous ID would match up and the previous day of the week would match up to show the direction. I can do it this way and it will work:

       "SELECT A2._id, A2.direction " +
       "FROM TimeTable A2 " + 
       "WHERE A2._id = ? " +
       "GROUP BY A2.direction", 
				
        

        new String[]{""+idNum}); 

But im guessing that the direction would just show for that ID but not take in affect that the direction might not be available on certain days. So it wouldn't filter that out. That's why im was wondering if you could pass two intents and use them in the sql statement.

This post has been edited by hall882006: 21 September 2011 - 05:01 PM

[hall882006]

Im still having trouble i cant seem to figure out how to tie my previous reference together for each previous intent, would i have to create more tables and reference a primary key each time if the intent from each table cant be passed and used twice as i explained above. Like Example:

Create Bus{
id int primary
BusId varchar
busName varchar
}

Create days{
dayid int primary
weekId int
weekName varchar
}

Create direction{
id int primary
dayId int
directSymbol varchar
directionName varchar
}
}

Something like this but i should be creating a reference each table to the previous table it connects to if i want to tie the information togetehr once i query the database.


}

[DarenR]

during the execution of your code if I may add, you should place something like nolock after your table names so it doesn't freeze the reset of your program.

It would look something like tablename with(NOLOCK)

[hall882006]

I figured out the times and i got them to display the right times. But when i tried to format the times and then display them they kept displaying the same times over and over again and they were all listed as PM i cant tell if this would be a problem since its displaying the same times but i have feeling that even with the right times they would either all be AM or PM. Either or i followed this tutorial and did everything they said only difference was i wanted to display only my times and im getting my data from a sql database. So some where in the conversion by adapting the code to fit my project i made a mistake but cant seem to tell exactly where. heres my updated code:

 Cursor cursor = db.rawQuery
        (
        	"SELECT A2._id, A2.dayOfWeek, A2.direction, A2.streetName, A2.records " +
       	    "FROM TimeTable A2 " + 
       	    "WHERE (A2._id = ?) AND (A2.dayOfWeek = ?) AND (A2.direction = ?) AND (A2.streetName = ?) " ,
            
       new String[]{(Integer.toString(idNum)),(idDay),(idDir),(idStreet)});        


        class MyAdapter extends CursorAdapter {
            private final LayoutInflater mInflater;

            public MyAdapter(Context context, Cursor cursor) {
                super(context, cursor, false);
                mInflater = LayoutInflater.from(context);
            }

            @Override
            public View newView(Context context, Cursor cursor, ViewGroup parent) {
                 return mInflater.inflate(R.layout.time_list_item, parent, false);
            }

            @Override
            public void bindView(View view, Context context, Cursor cursor) {
                short mTime = cursor.getShort(cursor.getColumnIndex("records")) ;

                String format = "hh:mm a";
                SimpleDateFormat sdf = new SimpleDateFormat(format);
                String dateString = sdf.format(mTime);

                ((TextView) view.findViewById(R.id.timeList)).setText(dateString);
            }
        }
        
        startManagingCursor(cursor);
        setListAdapter(new MyAdapter(this,cursor));
               
	        
    }	

And my times in my sql look like this... 900 915 930 1000 1115 || 1230 1245 100 115 130
The || just to show the difference from AM and PM

[hall882006]

Im still struggling with trying to format these times i made a little progress but now i just cant figure out a way to make the times display AM or PM. i was thinking i should be able to display AM until it reaches 12 as its substring then once it does, that should be the switch for it to display PM. Then vice verse once it reaches 12 again as it substring until there are no more times to display for that list. Heres what i implemented so far, i dont know the next step to implement this part or even if im on the right track. Heres what im trying.

 public void bindView(View view, Context context, Cursor cursor) {
                String mTime = (cursor.getString(cursor.getColumnIndex("records"))) ;
                String s = null;  
                if(mTime.length() == 3){
                	String a = mTime.substring(0, 1);
                	String b = mTime.substring(1, 3);
                	
                	 s = a +':'+b;                	
                	 
                	
                }
                else{
                	String a = mTime.substring(0, 2);
                	String b = mTime.substring(2, 4);
                	
                	 s = a +':'+b;    
                	
                	
                }

[hall882006]

Im getting closer and closer but i still cant get it to do what i want, I want the times to display PM once it reaches a time at 12 noon. then once it gets back to 12 midnight it should display AM again. What it displays so far is the times in AM then when it reaches noon it displays those times in PM but then displays the rest in AM again. I cant figure out the algorithm to accomplish this.

     boolean time_display = false;
                String s = null;
                String am_pm = null;
                String a = null;
                String b = null;
                
                if (time_display == false){
                	am_pm = "AM";
                }
                

                
                if(mTime.length() == 3){
                	 a = mTime.substring(0, 1);
                	 b = mTime.substring(1, 3);
                	
                	 s = a +':'+b+ " " +am_pm;                	
                	 
                	
                }
                else {
                	
                	if (time_display == false){
                    	am_pm = "AM";
                    }
                	else{
                		am_pm = "PM";
                	}
      
                	 a = mTime.substring(0, 2);
                	 b = mTime.substring(2, 4);
             	 
                	 
                 	if(mTime.subSequence(0, 2).equals("12") ){
                 		am_pm = "PM";

                 		time_display = true;
                 	}
                 	
                 	
                 	s = a +':'+b+ " " +am_pm;    
                	
                	
                }