bionic25

hey guys, i am having problems with foreign keys. my code below all executes fine in mysql, returning data for all the tables, except for 'activities' which is usinf foreign keys to reference the correctly functioning other tables. Can anyone see what is wrong because after looking at other examples for hours i cant figure it out! CREATE TABLE modules ( module_id INT AUTO_INCREMENT, deptcode VARCHAR(10), module VARCHAR(10), title VARCHAR(255), Primary Key(module_id) ); CREATE TABLE lecturers ( lecturer_id INT AUTO_INCREMENT, fname VARCHAR(50), lname VARCHAR(50), Primary Key(lecturer_id) ); CREATE TABLE groups ( group_id INT AUTO_INCREMENT, groupName VARCHAR(12), Primary Key(group_id) ); INSERT INTO modules (deptcode, module, title) SELECT deptcode, module1, title1 FROM tempHolder; INSERT INTO lecturers (fname, lname) SELECT SUBSTRING_INDEX(lecturer1, ', ', -1), SUBSTRING_INDEX(lecturer1, ', ', 1) FROM tempHolder; INSERT INTO groups (groupName) SELECT group1 FROM tempHolder; CREATE TABLE activities ( activity_id INT AUTO_INCREMENT, module_id integer, lecturer_id integer, group_id integer, Primary Key (activity_id), Foreign Key (module_id) references modules(module_id), Foreign Key (lecturer_id) references lecturers(lecturer_id), Foreign Key (group_id) references groups(group_id) ); //For debugging, output all results select * from modules where module_id=3; select * from lecturers where lecturer_id=3; select * from groups where group_id=3; select * from activities; thanks in advance for any help

Thanks premiso, thats very useful. The main thing confusing me is this: There can be more than one lecturer per row. I have, therefore, made a separate table called "Lecturers". However each ID in this table only holds one lecturer's details, to comply with atomicity. How can I have the details of 2 Lecturers in the main table listing, or do I have to accept using multiple numbered columns like "Lecturer1", "Lecturer2" etc? thanks again for your replies

I agree with you! the problem i'm having is getting the database from unnormalised into 3NF :-\

ive already had some experience of 1st and thought id have a got at 3rd. ive also heard that u dont really need to go beyond 3rd until you're like doing really compliacted stuff

thanks for your quick reply its not homework lol just a project im using to try and learn sql, i did the same with php and am okay at that now. i dont want the work done for me just help figuring it out. the other problem is the raw data comes in a .csv file. when i input the data how do i get it to disperse to the other tables sensibly. for example if i had a 'modules' table to hold the modules, and it was linked from the main table like how do i get the right module_ids to show in the main table. obviously in the 'modules' table itself the data will just be id'd with auto_inc but i dont get how the refernce in the main table will know what it is

i am trying to figure out how to turn the following into a 3NF database but am really stuck and going a bit crazy over it. the field names of the non-normalised table are: "deptcode","module1","title1","module2","title2","module3","title3","group1","group2","group3","group4","group5","type","dayno","start","finish","weeks","lecturer1","lecturer2","lecturer3","lecturer4","lecturer5","lecturer6","lecturer7","lecturer8","room1","room2","room3","room4","room5" its for a university timetable. if anyone could help me out with how to make this 3NF, like what new tables foreign keys etc I would be SOOOO grateful thanks!!

Brilliant works great now! i see the problem looks so obvious now but well done for spotting it! thanks to all the responses appreciate it just need to get good enough to help out some others now !

also bearing in mind that it does actually output the right choice in some way qty=4&submit=add&qty=1&qty=1&qty=1 couldnt i set it as like $qtyString or something and then maniuplate it to cut the dead bits and set the correct bit of string as $qty???

