shenagsandnags Posted January 21, 2011 Share Posted January 21, 2011 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. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted January 21, 2011 Share Posted January 21, 2011 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). Quote Link to comment Share on other sites More sharing options...
mikosiko Posted January 21, 2011 Share Posted January 21, 2011 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 Quote Link to comment Share on other sites More sharing options...
shenagsandnags Posted January 23, 2011 Author Share Posted January 23, 2011 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 Quote Link to comment Share on other sites More sharing options...
mikosiko Posted January 23, 2011 Share Posted January 23, 2011 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 Quote Link to comment Share on other sites More sharing options...
shenagsandnags Posted January 24, 2011 Author Share Posted January 24, 2011 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. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted January 24, 2011 Share Posted January 24, 2011 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. Quote Link to comment Share on other sites More sharing options...
shenagsandnags Posted January 25, 2011 Author Share Posted January 25, 2011 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. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted January 25, 2011 Share Posted January 25, 2011 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 Quote Link to comment Share on other sites More sharing options...
shenagsandnags Posted January 26, 2011 Author Share Posted January 26, 2011 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! Quote Link to comment Share on other sites More sharing options...
mikosiko Posted January 26, 2011 Share Posted January 26, 2011 ... 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 } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.