[SOLVED] MySQL query result into PHP array?

[robr]

How can I access the data from a MySQL query result as a multidimensional array?

 

For example I want something like:

 

<?php 
$elements['17']['link_url']
?>

 

where 17 is the result row number and 'link_url' is the result column heading.

 

Is there a way to specify the array "row"? For example [pre]echo $elements['1']['webdir_e_ne'];[/pre] echos nothing, but [pre]echo $elements['webdir_e_ne'];[/pre] echos '10' which is the value of the first row for that column.

 

<?php 
$query = "SELECT webdir_e_id, webdir_e_type, webdir_e_title, webdir_e_pe, webdir_e_ne, webdir_e_eoc, webdir_e_status, webdir_e_linkid
FROM p1_webdir_elements
ORDER BY webdir_e_id";
if (!($result = @ mysql_query ($query, $connection))){
showerror();
}
$elements = @ mysql_fetch_assoc($result);
?>

 

That is what I have to put the data into the array $elements.

 

I am going to try, what i think must be a round about way of doing it, using a while loop and constructing my own array. But is there a better way?

 

<?php 
while($row = @ mysql_fetch_assoc($result)){
$id = $row['webdir_e_id'];

$elements [$id]['webdir_e_type'] = $row['webdir_e_type'];
$elements [$id]['webdir_e_title'] = $row['webdir_e_title'];
$elements [$id]['webdir_e_pe'] = $row['webdir_e_pe'];
$elements [$id]['webdir_e_ne'] = $row['webdir_e_ne'];
$elements [$id]['webdir_e_eoc'] = $row['webdir_e_eoc'];
$elements [$id]['webdir_e_status'] = $row['webdir_e_status'];
$elements [$id]['webdir_e_linkid'] = $row['webdir_e_linkid'];
}
?>

 

Will give that a go now, and report back. Any opinions?

 

Cheers, Rob

 

 

[Orio]

What you posted will work. You can even do it shorter, this way:

 

 

<?php 

$query = "SELECT webdir_e_id, webdir_e_type, webdir_e_title, webdir_e_pe, webdir_e_ne, webdir_e_eoc, webdir_e_status, webdir_e_linkid
FROM p1_webdir_elements
ORDER BY webdir_e_id";
if (!($result = @ mysql_query ($query, $connection))){
showerror();
}

$elements = array();
while($row = mysql_fetch_assoc($result))
{
foreach($row as $column => $val)
	$elements[$row['webdir_e_id']][$column] = $val;
}

?>

 

 

Orio.

[robr]

That is much better - why didn't I think of that?

 

Thank you Orio - I will use that code instead.

 

It does strick me that the data is in an array already and I am just moving it to another. If there was a way I could specify the row of the result from the query array it would render 'copying' the array unnecessary. Should I worry about the overhead of doing this? (I expect the result to return about 2000 rows.

[robr]

Orio, does this not give a problem though?

 

the first dimension of the array will the id value from the MySQL table (great), and the second dimension is an associated array of the row from the MySQL table but the first element in that associated array will be the id from the table.

 

It is not a problem in my code as I won't loop through the associated array but is messy (if i have followed the logic correctly).

 

Will test it now 

[robr]

Yeah, just tested and it was as i said. I feel, a bit like my C++ programming, that I am just hashing it.

 

<?php 
echo $elements['20']['webdir_e_id'];
?>

 

this echos 20

[Orio]

For your first reply

It depends what you are trying to do. Maybe the new array's setup is totally un-needed.

Could you share what you're trying to do?

 

For your second reply

What do you mean? The code basically stores everything you've selected in a way that you can choose a row (first dimension) and the column's name (second dimension).

 

For your third reply

So is it ok? I don't really understand  :-\

 

 

Orio.

[kenrbnsn]

If you don't want the value of the webdir_e_id to be stored in your array, skip it in your foreach loop:

<?php
foreach($row as $column => $val)
	if ($column != 'webdir_e_id']) $elements[$row['webdir_e_id']][$column] = $val;
?>

 

Ken

[robr]

Cheers Ken,

 

That makes it better.

[robr]

Orio,

 

[just seen your response]

 

1st dimension = id value (the row basically)

2nd dimension = column name

 

but one of the column names is the id. The id value is repeated. I was just saying that it is not *clean* totally efficient. I was being a perfectionist. for my application this does not cause a problem. Maybe for some appliction where i had to loop through the 2nd dimension it could be.

 

Anyway - I am sorry for not being clear.

 

What I am trying to do:

this might take a bit of explaining.

I am trying to make my web directory database driven (see http://directory.rieder.co.uk.

 

the web directory (simple html) can be thought of as a list of tags.

 

I want a user to be able to add links and move 'chunks' around.

I have defined bits of the code as block, h1, h2, h3, group, link - they are the elements which the array at the start of this post is about.

 

each element links to the next element - like a long chain of elements (or a beaded necklace). I want the array in the way it is above so that I can call a function that actions the next element:

 

<?php
next_element( $elements[$current_id]['webdir_e_ne']  );
?>

 

the id numbers will not be consequetive. I want to be able to move an element. This is easy to do by knowing where you are moving it to in the chain. Also i want to move an entire 'chunk' of elements around. this uses the webdir_e_eoc variable (end of chuck) which is the id of the last element in the chunk.

 

It becomes more comlicated when the elements do not have consistent parent/child relationships...

 

Maybe this gives you an idea of what i am trying to achieve. I enjoy coding but am at a loss for advice (other than here or text books or google searches) and so don't know how experienced coders would design a relational database system for example. Struggling to work out the logic of something like this page, basically dealing with ordering lists, I feel i am reinventing the wheel as people must surely have done this sort of thing before.

 

Thank you for your help Orio. Take care, Rob