current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
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 13 down vote favorite
7

I have a mysql table that looks like this:

id | uid | title     | description | parent
1  |  1  | Portraits | desc.       | photostream
2  |  1  | Abstract  | descr.      | photostream

and I am trying to build a multi-dimensional array that would end up looking like this:

Array
(
      [0]
          [id] => 1
          [uid] => 1
          [title] => Portraits
          [description] => desc.
          [parent] => photostream
      [1]
          [id] => 2
          [uid] => 1
          [title] => Abstract
          [description] => descr.
          [parent] => photostream
)

I am using the select query:

$query = mysql_query(
  "SELECT * FROM `table` WHERE `uid`='1' ORDER BY `id` DESC");

Does anyone know how to do this? Thanks, Levi

share|improve this question
1  
If uid is a numeric column, do not compare or assign it to a string (single quoted value). MySQL won't complain but you might be surprised when the cast from string to number doesn't result in the number you expected. –  Dan Grossman Feb 21 '11 at 0:06
    
+1 how did this question not get any up votes? "Is useful and clear". c'mon people –  themerlinproject Feb 10 '12 at 5:48

3 Answers 3

up vote 14 down vote accepted 
$query = mysql_query("SELECT * FROM table WHERE uid = '1' ORDER BY id DESC");
$results = array();
while($line = mysql_fetch_array($query, MYSQL_ASSOC)){
    $results[] = $line;
}
share|improve this answer
7  
+1 | Or $line = mysql_fetch_assoc($query). –  Felix Kling Feb 19 '11 at 22:15
    
yeah! that's a good one, haha –  akellehe Feb 19 '11 at 22:17
1  
Thank you, that worked! –  Levi Self Feb 19 '11 at 22:17
up vote 1 down vote

This does not include the sql functions for gathering the data but I made a table for my issue (was the same exact problem) This is my table also by the way. So its more of example than an explanation.

This is not a full on explanation but is a example code.

TABLE:
[ ID | Parent | name |href | title ]

CODE:

            foreach ($query->result_array() as $row)
{
    if ($row['parent'] === null)
        $data[$row['id']][0]= '<a href="'.$row['href'].'"'.($row['title']!==null)?'title="'.$row['title'].'"':''.'>'.$row['name'].'</a>';
    else 
    {
        $temp = '<a href="'.$row['href'].'"'.($row['title']!==null)?'title="'.$row['title'].'"':''.'>'.$row['name'].'</a>';
        array_push($data[$row['parent']],$temp);
    }
}

I used this to generate my links for my navbar. Then I used a function to make a multi level list out of them. For my issue. Its very similar issue but this was my solution.

If you would like. Instead of making your own version. I can make a similar code using your database scheme for the data instead..

Warning: It seems this might only work on a two level layout at moment. Ill improvise code some more and post the next version of my snippet.

share|improve this answer
up vote 0 down vote 
$query = mysql_query("SELECT * FROM table WHERE uid = '1' ORDER BY id DESC");
$results = array();
$num_fields=mysql_num_fields($query);
$num_rows=mysql_num_rows($query);
while($line = mysql_fetch_array($query)){

for($i=0;$i<$num_rows;$i++
{
  for($j=0;$j<$num_fields;$j++
  {

  $results[$i][$j]=$line[$i][$j];
  }

}

}
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.