corbin Posted March 19, 2009 Share Posted March 19, 2009 sorry about the tags.. well i plan to have user info in one table and then each leasson would be in another table and inside each lesson table there would be a series of questions.. i think that sound pretty normalized. still a noob(its been like 2wks learning php, css, mysql and html(again)) If you think that is normalized, you should read a bit more on DB normalization. Quote Link to comment Share on other sites More sharing options...
Mchl Posted March 19, 2009 Share Posted March 19, 2009 http://datacharmer.blogspot.com/2009/03/normalization-and-smoking.html I said I will start posting it, so I am. Quote Link to comment Share on other sites More sharing options...
jlavik Posted March 20, 2009 Share Posted March 20, 2009 You should already have an auto-incremented id in your table (usually the primary key) so your records always have a unique identifier. If you have one then you don't even need to include it in the INSERT statement, hence the name auto-increment. I'm guessing your reply was directed at my response, since I'm the one that mentioned auto-increment. I am certainly aware that you needn't add the auto-increment field to your insert statement. What I was assuming and alwaysbrasilian later confirmed was that he has another table with user information. His primary key in that table could be an auto-increment, SSN, Student ID, whatever as long as it's unique. That ID would be a foreign key in this table that links the quiz results to the student who took them. If the student can take the quiz more than once, it would not be unique. And since it is not the primary key in this table it should certainly be added. No? Quote Link to comment Share on other sites More sharing options...
Maq Posted March 21, 2009 Share Posted March 21, 2009 You should already have an auto-incremented id in your table (usually the primary key) so your records always have a unique identifier. If you have one then you don't even need to include it in the INSERT statement, hence the name auto-increment. I'm guessing your reply was directed at my response, since I'm the one that mentioned auto-increment. I am certainly aware that you needn't add the auto-increment field to your insert statement. What I was assuming and alwaysbrasilian later confirmed was that he has another table with user information. His primary key in that table could be an auto-increment, SSN, Student ID, whatever as long as it's unique. That ID would be a foreign key in this table that links the quiz results to the student who took them. If the student can take the quiz more than once, it would not be unique. And since it is not the primary key in this table it should certainly be added. No? I was referring to this chunk of code... He has "Where ID = '1' which is going to update the same record over and over: UPDATE lesson01 SET Q01='$Q01', Q02='$Q02', Q03='$Q03' Where ID='1' "; I agree with you, jlavik, that he should be using something similar to: $sql = "INSERT INTO lesson01 (Q01, Q02, Q03, ID) VALUES ('$ques[0]' , '$Q02', '$Q03', $User_ID)"; Because then he would have "ID" which would be the PK for the other user_table's PK. My point was that there should be another auto-increment "quiz_ID" that can keep track of each individual quiz. Quote Link to comment Share on other sites More sharing options...
jlavik Posted March 21, 2009 Share Posted March 21, 2009 I agree with you, jlavik, that he should be using something similar to: $sql = "INSERT INTO lesson01 (Q01, Q02, Q03, ID) VALUES ('$ques[0]' , '$Q02', '$Q03', $User_ID)"; Because then he would have "ID" which would be the PK for the other user_table's PK. My point was that there should be another auto-increment "quiz_ID" that can keep track of each individual quiz. Agreed! Quote Link to comment Share on other sites More sharing options...
redarrow Posted March 21, 2009 Share Posted March 21, 2009 I was reading on database structure, and whale i come across the same problem here. http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html I got to lath,it so easy but it so hard to get everybody to understand the importance of normalization. the best bit is there still huge codes going out to the public un normalized. amazing. why not just normalize who opens there wardrobe, and put there cloths in the washing machine, yee get it! Normalization is a part of relational theory, which requires that each relation (AKA table) has a primary key. Quote Link to comment Share on other sites More sharing options...
alwaysbrasilian Posted March 25, 2009 Author Share Posted March 25, 2009 I finally figured it out. I decided to use sessions to increment to a variable every time the page reloads to keep track of which question i want to display. Where the "where ID = '1' " the '1' would be the users session id number(i think, not sure yet) so i can update the table and keep the user info seperate in another table. the structure of my database hasn't changed since user data is put on a table differently from lessons. hopefully i can shorten/clean my code even more with OOP :-) I'll be back for more problems and if possible to help someone. index.php <?php session_start(); mysql_connect('localhost','', '' ); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Untitled</title> </head> <body> <?php if(@$_SESSION['question'] == 0){ ?> <h1> first question </h1> <form method="post" action="function.php"> <input type="radio" value="1" name="Q01" /> first <input type="radio" value="0" name="Q01" /> second <input type="submit" name="submit" /> </form> <?php } elseif($_SESSION['question'] == 1){ ?> <h1> Second question </h1> <form method="post" action="function.php"> <input type="radio" value="1" name="Q02" /> first <input type="radio" value="0" name="Q02" /> second <input type="submit" name="submit" /> </form> <?php } elseif($_SESSION['question'] == 2) { ?> <h1> third question </h1> <form method="post" action="function.php"> <input type="radio" value="1" name="Q03" /> first <input type="radio" value="0" name="Q03" /> second <input type="submit" name="submit" /> </form> <?php } else { $sql = " SELECT * FROM Lesson01 WHERE ID = '1' "; $result = mysql_query($sql); if($result){ if(mysql_num_rows($result) == 1){ $data = mysql_fetch_assoc($result); $first = $data['Q01']; $second = $data['Q02']; $third = $data['Q03']; } } print 'again'; session_destroy(); } ?> <?php ?> </body> </html> function.php <?php session_start(); $Q01 = @$_REQUEST['Q01']; $Q02 = @$_REQUEST['Q02']; $Q03 = @$_REQUEST['Q03']; function connect() { define( 'DB_UNAME', ''); define( 'DB_PWORD' , ''); define( 'DB_DATABASE', 'languages'); define( 'DB_HOST', 'localhost'); $db_handle = mysql_connect(DB_HOST, DB_UNAME, DB_PWORD) or die ('bad connect'); $db_found = mysql_select_db(DB_DATABASE) or die ('bad database'); } if(@$_SESSION['question'] == 0){ connect(); $sql = mysql_query("UPDATE Lesson01 SET Q01 = '$Q01' Where ID='1' "); $_SESSION['question'] = @$_SESSION['question'] + 1; } elseif ($_SESSION['question'] == 1) { connect(); $sql = mysql_query("UPDATE Lesson01 SET Q02='$Q02' WHERE ID = '1' "); $_SESSION['question'] = @$_SESSION['question'] + 1; } elseif ($_SESSION['question'] == 2) { connect(); $sql = mysql_query("UPDATE Lesson01 SET Q03='$Q03' WHERE ID = '1' "); $_SESSION['question'] = @$_SESSION['question'] + 1; } header ("Location: index.php") ?> 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.