Rutger_ADD Rutger_ADD - 4 months ago 15
MySQL Question

Unknown column 'd.user_id' in 'field list'

I have a procedure which will create views based on available data in a Wordpress & Buddypress database. This runs just fine on my local Mamp server but gives me an error whenever I call the procedure on my hosted server. Even when I copy the entire DB from my local server to the online server, it stil returns the error. I'm no mysql hero so I'm going off the deep end here.. Why won't this run?


The following query has failed: "CALL
stp_FormQueryBuilder
();
MySQL said: #1054 - Unknown column 'd.user_id' in 'field list'


BEGIN


DECLARE sqlText text;

DROP VIEW IF EXISTS vw_FormDataRows;

CREATE VIEW vw_FormDataRows AS
SELECT d.user_id, f.name,
CASE WHEN left(f.name, 4) = 'FOTO' && LEFT(d.value, 9) = '/profiles' THEN CONCAT("/wp-content/uploads",d.value)
ELSE d.value END AS 'value'


FROM wp_bp_xprofile_fields AS F
LEFT JOIN wp_bp_xprofile_data AS D ON F.id = D.field_id
WHERE
parent_id = 0
AND group_id = 1
AND d.user_id IN(SELECT U.user_id FROM wp_usermeta AS U WHERE U.meta_key = 'wp_user_level' AND U.meta_value = '0')

ORDER BY d.user_id, f.id;

DROP TEMPORARY TABLE IF EXISTS temp1;

CREATE TEMPORARY TABLE temp1 (SELECT b.user_id, b.name, b.value
FROM (SELECT d.user_id, f.name, d.value
FROM wp_bp_xprofile_fields AS F
LEFT JOIN wp_bp_xprofile_data AS D ON F.id = D.field_id
WHERE
parent_id = 0
AND group_id = 1
AND d.user_id IN(SELECT U.user_id FROM wp_usermeta AS U WHERE U.meta_key = 'wp_user_level' AND U.meta_value = '0')
ORDER BY d.user_id, f.id ) AS B);


SET @sql = NULL;

SET group_concat_max_len = 4096;

SELECT GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE WHEN name = ', name,
' THEN value END) AS `', replace(name, ' ','_'), '`'))
INTO sqlText
FROM temp1;


DROP VIEW IF EXISTS vw_FormDataHard;

SET @sql = CONCAT('CREATE VIEW vw_FormDataHard AS SELECT user_id, ', sqlText, '
FROM vw_FormDataRows
GROUP BY user_id');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

DROP TEMPORARY TABLE IF EXISTS temp1;

END

Answer

You never defined the alias d just D and these are different, because aliases are case sensitive.