How to insert data into database, tables with foreign keys via php

Question

I have 5 tables in database.

technologia:
    - id_typ_technologie
    - id_typ_energie
    - nazov_technologie

typ_energie:
    - id_typ_energie
    - popis_energie

dodavatel
    - id_dodavatel
    - nazov
    - email

obec:
    - id_obec
    - nazov_obec

zariadenie
    - id_zariadenia
    - nazov_zariadenia
    - id_typ_energie(FK)
    - id_technologie(FK)
    - vykon_zariadenia
    - jednotka
    - id_dodavatel(FK)
    - id_obec(FK)
    - geom
    - rok_instalacie
    - popis

I want to insert data to table zariadenia but i don´t know. I do a dropdown option from db for foreign keys but I don´t know how i insert id from selected option. For example in table typ_energie are values: 1 slnecna 2 veterna 3 vodna if someone select veterna i want to insert only id this option.I do something but i am new in php. geom is the geographic for insert i use a sql insert -('SRID=4326;POINT(17.072834 48.149280)') Can anyone help me with this insert via php ?

            $con=pg_connect("host= localhost port=5432 dbname=cvicna user=postgres password=admin");
            if (!$con)

                {

                    die('Could not connect: ' . pg_last_error()());

                }
                ?>


        <form action="dropdown.php" method="post">
                <p>
                    <label for="nazov">nazov:</label>
                    <input type="text" name="nazov" id="nazov">
                </p>
                <p>
                    <label for="typenergie">typ energie:</label>
                <select>
                <?php

                $qry=pg_query("select * from typ_energie ");
                while ($row=pg_fetch_array($qry)) 
                {
                ?>
                <option><?php echo $row["popis_energie"]; ?></option>
                }
                <?php
                }
                ?>
                </select>
                </p>

                <p>
                    <label for="technologia">technologia:</label>
                <select>
                <?php

                $qry=pg_query("select * from technologia ");
                while ($row=pg_fetch_array($qry)) 
                {
                ?>
                <option><?php echo $row["nazov_technologie"]; ?></option>
                }
                <?php
                }
                ?>
                </select>
                </p>
                <p>
                    <label for="vykon">vykon:</label>
                    <input type="text" name="vykon" id="vykon">
                </p>
                <p>
                    <label for="jednotka">jednotka:</label>
                    <input type="text" name="jednotka" id="jednotka">
                </p>
                <p>
                    <label for="dodavatel">dodavatel:</label>
                    <select>
                        <?php

                    $qry=pg_query("select * from dodavatel order by id_dodavatel ASC ");
                    while ($row=pg_fetch_array($qry)) 
                    {
                    ?>
                    <option><?php echo $row["nazov"]; ?></option>
                    }
                    <?php
                    }
                    ?>
                    </select>
                </p>


                <p>
                    <label for="obec">obec:</label>
                    <select>

                <?php

                $qry=pg_query("select * from obec ");
                while ($row=pg_fetch_array($qry)) 
                {
                    ?>
                <option><?php echo $row["nazov_obec"]; ?></option>
                }
            <?php
            }
            ?>

            </select>
                </p>

                <p>

                    <label for="x">x:</label>
                    <input type="text" name="x" id="x">

                </p>
                <p>

                    <label for="y">y:</label>
                    <input type="text" name="y" id="y">

                </p>

                <p>
                    <label for="rok">rok instalacie:</label>
                    <input type="text" name="rok" id="rok">
                </p>
                <p>
                    <label for="popis">popis</label>
                    <input type="text" name="popis" id="popis">
                </p>

                <input type="submit" value="Submit">
                </form>```

Chris Medina · Accepted Answer · 2020-05-18T02:55:56.127

My friends, please keep the value $1, $2, $3, $4, $5, $6, $7, $8, $9 inside of the $insert_prepare variable. It needs to be those arguments.

Full fix of your solution below:

if($con) {
    //$con = pg_connect( "host= localhost port=5432 dbname=cvicna user=postgres password=admin" );
    ?>


    <form action="dropdown.php" method="post">
        <p>
            <label for="nazov_zariadenia">nazov:</label>
            <input type="text" name="nazov_zariadenia" id="nazov_zariadenia">
        </p>

        <p>
            <label for="typ_energie">typ energie:</label>
            <select name="typenergie">
                <?php

                $qry = pg_query( "select * from typ_energie " );
                while ($row = pg_fetch_array( $qry )) {
                    ?>
                    <option value="<?php $row['id_typ_energie']; ?>"> <?php echo $row["popis_energie"]; ?></option>
                    }
                <?php
                }
                ?>
            </select>
        </p>

        <p>
            <label for="technologia">technologia:</label>
            <select name="id_technologie">
                <?php

                $qry = pg_query( "select * from technologia " );
                while ($row = pg_fetch_array( $qry )) {
                    ?>
                    <option value="<?php $row['id_technologie']; ?>"><?php echo $row["nazov_technologie"]; ?></option>
                    }
                <?php
                }
                ?>
            </select>
        </p>
        <p>
            <label for="vykon">vykon:</label>
            <input type="text" name="vykon" id="vykon">
        </p>

        <p>
            <label for="jednotka">jednotka:</label>
            <input type="text" name="jednotka" id="jednotka">
        </p>

        <p>
            <label for="dodavatel">dodavatel:</label>
            <select name="id_dodavatel">
                <?php
                $qry = pg_query( "select * from dodavatel " );
                while ($row = pg_fetch_array( $qry )) {
                    ?>
                    <option value="<?php $row['id_dodavatel']; ?>"><?php echo $row["nazov"]; ?></option>
                    }


                <?php
                }
                ?>
            </select>
        </p>

        <p>
            <label for="obec">obec:</label>
            <select name="id_obec">
                <?php

                $qry = pg_query( "select * from obec " );
                while ($row = pg_fetch_array( $qry )) {
                    ?>
                    <option value="<?php $row['id_obec']; ?>"><?php echo $row["nazov_obec"]; ?></option>
                    }
                <?php
                }
                ?>
            </select>
        </p>

        <p>
            <label for="rok">rok instalacie:</label>
            <input type="text" name="rok" id="rok">
        </p>

        <p>
            <label for="popis">popis</label>
            <input type="text" name="popis" id="popis">
        </p>

        <input type="submit" value="Submit">
    </form>

    <?php

    if ( ! empty( $_POST )) {

        $nazov_zariadenia = $_POST['nazov_zariadenia'];

        $id_typ_energie   = $_POST['typenergie'];
        $id_technologie   = $_POST['id_technologie'];
        $vykon_zariadenia = $_POST['vykon'];
        $jednotka         = $_POST['jednotka'];
        $id_dodavatel     = $_POST['id_dodavatel'];
        $id_obec          = $_POST['id_obec'];
        $rok_instalacie   = $_POST['rok'];
        $popis            = $_POST['popis'];

        $form_array = [
            $nazov_zariadenia,
            $id_typ_energie,
            $id_technologie,
            $vykon_zariadenia,
            $jednotka,
            $id_dodavatel,
            $id_obec,
            $rok_instalacie,
            $popis
        ];

        $insert_prepare = 'INSERT INTO zariadenie(nazov_zariadenia,id_typ_energie,id_technologie,vykon_zariadenia,jednotka,id_dodavatel,id_obec,rok_instalacie, popis ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)';

pg_prepare( $con, 'loopStmt', $insert_prepare);

        }
}
?>

Thank you, but geom is geometry data type Can you tell me how I insert x, y coordinates to database ? In SQL I use Insert into zariadenie(geom) Values('SRID=4326;POINT(17.106938 48.145232)'); — Eddie12, May 15 '20 at 21:27
It looks like you can use GEOMETRY type. Example: CREATE TABLE zariadenie(code int(5),descrip varchar(50), xy GEOMETRY); ALTER TABLE geotest ADD pt_loca POINT; See: https://www.w3resource.com/mysql/mysql-spatial-data-types.php — Chris Medina, May 15 '20 at 21:38
Yes i create geom GEOMETRY, but it is working in qgis map . Do i use alter tablr geotest ADD pt_loca POINT ? Or what i will do that it will working . And how i insert data via php to this geometry data type . Please help me . — Eddie12, May 15 '20 at 22:19
The closest thing I could find to this answer is at: https://stackoverflow.com/questions/51374304/how-to-insert-spatial-data-in-mysql-with-php . $sql = "INSERT INTO sp_house (geom, d_nop) VALUES (ST_GeomFromText(POINT(?)), ?)"; I will look into a pg_execute solution — Chris Medina, May 15 '20 at 22:24
I try to delete geom from table and I need insert others values from table zariadenie but I am retarded and I dont know write the correct code. Could you please correct my code so that the insertion works based on the selection from the given options? — Eddie12, May 16 '20 at 22:46

score 0 · Answer 2 · answered May 16 '20 at 22:52

I try to delete geom from table and I need insert others values from table zariadenie but I am retarded and I dont know write the correct code. Could you please correct my code so that the insertion works based on the selection from the given options?

            $con=pg_connect("host= localhost port=5432 dbname=cvicna user=postgres password=admin");
            if (!$con)

                {

                    die('Could not connect: ' . pg_last_error()());

                }
                ?>


        <form action="dropdown.php" method="post">
                <p>
                    <label for="nazov_zariadenia">nazov:</label>
                    <input type="text" name="nazov_zariadenia" id="nazov_zariadenia">
                </p>
                <p>
                    <label for="typ_energie">typ energie:</label>
                <select name="typ_energie">
                <?php

                $qry=pg_query("select * from typ_energie ");
                while ($row=pg_fetch_array($qry)) 
                {
                ?>
                <option value="<?php $row['id_typ_energie'];?>"> <?php echo $row["popis_energie"]; ?></option>
                }
                <?php
                }
                ?>
                </select>

                </p>

                <p>
                    <label for="technologia">technologia:</label>
                <select name="technologia">
                <?php

                $qry=pg_query("select * from technologia ");
                while ($row=pg_fetch_array($qry)) 
                {
                ?>
                <option value="<?php $row['id_technologie'];?>"><?php echo $row["nazov_technologie"]; ?></option>
                }
                <?php
                }
                ?>
                </select>
                </p>
                <p>
                    <label for="vykon">vykon:</label>
                    <input type="text" name="vykon" id="vykon">
                </p>
                <p>
                    <label for="jednotka">jednotka:</label>
                    <input type="text" name="jednotka" id="jednotka">
                </p>
                <p>
                    <label for="dodavatel">dodavatel:</label>
                    <select name="dodavatel">
                        <?php
                        $qry=pg_query("select * from dodavatel ");
                while ($row=pg_fetch_array($qry)) 
                {
                ?>
                <option value="<?php $row['id_dodavatel'];?>"><?php echo $row["nazov"]; ?></option>
                }


                    <?php
                    }
                    ?>
                    </select>
                </p>


                <p>
                    <label for="obec">obec:</label>
                    <select name="obec">

                <?php

                $qry=pg_query("select * from obec ");
                while ($row=pg_fetch_array($qry)) 
                {
                    ?>
                <option value="<?php $row['id_obec'];?>"<?php echo $row["nazov_obec"]; ?></option>
                }
            <?php
            }
            ?>

            </select>
                </p>



                <p>
                    <label for="rok">rok instalacie:</label>
                    <input type="text" name="rok" id="rok">
                </p>
                <p>
                    <label for="popis">popis</label>
                    <input type="text" name="popis" id="popis">
                </p>

                <input type="submit" value="Submit">
                </form>
                <?php


                $nazov_zariadenia = $_POST['nazov_zariadenia'];

                $id_typ_energie = $_POST['typenergie'];
                $id_technologie = $_POST['id_technologie'];
                $vykon_zariadenia = $_POST['vykon_zariadenia'];
                $jednotka   = $_POST['jednotka'];
                $id_dodavatel   = $_POST['id_dodavatel'];
                $id_obec    = $_POST['id_obec'];
                $rok_instalacie = $_POST['rok_instalacie'];
                $popis     = $_POST['popis'];

                $form_array = [ $nazov_zariadenia , $id_typ_energie , $id_technologie, $vykon_zariadenia, $jednotka, $id_dodavatel, $id_obec,  $rok_instalacie, $popis ];

                if( pg_prepare($con, 'loopStmt', 'INSERT INTO zariadenie(nazov_zariadenia,id_typ_energie,id_technologie,vykon_zariadenia,jednotka,id_dodavatel,id_obec,rok_instalacie ) VALUES ($nazov_zariadenia, $id_typ_energie, $id_technologie, $vykon_zariadenia, $jednotka, $id_dodavatel, $id_obec,$rok_instalacie, $popis )')) {
                if( pg_execute($con, 'loopStmt', $form_array  )) {
                    echo "Executed successfully";

}
?>

see my above solution if you haven't already fixed this. Without GEMETRY(you said you deleted it) the above code I posted works fine — Chris Medina, May 19 '20 at 20:20

How to insert data into database, tables with foreign keys via php

2 Answers2