check mysql table data befor insert into

Question

I need to know if I can query and check database table data before the php scrip will INSERT INTO that table ?

I have made an html with 2 fields and submit button(POST), and I also have no trouble with adding data to database table and getting its result, but I have duplicates and I don't want to have it ... I want script to check my data , the entering data and if no duplicate then add it.

Please assist.

Thank you

Code

<?php


$con=mysqli_connect("localhost","root","Opera1","railway");


if(mysqli_connect_errno())
{
echo "Failed to connect to MySQL: ", mysqli_connect_error;
}

$data1 = $_POST['data1'];
$data2 = $_POST['data2'];



$sql="replace into pipe (data1, data2) 
values ('$data1','$data2')";



if(!mysqli_query($con,$sql))
{
die('Error : ' . mysqli_error($con));
}


echo "Record to Registraton added";

$result = mysqli_query($con,"SELECT * FROM pipe");

echo "<table border='1'>
<tr>
<th>Data1</th>
<th>Data2</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
 echo "<tr>";
  echo "<td>" . $row['data1'] . "</td>";
  echo "<td>" . $row['data2'] . "</td>";
  echo "</tr>";
  }
echo "</table><br><br>";


mysqli_close($con);


?>

@kevinm: Triggers are probably overkill here. Probably he can just do as simple select, and if it returns something skip the insert. — mnagel, Jul 04 '13 at 10:47
@Narius... do u want to check for duplicate values in a particular column or all the columns? — kevinm, Jul 04 '13 at 10:49

kevinm · Accepted Answer · 2013-07-04T12:27:54.647

1

       <?php


    $con=mysqli_connect("localhost","root","Opera1","railway");


    if(mysqli_connect_errno())
    {
    echo "Failed to connect to MySQL: ", mysqli_connect_error;
    }

    $data1 = $_POST['data1'];
    $data2 = $_POST['data2'];

    $sql1="select * from pipe";
    $q1=mysql_query($con, $sql1);
    $t=0;
    while($row1=mysql_fetch_array($q1))
    {

    if($row1['data1']== '$data1' || $row1['data2']=='$data2')
    {
    $t=1;
    break;
    }
    }
    if($t==1)
     { 
       echo "Duplicate Entry!";
     }
    else
    {

    $sql="replace into pipe (data1, data2) 
    values ('$data1','$data2')";



    if(!mysqli_query($con,$sql))
    {
    die('Error : ' . mysqli_error($con));
    }


    echo "Record to Registraton added";

    $result = mysqli_query($con,"SELECT * FROM pipe");

    echo "<table border='1'>
    <tr>
    <th>Data1</th>
    <th>Data2</th>
    </tr>";

    while($row = mysqli_fetch_array($result))
      {
     echo "<tr>";
      echo "<td>" . $row['data1'] . "</td>";
      echo "<td>" . $row['data2'] . "</td>";
      echo "</tr>";
      }
    echo "</table><br><br>";

    }

    mysqli_close($con);


    ?>

edited Jul 04 '13 at 12:27

answered Jul 04 '13 at 10:52

kevinm

475
2
7

I really need to know every step of this script to understand. I just don't want to copy. WHERE NOT EXIST (SELECT 1 [what is this ?] FROM T1 AS T2 [and this ?] If you please give me some link that will describe this statement. – Narius Jul 04 '13 at 11:02
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /usr/local/www/apache22/data/pipe/pipe.php on line 18 --- $row1=mysql_fetch_array($q1); --- And also I am having a lot of troubles trying examples with fetch and getting same error. – Narius Jul 04 '13 at 11:50
@Narius-Use this `$q1=mysql_query($con, $sql1);` I think its because the query failed. Check your queries, whenever you face this situation. – kevinm Jul 04 '13 at 12:02
maybe and maybe not, but instead of making better it goes worst. Now it gives not only on fetch but also on the line you gave. it is --- $q1=mysql_query($con, $sql1); --- AND --- $row1=mysql_fetch_array($q1); – Narius Jul 04 '13 at 12:06
okay, check if there are any values in the pipe table and if so, how many records. If there is more than one, then, you would have to use a while loop to loop through each record. – kevinm Jul 04 '13 at 12:11
there are some records, and it is not empty. How should I do that ? – Narius Jul 04 '13 at 12:13
but dont you just want to replace a single record? else on what basis are you replacing your data into the table. There's no `WHERE` condition in your `REPLACE` query. – kevinm Jul 04 '13 at 12:15
Dear Kevin, please make any changes you find correct so it will drop any time it will find duplicate in the columns. Thank you – Narius Jul 04 '13 at 12:17
Not working. Exactly the same picture with on same query and fetch. I am getting crazy from morning. – Narius Jul 04 '13 at 12:35

score 0 · Answer 2 · answered Jul 04 '13 at 10:51

0

If you do not want to handle teh case when there is duplicate just use INSERT IGNORE INTO instead of INSERT INTO. It would work exactly as INSERT but would not fail if there are duplicates.

answered Jul 04 '13 at 10:51

Nedret Recep

726
5
8

would not fail ? I need it to be dropped when it finds duplicate – Narius Jul 04 '13 at 10:53

score 0 · Answer 3 · edited May 23 '17 at 10:25

0

If you want to eliminate the duplicate records in 1 stroke, you can use REPLACE command available in mysql ( ref: http://dev.mysql.com/doc/refman/5.0/en/replace.html ). If a row with the same value already exists then it will delete the existing row and add new else it will insert the new record.

You can even look for other options at Insert into a MySQL table or update if exists

edited May 23 '17 at 10:25

Community

1
1

answered Jul 04 '13 at 11:00

Sandeep Sukhija

1,156
16
30

This is interesting , let me try. – Narius Jul 04 '13 at 11:03
do you have a primary key or unique index in your table. The replace feature works on the primary key as listed in document. Also you can try insert on duplicate ( http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html ) – Sandeep Sukhija Jul 04 '13 at 11:40
how to add primary key to existing row ? – Narius Jul 04 '13 at 12:10
you can add primary key by reading mysql documentation for ALTER TABLE. Well this question is not part of context for asked question. – Sandeep Sukhija Jul 04 '13 at 12:44

check mysql table data befor insert into

3 Answers3