Need help on creating a confusing statement page

[shenagsandnags]

Before i go any further, here is the structure of my DB. i only have 2 tables.

 

Movies Table

_____________

ID  |Title  |Category  |URL

_________________________________________

1    Avatar    Action      www.abc.com

2    Bait      Thriller    www.whatever.com

3    Conair    Drama      www.sddfssdd.com

4    Craft    Horror      www.gdgsdfsd.com

5    Erie      Horror      www.fsadfa.com

6    Fighter  Drama      www.lkjlkjl.com

7    GTown    Action      www.jkkjlk.com

 

 

 

 

 

Categories Table

________________

ID  |Category

_________________

10    Action

11    Drama

12    Horror

13    Thriller

 

 

Ok so I need to create a php page and add a statement to retrieve data from 2 tables. i need the statement to create a table for each category listed in the CATEGORIES Table. Like so,

 

Drama

--------

 

 

Action

--------

 

 

Thriller

--------

 

 

Horror

--------

 

 

Then i need to fill each one of those Category tables (above) with the titles from the MOVIES Table, depending which category the titles have next to them in the MOVIES Table is what determines which category table they will fall into.  :

 

 

Drama

--------

Conair

Fighter

 

 

Action

--------

Avatar

Gtown

 

 

Thriller

--------

Biat

 

 

Horror

--------

Craft

Erie

 

 

Also keep in mind that there may be times that i need to Add/Delete categories and i hope that if i was too add a new category in the CATEGORIES Table it would automaticly show on my php page without having to edit my statement (i hope thats possible anyways). As far as what order the tables would go it would be kind of neat if it would show the Category with the most Titles in it to be placed at the top and so on.

 

One last thing, you may notice that there is a URL field in the MOVIES Table. thats because when the titles are being shown in the php statement page results i need the title to be the actual link itself insteal of it showning the URL link beside the title. i have already asked this once before but i dont know how i would add this into all of the above.

 

I hope i dont seem im asking for alot but the least anyone can do is point me in the right direction, details in what exactly im looking for. i have been reading about order and sorting over and over so much and im just stuck.

[mikosiko]

the first thing you must do is fix your  Movie Table

This:

 

Movies Table

_____________

ID  |Title  |Category  |URL

_________________________________________

1    Avatar    Action      www.abc.com

2    Bait      Thriller    www.whatever.com

3    Conair    Drama      www.sddfssdd.com

4    Craft    Horror      www.gdgsdfsd.com

5    Erie      Horror      www.fsadfa.com

6    Fighter  Drama      www.lkjlkjl.com

7    GTown    Action      www.jkkjlk.com

 

should be:

Movies Table

_____________

ID  |Title  |Category  |URL

_________________________________________

1    Avatar    10      www.abc.com

2    Bait        13    www.whatever.com

3    Conair    11      www.sddfssdd.com

4    Craft      12      www.gdgsdfsd.com

5    Erie      12      www.fsadfa.com

6    Fighter  11      www.lkjlkjl.com

7    GTown  10      www.jkkjlk.com

 

that mean that you must change your table definition and be sure that the field "category" have the same datatype/length that the corresponding in the table Categories and also that you define a FOREIGN KEY on that field.

 

after that a SELECT like this will pull the data from both tables ordered by category

 

SELECT a.ID, 
       a.Title,
       b.Category,
       a.URL
  FROM Movies AS a 
     JOIN Categories b ON a.ID = b.ID
  ORDER BY b.Category

 

the rest is only write the logic to display the info in the way that you want  (lot of examples here in the forum).

 

[mikosiko]

correction... the select should be:

SELECT a.ID, 
       a.Title,
       b.Category,
       a.URL
  FROM Movies AS a 
     JOIN Categories b ON a.Category = b.ID
  ORDER BY b.Category

[shenagsandnags]

