Jump to content


Photo

Assigning a single value from MySQL to variable


  • Please log in to reply
9 replies to this topic

#1 jtravis

jtravis
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 12 March 2006 - 06:36 PM

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:

//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;
    }

The echos above are just for troubleshooting and will be removed once everything is working..

//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());

Everything seems to be working fine until here.

//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());

At this point, the script terminates with the following error:

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`))

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
echo "<br>Value of Type is $type. TypeID is $typeID which is a ".gettype($typeid)." value.<br>";//troublshooting..

Typically the results that are returned would go into an array, but how do you assign single row query results to a variable?

#2 keeB

keeB
  • Staff Alumni
  • Advanced Member
  • 1,078 posts
  • LocationCalifornia

Posted 12 March 2006 - 07:48 PM

[!--quoteo(post=354249:date=Mar 12 2006, 06:36 PM:name=jtravis)--][div class=\'quotetop\']QUOTE(jtravis @ Mar 12 2006, 06:36 PM) View Post[/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:

//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;
    }

The echos above are just for troubleshooting and will be removed once everything is working..

//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());

Everything seems to be working fine until here.

//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());

At this point, the script terminates with the following error:

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`))

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
echo "<br>Value of Type is $type. TypeID is $typeID which is a ".gettype($typeid)." value.<br>";//troublshooting..

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 break();

    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:

etc.. i'll check the rest in the meantime..

Come visit my site to see my latest projects
http://nick.stinemates.org/wordpress/


#3 jtravis

jtravis
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 12 March 2006 - 07:51 PM

Noted and added... Thanks!

#4 keeB

keeB
  • Staff Alumni
  • Advanced Member
  • 1,078 posts
  • LocationCalifornia

Posted 12 March 2006 - 08:01 PM

Can you also post some of the debugging information like.. print the query out?

That might make it a bit easier to troubleshoot.

Come visit my site to see my latest projects
http://nick.stinemates.org/wordpress/


#5 jtravis

jtravis
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 12 March 2006 - 08:20 PM

[!--quoteo(post=354274:date=Mar 12 2006, 03:01 PM:name=keeB)--][div class=\'quotetop\']QUOTE(keeB @ Mar 12 2006, 03:01 PM) View Post[/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
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;
    }
//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());


[!--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 #9
tRow= tRow
typeID= 3

Value 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]

#6 jtravis

jtravis
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 12 March 2006 - 08:33 PM

[!--quoteo(post=354282:date=Mar 12 2006, 03:20 PM:name=jtravis)--][div class=\'quotetop\']QUOTE(jtravis @ Mar 12 2006, 03:20 PM) View Post[/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 #9
tRow= tRow
typeID= 3

Value 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.
echo "<br>Value of Type is $type. TypeID is $typeID which is a ".gettype($typeid)." value.<br>";


#7 jtravis

jtravis
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 12 March 2006 - 09:28 PM

[!--quoteo(post=354289:date=Mar 12 2006, 03:33 PM:name=jtravis)--][div class=\'quotetop\']QUOTE(jtravis @ Mar 12 2006, 03:33 PM) View Post[/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.
echo "<br>Value of Type is $type. TypeID is $typeID which is a ".gettype($typeid)." value.<br>";
[/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

#8 keeB

keeB
  • Staff Alumni
  • Advanced Member
  • 1,078 posts
  • LocationCalifornia

Posted 12 March 2006 - 10:22 PM

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!! [=

Come visit my site to see my latest projects
http://nick.stinemates.org/wordpress/


#9 jtravis

jtravis
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 12 March 2006 - 10:30 PM

[!--quoteo(post=354322:date=Mar 12 2006, 05:22 PM:name=keeB)--][div class=\'quotetop\']QUOTE(keeB @ Mar 12 2006, 05:22 PM) View Post[/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

#10 keeB

keeB
  • Staff Alumni
  • Advanced Member
  • 1,078 posts
  • LocationCalifornia

Posted 12 March 2006 - 10:47 PM

yup.. looks something like..

if (debug) print whatever;

Come visit my site to see my latest projects
http://nick.stinemates.org/wordpress/





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users