By wildlettuce on

After designing the site for a small online library using the Biblio module, the owner of the site had the old site's custom database information dumped into the Drupal database. This effectively broke each of these nodes. The site manager has been adding new nodes correctly, but now would like me to delete all the broken nodes without deleting the new nodes.

I found the broken nodes through the "changed date" within node.

SELECT *
FROM `node`
WHERE `changed` =1220464532

If I delete these nodes in the database, will that remove them completely, or do I need to delete info in other tables too? If so which?

PS I know it is better to mass delete through Admin - Content, but I am not able to sort these by date and it is too time intensive to delete each entry one by one.

⋅ Categories: Drupal 5.x

Comments

matt2000’s picture

matt2000 commented

There's no telling where data related to the nodes might be stored, because different modules have different places.

To get everything you should use a PHP snippet with node_delete, e.g.,


$q = db_query("SELECT nid FROM node WHERE changed = 1220464532");

while ($nid = db_result($q)) {
  node_delete($nid);
}

Drop that in the add page form with the input format set to PHP, a click preview, and the job is done, clean & proper.

wildlettuce’s picture

wildlettuce commented

This seems to do a really good job of deleting the nodes one at a time. I have ran the script twice and each time the site will let me know several times that it has essentially deleted one entry.
eg:
Pressure and the Spirit of Play has been deleted.
Pressure and the Spirit of Play has been deleted.
Pressure and the Spirit of Play has been deleted.
Pressure and the Spirit of Play has been deleted.
Pressure and the Spirit of Play has been deleted.
Pressure and the Spirit of Play has been deleted.
Pressure and the Spirit of Play has been deleted.
Pressure and the Spirit of Play has been deleted.
Pressure and the Spirit of Play has been deleted.
Pressure and the Spirit of Play has been deleted.
Pressure and the Spirit of Play has been deleted.
Pressure and the Spirit of Play has been deleted.

I am new to php and SQL, so all help is really appreciated.

yelvington’s picture

yelvington commented

BACKUP first.

You need to identify all the items you want deleted. The example SQL that you posted looks for one specific timestamp.

Is there some other criteria you can use to identify the nodes?

TEST YOUR CODE FIRST using phpMyAdmin and "SELECT * from ... " to see what exactly shows up.

wildlettuce’s picture

wildlettuce commented

When I ran the query in my first post within phpmyadmin it displayed all of the nodes I would like to delete (around 750 rows.)

For some reason this php delete node script only deletes one node at a time.

I backed up the database before starting on this experiment.

Basically I would like to delete all the nodes that were last changed 1220464532 - this was the moment when the old data was dumped into the new website. I really hope there is an easy way to do this.

Red Paul’s picture

Red Paul commented

Hi Matt, I have a similar problem; I somehow seem to have an orphaned node with no Node id number or title:

<div id="node-" class="node node-unpublished">
  <h2><a href="/node/" title=""></a></h2>

Can I use your php snippet (below) to delete this odd Node? It's not visible via Admin Content Management.
If so, could you let me know the exact query to select this one node? (sorry, I've been using Drupal for a while but am a numptie when it comes to the back-end stuff).

Or is there an easier way of getting rid of this? It seems to be just a single instance - not sure where it came from, but I've been experimenting with Taxonomies, Translations and FAQ - Ask (the experts)

Many thanks,

Paul

---------------
To get everything you should use a PHP snippet with node_delete, e.g.,

$q = db_query("SELECT nid FROM node WHERE changed = 1220464532");

while ($nid = db_result($q)) {
  node_delete($nid);
}

Drop that in the add page form with the input format set to PHP, a click preview, and the job is done, clean & proper.
---------------------

nevets’s picture

nevets commented

No, that will not completely delete the nodes, there are other tables involved (tables depend on features used for content type).

wwwoliondorcom’s picture

wwwoliondorcom commented

Hi,

Can you tell me how to delete all nodes that are older than 1 year directly in my database ?

I need to delete all nodes which type is "news" an that are older than 1 year.

Thanks a lot.