CODEWITHSUNDEEP
mysqldatabasepdoleft-join
JasonDavis
JasonDavis

Reputation: 48933

MySQL JOIN makes Database column names collide

I am working on a project for a client, I need to migrate 2 Drupal and 1 WordPress installs into 1 single WordPress CMS. I have to maintain the exact same URLs and make sure the new site is pretty much the same as the current mess.

I have come up with this monster of an SQL query below to extract all the Drupal posts for insertion into WordPress.

My problem right now, is there is 2 MySQL Table Columns with the name title in the Query below....

SELECT n.nid, n.uid, FROM_UNIXTIME( n.created ) created, FROM_UNIXTIME( n.changed ) modified, n.TYPE, n.status, n.title, r.teaser, r.body, u.dst url, m.path, m.title, m.description, m.keywords
FROM drupal_node n
LEFT JOIN drupal_node_revisions r ON n.nid = r.nid
LEFT JOIN drupal_url_alias u ON CONCAT(  'node/', n.nid ) = u.src
LEFT JOIN drupal_simplemeta_data m ON CONCAT(  'node/', n.nid ) = m.path
WHERE n.TYPE
IN (
 'blog',  'story',  'page',  'forum',  'largeimage',  'error'
)

So when I am going over the result, this causes a problem as my title result is going to be just the first Column.

Is there a way to rename it for my result? Or some other trick?

A var_export() shows it just exposing 1 title

I am using PHP and something like this to return a PDO Object...

$result = $stmt->fetchAll(PDO::FETCH_OBJ);
foreach($result as $row) {

    echo $row->title;

}

Upvotes: 0

Views: 234

Answers (1)

Fabio
Fabio

Reputation: 23480

I think you can use AS to give variable a new name

SELECT n.nid, n.uid, FROM_UNIXTIME( n.created ) created, FROM_UNIXTIME( n.changed ) modified, n.TYPE, n.status, n.title AS mastertitle, r.teaser, r.body, u.dst url, m.path, m.title AS slavetitle, m.description, m.keywords

Now you can call them separately

echo $row->mastertitle;
echo $row->slavetitle;

Upvotes: 2

Related Questions