Compare Multiple coloumns in MySql

Question

I need to compare 2 different columns in a table: tbl_Try (country, name). if there are 2 coloumns with the same value do nothing. If there is no the same values of these 2 columns the same insert.

this is the connection to the database:

      <?php
        require_once("menu.php");
        require_once("function.php");

    ?>

Here is the main code (read the comments to try to understand)

            <?php
             $conn = ConnectToSql();

             $query= "Select *  FROM tbl_countries";
             $result = mysqli_query($conn, $query)
             or die("Error in query: ". mysqli_error($conn));

             $choose = '';

            while ($row = mysqli_fetch_assoc($result))
            {

                $choose .= '<option value = "'.$row['name'].'">'.$row['name'].'</option>';
            }

            ?>
        <div class="form-group">
          <label class="control-label col-sm-2" for="Country">Choose a country:</label>
          <div class="col-sm-5">
              <select class="form-control" name ="reg_country" >
                  <option></option>
                  <?php echo $choose;?></select>
          </div>
            </div>
            <br>
            <div class="form-group">
          <div class="col-sm-offset-4 col-sm-10">
            <button type="submit" name="submit" class="btn btn-default">Submit</button>
          </div>
        </div>

            <?php

             if(isset($_POST['submit']))
             {

                $country = $_POST['reg_country'];

                $_SESSION['country'] = $country;




                 $query2 = "SELECT name,id FROM tbl_flowertypes ";
                 $result2 = mysqli_query($conn, $query2) or die("Error in query: ". mysqli_error($conn));
                 $result2_rows = mysqli_num_rows($result2);


              //this code is not working. Need to count the 2 columns (name AND country) 
              // if in tbl_try there is already country:italy name:12 red roses. DO NOTHING if there is no data the same
              // insert data
                 $query3 = "SELECT count(*) FROM tbl_try WHERE name='$_SESSION[flower_type_name]' AND country ='$_SESSION[country]' ";


                 $result3 = mysqli_query($conn, $query3) or die("Error in query: ". mysqli_error($conn));
                 $result3_rows = mysqli_num_rows($result3);




               // loop counting how many record are in table flowertype and loop to insert data in tbl_try (this code is working fine)     
            for($i = 1; $i <= $result2_rows;$i++)
                {



                    while($row = mysqli_fetch_assoc($result2))
                        {

                            $_SESSION['flower_type_name'] = $row['name'];
                        // this is the insertion of the data
                            $inserting = "INSERT INTO tbl_try(name,country) VALUES ('$row[name]','$_SESSION[country]')";
                            $result3 = mysqli_query($conn, $inserting) or die("Error in query: ". mysqli_error($conn));

                        }



                }
             }

the result need to be:

the database need to do in insertion once . ONLY IF THERE IS NO THE SAME COLUMNS IN THE TABLE. IF THERE IS THE SAME DATA JUST DO NOTHING.

I need to complete this code by showing a table with some records but i know how to do that.

Any Suggestions?

Why don't you just add an unique index on these two columns? — Paul, Jul 28 '16 at 08:41

score 1 · Accepted Answer · answered Jul 28 '16 at 08:45

1

The best way is to add unique definition for this two columns to DB like this:

ALTER TABLE tbl_Try
ADD UNIQUE (name, country)

Then you can check the result code from DB and on the basis of result code you now, if the row was stored to DB or not due to case, that the same name and country is already in the DB.

answered Jul 28 '16 at 08:45

von Oak

813
5
14

thank you very much . can you tell me exactly what to do because i am still a student and im trying to get experience , . I think the problem is that if the user add another name he will input all records again ? – Sigmond Gatt Jul 28 '16 at 09:42
According to snippet I wrote higher, you need add it to *.sql script and run on MySQL or use e.g. [phpMyAdmin](https://www.phpmyadmin.net/) and define this two columns as unique in there. Second question: It is not necessary for user to input all records again. It depends, how you create user form and pre-fill the form data on the basis of previous data from the user. – von Oak Jul 28 '16 at 10:09
i am using phpmyadmin . so i need to go to phpymyadmin , go sql and write alter tbl.... ?? and then what i need to do .. sorry that i am wasting your time but i am eager to learn :/ and if i dont ask questions i dont learn – Sigmond Gatt Jul 28 '16 at 12:12
Simalar question: [How do I specify a composite unique constraint in MySQL?](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql). In phpMyAdmin you can go to sql and write this unique definition via alter table or you can setup these unique keys in table definition. Try, research and learn. You can find many online courses on the Internet about PHP, MySQL and so on. – von Oak Jul 28 '16 at 12:51

Compare Multiple coloumns in MySql

1 Answers1