if you want the whole code heres the page that holds it. <?php // Include functions require_once('functions.php'); // Start the session session_start(); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <script src="jq.js"></script> <script> var refreshId = setInterval(function() { $('#refresh')./*fadeOut("slow").*/load('getCart.php')/*.fadeIn("slow")*/; }, 1000); </script> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <div id="refresh" style="float: right; width: 300px; background: #ccc;"> </div> <h1>products</h1> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get" name="qty"> <?php dbcon(); productDump(); addItem(); onDelete(); ?> </form> </body> </html> heres the functions page <?php //FUNCTIONS function dbcon(){ mysql_connect("db1", "**", "**") or die(mysql_error()); mysql_select_db("**") or die(mysql_error()); } function getCart(){ echo "<h2>Basket:</h2>"; dbcon(); /* //testing purposes session_start(); $_SESSION['cart']="123,456,123,123"; */ //eg entry ,123456*01 $items=$_SESSION['cart']; $item=explode(',',$items); foreach ($item as $x) { $pidBit = substr($x, 0, 6); echo "$pidBit"; $result = mysql_query("select * from products where id= $pidBit"); while($r=mysql_fetch_array($result)) { $title = $r['title']; $description = $r['description']; $price = $r['price']; echo "$title<br />$description<br />$price <a href=this.php?action=remove&pid=$pidBit>remove</a><hr />"; } } } function onDelete(){ if ($_GET['action']=='remove') { $delPid = $_GET['pid']; $items=$_SESSION['cart']; $item=explode(',',$items); foreach ($item as $x) { if ($pidBit == $x) { //$items - $x = (session-pid) $rep = ",".$pidBit; $newCart = str_replace($rep, "", $items); /* //handles first item if that is item being deleted $newerCart = str_replace($x, "", $newCart); */ $_SESSION['cart']=$newCart; } else { } } echo "new calculated string: $newCart"; $ses=$_SESSION['cart']; echo " new session output: $ses"; } else{} } function addItem(){ if ($_GET['action']=='add') { $newPid = $_GET['pid']; $qty = $_GET['qty']; $oldCart = $_SESSION['cart']; $newCart = $oldCart.','.$newPid.'*'.$qty; $_SESSION['cart']=$newCart; } else { } } function productDump(){ $result = mysql_query("select * from products"); while($r=mysql_fetch_array($result)) { $title = $r['title']; $description = $r['description']; $price = $r['price']; $pid = $r['id']; echo "$title<br />$description<br />$price <select name=qty id=qty> <option value='1'>1</option> <option value='2'>2</option> <option value='3'>3</option> <option value='4'>4</option> <option value='5'>5</option> </select> <input type=submit name=submit value=add /><hr />"; $qty = null;//ADD if (isset($_GET['submit'])) { $qty= (int)$_GET['qty'];//ADD the (int) $url="this.php?action=add&pid=$pid&qty=$qty"; echo "$qty"; //echo "<script>window.location = \"$url\"</script>"; /* echo "<script>window.location = \"$url\"</script>"; */ } else { } } } ?> please note the ocde is still very much under construction and done from scratch with limited knowhow if it looks clumsy.

And there is our problem! It's submitting it right... but you have other form elements named "qty" as well that seems to submit "1". "qty=4&submit=add&qty=1&qty=1&qty=1" Why does your URL look like that? Your $_GET['qty'] can only retrieve the last value set in for 'qty' which is not 4, but 1. how can i make this so it retrieves the chosen value then??

as in add to form tag <form ... name="qty"> ??

yeah i tried that earlier. although it doesnt fix it it looks promising. heres the output in add bar qty=4&submit=add&qty=1&qty=1&qty=1 the qty=4 at the start IS THE CORRECT choice. how can i get this working properly though???

see why u thought that didnt work though. im using numbers in that form to standardise string length in session when users choose a load of items i.e. upto 99. made no difference unfortunately. maybe i should totally rethink this. is there another way i could get the users quantity preference? otherwise i spose i could try having the form on another page and then have <form action="php-page.php"> and have the function on that page any opinions|?

its just outputting '1' now. its still totally ignoring the option i choose. any more ideas? im getting out of my depth now! as for the firefox thing i dont think so, ive tried in IE too and exactly the same

i dumped $qty and got 01string(2) "01" as out put after i choose one of the options

Thanks again for looking into it. still no luck though, its doing what is was before, just returning '01' whatever number u select. heres some screenys with your new code implemented before selection: after choosing option '4' arghh this is soo annoying

thanks for the answer. the problem isnt in constructing the new url. the problem is what you said: $qty doesnt return anything thats why im calling echo "$qty"; to check if it has a value. the most success ive managed to have is using, print_r when i manage to get it to print something like Array ([0]=>4 [1]=>1 [2]=>1 [3]=>1) or something like that. however i couldnt separate these either. its a real headache and the only problem i rly cant figure out any ideas|

just realised i didnt explain this very well. the form that the $_post gets info from is on the same page using the echo server self thing. the problem is that $qty doesnt reutrn a value. how can i get the value from the select option box chosen to add it to the url redirect and therefore add it to the basket. pleeeease help this is frustrating the hell out of me!

