jtravis Posted March 12, 2006 Share Posted March 12, 2006 Hello all! Thanks for looking at my post!(See bottom of this post for actual question) :)I'm working on a project that will allow my to track all of my MTG trading cards and having some difficulties with some queries. I have a form that takes in all the data for the cards and posts it to a page that 1) allows viewing and verification of the data and 2) allows the inserting of the data. The first part works like a clock. It's as failsafe as I think it needs to be. The second is the part that's giving me problems specifically with querying the data from my relational tables and inserting new data into those tables.I've searched high and low and cannot find an answer for this. The initial insert of data works fine see below:[code]//Check for Null values and adjust our SELECT statement that we use to gather the cardID. //This will also decide how our INSERT statement will be contructed. if (($cardCost == "") and ($power == "") and ($toughness == "")){ echo $queryCardID = "select id from t_card where name='$cardName' and text='$cardText' and flavor_text='$flavorText'"; $insertID = 1; }elseif (!($cardCost == "") and ($power == "") and ($toughness == "")){ echo $queryCardID = "select id from t_card where name='$cardName' and cost='$cardCost' and text='$cardText' and ". "flavor_text='$flavorText'"; $insertID = 2; }else{echo $queryCardID = "select id from t_card where name='$cardName' and cost='$cardCost' and text='$cardText' ". "and flavor_text='$flavorText' and power='$power' and toughness='$toughness'"; $insertID = 3; }[/code]The echos above are just for troubleshooting and will be removed once everything is working..[code]//insert initial card data into parent table switch ($insertID){ case 1: $insertDataT_Card = "insert into t_card(name, text, flavor_text, quantity)". " values('$cardName','$cardText','$flavorText', '$quantity')"; mysql_query($insertDataT_Card, $magicDB) or die(mysql_error()); case 2: $insertDataT_Card = "insert into t_card(name, cost, text, flavor_text, quantity)". " values('$cardName', '$cardCost', '$cardText','$flavorText', '$quantity')"; mysql_query($insertDataT_Card, $magicDB) or die(mysql_error()); case 3: $insertDataT_Card = "insert into t_card(name, cost, text, flavor_text, quantity, power, toughness)". " values('$cardName', '$cardCost', '$cardText','$flavorText', '$quantity', '$power', '$toughness')"; mysql_query($insertDataT_Card, $magicDB) or die(mysql_error()); [/code]Everything seems to be working fine until here.[code]//query the auto id of the type that's selected and assign it to a value. SHOULD ONLY RETURN ONE. $queryTypeID = "select id from t_type where type='$type'"; $typeRowID = mysql_query($queryTypeID, $magicDB); while($tRow = mysql_fetch_assoc($typeRowID)) { $rarityID = $tRow[id]; } //insert the id's into the relational tables //TL_CARD_TYPE TABLE echo "<br>Value of Type is $type. TypeID is $typeID which is a ".gettype($typeid)." value.<br>";//troublshooting.. $insertDataTL_Card_Type = "insert into tl_card_type(t_card_id, t_type_id) values('$cardID', '$typeID')"; mysql_query($insertDataTL_Card_Type, $magicDB) or die(mysql_error());[/code]At this point, the script terminates with the following error:[code]Cannot add or update a child row: a foreign key constraint fails (`test/tl_card_type`, CONSTRAINT `tl_card_type_ibfk_1` FOREIGN KEY (`t_card_id`) REFERENCES `t_card` (`id`))[/code]Now... General structure of the database can be found [a href=\"http://internetnothing.com/dbase/dbaseImage.jpg\" target=\"_blank\"]here[/a].I do indeed have foreign keys, however from the CLI I am able to insert this information, so I think the query itself is good and think the problem is with the last section of code. Also the name have all been lowercased.I checked the values of the results and printed it to the screen with [code]echo "<br>Value of Type is $type. TypeID is $typeID which is a ".gettype($typeid)." value.<br>";//troublshooting..[/code]Typically the results that are returned would go into an array, but how do you assign single row query results to a variable? Quote Link to comment Share on other sites More sharing options...
keeB Posted March 12, 2006 Share Posted March 12, 2006 [!--quoteo(post=354249:date=Mar 12 2006, 06:36 PM:name=jtravis)--][div class=\'quotetop\']QUOTE(jtravis @ Mar 12 2006, 06:36 PM) [snapback]354249[/snapback][/div][div class=\'quotemain\'][!--quotec--]Hello all! Thanks for looking at my post!(See bottom of this post for actual question) :)I'm working on a project that will allow my to track all of my MTG trading cards and having some difficulties with some queries. I have a form that takes in all the data for the cards and posts it to a page that 1) allows viewing and verification of the data and 2) allows the inserting of the data. The first part works like a clock. It's as failsafe as I think it needs to be. The second is the part that's giving me problems specifically with querying the data from my relational tables and inserting new data into those tables.I've searched high and low and cannot find an answer for this. The initial insert of data works fine see below:[code]//Check for Null values and adjust our SELECT statement that we use to gather the cardID. //This will also decide how our INSERT statement will be contructed. if (($cardCost == "") and ($power == "") and ($toughness == "")){ echo $queryCardID = "select id from t_card where name='$cardName' and text='$cardText' and flavor_text='$flavorText'"; $insertID = 1; }elseif (!($cardCost == "") and ($power == "") and ($toughness == "")){ echo $queryCardID = "select id from t_card where name='$cardName' and cost='$cardCost' and text='$cardText' and ". "flavor_text='$flavorText'"; $insertID = 2; }else{echo $queryCardID = "select id from t_card where name='$cardName' and cost='$cardCost' and text='$cardText' ". "and flavor_text='$flavorText' and power='$power' and toughness='$toughness'"; $insertID = 3; }[/code]The echos above are just for troubleshooting and will be removed once everything is working..[code]//insert initial card data into parent table switch ($insertID){ case 1: $insertDataT_Card = "insert into t_card(name, text, flavor_text, quantity)". " values('$cardName','$cardText','$flavorText', '$quantity')"; mysql_query($insertDataT_Card, $magicDB) or die(mysql_error()); case 2: $insertDataT_Card = "insert into t_card(name, cost, text, flavor_text, quantity)". " values('$cardName', '$cardCost', '$cardText','$flavorText', '$quantity')"; mysql_query($insertDataT_Card, $magicDB) or die(mysql_error()); case 3: $insertDataT_Card = "insert into t_card(name, cost, text, flavor_text, quantity, power, toughness)". " values('$cardName', '$cardCost', '$cardText','$flavorText', '$quantity', '$power', '$toughness')"; mysql_query($insertDataT_Card, $magicDB) or die(mysql_error()); [/code]Everything seems to be working fine until here.[code]//query the auto id of the type that's selected and assign it to a value. SHOULD ONLY RETURN ONE. $queryTypeID = "select id from t_type where type='$type'"; $typeRowID = mysql_query($queryTypeID, $magicDB); while($tRow = mysql_fetch_assoc($typeRowID)) { $rarityID = $tRow[id]; } //insert the id's into the relational tables //TL_CARD_TYPE TABLE echo "<br>Value of Type is $type. TypeID is $typeID which is a ".gettype($typeid)." value.<br>";//troublshooting.. $insertDataTL_Card_Type = "insert into tl_card_type(t_card_id, t_type_id) values('$cardID', '$typeID')"; mysql_query($insertDataTL_Card_Type, $magicDB) or die(mysql_error());[/code]At this point, the script terminates with the following error:[code]Cannot add or update a child row: a foreign key constraint fails (`test/tl_card_type`, CONSTRAINT `tl_card_type_ibfk_1` FOREIGN KEY (`t_card_id`) REFERENCES `t_card` (`id`))[/code]Now... General structure of the database can be found [a href=\"http://internetnothing.com/dbase/dbaseImage.jpg\" target=\"_blank\"]here[/a].I do indeed have foreign keys, however from the CLI I am able to insert this information, so I think the query itself is good and think the problem is with the last section of code. Also the name have all been lowercased.I checked the values of the results and printed it to the screen with [code]echo "<br>Value of Type is $type. TypeID is $typeID which is a ".gettype($typeid)." value.<br>";//troublshooting..[/code]Typically the results that are returned would go into an array, but how do you assign single row query results to a variable?[/quote]At first glance.. after every switch statement you need a [b]break();[/b][code] switch ($insertID){ case 1: $insertDataT_Card = "insert into t_card(name, text, flavor_text, quantity)". " values('$cardName','$cardText','$flavorText', '$quantity')"; mysql_query($insertDataT_Card, $magicDB) or die(mysql_error()); break(); case 2:[/code]etc.. i'll check the rest in the meantime.. Quote Link to comment Share on other sites More sharing options...
jtravis Posted March 12, 2006 Author Share Posted March 12, 2006 Noted and added... Thanks! Quote Link to comment Share on other sites More sharing options...
keeB Posted March 12, 2006 Share Posted March 12, 2006 Can you also post some of the debugging information like.. print the query out?That might make it a bit easier to troubleshoot. Quote Link to comment Share on other sites More sharing options...
jtravis Posted March 12, 2006 Author Share Posted March 12, 2006 [!--quoteo(post=354274:date=Mar 12 2006, 03:01 PM:name=keeB)--][div class=\'quotetop\']QUOTE(keeB @ Mar 12 2006, 03:01 PM) [snapback]354274[/snapback][/div][div class=\'quotemain\'][!--quotec--]Can you also post some of the debugging information like.. print the query out?That might make it a bit easier to troubleshoot.[/quote]Sure..No problem.Echoing[code]switch ($insertID){ case 1: echo "<br>insertDataT_Card =".$insertDataT_Card = "insert into t_card(name, text, flavor_text, quantity)". " values('$cardName','$cardText','$flavorText', '$quantity')"; mysql_query($insertDataT_Card, $magicDB) or die(mysql_error()); break; case 2: echo "<br>insertDataT_Card =".$insertDataT_Card = "insert into t_card(name, cost, text, flavor_text, quantity)". " values('$cardName', '$cardCost', '$cardText','$flavorText', '$quantity')"; mysql_query($insertDataT_Card, $magicDB) or die(mysql_error()); break; case 3: echo "<br>insertDataT_Card =".$insertDataT_Card = "insert into t_card(name, cost, text, flavor_text, quantity, power, toughness)". " values('$cardName', '$cardCost', '$cardText','$flavorText', '$quantity', '$power', '$toughness')"; mysql_query($insertDataT_Card, $magicDB) or die(mysql_error()); break; }[/code][code]//Continue gathering data $queryRarityID = "select id from t_rarity where rarity='$rarity'"; echo "<br>queryRarityID= ".$queryRarityID."<br>"; $rarityRowID = mysql_query($queryRarityID, $magicDB); echo "rarityRowID= ".$rarityRowID."<br>"; while($rRow = mysql_fetch_assoc($rarityRowID)) { echo "rRow= ".rRow."<br>"; $rarityID = $rRow[id]; echo "rarityID= $rarityID[id] <br>"; } $querySetID = "select id from t_set where name='$set'"; $setID = mysql_query($queryRarityID, $magicDB); $queryTypeID = "select id from t_type where type='$type'"; $typeRowID = mysql_query($queryTypeID, $magicDB); while($tRow = mysql_fetch_assoc($typeRowID)) { $rarityID = $tRow[id]; } //insert the id's into the relational tables //TL_CARD_TYPE TABLE echo "<br>Value of Type is $type. TypeID is $typeID which is a ".gettype($typeid)." value.<br>"; $insertDataTL_Card_Type = "insert into tl_card_type(t_card_id, t_type_id) values('$cardID', '$typeID')"; mysql_query($insertDataTL_Card_Type, $magicDB) or die(mysql_error());[/code][!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]select id from t_card where name='Name' and cost='1' and text='Text' and flavor_text='Text' and power='0' and toughness='0'insertDataT_Card =insert into t_card(name, cost, text, flavor_text, quantity, power, toughness) values('Name', '1', 'Text','Text', '1', '0', '0')queryTypeID= select id from t_type where type='Artifact'typeRowID= Resource id #9tRow= tRowtypeID= 3Value of Type is Artifact. TypeID is 3 which is a NULL value.Cannot add or update a child row: a foreign key constraint fails (`test/tl_card_type`, CONSTRAINT `tl_card_type_ibfk_1` FOREIGN KEY (`t_card_id`) REFERENCES `t_card` (`id`))[/quote] Quote Link to comment Share on other sites More sharing options...
jtravis Posted March 12, 2006 Author Share Posted March 12, 2006 [!--quoteo(post=354282:date=Mar 12 2006, 03:20 PM:name=jtravis)--][div class=\'quotetop\']QUOTE(jtravis @ Mar 12 2006, 03:20 PM) [snapback]354282[/snapback][/div][div class=\'quotemain\'][!--quotec--]select id from t_card where name='Name' and cost='1' and text='Text' and flavor_text='Text' and power='0' and toughness='0'insertDataT_Card =insert into t_card(name, cost, text, flavor_text, quantity, power, toughness) values('Name', '1', 'Text','Text', '1', '0', '0')queryTypeID= select id from t_type where type='Artifact'typeRowID= Resource id #9tRow= tRowtypeID= 3Value of Type is Artifact. TypeID is 3 which is a NULL value.Cannot add or update a child row: a foreign key constraint fails (`test/tl_card_type`, CONSTRAINT `tl_card_type_ibfk_1` FOREIGN KEY (`t_card_id`) REFERENCES `t_card` (`id`))[/quote]Null value? WTF? why would a value of '3' be null with gettype()?This is the code that genrates that line.[code]echo "<br>Value of Type is $type. TypeID is $typeID which is a ".gettype($typeid)." value.<br>";[/code] Quote Link to comment Share on other sites More sharing options...
jtravis Posted March 12, 2006 Author Share Posted March 12, 2006 [!--quoteo(post=354289:date=Mar 12 2006, 03:33 PM:name=jtravis)--][div class=\'quotetop\']QUOTE(jtravis @ Mar 12 2006, 03:33 PM) [snapback]354289[/snapback][/div][div class=\'quotemain\'][!--quotec--]Null value? WTF? why would a value of '3' be null with gettype()?This is the code that genrates that line.[code]echo "<br>Value of Type is $type. TypeID is $typeID which is a ".gettype($typeid)." value.<br>";[/code][/quote]FYI. I found the problem there. Apparently, I got so wrapped up in everything else, I forgot to correct the problem with gathering the cardID. That part at least is working, and that was my biggest hurdle at the moment. Now on to other problems...:D Quote Link to comment Share on other sites More sharing options...
keeB Posted March 12, 2006 Share Posted March 12, 2006 I always echo out my queries.. well, actually, I always create a 'debug mode' for complex parts of my code to make sure the inner workings are all fine and dandy.. [=That way, whenever silly things like that go wrong, you just set a simple flag and everything is displayed nicely for you!Glad I could help somewhat, if you have any more questions don't hesitate to ask!! [= Quote Link to comment Share on other sites More sharing options...
jtravis Posted March 12, 2006 Author Share Posted March 12, 2006 [!--quoteo(post=354322:date=Mar 12 2006, 05:22 PM:name=keeB)--][div class=\'quotetop\']QUOTE(keeB @ Mar 12 2006, 05:22 PM) [snapback]354322[/snapback][/div][div class=\'quotemain\'][!--quotec--]I always create a 'debug mode' for complex parts of my code to make sure the inner workings are all fine and dandy.. [=That way, whenever silly things like that go wrong, you just set a simple flag and everything is displayed nicely for you![/quote]Debug mode? Hmm...Interesting Idea. Just curious... You would start you code assignin a bool or something and in the other pieces of code you have an 'if' that echos out the results? What's your method? lol Quote Link to comment Share on other sites More sharing options...
keeB Posted March 12, 2006 Share Posted March 12, 2006 yup.. looks something like..[code]if (debug) print whatever;[/code] 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.