How to insert the value of

Question

I just want to insert the value or the name of PROVINCE, CITY and BARANGAY to the database. I'm new in PHP i'm a little confused about this one.

PHP

    <?php
    session_start();
    $link=mysqli_connect("localhost","root","");
mysqli_select_db($link,"mknr2");


    if(isset($_POST['submit'])){
    $province = $_POST['province'];
    $city = $_POST['city'];
    $barangay = $_POST['barangay'];




    $sql = "INSERT INTO users(PROVINCE, CITY, BARANGAY) VALUES ('$province','$city','$barangay')";
    if($link->query($sql) === TRUE){
        echo "Sent!";
    }
    else{
        echo "Error:" . $sql . "<br>" . $link->error;
    }


}
?>

HTML

<h2 style="font-size:20px;font-weight:bolder;font-family:arial;margin-left:2%;">Address</h2>
<!--  PROVINCE -->
    <select id="provincedd" name="province" onchange="change_province()" class="province">
        <option>Select</option>
        <?php
            $res=mysqli_query($link,"select * from province");
                while($row=mysqli_fetch_array($res)) {
            ?>
            <option value="<?php echo $row["id"]; ?>"><?php echo $row["name"]; ?></option>
            <?php } ?>
    </select>   
<!--  PROVINCE -->



    <select id="city" name="city" class="city">
            <option>Select</option>     
    </select>

    <select id="barangay" name="barangay" class="barangay">
            <option>Select</option>
    </select>

AJAX.php

<?php
$link=mysqli_connect("localhost","root","");
mysqli_select_db($link,"mknr2");

if(isset($_GET["province"]))
{
$province=$_GET["province"];

        $res=mysqli_query($link,"select * from city where province_id=$province");
        echo "<select id='citydd' onchange='change_city()'>";
        echo "<option>"; echo "Select"; echo "</option>";
        while($row=mysqli_fetch_array($res))
        {
        echo "<option value='$row[id]' selected >"; echo $row["name"]; echo "</option>";
        }
        echo "</select>";
}


if(isset($_GET["city"]))
{
    $city=$_GET["city"];

        $res=mysqli_query($link,"select * from barangay where city_id=$city");
        echo "<select>";
        echo "<option>"; echo "Select"; echo "</option>";

        while($row=mysqli_fetch_array($res))
        {
        echo "<option value='$row[id]' selected>"; echo $row["name"]; echo "</option>";
        }
        echo "</select>";
}



?>

JAVASCRIPT

<script type="text/javascript">
function change_province()
{
var xmlhttp=new XMLHttpRequest();
xmlhttp.open("GET","ajax.php?province="+document.getElementById("provincedd").value,false);
xmlhttp.send(null);
city.style.display = "block";
document.getElementById("city").innerHTML=xmlhttp.responseText;

 if(document.getElementById("provincedd").value=="Select")
 {
 document.getElementById("barangay").innerHTML="<select><option>Select</option></select>";
 }


}

function change_city()
{
var xmlhttp=new XMLHttpRequest();
xmlhttp.open("GET","ajax.php?city="+document.getElementById("citydd").value,false);
xmlhttp.send(null);
document.getElementById("barangay").innerHTML=xmlhttp.responseText;

}
</script>

only the id in the database is inserting to my table.. i want to insert the name of the province, city and barangay itself. — Albert Cervantes, Jan 20 '20 at 07:03
I got your point, you want to insert id with name, for that you need to write a select query with where condition after receiving posted value. for example : $city_id = $_POST["city"]; $query = "SELECT * FROM cities WHERE id = $city_id"; $data = mysqli_query($link,$query); $city_name = $data->fetch_object()->city_name Now city name you have in variable so use that variable in insert query ..... like this you need to write — Manikanta, Jan 20 '20 at 07:06
That code is for callback 'option' in you form, you need just add new page to form and update/insert with a simple query.. — Simone Rossaini, Jan 20 '20 at 07:09
@Manikanta - i want to insert the name of the province, city and barangay. not the id because my ID column is incremented in the database. — Albert Cervantes, Jan 20 '20 at 07:14
@UmarAbdullah - Yes, but the only the id is posting in the database, not the name of the province itself. — Albert Cervantes, Jan 20 '20 at 07:15
check the code there is no value provided for city selectbox — Jatin Parmar, Jan 20 '20 at 08:25
@AlbertCervantes 1. you dont include db connection into codes. `$link=mysqli_connect("localhost","root","");` this is an empty connection see this for right db connection : https://tryphp.w3schools.com/showphpfile.php?filename=demo_db_select_proc I recommend you to spend some time on that side — , Jan 20 '20 at 08:41
Does this answer your question? [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) — Dharman, Jan 20 '20 at 09:11
@AlbertCervantes please check my answer,hope it will help you — Jatin Parmar, Jan 20 '20 at 12:33

