[RESOLVED] plz help me to solve this pblm...

rosemary

ive two tables bus_details and route_details
bus_details contains busid, busname,bustype
route_details contains routeid, busid, routename, route
(here route is a medium text area, where the main locations are listed delimited by using commas)

wat ive to do is just a search, using 2 locations in the route (from & to ), bustype. the result should contain the busid by searching the bus_details for bustype and route_details for the two locations searched.

i used this query

$qry = "select bus_details.busid from bus_details, route_details where bus_details.busid = route_details.busid and bus_details.bustype like '%$bus_type%' and route_details.route like '%$from%' and route_details.route like '%to%'";

in some cases it is working correctly, but evenif in the sql query runner in phpmyadmin gives zero rows as result set, the it is listing 1 row as result. which is some how took by using the bustype(not even checking the from, to etc from route )

can anyone plz help me...?
im a beginner in php...
any help will b appreciated

thanks in advance...

Rose

nemonoman

Don't be an database idiot! Comma separated values are a big red alert that your database design is WRONG.

You should add a third table: route_stops
route_id
stop_order
stop_location

Then for the route, populate stop 1, stop 2, stop 3, etc.
Then it will be a VERY simple matter to find which route goes from stop_location X to stop_location Y

alimadzi

nemonoman wrote:
Don't be an database idiot! Comma separated values are a big red alert that your database design is WRONG.

Though perhaps a bit harsh, this is absolutely correct. You really need to fix the issues with your database design before you build your app around it. It will save you lots of time and headaches down the road.

Read up on the first three normal forms and find some tutorials or books on the topic of database design. Here's one to get you started:

http://www.peachpit.com/articles/article.asp?p=30885&rl=1

We all learned this stuff at one time or another. Now is your time. Good luck!

Sxooter

Yeah, if you're going to be searching on parts of a route, then you need to break that out into another table as mentioned above. Give it a whirl, and when you get stuck, post back here with more questions. You'll have plenty. 🙂

Oh, and in the future post with a topic that describes your problem. Please help isn't the most useful topic since we can't really tell what you're needing help with.

And welcome to the board. Hopefully you'll find your answers here.

rosemary

thanks a lot for all of ur help... ill create one more table and do it as u people said...

nemonoman... wat u said z true... im a database idiot... :-) i agree... im new to this all... anyway its ok... thanks 4 ur help...

alimadzi... thanks 4 that link... i studied normalization before.. but since ive no experience in creating a database, these all errors came... ill correct to rectify all....

sxooter said is true, ive plenty more doubts to get cleared....

so meet u all later with another doubt...

bradgrafelman

Don't forget to mark this thread resolved.