Jump to content
Larry Ullman's Book Forums

Recommended Posts

Larry I want to understand very well join commands and in this direction I made a database to work with, but I'm not sure if I designed in in the right way. The example is cars.

 

table car_name:
car_id
car_name

 

 

table car_type:
type_id
car_id
car_type

 

 

table drivers:
driver_id
driver_name

 

 

table about_car:
id
car_id
type_id
driver_id
description

 

Should I put cars, car type and description in one table and driver separate or leave it how it is right now?

Link to comment
Share on other sites

Could you provide more details about your example? What are you trying to model? What questions will be asked? How will it be used? For example, in real life, one driver is not tied to one car. Also, I can say it's going to be a problem that you have car_id in both car_type and about_car. What is the purpose of the about_car table?

Link to comment
Share on other sites

I will show you a better example. Let's say that this is a site for jokes. I will have categories, that will be the menu as well: jokes, short films, stories. So these are the categories.

Now the subcategories will be for 

jokes: sailors, plolice men, blondes, etc

shorts films: funny films, crazy films, wow films

stories: funny stories, wise stories

 

Subsubcategories

stories: funny stories: usa, canada, europe

 

Of course the list for categories and subcategories could be longer then what I put here.

So in this direction I made the tables:

CREATE TABLE `categories` (
    `category_id` SMALLINT NOT NULL AUTO_INCREMENT,
    `category` VARCHAR(30) NOT NULL,
    PRIMARY KEY (`category_id`),
    UNIQUE KEY `category` (`category`)
) ;

CREATE TABLE `subcategories` (
    `subcategory_id` SMALLINT NOT NULL AUTO_INCREMENT,

    `category_id` SMALLINT NOT NULL,
    `subcategory` VARCHAR(30) NOT NULL,
    PRIMARY KEY (`subcategory_id`),
    UNIQUE KEY `subcategory` (`subcategory`)
) ;

 

CREATE TABLE `subsubcategories` (
    `subsubcategory_id` SMALLINT NOT NULL AUTO_INCREMENT,    

    `subcategory_id` SMALLINT NOT NULL,
    `subsubcategory` VARCHAR(30) NOT NULL,
    PRIMARY KEY (`subsubcategory_id`),
    UNIQUE KEY `subsubcategory` (`subsubcategory`)
) ;


