Services

  • Drupal CMS Developer
  • Website speed optimisation
  • Drupal 6 to Drupal 8 migrations

Welcome

Welcome, I am a Web Developer based in Madrid, Spain originally from the UK. I studied Computer Science & eBusiness at Loughborough University. I specialise in Content Management System websites

MYSQL

Mysql delete duplicate rows

Posted Sat, 06/06/2009 - 21:56 by admin

The script removes only one duplicate entry at a time, you have to run it several times to remove all duplications (I advise running it in a php script which then counts duplicates left afterwards) or just run it recursively whilst testing for duplicates. As ever, remember to backup your database first!!!

DELETE tablename
FROM tablename,
(SELECT MAX(uid) AS dupid,COUNT(uid) AS dupcnt
FROM tablename
GROUP BY id,url HAVING dupcnt>1)
AS dups
WHERE tablename.uid=dups.dupid;

Replace string in a table

Posted Sat, 06/06/2009 - 21:55 by admin

If you want to change certain rows in a database where a query applies … for example an “if” query finding all entries which contain “samplesearchtext”… then run the following script either via mysql/phpmyadmin/php

<?php
mysql_query("update mytable set myfield =replace (myfield, 'samplesearchtext', 'samplereplacetext')");
?>

Quick and simple Mysql connect

Posted Sat, 06/06/2009 - 21:47 by admin

This is the simplest and neatest way to connect to a MySQL database using php

$dbh=mysql_connect ("localhost", "username", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("database_name");

Syndicate content