How can I use INSERT with full variables

[ccoronado]

Hi

I would appreciate it if someone can guide me with the following problem, I need to do a INSERT into a MySQL database with variables, here is my code example:

 

$table = $dta.$year;

$insert = "ano, id, idas".$valor2.", total";
$value = $year.", ".$id.", ".$idas.$tit2.", ".$monto3;
$sql = "INSERT INTO `$table` (`$insert`) VALUES (`$value`)";

 

I know it is not correct as it does not insert; please help...

 

Thank You...
 

[Jacques1]

Can you read?

 

 

This is NOT a help forum! Do not post topics asking for help that are not related to the website.

 

That message is red, blinking text. What more do you need?

 

Moved.

[mac_gyver]

and here's everything that isn't correct about what you are doing -

 

1) you should use a prepared query, with a place-holder for each value in the sql query statement, and supply the values when you execute the query.

 

2) if you were putting data values directly into the sql query statement, back-ticks ` are not used at all. you would use single-quotes around each value that is a string (for a prepared query, single-quotes are not put around the place-holders.)

 

3) back-ticks ` are used around individual identifiers - database, table, or column names and should only be used when necessary, when the identifier is or could be a reserved keyword.

 

4) idas".$valor2." - this indicates you have a series of named columns and are treating the database like it is a spreadsheet and are inserting multiple data items into each row. this is a bad design that takes more code to manage the data. you should instead insert each data item as its own row in the database table.

 

5) the id column, since you are inserting a value for it, is most likely a foreign key from another table. you should name it relevant to that table and reserve the use of the name id for a column relevant to the current table.

 

6) $dta.$year - this indicates you will/do have a table for each year. this is again a bad design that makes it harder to manage data and to query for common data between the tables. you should instead have one table that holds all the same meaning data. the year column in that single table would distinguish the data for each year within that table.

 

7) you should only use variables for things that are variable. all the static/fixed information for an sql query should be written directly in the sql query statement.

 

if you do have a needed for variables for something, you can put them directly inside a double-quoted string. there's no need for all the concatenation dots and extra quotes.

 

implementing all these points will result in an sql query statement that looks like this -  

$sql = "INSERT INTO `$dta`.a_single_relevantly_named_table (ano, a_relevantly_named_id_column, a_single_relevantly_named_data_column, total) VALUES (?,?,?,?)";

[ccoronado]

[[ merged from a second thread OP created --requinix ]]

 

Hi

 

I would apreciate it immensly if somebody can help me with the following problem. I need to create an insert using only variables. To explain what I am trying to do, I need to turn this:

$sql = "INSERT INTO table (column1, column2) VALUES (value1, value2)";

into something like this:

$table = table;
$column = $column1.", ".$column2;
$value = $value1.", ".$value2;
$sql = "INSERT INTO `$table` (`$column`) VALUES (`$value`)";

The reason for doing so is that it is impossible to know the number of actual columns to be inserted as the table itself is created dynamically.

I will add my code at the end so it is understanble.

 

I am really stuck and need help on this one. I do apologise for my structure or errors as I am not a programmer.

Any solution or a different approach is welcome.

 

Thanks for the help..

 <?php
 
# Set Variables and connections
 
            set_include_path('include/');
            include 'db_connect.php';
            $year = date("Y");
            $year = $year + 1;
            $nombre_u = $_SESSION['nombre_u'];
            $apellidop_u = $_SESSION['apellidop_u'];
            $depa = $_SESSION['depa2'];
            $departamento2 = $_SESSION['depa'];
            $_SESSION['departamento2'] = $departamento2;
 
            #$gest = $_SESSION['gest'];
            $progr = $_SESSION['progr'];
 
 
# Variable related to the category selected
 
$gestion = 3;
 
# Create table using variables to define number of columns <---
 
        $data = "SELECT * FROM progdesc where gestion = '$gestion'" or die("¡Error No existe ese departamento!"); 
        $result = mysql_query($data) or die("¡Error No existe ese dep!");
 
        while(($row = mysql_fetch_array($result)) !== FALSE)
            {
            $columna=$row['pn'];
            $valor= $valor." pt".$columna." VARCHAR(50),";
            $valor2= $valor2.", pt".$columna;
            $gestion = '3';
            $table = "sumag".$gestion.$year;
             }
 
        $valor= "ano VARCHAR(4), id VARCHAR(4), idas VARCHAR(13), ".$valor." "."total VARCHAR(10)";
 
        mysql_query("CREATE TABLE `".$table."`(".$valor.")") or die(mysql_error());
 
    #End of table creation --->
 
