Difficult sql - MAX(CASE)

[sdelaney]

I'm trying to extract some order information from the database and display it on an EZ Page. I have done all the things to enable PHP for the EZ Page and it is working fine. I am "including" a php file that does the database access and displays the data I want.

I have been plugging away at all this code and have pretty much got it all coming together. But I have reached an impasse with the sql. If someone can spot what I am doing wrong, it would be highly appreciated!

I have tested the sql on phpAdmin and it works (well it has to be slightly different than as it would appear here) - I get the data I expect.

I picked apart my included file to see where my problem lies. The looping etc is working just fine. The sql appears to execute and return the right number of rows. I tested this by simply outputting a string instead of any data, and the string was displayed the correct number of times.

Whenever I attempt to manipulate or display data from the result set, the screen is blank from that point. I figure I must not be getting the Execute statement correct? I have tried adding in BindVars and that gives me an undefined variable type error. Therefore, I am pretty sure something is wrong in the sql or the functions executing the sql, producing an unusable result set (that has the right number of rows). Any ideas?

Zen 1.3.8a
Database Patch Level 1.3.8
PHP 5.2.9
Zend 2.2.0
MySql 5.0.89

Code:

<?php

$sql_list = "SELECT zo.customers_name, zop.products_id, MAX(CASE WHEN zopa.products_options_id = '11' THEN zopa.products_options_values END) fname, MAX(CASE WHEN zopa.products_options_id = '12' THEN zopa.products_options_values END) lname FROM " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " zopa LEFT JOIN " . TABLE_ORDERS . " zo ON zo.orders_id = zopa.orders_id LEFT JOIN " . TABLE_ORDERS_PRODUCTS . " zop ON zop.orders_products_id = zopa.orders_products_id GROUP BY zopa.orders_id HAVING zop.products_id = '6'";

// $sql_list = $db->bindVars($sql_list); ???????

$sql_result = $db->Execute($sql_list);

echo "<ul>";

while (!$sql_result->EOF) {
If $sql_result->fields['fname'] = '' {
$sql_concat = $sql_result->fields['customers_name']);
} else {
$sql_concat = $sql_result->fields['fname'] . " " . $sql_result->fields['lname'];
}
echo '<li>' . $sql_concat . '</li>';

$sql_result->MoveNext();
}

echo "</ul>";

?>

[skipwater]

while (!$sql_result->EOF) {
If $sql_result->fields['fname'] = '' {
$sql_concat = $sql_result->fields['customers_name']);
} else {
$sql_concat = $sql_result->fields['fname'] . " " . $sql_result->fields['lname'];
}

while (!$sql_result->EOF) {
If ($sql_result->fields['fname'] = ''){
$sql_concat = $sql_result->fields['customers_name'];
} else {
$sql_concat = $sql_result->fields['fname'] . " " . $sql_result->fields['lname'];
}

You might want to load DrBytes debug tool that would help you.

Skip

[sdelaney]

Thank you for spotting the missing brackets! That will help for sure. I will also check out this debug tool.

I did want to note, that in my initial testing, I used a simple sql statement (just a couple of columns from the orders table) and those I was able to display with no problem.

[sdelaney]

Okay, I managed to figure things out. For those who are interested:

• I had assigned token names to the evaluated columns retrieved in my sql, but not to the "ordinary" columns. When I added token names for the "ordinary" columns, all the columns were returned correctly. Before that, the "ordinary" columns returned NULL.

• I changed the If clause to use the is_null() function and got the brackets done correctly, and then everything worked perfectly.

It is definitely a nuisance when you have two un-related errors in a script!

[Steven300]

If you want to compare something, shouldn't it be == instead of = ?