PDA

View Full Version : quick database question


ali_b
07-11-2007, 10:56 AM
hey.

i have a feild in my database which is currently a blob i think.

i display this feild on one of my pages, but it is more than one word.... that doesnt really make sence so ill show u and example:

this is what it looks like in the database:

Service 1 Service 2 Service 3

so obviously when i echo it it is displayed like this:

Service 1 Service 2 Service 3

but what i want it to do is be displayed like this :

Service 1
Service 2
Service 3

here is what the database looks like:

184

cheers,

ali

davidj
07-11-2007, 11:38 AM
can you store the values like this...

Service1 Service2 Service3

or like this (using a pipe)

Service 1 | Service 2 | Service 3

ali_b
07-11-2007, 11:41 AM
i dont reallly know :S... all i have done was typed them in in one line like this:

Service1 Service2 Service3

because i didnt know how else to do it or if there was a special feild type to use...

what do you mean by using "|" i remember you oput this in the other thread but i dont really understand it, could you please explain it a bit more, cheers :)

davidj
07-11-2007, 11:45 AM
if you typed them in like ...

Service1 Service2 Service3

then your ok as long a there is only one space between the values

ok you can print them to screen ok but they print horizontally yes

davidj
07-11-2007, 11:57 AM
you could store them as

Service1<br>Service2<br>Service3

include the <br> in the database

ali_b
07-11-2007, 11:57 AM
yes, its all working as it should but i would like them to print vertically

ali_b
07-11-2007, 11:58 AM
oh ok, ill give that a go

ali_b
07-11-2007, 12:01 PM
cheers, that worked :D i never knew you could use html tags in a database

davidj
07-11-2007, 12:05 PM
its just a string. The database dosent care or know what it holds in the fields

when you echo the string to the browser thats when it becomes html and is interpreted

ali_b
07-11-2007, 12:21 PM
oh right kl, cheers, another question :P

i was looking at this: http://www.menupages.com/screenmenu.asp?restaurantId=5015&htmllink=6129A54318551B1C6BB3E1C265BA666CB0C59792A B291F82F3263A65BD50ADA7082D87F6EFEFBA99&taglineid=0

and how they display the menu... i understand its a big table but i dont understand how its done....

the only way i can think of it being done is to have a million feilds in a database and echo them out in a table but because they have thousands of restaurants there must be an easier way of doing it... do u know how i could create a unique menu for each restraunt using php and a database? because just now i am going to just make an image and echo the image but that doesnt look as good :( i hope you understand what im getting at :S

davidj
07-11-2007, 02:03 PM
you would have a food_type table which would contain...

Afghan (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=1&neighborhoodId=0) (5)
African (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=2&neighborhoodId=0) (36)
American (New) (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=3&neighborhoodId=0) (385)
American (Traditional) (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=4&neighborhoodId=0) (483)
Argentinean (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=5&neighborhoodId=0) (17)
Asian (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=6&neighborhoodId=0) (61)
Australian (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=77&neighborhoodId=0) (10)
Austrian (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=7&neighborhoodId=0) (70)
Bagels (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=8&neighborhoodId=0) (92)
Bar Food (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=9&neighborhoodId=0) (226)
Barbecue (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=10&neighborhoodId=0) (79)
Belgian (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=11&neighborhoodId=0) (22)
Bistro (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=72&neighborhoodId=0) (134)
Brazilian (http://www.menupages.com/restaurants.asp?areaid=0&cuisineid=12&neighborhoodId=0) (25)

etc

the food_type table would have an ID and would use this ID to link to restaurant table


so you would only need 2 tables not thousands

it works the same way as the dynamic dropdowns tutorial i wrote

ali_b
07-11-2007, 02:04 PM
ohhhh ok, that makes sense. ill have a fiddle with that just now. cheers :)

domedia
07-11-2007, 02:17 PM
ali, database design is quite important as you can see. Image going halfway through a project to find out your db architecture is wrong 8)

ali_b
07-11-2007, 02:22 PM
so what would be in the restraunt table? would it have the name, starter, main course and desert?

and yes, domedia, it creates quite alot of extra work lol.. good practice tho :)

davidj
07-11-2007, 02:41 PM
ok...

///////////////////////
*** type table ***
-----------
type_id (auto increment) || food_type

//////////////////////


//////////////////////
*** restaurant table ***
-----------
rest_id (auto increment) || type_id || restaurant || menu_starter || menu_main || menu_sweet

/////////////////////

ali_b
07-11-2007, 02:54 PM
ok kl, thanks for that. and the last question iss....

what do i do if a restraunt has say... 10 starters and i want to display it like this:

Starters

starter 1 .................................................. ... 234
starter 2 .................................................. ... 234
starter 3 .................................................. ... 243
and so on

then main course then desert