I'm working on my first shopping cart app. im very nearly finished, but ive got stuck on the bit where a new item is added. problem is the form that this info is taken from doesnt seem to be reutrning a value. really cant figure out where im going wrong. function productDump(){ $result = mysql_query("select * from products"); while($r=mysql_fetch_array($result)) { $title = $r['title']; $description = $r['description']; $price = $r['price']; $pid = $r['id']; echo "$title<br />$description<br />$price <select name=qty id=qty> <option value=01>1</option> <option value=02>2</option> <option value=03>3</option> <option value=04>4</option> <option value=05>5</option> </select> <input type=submit name=submit value=add /><hr />"; if (isset($_POST['submit'])) { $qty= $_POST['qty']; $url="this.php?action=add&pid=$pid&qty=$qty"; echo "$qty"; /* echo "<script>window.location = \"$url\"</script>"; */ } else { } } } thanks for any help ~~bionic

'vaginal intercourse', 'sex diagram'?!?! lol you have some seriously strange porn searchers on your site

Its a bit bland and looks very templatey, don't know if you've used one but im guessing so. also maybe reread your text before posting, there are typos and the general grammar and style make it quite disjointed sounding. your homepage needs to give the right impression so its really crucial to make it sound aswell as look professional.

I feel like the biggest n00b ever. i only thought it wasnt working cos it wasnt displaying anything and id forgotten to put the header redirect at the bottom. blank page + me being an idiot = me thinking php hasnt executed due to error

thanks for the answer. tried adding the quotes but unfortunately no luck. any other ideas anyone\??

really? because it sets the cookie fine in the bit to add the cookie its only the code to delete it that doesnt work? and i dont use quotes in the cookie bit that works

hey people, need some help with this ecommerce site im working on. this is the code to set a cookie which contains the 'basket' or 'cart' info for the user's session when they click to add a product. it sets the cookie to be a 5 digit string with the product's 3 digit id number then a comma then a space. eg. '123, '. function addItem() { if (isset($_COOKIE['pid'])) { $existPid = $_COOKIE['pid']; $newPid = $_GET['pid']; $sep = ", "; $totalPid = $existPid.$sep.$newPid; $display = $totalPid; //set the cookie setcookie(pid, $totalPid, time()+3600*24); } else { $newPid = $_GET['pid']; setcookie(pid, $newPid, time()+3600*24); } } //add item function called addItem(); the problem is the code i'm using to delete an individual item from the cart isnt working. here it is $item = $_GET['delItem']; if (!isset($_GET['delItem'])) { setcookie("pid","", time()-3600); } else if($_GET['delItem'] == 1) { $str = $_COOKIE['pid']; $newPid = substr_replace($str,'',0,5); setcookie(pid, $newPid, time()+3600*24); } else if($_GET['delItem'] == 2) { $str = $_COOKIE['pid']; $newPid = substr_replace($str,'',5,10); setcookie(pid, $newPid, time()+3600*24); } else if($_GET['delItem'] == 3) { $str = $_COOKIE['pid']; $newPid = substr_replace($str,'',10,15); setcookie(pid, $newPid, time()+3600*24); } else if($_GET['delItem'] == 4) { $str = $_COOKIE['pid']; $newPid = substr_replace($str,'',15,20); setcookie(pid, $newPid, time()+3600*24); } else if($_GET['delItem'] == 5) { $str = $_COOKIE['pid']; $newPid = substr_replace($str,'',20,25); setcookie(pid, $newPid, time()+3600*24); } else if($_GET['delItem'] == 6) { $str = $_COOKIE['pid']; $newPid = substr_replace($str,'',25,30); setcookie(pid, $newPid, time()+3600*24); } else if($_GET['delItem'] == 7) { $str = $_COOKIE['pid']; $newPid = substr_replace($str,'',30,35); setcookie(pid, $newPid, time()+3600*24); } else if($_GET['delItem'] == { $str = $_COOKIE['pid']; $newPid = substr_replace($str,'',35,40); setcookie(pid, $newPid, time()+3600*24); } else if($_GET['delItem'] == 9) { $str = $_COOKIE['pid']; $newPid = substr_replace($str,'',40,45); setcookie(pid, $newPid, time()+3600*24); } else if($_GET['delItem'] == 10) { $str = $_COOKIE['pid']; $newPid = substr_replace($str,'',45,50); setcookie(pid, $newPid, time()+3600*24); } else if($_GET['delItem'] == 11) { $str = $_COOKIE['pid']; $newPid = substr_replace($str,'',50,55); setcookie(pid, $newPid, time()+3600*24); } i made this code from scratch so doubt its done properly as im new to php. however if some could tell me why the $newPid variable is not changing the pid cookie to the correct string with the right bit removed? thanks in advance for any help