CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
Alan Daniel
Alan Daniel

Reputation: 69

Store Arrays in a Mysql database, from a Max ammount of Items

This is a directory of restaurants in the city and every restaurant have the menu and items stored in it.

Restaurants is the Table name

{ID,Name,MenuType(),Items() }

i want to Store different MenuTypes() for Every restaurant added to the MysqlDB.

For example:

McDonalds would have 3 different MenuTypes(): "Breakfasts","Hamburgers","Desserts" where they have X AMMOUNT of Items inside every menu

So i'd need Arrays for MeuTypes() and Items()

In Phpmyadmin how would i store those as? Varchar only?

and how could i get the items() stored inside the MenuTypes() throught a loop in PHP(SQL)?

Thanks alot in advance!

Upvotes: 1

Views: 92

Answers (2)

mtotho
mtotho

Reputation: 52

Like above, you will probably want to use relational databases. Here is a rough idea below.. "pk"=primary key, "fk" = foreign key. Obviously I am omitting some other important fields

tblRestaurant
   pkRestaurant

tblRestaurantTypesItems
   fkRestaurant
   fldMenuType
   fldItem

To retrieve your items, you could run a query like this

SELECT * FROM tblRestaurant R INNER JOIN tblRestaurantTypesItems RTI ON
               R.pkRestaurant=RTI.fkRestaurant ORDER BY R.pkRestaurant

I haven't tested this query but thats a rough idea

This would return a recordset like:

|Arbys    |Breakfast|EGGS
|Arbys    |Lunch    |Burger
|McDonalds|Breakfast|McMuffin
|McDonalds|Breakfast|Hashbrown
|McDonalds|Breakfast|Coffee
|McDonalds|Lunch    |Big Mac
|McDonalds|Dinner   |Fries

Or if you want to get more specific

SELECT * FROM tblRestaurant R INNER JOIN tblRestaurantTypesItems RTI ON
               R.pkRestaurant=RTI.fkRestaurant WHERE R.pkRestaurant="McDonalds"
               AND RTI.fldMenuType="Breakfast"

Which would return something along the lines of

|McDonalds|Breakfast|McMuffin
|McDonalds|Breakfast|Hashbrown
|McDonalds|Breakfast|Coffee

edit: I realize I strayed of OPs meaning of "menu types" by using breakfast lunch dinner instead of breakfast, burgers, desserts. Would be the same idea.

So in PHP if you had an array of $menuTypes() and each type had an array of $items() like you said in your original question, such that $menuTypes['breakfast'] = $breakfastItems(), you could do something like this.. Not the best way to do it but meh

$restaurant = "McDonalds"
foreach ($menuTypes as $items){
    $menuTypeName = key($items); //not sure if key() works on elements of arrays
                                 // which are arrays themselves

     foreach($items as $item){
       //Assuming "McDonalds" Is already in tblRestaurant so we don't need to
       //insert it now
       $query="INSERT INTO tblRestaurantTypesItems VALUES('".$restaurant."',
              '".$menuTypeName."', '".$item."')"

        //execute query
     }
}

Upvotes: 1

Kylie
Kylie

Reputation: 11749

Look into serialize() function....the process by which you serialize arrays into text...

http://www.webmaster-source.com/2009/02/15/php-serialize-function/

what's the point of serializing arrays to store them in the db?

Although I will say, that there are far better ways of doing what you want....with relationships, but Im not gonna tell you how to structure your database...

But Mysql is a relational database, so why not take advantage of that.

ie... What will you do if you wanna filter?? Or search the menu data???
The way you wanna do it, you wont be able to.

Or, its not that you wont be able to, it'll just involve extra steps, instead of just a query to the database, you'll have to unserialize the data, and do the filtering client-side with Jquery or something like that.

Not a big deal.....but definitely try to think of a better structure before embarking down the serialization route.

Upvotes: 0

Related Questions