perl sql concat error

[heals1ic]

I am trying to execute a mysql query-

$sql = "SELECT S.supplier, S.sup_img, C.prodid, D.product, D.catid, C.minprice
	FROM  suppliers S 
	INNER JOIN (SELECT B.supplierid, A.prodid, A.minprice 
	FROM sup_prices B 
	INNER JOIN (SELECT X.prodid, MIN(X.price_each) minprice
	FROM sup_prices X
	INNER JOIN (SELECT PP.prodid, PP.product
	FROM products PP
	INNER JOIN prefs PR 
	ON PP.catid = PR.catid
	AND PP.prodid = PR.pref
	WHERE userid = $urow->{userid}) Y 
	ON X.prodid = Y.prodid
	GROUP BY X.prodid) A 
	ON B.prodid = A.prodid 
	AND B.price_each = A.minprice) C 
	ON S.supplierid = C.supplierid 
	INNER JOIN products D 
	ON C.prodid = D.prodid 
	ORDER BY D.catid";
				
$result = $dbh->prepare($sql)
$result->execute();

I keep getting a concatination error-
"Use of uninitialized value in concatenation (.) or string"
Is there a problem with this sql for DBI?

 

[TonyGroves]

Has $urow->{userid} been initialised?

 

[mbrooks]

$result = $dbh->prepare(qq{
	SELECT S.supplier, S.sup_img, C.prodid, D.product, D.catid, C.minprice
	FROM suppliers S
	INNER JOIN (SELECT B.supplierid, A.prodid, A.minprice
	FROM sup_prices B
	INNER JOIN (SELECT X.prodid, MIN(X.price_each) minprice
	FROM sup_prices X
	INNER JOIN (SELECT PP.prodid, PP.product
	FROM products PP
	INNER JOIN prefs PR
	ON PP.catid = PR.catid
	AND PP.prodid = PR.pref
	WHERE userid = $urow->{userid}) Y
	ON X.prodid = Y.prodid
	GROUP BY X.prodid) A
	ON B.prodid = A.prodid
	AND B.price_each = A.minprice) C
	ON S.supplierid = C.supplierid
	INNER JOIN products D
	ON C.prodid = D.prodid
	ORDER BY D.catid";
});
$result->execute();

M. Brooks

http://mbrooks.info

 

[mbrooks]

Actually this is the correct one. Sorry about that.

$result = $dbh->prepare(qq{
    SELECT S.supplier, S.sup_img, C.prodid, D.product, D.catid, C.minprice
    FROM suppliers S
    INNER JOIN (SELECT B.supplierid, A.prodid, A.minprice
    FROM sup_prices B
    INNER JOIN (SELECT X.prodid, MIN(X.price_each) minprice
    FROM sup_prices X
    INNER JOIN (SELECT PP.prodid, PP.product
    FROM products PP
    INNER JOIN prefs PR
    ON PP.catid = PR.catid
    AND PP.prodid = PR.pref
    WHERE userid = [b]'[/b]$urow->{userid}[b]'[/b]) Y
    ON X.prodid = Y.prodid
    GROUP BY X.prodid) A
    ON B.prodid = A.prodid
    AND B.price_each = A.minprice) C
    ON S.supplierid = C.supplierid
    INNER JOIN products D
    ON C.prodid = D.prodid
    ORDER BY D.catid
});
$result->execute();

M. Brooks

http://mbrooks.info

 

[heals1ic]

I have tried your code and it still gives me an error:

Use of uninitialized value in concatenation (.) or string at email_prefs.pl line 16.

Line 16 is the line where the prepare statement is.

Is there a problem with the tablename.fieldname naming in the SQL. Does Perl see this as a concatenation attempt rather than the name of a field in a table?

 

[mbrooks]

Have you tested $urow->{userid} to see if it contains a value?

M. Brooks

http://mbrooks.info

 

[MikeLacey]

Print out the query and see if it contains what you were expecting. Should be easy to see what's not as it should be then.

Mike

I am not inscrutable.

Want great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[heals1ic]

mbrooks:

Have you tested $urow->{userid} to see if it contains a value?

$urow->{userid} does contain the desired result. I don't think it would be the cause of this error anyway.

MikeLacey:

Print out the query ....

The printout is just what I intent to send as an sql statement.

 

[heals1ic]

I seem to have solved the problem with:

use (strict);

and declaring the variables using "my".

Thanks people for your help