upload pdf/path to pdf to database

[studioeightyone]

Hi everyone, first post on the forum, so sorry if I've missed anything...

 

I've been doing small bits of php/msql recently and I'm trying to write some code that will display a pdf or jpg that has certain keywords associated with it.

 

I've created a table and added 4 fields/columns. ID, filename, thumbnail, keywords

 

How can I get php to display a PDF/JPG that has a certain keyword associated with it? so... say I had 10 different animal pictures, and I wanted to see the dog picture and the dog picture had 'brown' and 'dog' as keywords in the database how and what would the code look like to display this on a webpage search?

 

Hope I'm making sense

 

Stu

[.josh]

You shouldn't have a single column with multiple keywords to search through. It's possible to work with what you have as-is, but it's inefficient, especially if/when you need to get into selecting files with common keywords or visa versa.

 

What you really should do is have 3 tables so as to have a many-to-many relationship between your files and the keywords.

 

Example you'd have

 

Table Files
id filename        thumbnail
1  somefile.pdf    someimage.jpg
2  anotherfile.pdf anotherimage.jpg


Table Keywords
id keyword
1  foo
2  bar
3  blah
4  something
5  dog


Table Files_Keywords
file_id keyword_id
1       1
1       2
1       3
2       1
2       2
2       4
2       5

[studioeightyone]

Thanks Josh.... I've changed it to the 3 tables now... Taking a wild guess at what  should be in each but my main question at the moment is how in php do i show a pdf related to 1 of the keywords?

 

I'm pretty new to php... i'm 3 weeks into a 12 week php introduction course... and we've not covered this stuff yet...

 

Hope you're enjoying your weekend..

 

 

Stu

[.josh]

First, an explanation of what the tables/columns are for:

 

Table Files - this is pretty much setup same way as you had it before, except without the keywords column (because you now have a separate table for them)

• id - this should be a unique value for each row (file), the primary key.
• filename - this is the name of the file like somefile.pdf
• thumbnail - this is the thumbnail for the file somefile.jpg

Table Keywords - this table will just have the keywords and a unique id for them. All keywords for all files should be here. There should be no duplicate keywords

• id - this should be a unique value for each row (keyword), the primary key
• keyword - the keyword, like "dog" or "cat"

Table Files_Keywords - this is the table that joins the two other tables

• file_id - values in this column should be the same as your Files.id column
• keyword_id - values in this column should be the same as your Keywords.id column

So the main magic here is in Files_Keywords. This table is basically a lookup table to establish a relationship between Files and Keywords.  So going back to my example data:

 

Table Files
id filename        thumbnail
1  somefile.pdf    someimage.jpg
2  anotherfile.pdf anotherimage.jpg


Table Keywords
id keyword
1  foo
2  bar
3  blah
4  something
5  dog


Table Files_Keywords
file_id keyword_id
1       1
1       2
1       3
2       1
2       2
2       4
2       5

In Files_Keywords, the first 3 rows are for the file that has an id of 1. In table Files you see that somefile.pdf has that id. So you see that each row with file_id of 1 has a different keyword_id value. That's the id from Keyword. So in this example, I show that somefile.pdf is associated with keywords "foo" "bar" and "blah". Then the next 4 rows in Files_Keywords are for anotherfile.pdf, and the keyword_id values show that it also has keywords "foo" and "bar" associated with it, but also has "something" and "dog" as well.

[.josh]

some example queries

 

Get a listing of all the data in one place:

SELECT f.filename, f.thumbnail, fk.file_id, fk.keyword_id, k.keyword
FROM Files AS f
LEFT JOIN Files_Keywords AS fk ON f.id = fk.file_id
LEFT JOIN Keywords AS k ON fk.keyword_id = k.id

results:

 

filename	thumbnail	 file_id keyword_id keyword	
somefile.pdf	someimage.pdf	 1	 1	    foo
somefile.pdf	someimage.pdf	 1	 2	    bar
somefile.pdf	someimage.pdf	 1	 3	    blah
anotherfile.pdf	anotherimage.jpg 2	 1	    foo
anotherfile.pdf	anotherimage.jpg 2	 2	    bar
anotherfile.pdf	anotherimage.jpg 2	 4	    something
anotherfile.pdf	anotherimage.jpg 2	 5	    dog

Get all filenames/thumbails for keyword "foo":

SELECT k.keyword, f.filename, f.thumbnail
FROM Files AS f
LEFT JOIN Files_Keywords AS fk ON f.id = fk.file_id
LEFT JOIN Keywords AS k ON fk.keyword_id = k.id
WHERE k.keyword =  'foo'

results:

 

keyword	filename	thumbnail	
foo	somefile.pdf	someimage.pdf
foo	anotherfile.pdf	anotherimage.jpg

Get all the keywords for somefile.pdf:

SELECT f.filename, k.keyword
FROM Files AS f
LEFT JOIN Files_Keywords AS fk ON f.id = fk.file_id
LEFT JOIN Keywords AS k ON fk.keyword_id = k.id
WHERE f.filename =  'somefile.pdf'

results:

 

filename	keyword	
somefile.pdf	foo
somefile.pdf	bar
somefile.pdf	blah