Using CSV Files to Update Database Tables with phpMyAdmin

vim2-html

For many reasons either when moving between different cms systems perhaps or for more general reasons it is useful to import data with csv files.  Often this is because it is easier to manipulate the data in a spreadsheet and then create mass updates.

phpMyAdmin doesn’t allow you to update data from a CSV file directly.  You can import the data into an empty table, but you cannot update existing records.  To get around this, you can follow these steps:

  1. Export the data that you need from the current table – in part so you can see how it is structured.
  2. Then edit this file in a spreadsheet program.  After you changes are finished then export the file as csv.  In this example we are updating references to page urls in a table “pages” and our data file has simply id and a url instead of all the data.
  3. In phpmyadmin create a new temporary table
    CREATE pages_url_update LIKE pages (which would create a replica -- or just create the columns you need -- in this example an id and a url)
  4. Open that table in phpMyAdmin and then choose “import”
    — Select the csv to upload
    — And choose CSV Using LOAD DATA under Format
  5. Then select the true data table and click on the sql button
  6. Then do
    UPDATE pages
    INNER JOIN pages_url_update on pages_url_update.id = pages.id
    SET pages.url = pages_url_update.url
  7. Then drop the temporary table – DROP pages_url_update

Customizing Drupal User Profiles

vim-html-code

By default, the drupal content management system offers fairly plain looking user profiles. For people using drupal in a social networking setting and/or in a content publishing situation with numerous authors that you would like to highlight, most people will want to improve upon the standard user profile layout.

While there are a number of user profile related modules, this article will cover some php coded methods that will help to accomplish some of the more common uses.

The first step in customizing the user profile is to create a file called “user_profile.tpl.php” into your themes directory. Having this file will override drupal’s default layout. Within that file you can use a variety of php, sql, and html code to achieve what you are looking for. Once you get all of the data you want in the profile you can then customize it with CSS – adding rounded boxes around certain content, etc.

The example that I will be describing today is from an acupuncture information site that I created. An example of a detailed user profile is here.

First, I’ll list the code in general and then below I’ll break it apart and discuss certain sections in detail.

This site has different member roles, some are highlighted and certain functions are added to their profile based on what role they are in. For general users the profile is not particularly different from the standard drupal layout (with the addition of recent forum postings/comments). For other member roles, however, sections are added to their profile that don’t exist for other users.
<div class=”userlist”>

<?php

/* *** */

/* If the user has a picture picture, print it */

/* *** */

if($user->picture): ?>

<img src=”/<?php print $user->picture ?>” alt=”personal picture” align=”right” class=”pictureframe”>

<?php endif; ?>

<?php

/* *** */

/* If premium student members, say so */

/* *** */

if (in_array(‘student member’, $user->roles)) {

print ‘<div class=”practitionerbold”><p>I am a premium <a href=”/student_members/become_a_student_member”>Student

Member</a>.</p><div class=”item-list”><ul><li><strong>Read</strong> <a

href=”/student_members/eastern_medicine_contributions”>Student Member</a> Contributions</li><li><a

href=”/user/$userid/contact”>Contact Me</a></li></ul></div></div>’;

} ?>

<?php

/* *** */

/* If premium practitioner member, create a link to their individual details page */

/* This is a cck node that users can create in certain roles to describe more details about themselves and their work */

/* They are only allowed to create one */

/* *** */

$nlimit = 1;

$userid=$user->uid;

$result1 = pager_query(db_rewrite_sql(“SELECT n.nid, n.created FROM {node} n WHERE n.status = 1 AND n.uid = $userid AND

n.type = ‘content_about_my_practice’ ORDER BY n.created DESC”), variable_get(‘default_nodes_main’, $nlimit));

while ($node = db_fetch_object($result1))

{$setpracpage .= node_view(node_load(array(‘nid’ => $node->nid)), 1);}

if (in_array(‘practitioner member’, $user->roles)):

print ‘<div class=”practitionerbold”><p>I am <a href=”/directory/get_listed”>listed</a> in the <a

href=”/directory”>Practitioner Directory</a><br></p><div class=”item-list”><ul>’;

if (isset($setpracpage)): ?>

/* *** */

/* Below are just some individual user accounts that we would like to highlight by adding fields to the profile */

/* *** */

<?php if ($userid == “2”) {

print ‘<li>I Am <strong>On Staff</strong> At The Yin Yang House <a href=”/clinic

/chattanooga_acupuncture_and_wellness_center”>Acupuncture &amp; Wellness Center<

/a> In <a href=”/clinic/yinyanghouse_directions_contacts”>Chattanooga, TN</a>.</

li>’;

} ?>

<?php if ($userid == “840”) {

print ‘<li>I Am A Massage Therapist <strong>On Staff</strong> At The Yin Yang Ho

use <a href=”/clinic/chattanooga_acupuncture_and_wellness_center”>Acupuncture &a

mp; Wellness Center</a> In <a href=”/clinic/yinyanghouse_directions_contacts”>Ch

attanooga, TN</a>.</li>’;

} ?>