coz if i keep adding data to the menu_starter feild the it will change the rest_id.... thats the bit i dont understand :( :(

do u know what i mean??

davidj
07-11-2007, 03:24 PM
you could setup a 3rd table called menu

///////////////////////
*** type table ***
-----------
type_id (auto increment) || food_type

//////////////////////


//////////////////////
*** restaurant table ***
-----------
rest_id (auto increment) || type_id || restaurant_name
/////////////////////


//////////////////////
*** menu table ***
-----------
menu_id (auto_increment) || rest_id || course_id|| food_menu || price ||
/////////////////////

the rest_id in the menu table carries the id of the restaurant. The course_id is 1, 2 or 3 (starter, main, sweet)

so the table would look like this..

+----------------------+
| 1 | 53 | 1 | shrimp | 3.70
-----------------------+
| 2 | 53 | 1 | bread | 2.50
-----------------------+
| 3 | 53 | 2 | steak | 13.70
+----------------------+
| 4 | 54 | 1 | soup | 3.70
-----------------------+
| 5 | 54 | 1 | mushrooms | 2.50
-----------------------+
| 6 | 54 | 2 | fish | 8.70


53 = restaurant id (rest_id). Then you have the course (123) then the food. You still require an auto increment so you can target specific rows when updataing or deleting

ali_b
07-11-2007, 03:43 PM
cheers m8 :) thats helped alllottt :D ill have a go at all of that... have to change most of my website now loool! but good practice hehe

cheers again :D

davidj
07-11-2007, 03:47 PM
in your restaurant table now that your not storing menu's in there you could use it to hold contact details and even google map references

davidj
07-11-2007, 03:51 PM
addition...

this is a great little project to get your teeth into coding and working with databases.

ali_b
07-11-2007, 04:32 PM
ok, ive done all that... this is the code i have just now

<table width="100%" border="1">
<?php do { ?>
<tr>
<td><?php echo $rowShop['food_menu']?></td>
</tr>
<?php } while ($rowShop = @mysql_fetch_array($result)); ?>
</table>


but i want to have this:

Starters
aksdlak;d
a;ksalkd
alsdka;ld

Main course
laksd;ladk
l;dka;ldk
;alsdk;;lkas

Pudding
;alsfd;l
alskd;al
laksd;a


but obvioulsly what i have just displays it all... i need something like "where course_id = 1 then display starters then where course_id=2 display maincourses... and so on

but im not too sure what the code should be to be able to do that and also display it all.... which the "<?php } while ($rowShop = @mysql_fetch_array($result)); ?>" bit does.



so basically i want it to sort of be like this...


<table width="100%" border="1">
<?php do { ?>
<tr><td>Starters</td></tr>
<tr>
<td><?php echo $rowShop['food_menu'] where course_id="1"?></td>
</tr>
<tr>
<td>Main courses</td>
</tr>
<tr>
<td><?php echo $rowShop['food_menu'] where course_id="2"?></td>
</tr>
<tr>
<td>Deserts</td>
</tr>
<tr>
<td><?php echo $rowShop['food_menu'] where course_id="3"?></td>
</tr>
<?php } while ($rowShop = @mysql_fetch_array($result)); ?>
</table>


that isnt proper code but i hope that sort of helps u to understand what i want to do..

cheers

davidj
07-11-2007, 07:48 PM
do that in your SQL query...
you have 1 query for starters 1 for main and 1 for sweet

example...

select * from menu where course = 1

ali_b
07-11-2007, 10:44 PM
double post

ali_b
07-11-2007, 10:56 PM
here are the query bits
<?php
require_once("connections/connection.php"); //database connection
$shop_id = $_GET['shop_id']; // << $_GET *********
/////////////////////////////////////////////////////
$query = sprintf("SELECT * FROM menu where course_id='1' and shop_id='$shop_id'");
$result = @mysql_query($query);
$starter = @mysql_fetch_array($result);
////////////////////////////////////////////////////
/////////////////////////////////////////////////////
$query = sprintf("SELECT * FROM menu where course_id='2' and shop_id='$shop_id'");
$result = @mysql_query($query);
$main = @mysql_fetch_array($result);
////////////////////////////////////////////////////
/////////////////////////////////////////////////////
$query = sprintf("SELECT * FROM menu where course_id='3' and shop_id='$shop_id'");
$result = @mysql_query($query);
$desert = @mysql_fetch_array($result);
////////////////////////////////////////////////////
?>


here is the code:

<table width="100%" border="1">
<tr>
<td align="center" bgcolor="#B90000" style="color:#000000; font-size: large"><strong>Starter</strong></td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $starter['food_menu'];?></td>
</tr>
<?php } while ($starter = @mysql_fetch_array($result)); ?>
<tr><td align="center" bgcolor="#B90000" style="color:#000000; font-size: large"><strong>Main Course</strong></td></tr>
<?php do { ?>
<tr>
<td><?php echo $main['food_menu'];?></td>
</tr>
<?php } while ($main = @mysql_fetch_array($result)); ?>
</table>


that what i have and its wrong coz nothing it coming up :( but i cant work out what coz im a newbie :( hhehe

davidj
07-12-2007, 07:13 AM
get the first query working first

delete the rest

ali_b
07-12-2007, 09:15 AM
hey,

i got it all working :D i just changed the result variable in each one so it was different and it seems to be working..

however i dont know what feild type i need to use to put currency in..

cheers

davidj
07-12-2007, 09:24 AM
a DECIMAL with a precision of 2

ali_b
07-12-2007, 09:27 AM
ok cheers,

also how do i do that dot thing inbetween the food and the price.. i have a <td></td> inbetween the these other two <td>'s but i want it to automatically adjust depending on how long the name of the food is etc..

is this possible?

davidj
07-12-2007, 10:08 AM
ali

please post a new question or there will be hundreds of unrelated question in this thread

ali_b
07-12-2007, 10:09 AM
okkl, sorry, will do :)