Retrieve multidimensional array from mysql??? HELP!!

[guts118]

Hi,

  I am developing a site to sell dvds. Each dvd can have multiple actors, I have created a select query that returns the film information and if there is more than 1 actor associated, more than 1 row is returned. I have tried to use the mysqli_fetch_array function to retrieve all the rows but this only seems to retrieve the first row. I assumed it would create a multidimensional array but I cannot find a second row. I have tried such things as $row[0][actor_name], $row[1][actor_name] but this does not work.

 

Can anyone give me suggestions?

 

Thanks

Guts

[mpharo]

I am having a similar issue, What I thought about doing is checking if the query returns more than one result using the mysql_num_rows() function and then using the array indexes to import them to a single variable...something like this....

 

$var="$sql[0],$sql[1]";

 

I am not sure if this will work in your case because it seems your array indexes are not working, have you tried just printing the array results with the print_r() function?

[boo_lolly]

what does your table look like? i don't see why you should have multiple rows for a single movie. just store all the actors in one cell separated by a comma.

[mpharo]

After re-reading the post it seems like boo is correct, you should be getting only 1 result if all the actors are in the same field in the same table....

[per1os]

In order to fetch a multiple array from mysql you have to create your own hybrid function as to say.

 

function fetchMultiArr($result) {
    if (!eregi('result', $result)) {
            return false; // not a result
     }

     $i=0;
     while ($row = mysql_fetch_assoc($result)) {
           $rows[$i++] = $row;
     }

     return $rows;
}

 

Using that will return a multi dimm array.

[mjlogan]

i use

 

if ($connDatabase->resultsCount($query) > 0) {
while($data = $connDatabase->fetchArray($query)){
	$array[$i]['field_01'] = $data['field_01'];
	$array[$i]['field_02'] = $data['field_02'];
	$array[$i]['field_03'] = $data['field_03'];
	$i++;
}
}

[per1os]

That works mjlogan, as long as you always want to define the field and have that code duplicated 20 times =)

 

Mine will return a multiDimm array for any query not caring what the rows names are so you can worry about that after it is returned.  The nice thing in having it as a function like I put is that it will create it on the fly without any extra data. This will work for 100 queries with the same code.

[boo_lolly]

i was thinking more along the lines of retrieving a single row, where one of the cells has many comma-separated entries, and when retrieving this row, explode()ing that cell into an array.

[per1os]

If that is what you were thinking, I think you may have mis-read the post =)

 

have tried to use the mysqli_fetch_array function to retrieve all the rows but this only seems to retrieve the first row.

 

Which generally means they are not using a while loop to retrieve the rows like most people learn the first day of mysql and php programming. That you have to call the fetch array in a while loop to return each row.. But to return each row as a multiDimm array you have to create your own function like I displayed above =)

[mpharo]

So if your wondering ( prolly not ) but here is a little code to represent what he is saying in the above post....

 

$query="SELECT * FROM table";

$i=0;

 

while($sql=mysql_fetch_array($query)) {

echo "Result: " . $sql[$i++] . "\n";

}

 

Something like that...might be a little off...if you dont want to do it by array index's then just replace the $i++ with the column name you want to view....

[per1os]

So if your wondering ( prolly not ) but here is a little code to represent what he is saying in the above post....

 

$query="SELECT * FROM table";

$i=0;

 

while($sql=mysql_fetch_array($query)) {

echo "Result: " . $sql[$i++] . "\n";

}

 

Something like that...might be a little off...if you dont want to do it by array index's then just replace the $i++ with the column name you want to view....

 

Yea just a little off... That will only print the first row from the table, if that is the desired result try this:

 

$query="SELECT * FROM table";
$i=0;

while($sql=mysqli_fetch_array($query)) {
echo "Result: ", print_r($sql) , "\n";
}

 

Which of courses will print out the full array. I think he wants the function posted above that will return a mysql query in a multi-dimensional array.

[Barand]

Check out mysql GROUP_CONCAT function.

[neel_basu]

I hope this will help you a lot

http://zigmoyd.sourceforge.net/man/db.php#browse

[yzerman]

guts118, this article actually explains the use of multidimensional arrays, and is the one which explained the uses for this type of array to me. It was a great help, I suggest you read over it:

 

http://www.samspublishing.com/articles/article.asp?p=31840&seqNum=4&rl=1

[guts118]

Hi,

  Thanks for all your replies. The reason I am receiving more than 1 row for each film is due to the tables that I am using. I have a many to many relationship between the film table and actor table (I have had to create an intersection table to allow for the many to many), I understand that it would be easier to have an actor cell and separate each name with a comma. However, I cannot do this though as this project is for college and I need to follow the rules of normalisation when creating the database to appease my tutor.

 

Anyway, thanks everyone, especially frost110, your function did the business.

 

Thanks

Guts

[per1os]

3rd Normal Form is the best way to go. That is the only way I will create databases. It can be a pain in the ass, but in the long run it makes life soo much easier. Especially since for a user table the username is only ever in one place, you reference everything else by the userid. It makes life a ton easier that way =)

 

At least teachers teach database design somewhat right.