Untitled

/* Search function. To generate the SQL query to search in the DB base on user search query*/
		public function search(input:String):ArrayCollection
		{
			var whereClause:String=null;
			var my_array:Array = input.split(" ");
 
			var i:int=0;
			var output:ArrayCollection=null;
			var sqlStatement:String="";
			removeDuplicate(my_array);
 
			if(my_array.length==1){
				sqlStatement ="select imgPath, count(*) as count from(" +
					"select t.*, e.eventName as name , i.imgPath from TaggedImagesDetails t , Event e , ImageDetails i " +
					"where e.eventId=t.TagId and choice='Event' and i.imgId=t.imgId" +
					" UNION ALL " +
					"select t.*, pp.peopleName as name , i.imgPath from TaggedImagesDetails t , People pp, ImageDetails i  " +
					"where pp.peopleId=t.TagId and choice='People'  and i.imgId=t.imgId" +
					" UNION ALL " +
					"select t.*, p.placeName  as name, i.imgPath from TaggedImagesDetails t , Place p , ImageDetails i" +
					" where p.placeId=t.TagId and choice='Place'  and i.imgId=t.imgId" +
					") WHERE name like '"+my_array[0]+"' group by imgPath order by count desc ;";
 
				output=database.searchImage(sqlStatement,1);
 
				if(output==null || output.length<1){
					trace("enter search if(output==null || output.length<1){ ");
					sqlStatement ="select imgPath, count(*) as count from(" +
						"select t.*, e.eventName as name , i.imgPath from TaggedImagesDetails t , Event e , ImageDetails i " +
						"where e.eventId=t.TagId and choice='Event' and i.imgId=t.imgId" +
						" UNION ALL " +
						"select t.*, pp.peopleName as name , i.imgPath from TaggedImagesDetails t , People pp, ImageDetails i  " +
						"where pp.peopleId=t.TagId and choice='People'  and i.imgId=t.imgId" +
						" UNION ALL " +
						"select t.*, p.placeName  as name, i.imgPath from TaggedImagesDetails t , Place p , ImageDetails i " +
						"where p.placeId=t.TagId and choice='Place'  and i.imgId=t.imgId" +
						") WHERE name like '%"+my_array[0]+"%' group by imgPath order by count desc ;";
 
					output=database.searchImage(sqlStatement,2);
				}
 
			}else{
				sqlStatement ="select imgPath, count(*) as count from(" +
					"select t.*, e.eventName as name , i.imgPath from TaggedImagesDetails t , Event e , ImageDetails i " +
					"where e.eventId=t.TagId and choice='Event' and i.imgId=t.imgId" +
					" UNION ALL " +
					"select t.*, pp.peopleName as name , i.imgPath from TaggedImagesDetails t , People pp, ImageDetails i  " +
					"where pp.peopleId=t.TagId and choice='People'  and i.imgId=t.imgId" +
					" UNION ALL " +
					"select t.*, p.placeName  as name, i.imgPath from TaggedImagesDetails t , Place p , ImageDetails i " +
					"where p.placeId=t.TagId and choice='Place'  and i.imgId=t.imgId" +
					") WHERE name like '"+input+"' group by imgPath order by count desc ;";
 
				output=database.searchImage(sqlStatement,1);
 
				if(output==null || output.length<1){
					sqlStatement="select count(*) as count, imgPath from (";
					for ( i = 0; i<my_array.length; i++) {
						if(i==0)
						{
							sqlStatement=sqlStatement +"\n select imgPath,tagId from (" +
								"select t.*, e.eventName as name , i.imgPath from TaggedImagesDetails t , Event e , ImageDetails i" +
								" where e.eventId=t.TagId and choice='Event' and i.imgId=t.imgId " +
								" UNION ALL" +
								" select t.*, pp.peopleName as name , i.imgPath from TaggedImagesDetails t , People pp, ImageDetails i  " +
								"where pp.peopleId=t.TagId and choice='People'  and i.imgId=t.imgId " +
								" UNION ALL" +
								" select t.*, p.placeName  as name, i.imgPath from TaggedImagesDetails t , Place p , ImageDetails i " +
								"where p.placeId=t.TagId and choice='Place' and i.imgId=t.imgId " +
								")  where name like '%"+my_array[i]+"%'";
						}else
						{
							sqlStatement=sqlStatement +"\n UNION ALL " +
								"\n select  imgPath,tagId from (" +
								"select t.*, e.eventName as name , i.imgPath from TaggedImagesDetails t , Event e , ImageDetails i " +
								"where e.eventId=t.TagId and choice='Event' and i.imgId=t.imgId " +
								" UNION ALL" +
								" select t.*, pp.peopleName as name , i.imgPath from TaggedImagesDetails t , People pp, ImageDetails i  " +
								"where pp.peopleId=t.TagId and choice='People'  and i.imgId=t.imgId " +
								" UNION ALL" +
								" select t.*, p.placeName  as name, i.imgPath from TaggedImagesDetails t , Place p , ImageDetails i " +
								"where p.placeId=t.TagId and choice='Place'  and i.imgId=t.imgId " +
								")  where name like '%"+my_array[i]+"%'";
						}
 
					}
 
					sqlStatement=sqlStatement+"\n ) group by imgPath order by count desc;";
 
					output=database.searchImage(sqlStatement,1);
				}
			}
			return output;
		}