[SOLVED] Getting dupe record value from database

PHP
1

Hello
I have a script that checks mysql for dupe values. This is working ok. However because it is a multiple I need to know which record is already in the db. I have a feeling that I need to do a while loop but need help to do so.

What I am trying to do is find which item is the dupe and echo it out.

The variables $outString. = “$box”. " "; and $boxitem = $box; are used further on to insert if there are no dupes. I shall change the else statement.

I would be grateful if someone could help with this. many thanks

$boxitems = mysqli_real_escape_string($conn, $_POST['box']);
$box = implode(",", $boxitems);
$array = array();
$array = $boxitems;
foreach($array as $boxes) {
  $outString. = "$box". "  ";
  $boxitem = $box;
  $sql = "SELECT * FROM act where item = '".$boxes."'";
  $result = mysqli_query($conn, $sql) or die('Error selecting item: '.mysqli_error());
  if (mysqli_num_rows($result) > 0) {
    echo "record already exists";
    exit;
  } else {
    echo 'no dupes found';
    exit;
  }
}
2

Let SQL do the heavy lifting:

SELECT item FROM act GROUP BY item HAVING COUNT(*) > 1

Gives you all duplicate items.

3

what?!

4

Hi Dormillich
Thanks for reply. However, that dosen’t tell me what the dupe item is. Thanks

5

Hi chorn
Sorry? don’t understand your comment.

6

You’re escaping a variable that is expected to be an array or imploding a variable that is expected to be a string. Both those things contradict one another. If you expect a value to be an array than you need to escape each value in the array separately, rebuilding the array. I’m surprised your code gets past that first line unless the value isn’t an array.

7

Hi ZooKeeper
I am quite new to php and scripting. Could you do example for me please as I do not fully understand your comment. Thanks

8

I think this needs to be the first line.

$boxitems = array_map(function($value) use ($conn) { return mysqli_real_escape_string($conn, $value); }, $_POST['box']);

This isn’t needed.

$box = implode(",", $boxitems);
$array = array();
$array = $boxitems;

Just change:

foreach($boxitems as $boxes) {

9

ZooKeeper
Can we take that out the equation for the moment and help me to display which is the dupe item. Do i have to do a while loop to cycle through the $result? Not sure. Thanks

10

This is my best guess of what you are actually looking for.

$boxitems = array_map(function($value) use (&$conn) { return mysqli_real_escape_string($conn ,$value); }, ['one','two', 'three']);
foreach($boxitems as $boxes) {
  $outString. = "$box". "  ";
  $boxitem = $box;
  $sql = "SELECT * FROM act where item = '".$boxes."'";
  $result = mysqli_query($conn, $sql) or die('Error selecting item: '.mysqli_error());
  if (mysqli_num_rows($result) > 0) {
    echo "record already exists";
    exit;
  } else {
    echo 'no dupes found';
    exit;
  }
}
11

I really don’t understand this code. What is [‘one’,‘two’, ‘three’]? This is what is showing in inspector when i post to the php script: [“aaa”,“ggg”] and it is these values I need to check for dupe. That value is obviously dynamic. Sorry to be a pain. Thanks

12

Try this

$boxitems = array_map(function($value) use (&$conn) { return mysqli_real_escape_string($conn ,$value); },  $_POST['box'] );
foreach($boxitems as $boxes) {
  $outString. = "$box". "  ";
  $boxitem = $box;
  $sql = "SELECT * FROM act where item = '".$boxes."'";
  $result = mysqli_query($conn, $sql) or die('Error selecting item: '.mysqli_error());
  if (mysqli_num_rows($result) > 0) {
    echo "record already exists";
    exit;
  } else {
    echo 'no dupes found';
    exit;
  }
}
13

ZooKeeper
That is showing the same result as my code. How does that help me display the dupe items to user. Thanks

14

My bad.

Without being able to recreate the problem and my smart debugger I’m just throwing darts blind folded.

15

I have tried while loop but it is only showing 1 result and there should be 2. based on my original code, how would you code for displaying dupe values to user. Thanks

16

just make a quick example of the database you have and the results you want

<?php

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->query('create table act(item text)');
$pdo->query('insert into act(item) values(1)');
$pdo->query('insert into act(item) values(2)');
$pdo->query('insert into act(item) values(3)');
$qry = $pdo->query('select * from act');
print_r($qry->fetchAll());
17

I have modified code to use while loop but it is only displaying 1 item instead of 3. Where have I gone wrong. Many thanks

foreach ($array as $boxes) {

    $sql = "SELECT item FROM act WHERE item = '".$boxes."' GROUP BY item HAVING COUNT(*) > 1";
    $result = mysqli_query($conn, $sql) or die('Error selecting item: ' . mysqli_error());
    $num_rows = mysqli_num_rows($result);
      if($num_rows) {
        while ($row = mysqli_fetch_array($result)) {
              $data[] =  $row['item'];
          }
          echo '<div style="width: 50%; margin-bottom: 20px; border-radius: 5px; border: 1px solid black; background: red; font-size: 16px; color: white; height: 50px; padding: 15px; line-height: 1.3;">';
          echo json_encode($data)  . ' already exists. Please enter a unique box reference.';
          echo '</div>';
          exit;
        } else {
          echo '<div style="width: 50%; margin-bottom: 20px; border-radius: 5px; border: 1px solid black; background: #63c84c; font-size: 16px; color: white; height: 50px; padding: 15px; line-height: 1.3;">';
          echo 'No dupes found in database.';
          echo '</div>';
          exit;
        }
    }
18

Perhaps if you show examples of the table duplicated rows it would be helpful?

19

Think I have sorted it thanks John. There were 2 grey areas. 1 was the exit; and the other was I changed >1 to >0 in the select query and it is now working. Many thanks for reply. I shall update title to solved.

1 Like
20

I cannot edit original post. Is there an option to mark as solved. Thanks

1 Like