ok great great this is what i needed! ok first question given the chance that i get to try this out before you see this i may have it figured out but just to be on the safe side.  so first im going to make sure MOVIES/Category field and CATEGORIES/Category should both be INT/11 or VARCHAR/24 ? (im going with INT/11)  and then as far as the keys, i forgot to mention while i was typin out my db structure in my post that ID in both tables are set to PRIMARY. as i am just learning i am going to take a wild guess and assume that you are telling me that i need to change ID on the CATEGORIES table to FOREIGN KEY OR set Category in CATEGORIES to FOREIGN KEY ? to make sure it points to the PRIMARY on MOVIES.

 

 

just a little side question here,  you dont have to reply if you dont have the time this is just more of being curious and wanting to know how things work. in the select that you typed out i noticed that using a and b before the fields (a.Title,b.Category, JOIN Categories b).  from all the tutorials and lessons i have read i have never seen those used like that and maybe its something i just haven't got to yet but could you tell me exactly what those are doing ?

 

BTW thanks for taking the time out for you previous reply because what you are showing me i am using to go back to the tutorials go back over everything and now i am seeing more of what makes X do Y because of something that i "created" in my head and i know for most people the tutorials may do just fine but for some reason the way my mind works those alone are just too difficult so this is giving me the hands on that i need. in other words thanks for help teaching me how too fish

[mikosiko]

so first im going to make sure MOVIES/Category field and CATEGORIES/Category should both be INT/11 or VARCHAR/24 ? (im going with INT/11)

 

You must use the data type that provide your model with the necessary room to fulfill the requirements of your business...

take a look to the available data types an choose the best one in the previous terms and storage

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html ... I'll probably choose a SMALLINT in your case.

 

OR set Category in CATEGORIES to FOREIGN KEY ? to make sure it points to the PRIMARY on MOVIES.

Correct.

 

curious and wanting to know how things work. in the select that you typed out i noticed that using a and b before the fields (a.Title,b.Category, JOIN Categories b).

 

When you have operations over tables that have columns with the same name you must avoid ambiguities , therefore you must qualify the columns either using the table name or using alias as a prefix as I did in the example

 

 

 

 

[shenagsandnags]

well i guess i suck at fishing...

 

 

so after messing around with it some somehow i ended up changing the password to my db and got locked out of phpmyadmin. i dunno what the hell i did but i thought i had it working but couldnt test it out because something was going on with apache from a previous problem that i had forgot about.  ANYWAYS, i started fresh on a different computer annnnnnd this is my story...

 

once upon a time i created 2 tables in phpmyadmin.

 

CREATE TABLE `M`.`Movies` (
`ID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Title` VARCHAR( 24 ) NOT NULL ,
`Category` SMALLINT( 11 ) NOT NULL ,
`URL` VARCHAR( 24 ) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `M`.`Categories` (
`ID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Category` SMALLINT( 11 ) NOT NULL
) ENGINE = InnoDB;

 

I then index Categories/Category and click on relational view to set FK.

under relational view/Category (second drop down menu, not internal relations)  i select  M' 'Categories' 'Category' ON DELETE AND ON UPDATE both cascade, click save and it creates:

 

 

ALTER TABLE `categories` ADD FOREIGN KEY ( `Category` ) REFERENCES `M`.`categories` (
`Category`
) ON DELETE CASCADE ON UPDATE CASCADE ;

 

 

 

So i then go back to my categories table and attempt to insert some data under Category and i get a error:

 

 

 

SQL query: Edit

INSERT INTO `M`.`categories` (
`ID` ,
`Category`
)
VALUES (
NULL , 'Drama'
)

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`m`.`categories`, CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`Category`) REFERENCES `categories` (`Category`) ON DELETE CASCADE ON UPDATE CASCADE) 

 

 

 

now i know what your thinking, im trying to type in letters in the value when its SMALLINT, well even when i try to put in a number i still get the same error.  ok so before i had my dumbass attack and locked myself from my old DB i had actually used INT.  well everything worked smooth untill after doing some views i realized that it was only showing numbers instead of the actual name of the categorys, its obvious why, so i switched to VARCHAR just for the hell of it and i thought i had it working but as i was about to test out the select code is when i realized my apache problem and then right after that is when i locked myself out. note that when doing all this i had been past due for bedtime by about 4 hours and only had about 4 hours of sleep that day so ITS hard telling what the hell i did but point being i wasnt gettin the error i am getting now on the new computer and DB. this is probably like pissing in the wind for you but its had me scratching my head for hours.. i dunno what i have done different.

 