<?php if ($userid == “841”) {

print ‘<li>I Am A Massage Therapist <strong>On Staff</strong> At The Yin Yang Ho

use <a href=”/clinic/chattanooga_acupuncture_and_wellness_center”>Acupuncture &a

mp; Wellness Center</a> In <a href=”/clinic/yinyanghouse_directions_contacts”>Chattanooga, TN</a>.</li>’;

} ?>

/* *** */

/* Add the individuals detail page if it exists */

/* *** */

<?php

print ‘<li>Read <strong>Details About</strong>&nbsp;’;

$node_type = “content_about_my_practice”;

$list_no =1;

$sql = “SELECT node.title, node.type, node.nid FROM node WHERE node.type = ‘$node_type’ AND node.uid = ‘$userid’ LIMIT

$list_no”;

$result2 = db_query($sql);

while ($anode = db_fetch_object($result2)) {

$output2 .= l(“My Practice”, “node/$anode->nid”);

}

print $output2;

?></li><?php endif; ?>

/* *** */

/* Add Contact Link */

/* *** */

<li><a href=”/user/<?php print $userid; ?>/contact”><strong>Contact Me</strong><

/a></li><?php if($user->profile_dirlisting): ?>

<li><strong>Return</strong> to the <?php print $user->profile_dirlisting ?> Dire

ctory</li><?php endif; ?></ul></div></div>

/* *** */

/* Customized News and Event Postings (a member role limited content type) */

/* Show 5 most recent for this user */

/* *** */

<br>

<div class=”clinicbold”>

<?php

$uid = arg(1);

$nlimit = 5;

$result = db_query(“SELECT n.created, n.title, n.nid, n.changed

FROM node n

WHERE n.uid = $uid

AND n.status = 1

AND n.type = ‘practitioner_member_news’

ORDER BY n.changed

DESC LIMIT $nlimit”);

$output4 .= “<div class=”item-list”><ul>n”;

$output4 .= “<li><strong>My Recent News and Event Postings:</strong></li>n”;

$output4 .= node_title_list($result);

$output4 .= “</ul></div>”;

print $output4;

?>

/* *** */

/* Customized Article Postings (a member role limited content type) */

/* Show 5 most recent for this user */

/* *** */

<?php

$uid = arg(1);

$nlimit = 5;

$result = db_query(“SELECT n.created, n.title, n.nid, n.changed

FROM node n

WHERE n.uid = $uid

AND n.status = 1

AND n.type = ‘practitionerarticle’

ORDER BY n.changed

DESC LIMIT $nlimit”);

$output5 .= “<div class=”item-list”><ul>n”;

$output5 .= “<li><strong>My Recent Article Submissions:</strong></li>n”;

$output5 .= node_title_list($result);

$output5 .= “</ul></div></div>”;

print $output5;

?>

<?php endif; ?>

/* *** */

/* Below is the standard membership information that will show up on every users account */

/* This will read in some profile fields that are available at signup and show their most */

/* recent forum postings and comments */

/* *** */

<br>

<div class=”clinicbold”>

<div class=”item-list”>

<ul>

<li><strong>Yin Yang House Membership Information:</strong><hr></li>

<li><strong>I Am:  </strong><?php print check_plain($user->profile_userlevel) ?><br><br></li>

<li><strong>From:  </strong><?php print check_plain($user->profile_usercountry) ?><br><br></li>

<li><strong>My Interests:  </strong><?php print check_plain($user->profile_userinterests) ?><br><br></li>

<li><strong>YYH Member for:  </strong><?php print (format_interval(time() – $user->created));?><br></li>

</ul></div>

<br><hr>

/* *** */

/* Most Recent Forum Postings and Site-Wide Comments */

/* *** */

<?php

$uid = arg(1);

$nlimit = 5;

$result = db_query(“SELECT n.created, n.title, n.nid, n.changed

FROM node n

WHERE n.uid = $uid

AND n.status = 1

AND n.type = ‘forum’

ORDER BY n.changed

DESC LIMIT $nlimit”);

$output3 .= “<div class=”item-list”><ul>n”;

$output3 .= “<li><strong>My Recent Comments and Forum Postings:</strong></li>n”;

$output3 .= node_title_list($result);

print $output3;

/* *** */

/* Most Recent User Comments that Have been Approved and are Published */

/* *** */

$output = “”; $nlimit = 5;

$userid=$user->uid;

/* *** */

/* ATTENTION: status=0 – approved, status=1 in queue. */

/* *** */

$query= “SELECT c.cid, c.nid, c.name, c.subject FROM {comments} c WHERE c.uid = %d AND c.status = 0

ORDER BY c.timestamp DESC”;

$result = db_query_range($query,$userid,0,$nlimit);

$no_comments = mysql_affected_rows ();

if ( $no_comments > 0 ) {

while ($obj = db_fetch_object($result)) {

$link = url(“node/$obj->nid”);

$link = $link.”#comment-“.$obj->cid;

$output6 .= “<li><a href=”$link”>$obj->subject</a>”;

}

}

$output6 .= “</ul></div></div>”;

print $output6;

?>

</div>