SQL code in variable within a variable

Question

I have been googling for hours and have yet to find an answer to the error I keep receiving. I have the following code:

$varFirstName = $_POST['FirstName'];
$varPast = $varFirstName.' Past';
$varPresent = $varFirstName.' Present';
$varPastandPresent = $varFirstName.' Past and Present';
$CharacterID = "SELECT CharacterID FROM CHARINFO WHERE FirstName='$varFirstName'";

$sql="INSERT INTO CHARACTER_RELATIONS (RelationshipWith, CharacterID)
VALUES('$varPast', '$CharacterID'),
('$varPresent', '$CharacterID'),
('$varPastandPresent', '$CharacterID')";

mysqli_query($con,$sql)
or  die('Error: ' . mysql_error());

And I keep getting "Error:" every time I run this. If I make $CharacterID=49; then it works just fine, so I feel like something is wrong with "SELECT CharacterID FROM CHARINFO WHERE FirstName='$varFirstName'" but I can't figure out what. Suggestions?

Suggestions? Such as parameter binding, or using a subquery, or a prior database query, and getting rid of mixed-cased variable names? — mario
– mario, Commented Dec 12, 2015 at 3:48
get the real error mysqli_error($con) that mysql_error() doesn't mix with mysqli_ — Funk Forty Niner
– Funk Forty Niner, Commented Dec 12, 2015 at 3:48
and your POST array(s) might be failing. check for errors on PHP. you're also not querying on the first query. — Funk Forty Niner
– Funk Forty Niner, Commented Dec 12, 2015 at 3:48

Vigneswaran S · Accepted Answer · 2015-12-12 04:21:36Z

2

$CharacterID= "SELECT CharacterID FROM CHARINFO WHERE firstName='$varFirstName'"; // this returns an array object. so you have to retrieve value of each row by using mysqli_fetch_array() as given below
//print_r($CharacterID);exit();// to check the array value and also you dint executed the $CharacterID. ie mysqli_query($con,$CharacterID);

$varFirstName = $_POST['FirstName'];
$varPast = $varFirstName.' Past';
$varPresent = $varFirstName.' Present';
$varPastandPresent = $varFirstName.' Past and Present';
$Character= "SELECT CharacterID FROM CHARINFO WHERE FirstName='$varFirstName'";// this returns an array object
       $CharacterID= mysqli_query($con,$Character);
        while($row=mysqli_fetch_array($CharacterID)){
        $id=$row['CharacterID '];
        $sql="INSERT INTO CHARACTER_RELATIONS (RelationshipWith, CharacterID)
        VALUES('$varPast', '$id')";      
        mysqli_query($con,$sql)
        or  die('Error: ' . mysqli_error($con));  
}

edited Dec 12, 2015 at 4:21

answered Dec 12, 2015 at 4:02

Vigneswaran S

2,0941 gold badge22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Funk Forty Niner Over a year ago

how can you run a query inside mysqli_fetch_array()? that would translate to while($row=mysqli_fetch_array(SELECT CharacterID FROM CHARINFO WHERE FirstName='John'))

Funk Forty Niner Over a year ago

if this is some SQL guru trick I don't know about, I'd sure like to know about it.

Vigneswaran S Over a year ago

sorry i forgot to add mysqli_query ()

Funk Forty Niner Over a year ago

you should edit your answer for the OP and for future visitors who may not know what to do here. edit: thanks for the edit ;-)

Vigneswaran S Over a year ago

@Fred-ii- second time you helped me to find the code error.thanks :).

Collectives™ on Stack Overflow

SQL code in variable within a variable

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related