Posts by vjwilson

I have run into an interesting problem. I have inherited a database for daemon process that polls and accepts reports from remote embedded systems. Each site that has one of these systems can monitor over a dozen different fuel tanks. (In practice, most monitor 2, 3 or 4 tanks.)

When something happens, such as a tank being refilled or a tank reaching a minimum level, the program saved that event in a Postgres database. The way the database was originally constructed, it was saving all the information from each fuel tank (type of fuel, etc.) in the event record, even though there was a separate "tanks" table. I added a foreign key field to the table to associate it with a particular embedded unit, and a foreign key to the events table to associate it with a particular tank.

Now here's the problem: tanks can be added, removed, or have the type of fuel they store changed at any time. Adding tanks shouldn't be a problem, but if one is removed, those event recorded would be "orphaned". Worse if the type of fuel is changed, from say "jet" to "rocket", then when someone searches through the history, they would think all those old events happened to the "rocket" fuel, when in fact they happened to the "jet" fuel.

I have received a couple of suggestions offline: (1) make a second, archive table of the tanks, and when anything changes, move that tank record, with its unique ID, …

I think you are asking whether you can store the actual images in subfolders for each user, and that should be fine.

The file structure could look something like this:

/images/
|
|-------->oneuser/
| |
| |------>image1.jpg
| |
| |------>image2.jpg
|
|-------->anotheruser/
| |
| |------>image1.jpg
| |
| |------>image2.jpg
|
.... and so on

where oneuser and anotheruser are user IDs in the users table of the database. You could use a mkdir function to create the subfolder when a user creates an account, and then when you want to save or retrieve one of their images you could build the path on the fly with something like:

$path_to_image_file = "/images/" . $username . "/" . $image_to_retrieve;

(You need to make sure there all the characters in the usernames and image names are legal in your webserver's file system. You can limit which characters a user can choose when they sign up. You may want to assign each uploaded image a new name--perhaps based on a timestamp--before you save it permanently.)

I still think you would want to keep a record of each image uploaded in the database, but you would just keep the filename, not the full path, in each record. You could then add meta-data to each image record if you wanted, like a caption, camera used, date taken, tags, etc. (You could store the image data …

I think that if you are using a WHERE clause to find an existing row in a table to change a value, you should be using UPDATE instead of INSERT INTO. (You also have to use the syntax "SET column=value"

Something like:

$loginid = mysql_query("UPDATE userinformation SET login_id='".$randomkey."' WHERE `username` = '".$username."'") or die(mysql_error());

Double-check this in the MySQL manual for your version of MySQL, since I am hardly an expert.

I think you can easily use just one table to store the images, or better yet, filepaths to images that you save in an directory on your filesystem.

Then, just have a foreign key in your images table that references your user accounts table. When a user wants to see only her images, your app can retrieve just those images with a "WHERE account_id = $account_id" (in PHP for instance).

You could also add a flag to each image record which indicates whether the image is also available for public view, a la Flickr.

For example,

CREATE  TABLE IF NOT EXISTS `accounts` (
  `account_id` INT(10) NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(255) NOT NULL ,
  `password` CHAR(64) NULL ,
  PRIMARY KEY (`account_id`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `images` (
  `image_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `filename` VARCHAR(255) NOT NULL ,
  `account_id` INT(10) UNSIGNED NOT NULL ,
  `is_public` TINYINT(1) NOT NULL DEFAULT 1 ,
  PRIMARY KEY (`image_id`) ,
 INDEX `fk_image_account1` (`account_id` ASC) ,
  CONSTRAINT `fk_image_account1`
    FOREIGN KEY (`account_id` )
    REFERENCES `accounts` (`account_id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;

I hope this helps.

I am updating a conference website, and I've come across a design problem that has me stumped. I have tables for:
-a company's account
-person (each company can have many persons in its account)
-conference sections (for example, 1-day, 2-day, or 3-day attendance)
-a lookup table relating people to sections (this would allow a company to have 3 people attend this year, but then only send 2 of those 3 next year, for instance)

Here are abbreviated CREATE statements:

CREATE  TABLE IF NOT EXISTS `accounts` (
  `account_id` INT(10) NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(255) NOT NULL ,
  `password` CHAR(64) NULL ,
  `email` VARCHAR(255) NOT NULL ,
  `company` VARCHAR(255) NULL ,
  PRIMARY KEY (`account_id`) )  (... remainder of CREATE statement omitted)


CREATE  TABLE IF NOT EXISTS `persons` (
  `person_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `firstname` VARCHAR(45) NOT NULL ,
  `lastname` VARCHAR(45) NOT NULL ,
  `jobtitle` VARCHAR(255) NULL DEFAULT NULL ,
  `phone` CHAR(12) NULL DEFAULT NULL ,
  `email` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`person_id`) ,
  INDEX `fk_person_account` (`account_id` ASC) ,
  CONSTRAINT `fk_person_account`
    FOREIGN KEY (`account_id` )    (... remainder of CREATE statement omitted)

CREATE  TABLE IF NOT EXISTS `sections` (
  `section_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `startdate` DATE NULL ,
  `enddate` DATE NULL ,
  `base_price` INT(10) NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`section_id`)     (... remainder of CREATE statement omitted)

CREATE  TABLE IF NOT EXISTS `person_attending_section` (
  `person_id` INT(10) UNSIGNED NOT NULL ,
  `section_id` INT(10) UNSIGNED NOT NULL , …

You should try to make 3 tables: one for the chapters, one for the sections, and one for the questions. Then, you can use the primary key of the Chapters table as a foreign key for the Sections table, and use the primary key of the Sections table as a foreign key of the Questions tables.

This way, you can expand the number of chapters, sections, or questions at will, as well as delete questions. If you delete a section or chapter, you will need a plan for what happens to the orphaned questions (or sections and questions).

Hope this gets you started.

ShawnCPlus is right about the missing elements in the snippet.

You will need to include the jQuery library in your page. See this article for different ways to do that:

http://www.ajaxprojects.com/ajax/tutorialdetails.php?itemid=609

To learn more about jQuery itself, the best place to start is the project site:

jquery.com

I've found that learning about Javascript is best done by task.

For instance, make a "Contact" form, and add Javascript to verify that fields are filled out, email and phone numbers are valid. This will be re-inventing the wheel, but there are many online resources to "check your answer", so to speak.

Others exercises you might try are showing/hiding elements on the page, like making an FAQ where only the questions are visible and then when a question is clicked, the answer appears below it. Again, you can find many functions to do this online, but the act of doing it yourself will teach you a lot.

Good luck.

I think PHP is a good intro to server-side programming. (Javascript is useful, but since it's client-side only, it can't handle things like form submission or interacting with a database.)

I started learning PHP on my own about the same time I had to learn some ASP/ASP.NET for my job, and PHP is definitely easier to learn on your own.

I started with a book, "Build Your Own Database Driven Website Using PHP and MySQL" by Kevin Yank. It was very good for a beginning, although the cheesy humor may not be for everyone's taste. I've been working with PHP for over a year now and am currently reading "PHP and MySQL Web Development" by Luke Welling and Laura Thompson (Addison-Wesley). It's well-written, but more dense. Have a look at both books to see if either of their styles suit the way you learn.

w3schools.com has some good online tutorials and a reference guide to PHP (among other languages) that's more streamlined than the official docs at php.net

Good luck.