Page 1 of 1

Retriving a Guide from MYSQL Database - Text/Image Layout Qu

Posted: Wed Apr 11, 2007 12:12 pm
by laura21london
Hello, thanks for reading, can you help a girl in distress :P..

I am trying to build a website which provides various tutorials guides and articles, now these guides for sure will contain pictures. What I dont know is how to layout and pull this information from the database correctly, now since starting this I have had an idea and would like to know if it is a good and correct way of doing it.

First I am thinking of having a table called Guides and Articles

Guides

Guide_ID - Guide_Name - Written_By - Date_Written - Word_Count - Paragraph1 - Paragraph2 - P3 - P4 and maybe 10 Paragraphs in total - Image1 - Image2 - Image3
-4,5,6,7,8 You get my idea right?

So I was thinking The first query would show this information

Select Guide_Name, Written_By, Date_Written, Word_Count from Guides where Guide_ID = 1

This would be put into the first table (4 Columns going across)

Then I would have an Intro Text which would be Paragraph1 so Maybe I could rename that to Intro_Text before I move onto paragraphs. So it would be

select Intro_Text FROM Guides where Guide_ID = 1

This would come into one table of course below the first query

I would then start the Guide with step1 (Paragraph1)

select Paragraph1 FROM Guides where Guide_ID = 1

Then I would have a picture to show what I was talking about, so another table below this would be

select Image1 FROM Guides where Guide_ID = 1, I think you now understand what I am getting at and how I want it to look on the page, I just dont know if this is a stupid way of doing it and if there is a much simpler way of doing it (Less queries etc)

Thanks for your help so Much!
Laura :)

Posted: Wed Apr 11, 2007 12:48 pm
by pickle
First off, welcome to the forums.
Second, feel free to use the [ syntax=sql ][ /syntax ] tags around your SQL statements - makes it easier to read.
Third - I think you're looking for a templating solution. It seems like you know how to get the data you want out of the database - you're just interested in how to put it on the screen right? That's not really a database or PHP specific problem - more of a XHTML/clientside issue.

I'd look into Template Lite - it will let you take your data & put it into a page without mixing your business logic in with your display logic. It's based on the Smarty engine so it's pretty powerful.

For example:

Template file named "page.tpl"

Code: Select all

<html>
  <head>
    <title>
      {$title}
    </title>
  </head>
  <body>
    <img src = "/path/to/images/{$image1}" />
  </body>
</html>
PHP file

Code: Select all

<?PHP
require_once('/path/to/Template/Lite/class.template.php');
$tpl = new Template_Lite();
$tpl->compile_dir = "compiled/";
$tpl->template_dir = "templates/";

$tpl->assign('title','My Page');
$tpl->assign('image1','cat.png');
$tpl->display('page.tpl');
?>
Resulting code

Code: Select all

<html>
  <head>
    <title>
      My Page
    </title>
  </head>
  <body>
    <img src = "/path/to/images/cat.png" />
  </body>
</html>
The work you'd have to do then is just retrieving of the data, figuring out how you want the page to look, & building the template file accordingly.

Posted: Wed Apr 11, 2007 2:38 pm
by Begby
You want to do this in one query. You can do a SELECT * and get all the data, then reuse it throughout your page without having to do one query for each item.

Secondly, you may want to read up on database normalization. Instead of using a fixed number of images/paragraphs it might be better to have a separate images table and a separate paragraph table then query them using SQL JOIN syntax. This will allow your app to be more flexible in the future.

Posted: Thu Apr 12, 2007 8:03 am
by laura21london
I have been advised to layout the tables like this

Guides
Guide_ID
Guide_Name
Written_By
Date_Written
Word_Count
Intro Text

Guide_Contents
guide_id
Paragraph

guide_images
guide_id
Image_name
image_location


By this is this correct in terms of entering the data into the table?
Guide_Contents Table

Guide_ID Paragraph
1 In order to do this would must...
1 The next step is to

But if this is how it is done, How do I uniquely identify Paragraph 1 from Paragraph 2 when sorting the data out on the page. I have been told that my main concern is how to layout the data on the page as I am have only up to this point laid out text exactly how it is put in the table, like when it is returned it is still a horizontal table.

How to put Guide title, written by etc, and then below that put Intro Text followed by para1 and image 1 then para2 and image 2 is I think where I am having difficulty understanding. Could you please explain a little on this aspect of it :).

Thanks for the help again, I am determined to make this work :)