By Dublin Drupaller on

Hey..

Need help tweaking a bit of code in a module I'm playing with.

What I need to do is

a) check to see if a field exists in a table

b) if one exists...update it with a new setting

c) if the field doesn't exist, insert a new one.

am googled to death searching through help sites for php & mysql and thought I would post up here in case someone would spot it quickly.

the table striucture is as follows and is designed to store a link as the "home page" link of the site...i.e. the next time they log in they are redirected to that page.

table name =  frontlink
column 1 = uid  
column 2= front_url

So the table stores a "front url" for each user and is keyed by the user ID.

below is the function to write the information to the database and table. I'm a novice at this so I pasted an existing "save" function from another module and edited it. (apologies in advance)

function quicklinks_front_save($edit) {
  global $user;

  $edit['old_front_url'] = urldecode($edit['old_front_url']);
  if ($edit['old_front_url'] && db_result(db_query("SELECT COUNT(uid) FROM {frontlink} WHERE uid = %d AND front_url = '%s'", $user->uid, $edit['old_front_url']))) {
    db_query("UPDATE {frontlink} SET front_url = '%s' WHERE uid = $user->uid AND front_url = '%s'");
    drupal_set_message(t('Your front page has been updated.'));
  }
  else {
    db_query("INSERT INTO {frontlink} (uid, front_url) VALUES (%d, '%s')", $user->uid, $edit['front_url']);
    drupal_set_message(t('Your front page has now been set.'));
  }
}

Any keen eyed PHP/MYSQL drupallers out there able to help?

Or should I give up and invest in that hot dog stand?

Thanks in advance for any guidance...

Dub

Comments

Dru’s picture

Dru commented

I had that problem a few times and the easiest solution is usually just to delete the old record and insert a new one. If you need to keep old info just make sure you already have it in the $node object (doesn't seem to be needed in your case).
This way you can make sure the data is there and get no SQL error messages.
I have used that method in a few nodeapi changes I made in a company I worked before and it worked perfectly.
Cheers,
Dru - now in south america having fun - http://www.lokihostel.com

Dublin Drupaller’s picture

Dublin Drupaller commented

Hi Dru...

thanks a million for your tips...worked perfectly.

for anyone else out there writing modules that needs to do something similar..please note the code that does the following:

(note: this snippet is from a bespoke module that allows users to set their own front page and quicklinks/bookmarks on a drupal site. the database table is called frontlink..and the front page link is called front_url)

(a) When a USER sets a "new front page" link, check to see if they already have one stored in the database.

(b) if they do already have one. Delete it and insert a new one.

(c) if they don't already have one. Insert a new one.

function quicklinks_front_save($edit) {
 global $user;
 $edit['old_front_url'] = urldecode($edit['old_front_url']);
 @db_query("DELETE FROM {frontlink} where uid=%d", $user->uid);
 db_query("INSERT INTO {frontlink} (uid, front_url) VALUES (%d, '%s')",
 $user->uid, $edit['front_url']);
  drupal_set_message(t('Your front page has now been set.'));
 }

I'm not going to unleash the quicklinks.module as a release as it is is basically a modification of the BOOKMARKS module and duplicates 99% of the functionality of that..but if anyone is interested, email me using the contact form and I'll send you a copy.

Thanks again Dru and I hope the code above is useful for others as a reference.

Dub

DUBLIN DRUPALLER
___________________________________________________
A drupal user by chance and a dubliner by sheer luck.
Using Drupal to help build Artist & Band web communities.

Currently in Switzerland working as an Application Developer with UBS Investment Bank...using Drupal 7 and lots of swiss chocolate