CREATE TABLE `pages` (
    `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_id` SMALLINT UNSIGNED NOT NULL,
    `subcategory_id` SMALLINT UNSIGNED NOT NULL,
    `user_id` SMALLINT UNSIGNED NOT NULL,
    `title` VARCHAR(100) NOT NULL,
    `content_page` LONGTEXT NOT NULL,
    `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `category_id` (`category_id`),
    KEY `creation_date` (`date_created`)
);



CREATE TABLE `users` (
    `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` ENUM('member','admin', 'superadmin') NOT NULL,
    `username` VARCHAR(30) NOT NULL,
    `email` VARCHAR(80) NOT NULL,
    `pass` VARBINARY(32) NOT NULL,
    `first_name` VARCHAR(20) NOT NULL,
    `last_name` VARCHAR(40) NOT NULL,
    `date_expires` DATE NOT NULL,
    `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `date_modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `username` (`username`),
    UNIQUE KEY `email` (`email`)
) ;

 

Sometimes I will have subsubcategories, sometimes I will not have.

 

Please tell me if i designed the database corect.

Link to comment
Share on other sites

Um...close, but not quite. The first issue, which you made previously, is that you link two tables together in two places, which you should never do. Here you have category_id in the subcategories table, which makes sense, but you also have both category_id and subcategory_id in pages, which doesn't make sense. You may have an easier time spotting this issue if you draw out the relationships and tables on paper.

 

Second, this is too abstracted for my tastes. If you say you'd have top-level categories of jokes, short films, and stories, then I'd expect to see tables with those names on them. 

 

The exact design would also depend upon the answers to two questions:

 

- Would multiple top-level categories (e.g., jokes, short films, and stories) ever use the same subcategory?

- Would a top-level category have multiple subcategories?

Link to comment
Share on other sites

i understand what you are saying but that means to make a lot of tables if jokes, short films, and stories will have each one a table. If I will have 10 categories that means 10 tables.

If I follow this idea will mean that each subcategory from jockes will have a diff table. What will happening if I will have subsubcategories.

A lot of tables.

If I don't understand exactly what you said please take  a simple ex, or mine, and explain. Thank you.

Link to comment
Share on other sites

There's nothing wrong with lots of tables. In fact, lots of tables for a normalized database is to be expected. 

 

Here's a car example:

 

drivers: id, name

makes: id, make

models, id, make_id, model

cars: id, model_id, vin, color, any other identifying attributes

drives: id, driver_id, car_id, start, end

 

Again, what matters is what's trying to be answered: what questions will be asked of the data and what information would be provided in the replies. This database allows you to identify unique cars (as in the physical thing), by make and model and other identifying attributes. You also have unique drivers. And you can record who drove what car when and for how long. 

 

Notice that you get the car's make by going car.model_id -> models.id -> models.make_id -> makes.id -> make.

Link to comment
Share on other sites

Having this database:

CREATE TABLE `categories` (
    `category_id` SMALLINT NOT NULL AUTO_INCREMENT,
    `category` VARCHAR(30) NOT NULL,
    PRIMARY KEY (`category_id`),
    UNIQUE KEY `category` (`category`)
) ;

CREATE TABLE `subcategories` (
    `subcategory_id` SMALLINT NOT NULL AUTO_INCREMENT,
    `category_id` SMALLINT NOT NULL,
    `subcategory` VARCHAR(30) NOT NULL,
    PRIMARY KEY (`subcategory_id`),
    UNIQUE KEY `subcategory` (`subcategory`)
) ;



CREATE TABLE `pages` (
    `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_id` SMALLINT UNSIGNED NOT NULL,
    `subcategory_id` SMALLINT UNSIGNED NOT NULL,
    `user_id` SMALLINT UNSIGNED NOT NULL,
    `title` VARCHAR(100) NOT NULL,
    `content_page` LONGTEXT NOT NULL,
    `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `category_id` (`category_id`),
    KEY `creation_date` (`date_created`)
);


CREATE TABLE `users` (
    `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` ENUM('member','admin', 'superadmin') NOT NULL,
    `username` VARCHAR(30) NOT NULL,
    `email` VARCHAR(80) NOT NULL,
    `pass` VARBINARY(32) NOT NULL,
    `first_name` VARCHAR(20) NOT NULL,
    `last_name` VARCHAR(40) NOT NULL,
    `date_expires` DATE NOT NULL,
    `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `date_modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `username` (`username`),
    UNIQUE KEY `email` (`email`)
) ;

I want to perform a second Select tag into the form. This second Select tag will bring the items from database, depending on the first Select. I am not sure how to do the sql  WHERE for the second Select tag.

i hope you understand what I say. When I click on the first select and select on item, in the second select I want to have only the items related to the item_id that I selected.

 

So here is the add_page.php with the form and 2 select tags, from your book:

<?php
require_once ('includes/config.inc.php');
require ('includes/form_functions.inc.php');

// Redirect non-administrators:
//redirect_invalid_user('user_admin');

$page_title = 'Add a Site Content Page';
include ('includes/header.php');

// Require the database connection:
//require(MYSQL);

// Create an array for storing errors:
$add_page_errors = array( );

