Database Table Help

[danlew]

I have almost made all the script to place into database I have the following;

 

CREATE TABLE `news` (

  `id` bigint(20) NOT NULL auto_increment,

  `title` text NOT NULL,

  `date` text NOT NULL,

  `content` longtext NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

 

But I need to have also photo option in database, what is the data type name for images or folders?;

 

The database im trying to create will display images and title with description text and date similar to this site (worldpress.org)

 

Thanks in advance

 

 

[obsidian]

My recommendation would be not to store the images within the database directly, but rather to store a reference to the image and store the image within an images directory on your server.

 

If you do indeed choose to store images directly in the DB after all, you need to check out the BLOB (Binary Large Object) data type.

[danlew]

Oh, thats a very good thought, so to store the images in a folder like you suggest, how differently than will i need to set up the databse to read from that?

 

CREATE TABLE `news` (

  `id` bigint(20) NOT NULL auto_increment,

  `title` text NOT NULL,

  `date` text NOT NULL,

  `content` longtext NOT NULL,

  `blob` ?????

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

[obsidian]

If you are uploading the images to a folder, all you would need to do is store the image name in a varchar field and then reference the images folder when you print out your news item. So, your table would simply need an images column with a varchar:

CREATE TABLE news (
  id integer unsigned auto_increment primary key,
  title varchar(200) not null,
  `date` DATE not null,
  content longtext not null,
  image varchar(40) default null
)

 

Also, I've made some recommendations for other data types that may fit your columns slightly better. Hope this helps.

[danlew]

Thats Brilliant!!!!!! That seems much more like what I am after! 

[danlew]

Also will ( image varchar(40) default null ) be enough to have it outputed onto my webpage as 1 big image  as the main pic and when they click in it will display however many more pics on the next page? or will I have to set that in php?

[obsidian]

Also will ( image varchar(40) default null ) be enough to have it outputed onto my webpage as 1 big image  as the main pic and when they click in it will display however many more pics on the next page? or will I have to set that in php?

 

varchar(40) simply lets you store a string up to 40 characters long. What you do with it is entirely up to you. For instance, you most likely should only store the filename ('mypic.jpg', 'songster.mp3', etc) in that field. Then, when you query, you can simply use that name to display the image where you'd like:

<?php
$q = mysql_query("SELECT * FROM news ORDER BY `date` DESC");
if (mysql_num_rows($q) > 0) {
  while ($rec = mysql_fetch_assoc($q)) {
    echo "<div class=\"news_entry\">\n";
    echo "<h1>$rec[title]</h1>\n";
    echo !empty($rec['image']) ? "<img src=\"images/$rec[image]\" />\n" : '';
    echo "<p>" . nl2br($rec['content']) . "</p>\n";
    echo "<p>Posted " . date('F j, Y', strtotime($rec['date'])) . "</p>\n";
    echo "</div>\n";
  }
}
?>

 

Hope that helps some.

[danlew]

Thats fantastic!!! I think all my questions are solved. I will get back to you if I have any issues, but i think i will be good from here....

[danlew]

Actually I have the code working perfectly now (bottom of webpage "ball room" event except for one thing;

seo2webdesign.com/clients/nzsocietythailand.php

 

I want the "title"  and "image" to be a link so when they click on that it will also take them to a secondary page, do i need to edit code here to suit?

 

<?php

$q = mysql_query("SELECT * FROM news ORDER BY `date` DESC");

if (mysql_num_rows($q) > 0) {

  while ($rec = mysql_fetch_assoc($q)) {

    echo "<div class=\"news_entry\">\n";

    echo "<h1>$rec[title]</h1>\n";

    echo !empty($rec['image']) ? "<img src=\"images/$rec[image]\" />\n" : '';

    echo "<p>" . nl2br($rec['content']) . "</p>\n";

    echo "<p>Posted " . date('F j, Y', strtotime($rec['date'])) . "</p>\n";

    echo "</div>\n";

  }

}

?>

 

 

and do I need to make a secondary page and add code for it to follow from?

[danlew]

should i add this code somewhere?

<a href="<?php echo 'title-' . $link .'-'. strtolower($row['reference']) .'.php'; ?>">

[danlew]

I have the script working here now : nzsocietythai.org/nzsocietythailand.php

 

but i want to have it so when i click on the image it will go to page with info? what code do i need to set in different page and have link on it?

 

should i also add this code somewhere on the query? <a href="<?php echo 'title-' . $link .'-'. strtolower($row['reference']) .'.php'; ?>">

[joshi_v]

what do u want to display in second page?

[danlew]

say up to 3 images,

and the long description thats it. Nice and simple.....

 

the first page is just 1 image they click from this page "nzsocietythai.org/nzsocietythailand.php" than when it takes them to the secondary page it will show the same image but much bigger, with 2 more images beside it and longer description....

[joshi_v]

Well! then in href pass id of that photo..in the second page get the description for that photo from db using the id that is passed to the second file. display the descriptiong along with image.

 

am i correct?

[danlew]

Yes that is what i would like... what extra code will i need for that?