current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm having major headaches trying to create a multidimensional array from two separate MySQL selects.... I've been searching here and Google all day and have to finally admit defeat and ask for some help (I'm a newbie as well which doesn't help!!!).

I have two tables, one which contains a single row result per id and another which can contain several rows for an id. What I'm trying to do is combine the two into a multidimensional array.

My code (poor as it may be) looks like this:

require 'php/phpConnection.php';

$sqlString1 = mysql_query("SELECT id FROM supportstaff_section1_a");

$firstArray = array();
$secondArray = array();

while ($r = mysql_fetch_assoc($sqlString1)) {
    $applicantID = $r['id'];
    $sqlString2 = mysql_query("SELECT educationalname FROM supportstaff_section5 WHERE id = '$applicantID'");

    while ($x = mysql_fetch_assoc($sqlString2)) {
        $secondArray[] = $x;
    }
    $firstArray[] = $r + $secondArray;
    $secondArray  = array();
}
print json_encode($firstArray);
mysql_close($con);

The result is this:

[{"id":"8m8wwy","0":{"educationalname":"GCSE - English"},"1":{"educationalname":"GCSE - Maths"}},{"id":"wiL7Bn"},{"id":"zAw6M1"}]

But I think it needs to look something like this:

[{"id":"8m8wwy","Array2":"[{"educationalname":"GCSE - English"},{"educationalname":"GCSE - Maths"}]"},{"id":"wiL7Bn"},{"id":"zAw6M1"}]

Anyway, how can I insert my second SQL Select into my first SQL Select for each ID.

Thanks for any advice/help.

EDIT

Taken from W3Schools.com:

Array
(
    [Griffin] => Array
    (
    [0] => Peter
    [1] => Lois
    [2] => Megan
    )
[Quagmire] => Array
    (
    [0] => Glenn
    )
[Brown] => Array
    (
    [0] => Cleveland
    [1] => Loretta
    [2] => Junior
    )
)

I'm trying to make it work like the above.

share|improve this question
    
What's the difference between the first and the second - can you explain it? –  Nicholas Pickering Feb 23 '13 at 20:42
    
The difference between the first and second tables would be that the first will only ever contain a single row of results per ID... for example ID, First Name, Second Name etc. The second table can contain multiple rows per ID... for example: ID-A, Exam Result 1 Some Center A, ID-A Exam Result 2 Some Center B, ID-A Exam Result 3 Some Center C etc –  jaminben Feb 23 '13 at 20:47
    
I think what you want to do is an SQL join. Btw, something relevant for the future: Don't use mysql_ functions –  A. Rodas Feb 23 '13 at 20:51
    
A join would work but I dont want to use a join as I thought a multidimensional array would be better. I wouldn't end up with repeated results from table one which are not needed more than once. Thanks for the tip about mysql_ functions. –  jaminben Feb 23 '13 at 20:56

2 Answers 2

up vote 3 down vote accepted

You need to get a little creative here. Something like the following would work as a join AND with multi-dimensional data:

<?php
  require 'php/phpConnection.php';

  // ======================================================================
  // Create a join query (way faster than several separate ones!)
  $sqlquery =
    "SELECT SSSA.id, SSS5.educationalname" .
    " FROM supportstaff_section1_a SSSA" .
      " LEFT OUTER JOIN supportstaff_section5 SSS5 ON SSS5.id = SSSA.ID";


  // ======================================================================
  // Run the query and get our results
  $resultarray = array();
  if ($resource = mysql_query($sqlquery)) {
    while ($curarray = mysql_fetch_assoc($resource)) {
      // Create an array, if it doesn't exist
      if (!isset($resultarray[$curarray["id"]]))
        $resultarray[$curarray["id"]] = array();

      // Add to the array, if not null
      $curstring = (string) $curarray["educationalname"];
      if ($curstring != "")
        $resultarray[$curarray["id"]][] = $curstring;
    }
    mysql_free_result($resource);
  }


  // ======================================================================
  // Convert from a keyed array to a standard indexed array (0, 1, 2, etc.)
  $finalarray = array();
  foreach ($resultarray as $id => & $data) {
    // Start with just ID
    $newarray = array(
      "id" => $id
    );

    // Get the data, if we have any
    if (count($data))
      $newarray["educationalnames"] = & $data;

    // Add to our final array and clear the newarray
    $finalarray[] = & $newarray;
    unset($newarray);
  }


  // ======================================================================
  // Get the JSON of our result
  $jsonresult = json_encode($finalarray);


  // ======================================================================
  // Echo it to test
  echo $jsonresult;


  // ======================================================================
  // Close the database
  mysql_close($con);
?>

And the resulting $jsondata would look like this (but not so unravelled of course):

[
  {
    "id": "8m8wwy",
    "educationalnames": ["GCSE - English", "GCSE - Maths"]
  },
  {
    "id": "wiL7Bn"
  },
  {
    "id": "zAw6M1"
  }
]
share|improve this answer
    
Thanks Mark... it almost works. If I run the query in PHPMyadmin I get: 8m8wwy GCSE - English 8m8wwy GCSE - Maths wiL7Bn NULL zAw6M1 NULL Which is correct but in a browser I only get: [{"id":"id","educationalnames":["GCSE - English","GCSE - Maths"]}] So its sort of working but not returning all results or the correct id. –  jaminben Feb 23 '13 at 21:07
    
Actually, with your edit, it looks like you don't even want to convert the formatting as I have shown, so I would cut out the whole // Convert from keyed ... section and just do the $jsonresult = json_encode($resultarray); instead. –  Mark Ormston Feb 23 '13 at 21:10
    
@user1399098 - Nevermind, I found the problem. Fixing in the original! Oops! –  Mark Ormston Feb 23 '13 at 21:12
    
Its this: $jsonresult = json_encode($resultarray); which returns [{"id":"id","educationalnames":["GCSE - English","GCSE - Maths"]}] –  jaminben Feb 23 '13 at 21:17
    
@user1399098 - Can you try the updated version? Also, should I update this to give the EDITED version you were asking for? –  Mark Ormston Feb 23 '13 at 21:17
up vote 0 down vote

If you have an ID from the first Array, you can check for keys / values with this ID in the second Array.

If you want to get the key you should use

array_key_exists($string)

And if you want to get the value you should use 

in_array($string)

You can use a foreach loop to execute this functions!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.