Asked  8 Months ago    Answers:  5   Viewed   34 times

I have a table with 3 columns - id (pk), pageId (fk), name. I have a php script which dumps about 5000 records into the table, with about half being duplicates, with same pageId and name. Combination of pageId and name should be unique. What is the best way to prevent duplicates being saved to the table as I loop through the script in php?

 Answers

92

First step would be to set a unique key on the table:

ALTER TABLE thetable ADD UNIQUE INDEX(pageid, name);

Then you have to decide what you want to do when there's a duplicate. Should you:

  1. ignore it?

    INSERT IGNORE INTO thetable (pageid, name) VALUES (1, "foo"), (1, "foo");
    
  2. Overwrite the previously entered record?

    INSERT INTO thetable (pageid, name, somefield)
    VALUES (1, "foo", "first")
    ON DUPLICATE KEY UPDATE (somefield = 'first')
    
    INSERT INTO thetable (pageid, name, somefield)
    VALUES (1, "foo", "second")
    ON DUPLICATE KEY UPDATE (somefield = 'second')
    
  3. Update some counter?

    INSERT INTO thetable (pageid, name)
    VALUES (1, "foo"), (1, "foo")
    ON DUPLICATE KEY UPDATE (pagecount = pagecount + 1)
    
Wednesday, March 31, 2021
 
radmen
answered 8 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
44

You should be able to greatly increase the speed by putting your inserts inside a transaction. You can also move your prepare and bind statements outside of your loop.

$array = array("array", "with", "about", "2000", "values");
$query = "INSERT INTO table (link) VALUES (?)";
$stmt = $mysqli->prepare($query);
$stmt ->bind_param("s", $one);

$mysqli->query("START TRANSACTION");
foreach ($array as $one) {
    $stmt->execute();
}
$stmt->close();
$mysqli->query("COMMIT");

Edit:

I tested this code with 10,000 iterations on my web server.

Without transaction: 226 seconds. With transaction: 2 seconds. Or a two order of magnitude speed increase, at least for that test.

Tuesday, June 1, 2021
 
linjuming
answered 5 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 4 Months ago
52

Definitely not a text field, but a varchar -- perhaps. I wouldn't recommend parsing the results and storing them in individual columns unless you want to take advantage of that data in database sense -- statistics etc.

If you never see yourself asking "What is the average volume that users use?" then don't bother parsing it.

To figure out how to store this data ask yourself "How will i use it later?" If you will obtain the array and need to utilize it with PHP you can use serialize function. If you will use the values in JavaScript then JSON encoding will probably be best for you (plus many languages know how to decode it)

Good luck!

Wednesday, August 11, 2021
 
ANIS BOULILA
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share