PHP MySQL Query Not Returning Full Set of Desired Results

Question

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

Break the problem in pieces. Run the query directly in a Client so there is no php in the picture. After you make sure the query is okay, you forget the sql it is not in the debugging picture anymore, then php will be easy to debug, at last it's an array matter not something diffuclt. — Melsi
– Melsi, Commented Sep 17, 2011 at 20:18
how does the $row var change during the while loop? I'd see how it evolves w/ debugger. Or even a var_dump of it as the first thing. If something's wrong with it, it might impact further array construction for you. — maraspin
– maraspin, Commented Sep 17, 2011 at 20:23
I agree w/ Melsi. Mine is also a suggestion to break the problem in pieces. First find out if the query returns you expected results. Then, try to follow how the $row variable evolves with mysql_fetch_assoc calls. If all is as you expect, then also watch the new array upon each step of its creation. — maraspin
– maraspin, Commented Sep 17, 2011 at 20:25
@Melsi - Thanks for the advice. I ran the query directly in phpMyAdmin (I think that's what you meant). The query returned everything properly. So I guess the problem is in the PHP. I've never debugged PHP, what would you recommend? — stefmikhail
– stefmikhail, Commented Sep 17, 2011 at 21:28

Melsi · Accepted Answer · 2011-09-17 23:55:54Z

1

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

edited Sep 17, 2011 at 23:55

answered Sep 17, 2011 at 23:00

Melsi

1,4371 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Melsi Over a year ago

Already too late for me, bed time now. Happy to answear again when back, bye! I am struggling to think why it works for me and not for you, but... however do check my script exactly as it is and see what differences are there in comparison to yours. Bye!

stefmikhail Over a year ago

Ever feel like a complete moron? I realized (with the help of a very good de-bugging tool called MacGDBp), that when I was switching from using print_r to test, and $cnt[$row['COL1']] = $row['imgCount'];, I forgot to remove the $equalDimensions_array = mysql_fetch_assoc($equalDimensions_data); which was apparently storing, and removing, the first array. I feel awful to have made you go through all that for a stupid mistake on my part. Please accept my apology and thanks for all your help.

Collectives™ on Stack Overflow

PHP MySQL Query Not Returning Full Set of Desired Results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related