CODEWITHSUNDEEP
phpmysqlsql
stefmikhail
stefmikhail

Reputation: 7145

PHP MySQL Query Not Returning Full Set of Desired Results

I have the following mysql_query which I have placed in a PHP variable:

$equalDimensions_query = 
"SELECT 'allEqual' AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight = $maxImageHeight
UNION ALL
SELECT 'widthEqual' AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight != $maxImageHeight
UNION ALL
SELECT 'heightEqual' AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images  
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth != $maxImageWidth AND imgHeight = $maxImageHeight";

I am using the following PHP to place the results into one single associative array:

$equalDimensions_data = mysql_query($equalDimensions_query) or die('MySql Error' . mysql_error());

while ($row = mysql_fetch_assoc($equalDimensions_data)) { 
    $cnt[$row['COL1']] = $row['imgCount']; 
}

It is suppose to return a set of three arrays with the values of allEqual in the first, widthEqual in the second, and heightEqual in the third (the order does not matter).

Alas for some reason it is not returning allEqual:

Array
(
    [heightEqual] => 0
    [widthEqual] => 0
)

When I use print_r to display the retrieved data in its original 'three array' form, I get the same result of only two arrays:

Array
(
    [COL1] => heightEqual
    [imgCount] => 0
)
Array
(
    [COL1] => widthEqual
    [imgCount] => 0
)

Yet, if I use print_r without the loop as follows:

$equalDimensions_data = mysql_query($equalDimensions_query) or die('MySql Error' . mysql_error());
$equalDimensions_array = mysql_fetch_assoc($equalDimensions_data);

print("<pre>");
print_r($equalDimensions_array);
print("</pre>");

I am returned the previously missing allEqual array:

Array
(
    [COL1] => allEqual
    [imgCount] => 2
)

I understand that because of the absence of the while loop in the last case, I am only being returned one result; But why is it, that in the case of the while loop, the allEqual result appears to be skipped over? Is it a problem with my code? I appreciate any help you can provide. And I apologize for such a long question; I wanted to be sure I provided as much information as I could.

You can download my database schema here: https://files.me.com/stefanmelnychenko/453l4z

Upvotes: 1

Views: 488

Answers (1)

Melsi
Melsi

Reputation: 1462

This is where you copy from. You don't change variables or anything in order to copy any errors if any. sql echoed

I checked your schemma and everything was fine, this is the php script:

<?PHP

// Make a MySQL Connection
mysql_connect("localhost", "root", "") or die(mysql_error());

//select database   
mysql_select_db("new_arrivals_imgs") or die(mysql_error());


$imgId=1;
$maxImageHeight=1;
$maxImageWidth=1;

 $equalDimensions_query = 
"SELECT 'allEqual' AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight = $maxImageHeight
UNION ALL
SELECT 'widthEqual' AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth = $maxImageWidth AND imgHeight != $maxImageHeight
UNION ALL
SELECT 'heightEqual' AS COL1,COUNT(*) AS imgCount FROM (
    SELECT imgHeight, imgWidth, imgId AS primaryId FROM primary_images  
    UNION ALL 
    SELECT imgHeight, imgWidth, primaryId FROM secondary_images
) AS union_table
WHERE primaryId = $imgId AND imgWidth != $maxImageWidth AND imgHeight = $maxImageHeight";

$equalDimensions_data = mysql_query($equalDimensions_query)
 or die('MySql Error' . mysql_error());

while ($row = mysql_fetch_assoc($equalDimensions_data)) { 
    $cnt[$row['COL1']] = $row['imgCount']; 
}

print_r($cnt);

?>

and this is the result:

enter image description here

Upvotes: 1

Related Questions