# INSERT DATA INTO NEWLY CREATED TABLE
 
# Step 1 - Loop through the diferent fixed rows
 
    $data1 = "SELECT * FROM asigna" or die("¡Error No existe ese departamento!"); 
    $result1 = mysql_query($data1) or die("¡Error1!");
 
    while(($row = mysql_fetch_array($result1)) !== FALSE)
            {
                $id=$row['id'];
                $idas=$row['idas'];
                $sid=$row['sid'];
                $tit2 = NULL;
                $monto3 = 0;
 
# Step 2 - Select all the records that are related to the category selected
 
                $data2 = "SELECT * FROM progdesc WHERE gestion = '$gestion' and ano = '$year' " or die("¡Error No existe ese departamento!"); 
                $result2 = mysql_query($data2) or die("¡Error2!");
 
                while(($row2 = mysql_fetch_array($result2)) !== FALSE)
                        {
                            $dep=$row2['departamento'];
                            $titulo=$row2['titulo'];
                            $depf = $dep."final";
                            $pn = $row2['pn'];
                            $sidb = $row2['sid'];
 
 # Step 3 - Get all the records related to the programs related to the category and thier values.
 
                            $data3 = "SELECT * FROM $depf WHERE ano = '$year' and idas = '$idas' and gestion = '$gestion'and programa = '$pn'  " or die("¡Error No existe ese departamento!"); 
                            $result3 = mysql_query($data3) or die("¡Error3!");
 
                            while(($row3 = mysql_fetch_array($result3)) !== FALSE)
                                    {
                                        $monto=$row3['monto'];
 
                                        if (!($idas2 == $idas and $monto == 0)){
                                            $tit2 =  $tit2.", ".$monto;
 
                                        }elseif ($monto == 0){
                                            $tit2 =  $tit2.", 0";
 
                                        }else{
                                            $cero = 0;
                                            $pn2 = 0;
 
                                        if (!($monto == 0)){
 
                                            for ($i = 0; $i < $pn2; $i++) {
                                                $cero = $cero.", 0";
                                                }
 
                                            $tit2 =  $tit2.", ".$cero.", ".$monto;
 
                                        }
                                       }
 
                                    $idas2 = $idas;
                                    $ln = $ln + 1;
                                    $monto2 = $monto;
 # Sum the values to get a total                                   
                                    $monto3 = $monto3 + $monto;
                        }
                }
 
 # Insert the values into the table using the information provided as a sting of values           
 
            $insert = "ano, id, idas".$valor2.", total";
            $value = $year.", ".$id.", ".$idas.$tit2.", ".$monto3;
            $sql = "INSERT INTO `$table` (`$insert`) VALUES (`$value`)";
 
            mysql_query($sql);
       } 
 
?>

Edited March 28, 2017 by requinix

[ginerjm]

According to the Manual (which comes in handy!) in the first user-contributed note under PDO's prepare statement (which is where you should be) it begins by saying:

 

"To those wondering why adding quotes to around a placeholder is wrong, and why you can't use placeholders for table or column names:"....

 

There is more, so it would behoove you to read it.

 

http://php.net/manual/en/pdo.prepare.php

 

 

So ends your elaborate plan to make a be-all, end-all query.

Edited March 28, 2017 by ginerjm

[ccoronado]

Thanks for the tip, but how could I apply PDO to my code, I mean how can I prepare the variables... What I am after is the posibility to echo the statement and execute it or something similar.

Edited March 28, 2017 by ccoronado

[ginerjm]

"echo the statement"?

"execute it"?

 

All I was posting was that your approach to use substitute parms for the column and table names was not possible. Step back and re-think what you are trying to do here.

 

As for PDO - it is a very good interface to your MySQL database (and others). MySqlI has one too and it too is pretty easy to use, but I and many others here seem to prefer the former. Again - a little reading is knowledge. The manual lists all the available functions and gives examples of how to use them as well.