Why is my MySQL query returning a multidimensional array rather than a single-dimension array?

Question

This is my query:

SELECT DISTINCT ap.apptype_id
FROM tblSubmission AS s
JOIN tblApp AS ap ON ap.app_id = s.app_id
WHERE s.app_id =406
OR s.app_id =180
OR s.app_id =179

It returns just unique apptype_ids pertaining to rows in a database. It works right now like it should, except for the fact that it returns a multidimensional array, rather than a single-dimension array. I could go ahead and write a PHP function that resolves this, but I would rather have the query return the array properly rather than make the server do extra work to fix the problem with the query. Can anyone help me out?

Also, if anyone sees a way I can optimize this query to make it even quicker, that would be great as well!

function db_query($querystring) { 
   $rec_set = array(); 
   $result = mysql_query($querystring); 
   if (!$result) { 
       // do nothing, query might have failed but it might have just returned no results          
       #echo ("Invalid Query Result:<br /><b>" . $querystring . "</b><br />"); // debug 
    }elseif($result === true){ 
       // do nothing, query didn't return anything but succeeded 
    }else{ 
       while ($row = mysql_fetch_assoc($result)) { $rec_set[] = $row; }            
       mysql_free_result($result); 
    } 
    return $rec_set; 
  }

How are you fetching it? That should return a resource, if called from mysql_query. — AeroCross
– AeroCross, Commented Oct 16, 2012 at 15:59
can you show an example of the array it is returning, since I think that is more a function of what you are using to query the DB, not the query itself. — Doon
– Doon, Commented Oct 16, 2012 at 15:59
Interpretation of SQL results, as far as I know, depends on the php side. Where's php code? — walther
– walther, Commented Oct 16, 2012 at 15:59
That's just how queries work. It will return each successful select as a row so it will return an array of rows, each row holding an array of values. In this case, the data is just a single value (the id). — MatthewMcGovern
– MatthewMcGovern, Commented Oct 16, 2012 at 16:03
This is the array that is returned from the query: Array ( [0] => Array ( [apptype_id] => 2 ) [1] => Array ( [apptype_id] => 7 ) ) — mrinehart93
– mrinehart93, Commented Oct 16, 2012 at 16:05

aaron-bond · Accepted Answer · 2012-10-17 08:27:31Z

1

You're returning two records so it's multidimensional.

You have DISTINCT ap.apptype_id and your query finds two records with distinct ids.

EDIT: just to clarify; in a single dimensional array as you'd look for I believe the result you intended was:

array([7], [2]) // i.e your two distinct ids.

Try to remember that with mysql you get returned the column header too so this layout wouldn't make sense.

EDIT 2:

Maybe something like this would help?

$a = array(
    0 => array('first'=>7),
    1 => array('second'=>2)
);

$row = array_map('implode', $a);

OUTPUT:

array(2) { [0]=> string(1) "7" [1]=> string(1) "2" }

The array_map is the function you'd want here. It should work as you need.

edited Oct 17, 2012 at 8:27

answered Oct 16, 2012 at 16:11

aaron-bond

3,3494 gold badges28 silver badges41 bronze badges

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

2 Comments

mrinehart93 Over a year ago

Ah, I didn't know the column header would be returned as well. And yes, the array([7], [2]) is what I am trying to get. I assume there is no way to strip the column header?

aaron-bond Over a year ago

EDIT: Moved into main answer.

taufique · Accepted Answer · 2012-10-16 16:23:48Z

0

SELECT DISTINCT ap.apptype_id
FROM tblSubmission AS s
JOIN tblApp AS ap ON ap.app_id = s.app_id

Above part will select all distinct apptype_id for which app_id is both in tblSubmission and tblApp. And then you are taking if ID is either 406 OR 180 OR 179. So it will return all apptype_id for which corresponding IDs match with these three IDs and are in both tables.

Suppose,

tblApp(id, type) has three entries <406, 10>, <180, 11> and <100, 12>. tblSubmission(id, ...) has three entries <406, ...>, <180, ...> and <179, ...>. after joining table(id, type, ...) will have two entries <406, 10, ...> and <180, 11, ...> just think what will happen if you select distinct type? it will return two entries <10> and <11>

edited Oct 16, 2012 at 16:23

answered Oct 16, 2012 at 16:05

taufique

2,7891 gold badge32 silver badges40 bronze badges

Comments

Doon · Accepted Answer · 2012-10-16 17:58:44Z

0

It is returning the multi-dimensional array because you are asking it to with mysql_fetch_assoc($result)

If you move your code to something like PDO you can use fetch column to grab what you want. http://www.php.net/manual/en/pdostatement.fetchcolumn.php

In regards to your original question, the query is fine, the way you are fetching it from PHP is what is causing the issue. As for making it faster you might consider using IN as opposed opposed to the multiple ORs but I am not sure if that would make any difference with mysql (you can use EXPLAIN on the query to show what the DB is using to get the results)

edited Oct 16, 2012 at 17:58

answered Oct 16, 2012 at 17:49

Doon

20.3k3 gold badges42 silver badges44 bronze badges

1 Comment

mrinehart93 Over a year ago

Thank you for the answer. Unfortunately, I am not able to switch to PDO at the moment (maybe next year when the application is re-written). I'll just use PHP to flatten the array out instead. Once again, thank you!

Collectives™ on Stack Overflow

Why is my MySQL query returning a multidimensional array rather than a single-dimension array?

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related