Populate a 2d array from iterated database queries

Question

I apologize a head of time for the lengthy/busy question. I am trying to take results from a MySQL query and base64_encode the Image blob data, return it to the array and then finally json_encode the results so I can use them in my Android application. I know everything on the Android side is set up properly.

What I have is the following:

PHP/SQL:

$query = "SELECT `locations`.`businessName`, `photos`.`img` 
        FROM `locations` 
        JOIN `photos` ON `locations`.`co_id` = `photos`.`co_id` 
        WHERE `locations`.`businessName` = '".$companyID."'";
            
    mysql_connect($dbserver, $dbusername, $dbpassword) or die(mysql_error());
    mysql_select_db($dbname) or die(mysql_error());
    
    $result = mysql_query($query) or die(mysql_error());  
    $num = mysql_numrows($result);
    $row = mysql_fetch_assoc($result);
    
    $i = 0;
    $rows = array();
    while ($i < $num) {
        
        $img = mysql_result($result, $i, "img");
        $finalImg['img'] = base64_encode($img);
        $businessName['businessName'] = mysql_result($result, $i, "businessName");
        
        $finalArray = array_push($rows, $businessName, $finalImg);
        // I know that array_push is pushing each variable as a separate array item
        // I tried creating an alternative variable that amends the two together
        // But that didn't work, result printed [Array, Array] [Array, Array]
        // Was I on the right track?

        $i++;
    }
    
    print json_encode($rows);

Returns 8 results:

[0] => {
    ["businessName"]=> string(12) "Some Company" }
[1] => {
    ["img"]=> string(145968) "/9j/4AAQSkZJRgABAQEAYABgAAD/4QIw..." }

What I need:

I want the results to appear like this, with only 4 results.

[0] => {
    ["businessName"] => string(12) "Some Company" 
    ["img"] => string(145968) "/9j/4AAQSkZJRgABAQEAYABgAAD/4QIw..." }
[1] => {
    ["businessName"] => string(12) "Some Company", 
    ["img"] => string(145968) "/9j/4AAQSkZJRgABAQEAYABgAAD/4QIw..." }

Android Application snippet:

jArray = new JSONArray(result);
for (int i = 0; i < jArray.length(); i++) {
    JSONObject jObject = jArray.getJSONObject(i);
    String testerPhoto = jObject.getString("img");
    //Process image. Base64 decode... etc

Android Error:

07-18 11:28:52.573: E/onPostExecute(14562): FAILED: No value for img

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — Madara's Ghost
– Madara's Ghost, Commented Jul 18, 2012 at 15:41
I didn't know, hadn't needed to look in the manual for awhile on said syntax, thus no red box awareness. I will correct myself. Thanks for the references. — jnthnjns
– jnthnjns, Commented Jul 18, 2012 at 15:45

Ross Smith II · Accepted Answer · 2012-07-18 15:41:42Z

3

This is much simpler:

$i = 0;
$rows = array();
while ($i++ < $num) {
    $img = mysql_result($result, $i, "img");
    $rows[] array(
        'businessName' => mysql_result($result, $i, "businessName"),
        'img' => base64_encode($img),
    );
}

answered Jul 18, 2012 at 15:41

Ross Smith II

12.2k1 gold badge41 silver badges46 bronze badges

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

Comments

Zbigniew · Accepted Answer · 2012-07-18 15:40:23Z

1

Try this:

array_push($rows, array_merge($businessName, $finalImg));

answered Jul 18, 2012 at 15:40

Zbigniew

27.6k6 gold badges62 silver badges68 bronze badges

2 Comments

jnthnjns Over a year ago

I knew it had to be something little that I didn't think of. Thanks. Worked perfectly.

Zbigniew Over a year ago

No problem, all you really needed was array of arrays ;)

Besnik · Accepted Answer · 2012-07-18 15:44:54Z

1

What about this?

$rows[] = array(
  'img' => base64_encode($img),
  'businessName' => mysql_result($result, $i, "businessName")
);

Or

array_push($rows, array_merge($businessName, $finalImg));

answered Jul 18, 2012 at 15:44

Besnik

6,5581 gold badge33 silver badges34 bronze badges

Collectives™ on Stack Overflow

Populate a 2d array from iterated database queries

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related