query of unknown amount of pics/text
george
I have a page with lotsa pics, every pic is in a folder but I query their
name from a db (field pic: pic,pic,pic .. I know it'sa gainst relational
database theory but hey Im not a guru). Now when I click this pic a larger
one will appear. NP herebut now I want to add some comments to this pic.
Now im unsure on how to design my db for this cause I dont yet know how many
pics ill have per page. I can imagine creating a giant bunch of fields and
just hope that it's enough or just one field and explode the jpg-names/text
on a delimiter. But I guess neither one is a real good solution so if anyone
here has a hint on how to design this I would be real happy if they would be
willing to share that info with me :-)
Thx for reading
George
Edward Alfert
Table Name: pictures
Fields: id (unique number per record), url, filename, description
just create 1 record per picture...
example
1, /topic1/index.html, picture1.jpg, This is the first picture I took
2, /topic1/someotherpage.html, picture2.jpg, this is a good picture.
3, /topic2/index.html, pciture3.jpg, this is the first picture in a
different topic
4, /topic1/someotherpage.html, picture4.jpg, yet another picture i took.
This way, you don't need to know how many pictures per page ahead of
time...just add another picture and include the url of the page you want it
to appear in.
I hope this answers your question (if i understood it correctly)..
--
Edward Alfert
http://edward.alfert.com/
"Do what you love and you will never work a day in your life" - Confucious
****************************************
"george" <geo...@dontmail.com> wrote in message
news:3cd5429f$0$3857$e4fe...@dreader4.news.xs4all.nl...
Ewoud Dronkert
> Now im unsure on how to design my db for this cause I dont yet
> know how many pics ill have per page.
Scheme:
pagetable: int pageid (unique), varchar pagename.
pictable: int picid (unique), varchar pathtothumb, varchar pathtofull,
varchar comment.
linktable: int pageid, int picid.
Query per page:
select pathtothumb, pathtofull, comment
from pagetable, linktable, pictable
where pagename='thispage'
and pagetable.pageid=linktable.pageid
and linktable.picid=pictable.picid
Loop through resultset:
echo "<a href=\"{$row['pathtofull']}\">"
."<img src=\"{$row['pathtothumb']}\"> "
."{$row['comment']}</a>";
george
"Edward Alfert" <edw...@alfert.com> schreef in bericht
news:ab3fu3$epa0c$1...@ID-143141.news.dfncis.de...
> I'm not sure if i understand, but...how about...
>
> Table Name: pictures
> Fields: id (unique number per record), url, filename, description
>
> just create 1 record per picture...
> example
>
> 1, /topic1/index.html, picture1.jpg, This is the first picture I took
> 2, /topic1/someotherpage.html, picture2.jpg, this is a good picture.
> 3, /topic2/index.html, pciture3.jpg, this is the first picture in a
> different topic
> 4, /topic1/someotherpage.html, picture4.jpg, yet another picture i took.
>
> This way, you don't need to know how many pictures per page ahead of
> time...just add another picture and include the url of the page you want
it
> to appear in.
>
> I hope this answers your question (if i understood it correctly)..
Yes it does... I was so completely stuck looking in the other direction ...
THX
george
"Ewoud Dronkert" <nos...@invalid.info> schreef in bericht
news:3cd5485e$0$214$e4fe...@dreader3.news.xs4all.nl...
This is intersting ... would this be better/faster/have advantages then
edward his solution? I have about 600 pics on 15 pages and 150 visitors/day
Edward Alfert
I recomend using 1 table, and he recomends using 3 tables.
he is including an extra feature which is having page information stored in
a table whereas i'm not...i'm asuming you will hardcode page title/name in
the html code. That is why he needs 3 tables (1 for the picture info, 1 for
the page info, and 1 to link the two)
If you decide to make the entire website database driven (versus just the
pictures) then you will have to use multiple tables.
read up on "normalization"..."1nf (first normal form)", 2nf, 3nf, etc.
(Relational Database topics)
--
Edward Alfert
http://edward.alfert.com/
"Do what you love and you will never work a day in your life" - Confucious
****************************************
"george" <geo...@dontmail.com> wrote in message
news:3cd5a37f$0$205$e4fe...@dreader3.news.xs4all.nl...
Ewoud Dronkert
> This is intersting ... would this be better/faster/have advantages then
> edward his solution? I have about 600 pics on 15 pages and 150
visitors/day
One usually should keep different data objects in different tables, to avoid
duplication. It all depends on how you use the data. My scheme was very
general but may be too much for your needs. For example, if one picture is
only shown on one particular page ever, forget about the linktable and
insert the PageID directly in the picture table (many-many vs. one-many
relationship). If you don't store any other info about pages as a data
object in your db, you might even skip the page table and insert a PageName
field in the picture table, like Edward suggested. From your figures though
it looks like you can save some db space by maintaining a separate page
table. But space is cheap. The big downside of keeping it all in one table
is it may take a lot of (accurate) work if you ever want to change
something, like the name of one page, which can be in a 100 or more records.
And if (or when) you should decide to store more page-related info like
number of visitors per page, you sure need a separate page table.
Arjen
I know hwo to do it now and overall it's just become a little less foggy to
me.
George
Dave
I understand that you're learning, but you need to have basic understanding
of databases and PHP before you start coding PHP that uses databases. It's
not just so that we don't get thousands of posts every day asking why they
can't connect to a mysql server running on microsoft.com, or why links
starting file:// don't work on other PCs. If you have this basic learning,
everything you work on will give you more experience in correct coding. If
you go about it your own way, you might end up with some weird approaches to
pretty common problems (as you've highlighted ;)).
I wish you all the luck in the world, but please remember www.mysql.com and
www.php.net are your friends :)
Dave