Query output not being passed

[deepatpaul]

I have a Location component that has a paypal optional payment field populated, and a Disable webshop registration = yes in the Administration. A query on the front end pulls both of these fields, but is returing the paypal field blank. Below is a sample of the query. location.regDisabled is boolean, while location.paypalAddress is varchar.

Any idea on why a field that I KNOW is populated in the backend isn't returning anything in the query?

SELECT product.product_id, schedule.*, DAYOFWEEK(schedule.start) AS day, location.name AS locationName, location.city, location.state, location.regDisabled, location.regFile, location.paypalAddress
FROM mos_makinmusic_schedule AS schedule INNER JOIN
mos_makinmusic_class AS class ON (schedule.classid=class.id) INNER JOIN
mos_makinmusic_location AS location ON (schedule.locationid=location.id)

Output for template results below.

$bRegDisabled = false;
$bPaypalAddress = false;

if($aLocation['regDisabled']) // Currently, this is true
{
$bRegDisabled = true;

if(!empty($aLocation['paypalAddress'])) // this should not be empty
$bPaypalAddress = true;
}

 

[DRJ478]

Please let us know what kind of database backend it is.

 

[deepatpaul]

Sorry. It's mySQL.

 

[DRJ478]

I think it has to do with the way MySQL optimizes nested joins. There's a whole chapter about that:

http://dev.mysql.com/doc/mysql/en/nested-joins.html

However, have you tried your query in a query browser? WHat is the displayed result set?

 

[deepatpaul]

I get null output to the browser.

 

[deepatpaul]

Are you saying that the nested join treats boolean output differently than varchar?

 

[KingCrab]

The SQL looks fine besides the fact product.product_id has no table associated with it.

Since it is returning a value then the JOINS are working correctly. There must not be a value in that field.

Then again, I do not think we have the complete SQL you are using because as-is it would never return a thing.

 

[deepatpaul]

Here's the full script:

SELECT product.product_id, schedule.*, DAYOFWEEK(schedule.start) AS day, location.name AS locationName, location.city, location.state, location.regDisabled, location.regFile, location.paypalAddress, location.emailRegistration, class.name, class.beginAge, class.endAge, user.name AS teacher
FROM mos_makinmusic_schedule AS schedule INNER JOIN
mos_makinmusic_class AS class ON (schedule.classid=class.id) INNER JOIN
mos_makinmusic_location AS location ON (schedule.locationid=location.id) INNER JOIN
mos_makinmusic_schedule_teacher AS teacherTmp ON (teacherTmp.scheduleid=schedule.id) INNER JOIN
mos_makinmusic_teacher AS teacher ON (teacherTmp.teacherid=teacher.id) INNER JOIN
mos_pshop_product AS product ON (schedule.id=product.scheduleid) INNER JOIN
mos_users AS user ON (teacher.userid=user.id) WHERE schedule.published='1' AND schedule.classid=1
ORDER BY location.name, location.state, schedule.start, class.name, teacher

 

[KingCrab]

so whats happens when you do a

SELECT l.paypalAddress FROM schedule s
INNER JOIN location l ON l.id=s.locationid
WHERE s.classid=1

Do you get a value returned?

 

[deepatpaul]

I had 46 records retrieved, only about 1/3 of which were non-null. But, they were all the same value (since we're not using a DISTINCT in here).