// Validate the page title:
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    
    // Validate the category:
    if (filter_var($_POST['category'], FILTER_VALIDATE_INT, array('min_range' => 1))) {
        $cat = $_POST['category'];
    } else {
        $add_page_errors['category'] = 'Please select a category!';
    }
    
    // Validate the subcategory:
    if (filter_var($_POST['subcategory'], FILTER_VALIDATE_INT, array('min_range' => 1))) {
        $subcat = $_POST['subcategory'];
    } else {
        $add_page_errors['subcategory'] = 'Please select a subcategory!';
    }
    
    // Validate the title:
    if (!empty($_POST['title'])) {
        $t = mysqli_real_escape_string($dbc, strip_tags($_POST['title']));
    } else {
        $add_page_errors['title'] = 'Please enter the title!';
    }
    
    
    // Validate the content:
    if (!empty($_POST['content_page'])) {
        $allowed = '<div><p><span><br><a><img><h1><h2><h3><h4><ul><ol><li><blockquote>';
        $c = mysqli_real_escape_string($dbc, strip_tags($_POST['content_page'], $allowed));
    } else {
        $add_page_errors['content_page'] = 'Please enter the content!';
    }
    
    // If there are no errors, add the record to the database:
    if (empty($add_page_errors)) { // If everything's OK.
        $q = "INSERT INTO pages (category_id, subcategory_id, title, content_page, user_id) VALUES ($cat, '$subcat', '$t', '$c', 1)";
        $r = mysqli_query ($dbc, $q);
        if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.
            echo '<h4>The page has been added!</h4>';
            $_POST = array( );
        } else { // If it did not run OK.
        trigger_error('The page could not be added due to a system error. We apologize for any inconvenience.');
        }
    } // End of $add_page_errors IF.
} // End of the main form submission conditional.
?>



<form action="add_page.php" method="post" accept-charset="utf-8">
    <fieldset><legend>Completeaza toate campurile:</legend>
        
        <p><label for="category"><strong>Categorie</strong></label><br />
        <select name="category"<?php if (array_key_exists('category',
        $add_page_errors)) echo ' class="error"'; ?>>
        <option>Select One</option>
        <?php // Retrieve all the categories and add to the pull-down menu:
        $q = "SELECT category_id, category FROM categories ORDER BY category ASC";
        $r = mysqli_query ($dbc, $q);
        while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
            echo "<option value=\"$row[0]\"";
            // Check for stickyness:
            if (isset($_POST['category']) && ($_POST['category'] == $row[0]) )
            echo ' selected="selected"';
            echo ">$row[1]</option>\n";
        }
        ?>
        </select><?php if (array_key_exists('category', $add_page_errors))
        echo ' <span class="error">' . $add_page_errors['category'] .
        '</span>'; ?></p>
        
        
        
        <p><label for="subcategory"><strong>Subcategorie</strong></label><br />
        <select name="subcategory"<?php if (array_key_exists('subcategory',
        $add_page_errors)) echo ' class="error"'; ?>>
        <option>Select One</option>
        <?php // Retrieve all the subcategories and add to the pull-down menu:
        $q = "SELECT
        categories.category_id,
        categories.category,
        subcategories.subcategory_id,
        subcategories.category_id,
        subcategories.subcategory
        FROM
        categories
        INNER JOIN subcategories ON categories.category_id = subcategories.category_id
         ORDER BY subcategory ASC";
        $r = mysqli_query ($dbc, $q);
        while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
            echo "<option value=\"$row[0]\"";
            // Check for stickyness:
            if (isset($_POST['subcategory']) && ($_POST['subcategory'] == $row[0]) )
            echo ' selected="selected"';
            echo ">$row[1]</option>\n";
        }
        ?>
        </select><?php if (array_key_exists('subcategory', $add_page_errors))
        echo ' <span class="error">' . $add_page_errors['subcategory'] .
        '</span>'; ?></p>
        
        
        <p><label for="title"><strong>Titlu</strong>
        </label><br /><?php create_form_input('title', 'text',
        $add_page_errors); ?></p>
        
        
        <p><label for="content_page"><strong>Continut</strong></label>
        <br /><?php create_form_input('content_page', 'textarea',
        $add_page_errors); ?></p>
        
        <p><input type="submit" name="submit_button" value="Adauga"
         id="submit_button" class="formbutton" /></p>
    </fieldset>
</form>



<?php
include('includes/footer.php');
?>



Link to comment
Share on other sites