score 0 · Answer 1 · answered Jan 20 '20 at 07:24

0

you have set Inside set value to option value and insert this option value in database .

<select name="id" required="" onchange="change_province()" class="province">
        <option value="">SELECT CITY</option>
        <?php
            $res=mysqli_query($link,"select * from province");
                while($row=mysqli_fetch_array($res)) {
            ?>
<option value="<?php echo $row["id"]; ?>"><?php echo $row["name"]; ?></option>

            <?php } ?>
    </select>

data insert

<?php
    session_start();
    $link=mysqli_connect("localhost","root","");
mysqli_select_db($link,"mknr2");


    if(isset($_POST['submit'])){
    $province = $_POST['province'];
    $city = $_POST['city'];
    $id= $_POST['id'];




    $sql = "INSERT INTO users(PROVINCE, CITY, BARANGAY) VALUES ('$province','$city','$id')";
    if($link->query($sql) === TRUE){
        echo "Sent!";
    }
    else{
        echo "Error:" . $sql . "<br>" . $link->error;
    }


}
?>

answered Jan 20 '20 at 07:24

Jamil Ahmed

284
3
17

ID should be auto increment. your answer wont work. – Jan 20 '20 at 07:34
yea its not working... @Dilek if i set it to Auto increment, did it work? – Albert Cervantes Jan 20 '20 at 07:36
@AlbertCervantes this is your answer https://stackoverflow.com/a/59782595/12232340 dont forget to upvote :) – Jan 20 '20 at 07:38
Thanks, but i dont even get it.. i want the actual answer for my question huhu. – Albert Cervantes Jan 20 '20 at 07:49
How can I do it in PHP? I mean what is the code or statement to use so that the php will insert the name of the province into the users table. – Albert Cervantes Jan 20 '20 at 08:44
i have try it my codeigniter eCommerce project it still working – Jamil Ahmed Jan 20 '20 at 10:39
yes, but i have a value id on my option, that's why its getting the id of the table... i can't change that option value because its function is to get the data inside the database thru ajax – Albert Cervantes Jan 21 '20 at 00:52

score 0 · Answer 2 · answered Jan 20 '20 at 12:32

Whats wrong with your code?

your select box will not have any option to select as i can see in code

(a)you have not assigned any values to it.

 <select id="city" name="city" class="city">
        <option>Select</option>     
 </select>

<select id="barangay" name="barangay" class="barangay">
        <option>Select</option>
</select>

(b)your javascript to call ajax.php which render select box with options will never executed as change_province,change_city functions will not be executed on any event in this case if your form will be submitted without selecting province,on server side your post array will be like following

["submit"=>"value of submit","province"=>"","city"=>"","barangay"=>""]

now if you take look on your code to insert value your query for inserting data will be like this

INSERT INTO users(PROVINCE, CITY, BARANGAY) VALUES '','','')

causing to insert new record with id(in case id is auto-increment and these values are nullable)

What should you do?

(a)instead of making ajax call to fill select box you should render them at once inside form view

formview.php

      <h2 style="font-size:20px;font-weight:bolder;font-family:arial;margin-left:2%;">Address</h2>
      <!--  PROVINCE -->
      <select id="provincedd" name="province" onchange="change_province()" class="province">
          <option>Select</option>
           <?php
              $res=mysqli_query($link,"select * from province");
              while($row=mysqli_fetch_array($res)) {
           ?>
            <option value="<?php echo $row["id"]; ?>"><?php echo $row["name"];?></option>
          <?php } ?>
       </select>   
      <!--  PROVINCE -->
      <?php
          $res=mysqli_query($link,"select * from city");
            echo "<select id='citydd' onchange='change_city()'>";
            echo "<option>"; echo "Select"; echo "</option>";
            while($row=mysqli_fetch_array($res))
              {
               echo "<option value='$row[id]' selected >"; echo $row["name"]; echo "</option>";
              }
              echo "</select>";

          $res=mysqli_query($link,"select * from barangay");
          echo "<select>";
          echo "<option>"; echo "Select"; echo "</option>";
          while($row=mysqli_fetch_array($res))
               {
                echo "<option value='$row[id]' selected>"; echo $row["name"]; echo "</option>";
               }
          echo "</select>";
     ?>

(b)inside your code to insert data ,you should put some server side(also front-end) validation before making insert to data table(you can search google for it),this will prevent any empty entry to be inserted. hope this will help you.

It worked! but all the data in city is displaying on dropdown... All I want is to display the city/cities depends on the province that you have clicked. — Albert Cervantes, Jan 21 '20 at 02:46
@AlbertCervantes in that case you should update city selectbox by calling change_city function on call change of province selectbox — Jatin Parmar, Jan 21 '20 at 04:52

How to insert the value of to the database

2 Answers2

How to insert the value of