oh and by the way, if i am supose to set MOVIES/Category and CATEGORIES/Category both to a numerical data type such as SMALLINT then how am i supose to type in my Category names in my CATEGORIES table ?

 

i am going to continue going back everything just incase its something simple im missing here.. sorry like i said im still new.

[mikosiko]

this is an example for your tables (adjust the datatye/size as needed):

 

CREATE TABLE  `categories` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Category` varchar(45) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB;

 

CREATE TABLE  `movies` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Title` varchar(45) NOT NULL,
  `Category` int(10) unsigned NOT NULL,
  `URL` varchar(45) NOT NULL,
  PRIMARY KEY (`ID`),
  CONSTRAINT `FK_movies_1` FOREIGN KEY (`ID`) REFERENCES `categories` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB;

 

in your intent you defined the FK in the wrong table.

[shenagsandnags]

ok got it all sorted out now (thanks again, really) but the SELECT statement is not working like the way it supose to be so its gotta be something i am doing wrong again.

 

I entered 4 sample movies into the database and then ran this as test.php

 

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("M5", $con);

$result = mysql_query("SELECT a.ID, 
       a.Title,
       b.Category,
       a.URL
  FROM Movies AS a 
     JOIN Categories b ON a.Category = b.ID
  ORDER BY b.Category");

echo "<table border='1'>
<tr>
<th>Title</th>
<th>Category</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Title'] . "</td>";
  echo "<td>" . $row['Category'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?> 

 

 

and obviously from reading the code i get:

 

    Title              |  Category

--------------------------------------

Date Night      | Comedy

Up in smoke    |  Comedy

Titanic            | Drama

Avatar            | Drama

Saw                Horror

 

Instead of each one of those categories having their own table.

 

Just with my little knowledge that i have, i have a feeling that this may have something to do with something in the mysql_fetch_array ???  i know its not typed out right for the SELECT.

[mikosiko]

In my answer #1... I said by the end of that post:

 

....the rest is only write the logic to display the info in the way that you want  (lot of examples here in the forum).

 

... so.... start fishing

 

[shenagsandnags]

Im back from fishing and look what i caught paw!

 

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("M5", $con);

$result = mysql_query("SELECT a.ID, 
       a.Title,
       b.Category,
       a.URL
  FROM Movies AS a 
     JOIN Categories b ON a.Category = b.ID
  ORDER BY b.Category");

$result = mysql_query("SELECT Title From Movies");

while($row = mysql_fetch_array($result))
  {

echo "<table border='1'>
<tr>
<th>Title</th>
<th>Category</th>
</tr>";
  echo "<tr>";
  echo "<td>" . $row['Title'] . "</td>";
  echo "<td>" . $row['Category'] . "</td>";
  echo "</tr>";
while($row2 = mysql_fetch_array($result2))
{
  echo "<tr>";
  echo "<td>" . $row2['Title'] . "</td>";
  
  echo "</tr>";
}
echo "</table>";
  }

 

 

i have a Undefined Index on line 30 and if i take that line out completely i  get another error about a variable on the next line or so but obviously that is the least of my problems because i know i got something in this code messed up but i feel that im sooooo close.  i learned that i should go with "while loops" and this is about as far as i can get.  what is it that i have wrong ?  i know that this post has went on a little too long but you have no idea how much your help has been!

[mikosiko]

... lets see...

 

second while loop is totally unnecessary and wrong... what you need to do is something like this (one alternative) .. short and incomplete example just to show you the basic:

 

$category_old = "";
while($row = mysql_fetch_array($result)) {
  if ($row['category] != $category_old) {
       // print the category in the way that you want here... echo for now
       echo $row['category'];
       $category_old = $row['category'];
  {
   echo here the rest of your data
}