Your database design continues to have the same problem as your others: subcategories is related to categories but you have pages related to both subcategories AND categories. It should only be related to subcategories (since subcategories is related to categories already). As is, say subcategory 4 is related to category 2: your design allows a page to be associated with subcategory 4 but category 3, which would be bad. 

 

As another example, take the cars database I mapped out. You have makes--Toyota, Honda, etc.--and you have models--(Toyota) Camry, (Toyota) Sienna, (Honda) Fit, (Honda) Odyssey. When you represent individual cars, that table should only store the *model*. If you allowed the cars table to store both the make and the model, it could be possible to say a car is a Honda Sienna or a Toyota Fit, neither of which exists. On the other hand, if you store just the model, by knowing a car is a Fit, you can run a query to find that means it's a Honda Fit.

Link to comment
Share on other sites

Larry, on the add_page I have to have the categories, subcategories, title and description of the page. When I do the sql SELECT categories and bring in the select tag, then SELECT subcategories and bring in the select tag, I do that because I have to know from that a specific page belongs to an category and what subcategory.

So the database is in that way because when I do the insert

INSERT INTO pages (category_id, subcategory_id, title, content_page, user_id) VALUES ($cat, '$subcat', '$t', '$c', 1

I can't insert in pages only the category without the subcategory.

So this is the add_page live, of course I didn't put the css yet.

 

http://aproapetot.ro/add_page.php

 

In the left you can see all the categories that I brought from database. Those are jokes categories. So when I click on Bancuri(only in this I inserted) I will see the subcategories. Click on subcategory Albanezi an view the page.

 

So in the end you tell me not to put a subcategory Select tag? Because my question was about that tag not the database :).

If it's like that, I am confused. I don't know how to do the insert then and secondly to do that 'route' on php pages:

categories->subcategories->view page

Link to comment
Share on other sites

Okay, if you only want me to address the question of the SELECT tags (and allow you to make a very large, foundational mistake), so be it...

 

The values of the second SELECT menu are dictated by the choice in the first SELECT menu. Since you cannot update the second SELECT menu using PHP without a form submission, you have two choices:

  • Break the form into a two-step process, first selecting the category, and then the subcategory and everything else on the next page.
  • Use JavaScript to dynamically change the values in the second SELECT based upon the selection in the first.

The latter route is the more common and practical. If you search using "javascript chained selects" or "javascript related selects", you'll find some explanations and examples.

Link to comment
Share on other sites

A friend that knows js and php helped me. He told me this is Ajax.

After your answer and after his help, my question to you is: Can you, please, recommend me a js with php book?

$(document).ready(function() {
    //var q = $('#categ_id').val();
		$('.categ_id').on("change", function () {
			q = $(this).val();
			$.ajax({
				url: 'http://aproapetot.ro/ajax/get_subcateg.php',
				data: 'categ_id='+q,
				success: function (data) {
					$('#subcateg_id').html(data);
				}
			}).error(function() {
				alert ('An error occured');
			});
		});
	});


 

<?php
require_once ('../includes/mysql.inc.php');
    $categ_id = (isset($_GET['categ_id'])&&is_numeric($_GET['categ_id']))?$_GET['categ_id']:die("The category is not set or it has a wrong value.");
?>
<select name="subcategory">
    <option>Select One</option>
    <?php
        $q = 'SELECT `subcategory_id`, `subcategory` FROM `subcategories` WHERE `category_id`='.$categ_id.' ORDER BY `subcategory` ASC';
        $r = mysqli_query ($dbc, $q);
        while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
            echo "<option value=\"$row[0]\">$row[1]</option>\n";
        }
?>
Link to comment
Share on other sites

If your friend did the JavaScript properly, it's all in another file that gets included by the PHP page. As for a JavaScript + PHP book, my "Modern JavaScript: Development and Design" teaches the fundamentals of JavaScript and has a chapter explaining how to use PHP and JavaScript together. 

Link to comment
Share on other sites

 Share

×
×
  • Create New...