CODEWITHSUNDEEP
phpmysqljoinmysqli
JT Smith
JT Smith

Reputation: 741

Retrieving a JOIN array from MySql

I'm pulling a joined query from the DB using the following code:

$query = 'SELECT `profiles`.`city`, `users`.`first_name`, `users`.`last_name`'
         . ' FROM profiles, users'
         . ' WHERE ((`profiles`.`user_id` = 3) AND (`users`.`user_id` = 3))'
         . ' LIMIT 1';
  $result = $connection -> query($query);

  $arr = $result -> fetch_array();

  $feedback = $arr;

  $result -> close();
  return $feedback;

This isn't my final code, this is just preliminary to test my join.

The query seems to work fine, it pulls the single row of information I need but I noticed that when I var_dump it, it's put the array into 2 different indexes, a numerical index and an index of the name of the db field. Here's what the var_dump looks like:

array(6) { [0]=> string(4) "Reno" ["city"]=> string(4) "Reno" [1]=> string(4) "John" ["first_name"]=> string(4) "John" [2]=> string(3) "Doe" ["last_name"]=> string(3) "Doe" }

This is effectively doubling the size of my query result. For a single row this won't be that big of a deal but if/when I begin to use this same method to draw multiple records, doubling the size of the result can be costly.

I've tried to do a foreach loop:

foreach ($arr as $key=>$value) {
   $feedback[$key] = $value;
}

but it leaves me with the same result. Am I doing something wrong and if so, how do I correct it or is this normal?

Thanks for you help

Upvotes: 0

Views: 876

Answers (3)

Enrico
Enrico

Reputation: 673

If you use fetch row (http://www.php.net/manual/en/mysqli-result.fetch-row.php) you will fetch without the names. I think this is what you are looking for, right?

EDIT: This also apllied to mysql, but the function is mysql_fetch_row($result) instead of mysqli

Upvotes: 0

Phil
Phil

Reputation: 164871

The default result type for mysqli_result::fetch_array() is MYSQLI_BOTH meaning you get both named and numeric result array indices (see http://php.net/manual/en/mysqli-result.fetch-array.php).

Try using fetch_assoc() instead.

Upvotes: 1

Taylor Dondich
Taylor Dondich

Reputation: 658

To reduce the size, you'll want to look at the second optional argument to mysql_fetch_array. This is documented here:

http://www.php.net/manual/en/function.mysql-fetch-array.php

You can specify if you just want numerical-indexes or associative arrays.

Upvotes: 0

Related Questions