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.

After reading muiltiple stackoverflow questions I'm still confused on why I am not able to get this to work. I am attempting to take all of the rows of a mysql table and put them into a multidimensional array. I was hoping to just find a simple example but so far I'm not fully understanding.

The examples I've seen on google and stack is mostly about parsing or making it iterate on the page. I am attempting to have the mysql rows iterate into their arrays. The example code I started working with was similiar to this:

// This will hopefully get all of the categories
case 'get_all_categories' :
    $query = '
        SELECT `id`,`category_name`, `category_spam`
        FROM `category`
        ';

    if (!$go = @mysql_query($query)) {
        $results = Array(
                    'head' => Array(
                            'status' => '0',
                            'error_number' => '604', 
                            'error_message' => 'Select Failed. '.
                            'Probably wrong name supplied.'
                        ),
                    'body' => Array ()
                );
    } else {
        $fetch = mysql_fetch_row($go);
        $return = Array($fetch[0],$fetch[1]);  
        $results = Array(  
                    'body' => Array (  
                            'id'    => $return[0],  
                            'category_name' => $return[1]  
                        )  
                ); 
    }
    break;

This obviously provides me with the result of just ONE as that is what I'm asking it to do with fetch_row.

i.e.

//outputs
{"body":{"id":"1","category_name":"Star Wars"}}

I've attempted to use mysql_fetch_assoc and then do a foreach loop? I"ve also tried doing a mysql_fetch_all($go) and then attempt to put the rows into it.

if (!$go = @mysql_query($query)) {
    $results = Array(
                'head' => Array(
                        'status' => '0',
                        'error_number' => '604', 
                        'error_message' => 'Select Failed. '.
                        'Probably wrong name supplied.'
                    ),
                'body' => Array ()
            );
} else {
    while($myrow = mysql_fetch_row($go)){
        Array(
            'body' => Array(
                    'id' => $myrow[o], 
                    'category_name' => $myrow[1],
                    'category_spam' => $myrow[2]
                )
    }
    );
}
break;

I am attempting to make an api call where the body holds all of the categories and their id's (the point I believe is then for the categories to pull and show the lists...which I'm hoping is the right away to make an api(not a question just rambling)

So: how do I make the query take all of the mysql rows and loop them into arrays for the body array. (note: I'm new to API's if my terminology is wrong to what my overall goal is which is to get an api call to show my whole mysql array then please let me know).

EDIT Just adding more info from the comments

The output is JSON encoded (sorry I should have explained that)

switch ($_GET['format']) {
        case 'xml' :
                @header ("content-type: text/xml charset=utf-8");
                $xml = new XmlWriter();
                $xml->openMemory();
                $xml->startDocument('1.0', 'UTF-8');
                $xml->startElement('callback');
                $xml->writeAttribute('xmlns:xsi','http://www.w3.org/2001/XMLSchema-instance');
                $xml->writeAttribute('xsi:noNamespaceSchemaLocation','schema.xsd');
                function write(XMLWriter $xml, $data){
                        foreach($data as $key => $value){
                                if(is_array($value)){
                                        $xml->startElement($key);
                                        write($xml, $value);
                                        $xml->endElement();
                                        continue;
                                }
                                $xml->writeElement($key, $value);
                        }
                }
                write($xml, $results);

                $xml->endElement();
                echo $xml->outputMemory(true);
            break;
        case 'json' :
                @header ("content-type: text/json charset=utf-8");
                echo json_encode($results);
            break;
        case 'php' :
                header ("content-type: text/php charset=utf-8");  
                echo serialize($results);  
            break;
    }
share|improve this question
1  
You normally don't want to suppress errors like @mysql_query(), you want to solve them. –  Francisco Presencia Jan 22 at 3:01
    
I maybe missing something here but have you tried: $results .= Array( 'body'.. (Notice the dot before the = sign). –  jeff Jan 22 at 3:02
    
@FranciscoPresencia I'm a newbie but I was under the impression that by having !$go = @mysql_query() that was like saying if go doesn't have any suppressed errors do this.... or am I completely misunderstanding @?? –  Cody Jan 22 at 3:19
    
@jeff I attempted that and it didn't really help. I"m not a 100% sure how .= works either but I attempted it. Additionally The first part of the code works as you can see by my json output. I'm just having a hard time getting all of my table into an array and I haven't seen anyone asking this (or I"m asking this the wrong way?!) –  Cody Jan 22 at 3:20
1  
I'm not sure actually, it really looks overly complicated. I find it much easier to show the errors for development/debug and to log the errors/exceptions for production. From the documentation we can see that you don't need the @, since it returns false on error, it doesn't stop the code. Make sure you use if (mysql_query !== false), with !==, as the query might return 0 or "" without them being false. Also, try to update your code to PDO since mysql_* is being deprecated –  Francisco Presencia Jan 22 at 3:25
show 1 more comment

1 Answer

up vote 3 down vote accepted
   $res = mysql_query($query);   
   $results = array();
   $i = 0;
   while($row=mysql_fetch_array($res)) {
            $results['body'][$i]['id']            =  $row[o];
            $results['body'][$i]['category_name'] =  $row[1];
            $results['body'][$i]['category_spam'] =  $row[2];
            $i++;
   }
   if(empty($results['body'])){
          $results['head'] =  Array(
                            'status' => '0',
                            'error_number' => '604', 
                            'error_message' => 'Select Failed. '.
                            'Probably wrong name supplied.'
                             );
   }

   break;

Try the above code it will create $result array for your json response.

Rest everything is fine!

share|improve this answer
    
This displays my last entry. i.e. {"body":{"id":"5","category_name":"Sports"}} as opposed to {"body":{"id":"1","category_name":"Star Wars"}} Any thoughts? –  Cody Jan 22 at 4:30
    
@Cody answer edited –  Anubhav Jan 22 at 4:58
    
That does exactly what I was hoping for! So what I was missing in my initial while loop was the i/i++ and making an 'array''array' If I was going 3 arrays deep it would be like this ['array1']['array2'][$i]['value'] with $i being the array and rolling out what was in said array? –  Cody Jan 22 at 6:30
    
exactly you just need to check indexs of array...2D, 3D ...so on –  Anubhav Jan 22 at 6:39
add comment

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.