CODEWITHSUNDEEP
phpmysql
Jesper Olsen
Jesper Olsen

Reputation: 21

Insert into with variables php mysql

Im having some trouble getting my SQL query to 'insert into' my database, is it allowed to use variables as table name, field name, and values?

Here my code:

$nameOfDBFromA = "vagtplanA" . $_GET["from"];

$flytnedToQ1 = $con->prepare("SELECT * FROM $nameOfDBToA WHERE ansatId='$_GET[ansatId]' ORDER BY id DESC");
$flytnedToQ1->execute();
$flytnedTo1 = $flytnedToQ1->fetch();

$nameOfFieldToA1 = "a" . $_GET["to"] . "1";
$nameOfFieldToA2 = "a" . $_GET["to"] . "2";
$nameOfFieldToA3 = "a" . $_GET["to"] . "3";
$nameOfFieldToA4 = "a" . $_GET["to"] . "4";
$nameOfFieldToA5 = "a" . $_GET["to"] . "5";
$nameOfFieldToA6 = "a" . $_GET["to"] . "6";
$nameOfFieldToA7 = "a" . $_GET["to"] . "7";

$redigeringsTidspunkt = date("j M Y");

$flytnedTA = $con->prepare(
         "INSERT INTO $nameOfDBFromA 
                (ansatId, edit, $nameOfFieldToA1, $nameOfFieldToA2, 
                 $nameOfFieldToA3, $nameOfFieldToA4, $nameOfFieldToA5, 
                 $nameOfFieldToA6, $nameOfFieldToA7) 
          VALUES($_GET[ansatId], $redigeringsTidspunkt, 
                 $flytnedTo1[$nameOfFieldToA1], $flytnedTo1[$nameOfFieldToA2], 
                 $flytnedTo1[$nameOfFieldToA3], $flytnedTo1[$nameOfFieldToA4], 
                 $flytnedTo1[$nameOfFieldToA5], $flytnedTo1[$nameOfFieldToA6], 
                 $flytnedTo1[$nameOfFieldToA7]) ") 
          or die(mysql_error());
$flytnedTA->execute();

SOLVED! I just put my arrays into it own variable

$intoVarToA1 = $flytnedTo1[$nameOfFieldToA1];
$intoVarToA2 = $flytnedTo1[$nameOfFieldToA2];
$intoVarToA3 = $flytnedTo1[$nameOfFieldToA3];
$intoVarToA4 = $flytnedTo1[$nameOfFieldToA4];
$intoVarToA5 = $flytnedTo1[$nameOfFieldToA5];
$intoVarToA6 = $flytnedTo1[$nameOfFieldToA6];
$intoVarToA7 = $flytnedTo1[$nameOfFieldToA7];

Upvotes: 0

Views: 210

Answers (4)

printfmyname
printfmyname

Reputation: 971

One of your mistakes is when you want to access a value in an array inside of a string, you can't do:

"$flytnedTo1[$nameOfFieldToA1]"

You have to do it like this:

"{$flytnedTo1[$nameOfFieldToA1]}" // use curly brackets

Upvotes: 0

Barmar
Barmar

Reputation: 780714

You shouldn't substitute variables into the query, you should use bind_param() to provide parameter values for the prepared query.

$flytnedTA = $con->prepare(
         "INSERT INTO $nameOfDBFromA 
                (ansatId, edit, $nameOfFieldToA1, $nameOfFieldToA2, 
                 $nameOfFieldToA3, $nameOfFieldToA4, $nameOfFieldToA5, 
                 $nameOfFieldToA6, $nameOfFieldToA7) 
          VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?) ") 
          or die(mysqli_error($con));
$flytnedTA->bind_param("sssssssss", $_GET[ansatId], $redigeringsTidspunkt, 
                 $flytnedTo1[$nameOfFieldToA1], $flytnedTo1[$nameOfFieldToA2], 
                 $flytnedTo1[$nameOfFieldToA3], $flytnedTo1[$nameOfFieldToA4], 
                 $flytnedTo1[$nameOfFieldToA5], $flytnedTo1[$nameOfFieldToA6], 
                 $flytnedTo1[$nameOfFieldToA7]);
$flytnedTA->execute();

You also need to call mysqli_error($con), not mysql_error().

Upvotes: 1

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

Is not a good practice put _GET or _POST variables directly on query, use mysqli_real_escape_string to clear the value in variable.

The array values are not parsed directly in strings, you must enclose the expression in {}:

For this: " $flytnedTo1[$nameOfFieldToA3] " replace with: "'{$flytnedTo1[$nameOfFieldToA3]}'" , the result value also need to enclosed by '' singlequoes for sql string value.

$flytnedTA = $con->prepare("INSERT INTO $nameOfDBFromA (ansatId, edit, $nameOfFieldToA1, $nameOfFieldToA2, $nameOfFieldToA3,
    $nameOfFieldToA4, $nameOfFieldToA5, $nameOfFieldToA6, $nameOfFieldToA7) 
    VALUES({$_GET['ansatId']}, '$redigeringsTidspunkt', '{$flytnedTo1[$nameOfFieldToA1]}', '{$flytnedTo1[$nameOfFieldToA2]}', 
    '{$flytnedTo1[$nameOfFieldToA3]}', '{$flytnedTo1[$nameOfFieldToA4]}', '{$flytnedTo1[$nameOfFieldToA5]}', 
    '{$flytnedTo1[$nameOfFieldToA6]}', '{$flytnedTo1[$nameOfFieldToA7]}') ") or die(mysql_error());

Upvotes: 0

Shaig Khaligli
Shaig Khaligli

Reputation: 5485

If you have variables like that, you can insert data into db like below in php.

 $first_name = mysqli_real_escape_string($link, $_POST['firstname']);
    $last_name = mysqli_real_escape_string($link, $_POST['lastname']);
    $email_address = mysqli_real_escape_string($link, $_POST['email']);
    $sql = "INSERT INTO persons (first_name, last_name, email_address) VALUES    ('$first_name', '$last_name', '$email_address')";

Upvotes: 0

Related Questions