MySQLi query stops at first item in a column when I need all items

Question

I want to get all the image_name values from one of my tables where the category and account_id match. The query joins 3 tables.

I am using mysqli.

SELECT image_name 
FROM add_images, item, user_item
WHERE user_item.account_id = '$accountId' AND item.category = '$category';

However when this query executes and var_dump() the result, I find only the first record? there should be 8 items?

I have confirmed this by returning count() on the array which returned 1.

If I run this query in phpmyadmin the query runs successfully and it finds all 8 items, however they are repeated 8 times, not sure what that is about?

I have replaced image_name with * also I have tried image_name.* and image_name* all fail in my code and in phpmyadmin.

When I dump out the object I get:

object(mysqli_result)#3 (5) { ["current_field"]=> int(0) ["field_count"]=> int(1) ["lengths"]=> NULL ["num_rows"]=> int(512) ["type"]=> int(0) } int(1) string(29)

I have also tried the following suggested in: How get all values in a column using PHP?

while($row = mysqli_fetch_array($result,MYSQL_ASSOC))

but the suggested code also fails.

Below is my code:

public function searchItems($accountId, $catagory,$userId){
    $imagePath = "D:\imagesdb\images\\" . $userId;
    $thumb_path = $imagePath . "t\\";
    $conn = $this->create_connection('read');
    $sql = "SELECT image_name FROM add_images, item, user_item WHERE user_item.account_id='$accountId' AND item.catagory='$catagory'"; 
    $result = $conn->query($sql)or die(mysql_error());
    var_dump($result);
    while ($row = mysqli_fetch_array($result)){
        $path = $thumb_path . $row['image_name'];
        $result = count($path);
        var_dump($result);
        return $path;
    }
    $conn->close();
}

I have also tried a prepared statement I have the same issue which I expected!

Does anyone have any suggestions please? This has been driving me mad!

score 1 · Accepted Answer · answered Mar 06 '12 at 12:39

1

You are returning $path unconditionally in the while-loop. So the function returns after it has fetched one row. This is probably not what you intended.

You probably want to create an array, add all image paths to that and return the array at the end of the function.

answered Mar 06 '12 at 12:39

Thanks for the suggestion, yes this is not what i intended! I have modified my code problem is it still returns one item $path = array(); while($row = mysqli_fetch_array($result)){ $path = $thumb_path . $row['image_name']; } return $path; $conn->close(); } – Alan Mar 06 '12 at 12:49
replace `$path = ...` with `$path[] = ...` in the while loop. otherwise you are just replacing the array with the current value instead of adding elements to it. – Mar 06 '12 at 12:52
argh! thank you so much! it works any idea why items are repeated tho? – Alan Mar 06 '12 at 12:56
You're welcome. Duplicate items you not happen, check the SQL for errors again or just use `array_unique`. – Mar 06 '12 at 13:00
I will look into this thanks again for your help, much appricated – Alan Mar 06 '12 at 13:01

MySQLi query stops at first item in a column when I need all items

1 Answers1