MySQL Database

[lawrencee99]

hello guys. . . I would like to create a MySQL database that consists of the name of ten fruits.
The idea is to have the database return the word FRUIT, when queried with the word APPLES.

A query for the word "apple", should return the "fruits" value in the other below:

Item: Fruit
Location: Processing Plant
Destination:Market
Dispatch time: 10:30 AM
ETA: 2:00 PM

And if someone inputs the name of a fruit that isn't in the database , it would return the word "invalid". I am not sure about the difficulty level, I have tried my hands out on this project but keep on failing. . . Anyone care to help? I'd really appreciate it.
Thanks

[traq]

Do you know anything about MySQL? about how databases work in general?

Your question is unclear. When you design a database table, you need to start by deciding what information you need to store. From there, you can decide what tables and columns you need, and how to query them.

Looking at your post above, do you need to save the "Location" of the fruit? Its "Destination"? What else?

How are you going to query the DB—only by the name of the fruit? By destination? Do you want to see results from the past (i.e., that have already been delivered)? What should happen if there is more than one result for a type of fruit?

Please provide more information, and be as specific as possible.

• What do you want to accomplish? (Details!)
• What have you already tried? (Show us your code!)
• What problems did you encounter? (What happened, vs. what you expected to happen?)

[lawrencee99]

Do you know anything about MySQL? about how databases work in general?

Your question is unclear. When you design a database table, you need to start by deciding what information you need to store. From there, you can decide what tables and columns you need, and how to query them.

Looking at your post above, do you need to save the "Location" of the fruit? Its "Destination"? What else?

How are you going to query the DB—only by the name of the fruit? By destination? Do you want to see results from the past (i.e., that have already been delivered)? What should happen if there is more than one result for a type of fruit?

Please provide more information, and be as specific as possible.

• What do you want to accomplish? (Details!)
• What have you already tried? (Show us your code!)
• What problems did you encounter? (What happened, vs. what you expected to happen?)

Thanks for the reply.. I must tell you I am an absolute noob when it comes to Mysql or database in general. I am more of a basic web designer. My experimenting with MySQL was borne out of necessity.

And to answer your question. . Yes I intend having the fruit name,location,destination, dispatch time and ETA all stored in the Database. So when people search the name of one of the fruits in the Database. .. It returns a result in the order I pointed out in the first post. Clearer example of this would be the parcel tracking services freight companies offer on their site.

[traq]

I intend having the fruit name,location,destination, dispatch time and ETA all stored in the Database.

So, we've got a fair start on the database design. Let's explore a bit more:

You said earlier that you have a predefined list of fruits, and that any not in your list would be invalid.
Is the same true of Locations/Destinations? or are they arbitrary (any location is acceptable)?

What about multiple instances of a fruit? Is there only ever one apple at any given time, or might there be several going to/from different locations? same locations?
If there might be several apples, do you only want to know about one of them, or have all of them returned by the query? how would you distinguish between individual apples?

What about the dispatch time and ETA - is this the hour/minute only? or do you need to know the date as well?

So when people search the name of one of the fruits in the Database. .. It returns a result in the order I pointed out in the first post. Clearer example of this would be the parcel tracking services freight companies offer on their site.

Is that what you're actually trying to create? If so, there's probably a lot of other things you'll need to consider.

In what language are you writing your application? or are you using the database directly?

[lawrencee99]

So, we've got a fair start on the database design. Let's explore a bit more:

You said earlier that you have a predefined list of fruits, and that any not in your list would be invalid.
Is the same true of Locations/Destinations? or are they arbitrary (any location is acceptable)?

What about multiple instances of a fruit? Is there only ever one apple at any given time, or might there be several going to/from different locations? same locations?
If there might be several apples, do you only want to know about one of them, or have all of them returned by the query? how would you distinguish between individual apples?

What about the dispatch time and ETA - is this the hour/minute only? or do you need to know the date as well?

Yes the locations and destinations would be predefined. It only just occurred to me that their might be multiple instances of a particular fruit on the list. . . Do you think it will be possible to tag them with alphanumeric strings, so that rather than have their names typed into the search bars, I can simply use the alphanumeric string to bring out details of the "particular" apple attached to it.

Is that what you're actually trying to create? If so, there's probably a lot of other things you'll need to consider. In what language are you writing your application? or are you using the database directly?

I am trying to do this using the cPanel->MySQL Databases.

[traq]

It only just occurred to me that their might be multiple instances of a particular fruit on the list. . . Do you think it will be possible to tag them with alphanumeric strings, so that rather than have their names typed into the search bars, I can simply use the alphanumeric string to bring out details of the "particular" apple attached to it.

Depends on how you need to use it. If you simply need to know that two given apples are different (and be able to manipulate their records reliably), then you can simply let MySQL generate a serial id for them. If you need to be able to "look up" specific apples, then you need to decide on some sort of id format that your application can predict. (I think the former will probably be the better solution, in this case.)

I am trying to do this using the cPanel->MySQL Databases.

That really doesn't tell me anything. cPanel doesn't administer MySQL; it uses other software to do it. There might even be several to choose from (phpMyAdmin is one of the most common).

Yes the locations and destinations would be predefined.

Alright. Those items that are "predefined" -the types of fruit and locations, for now- get their own tables. You would have another table for your "tracking" records, which references those tables for fruit names and locations. For example:

create table `fruit`(
  name varchar(50) primary key
)engine=innodb;

create table `location`(
  `id` serial primary key,
  `address` varchar(50) not null,
  -- city, state, etc.
)engine=innodb;

create table `fruit_tracking`(
  `id` serial primary key,  -- an auto-generated id number
  `fruit` varchar(50) not null, -- the fruit.name of the fruit we are tracking
  `origin` bigint unsigned not null, -- the origin's location.id
  `destination` bigint unsigned not null, -- the destination's location.id
  `dispatched` datetime not null,
  `eta` datetime not null,
  -- a "foreign key" is a reference to the info in a record in another table.
  -- this allows the DB to make sure the kind of fruit, locations, etc., actually exist before using them.
  foreign key(`fruit`) references `fruit`(`name`),
  foreign key(`origin`) references `location`(`id`),
  foreign key(`destination`) references `location`(`id`)
)engine=innodb;

Let me know if you understand all that. I'm going to bed now; we can look at how to query your records next.

[lawrencee99]

Honestly, I am absolutely lost here.

[traq]

No problem. We can go over each item to make sure it's what you need.

Tell me what info you need to store about the fruit itself. The name of the fruit, obviously. Scientific name? variety? a product code of some sort?
This will help you decide on columns for the fruit table. (Are you only dealing with fruits? any other items you may need to track with this DB?)

Then think about the locations. The number, street, city, state, and zip, for starters. Do you need a phone number? contact/business name? hours open for delivery?
These columns will be in the location table. Both origins and destinations will reference this table.

The final table you need (from what you've told me so far) will be the tracking information for each item. From what I can tell, you'll have the fruit, origin, time out, destination, and ETA for each tracking record.

After your database is designed, we can create some queries that will help you view particular records or reports.

Did you find out what software you are using to admin your DB? Is it phpMyAdmin?