guts118 Posted April 11, 2007 Share Posted April 11, 2007 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 Quote Link to comment Share on other sites More sharing options...
mpharo Posted April 11, 2007 Share Posted April 11, 2007 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? Quote Link to comment Share on other sites More sharing options...
boo_lolly Posted April 11, 2007 Share Posted April 11, 2007 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. Quote Link to comment Share on other sites More sharing options...
mpharo Posted April 11, 2007 Share Posted April 11, 2007 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.... Quote Link to comment Share on other sites More sharing options...
per1os Posted April 11, 2007 Share Posted April 11, 2007 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. Quote Link to comment Share on other sites More sharing options...
mjlogan Posted April 11, 2007 Share Posted April 11, 2007 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++; } } Quote Link to comment Share on other sites More sharing options...
per1os Posted April 11, 2007 Share Posted April 11, 2007 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. Quote Link to comment Share on other sites More sharing options...
boo_lolly Posted April 11, 2007 Share Posted April 11, 2007 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. Quote Link to comment Share on other sites More sharing options...
per1os Posted April 11, 2007 Share Posted April 11, 2007 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 =) Quote Link to comment Share on other sites More sharing options...
mpharo Posted April 11, 2007 Share Posted April 11, 2007 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.... Quote Link to comment Share on other sites More sharing options...
per1os Posted April 11, 2007 Share Posted April 11, 2007 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted April 11, 2007 Share Posted April 11, 2007 Check out mysql GROUP_CONCAT function. Quote Link to comment Share on other sites More sharing options...
neel_basu Posted April 11, 2007 Share Posted April 11, 2007 I hope this will help you a lot http://zigmoyd.sourceforge.net/man/db.php#browse Quote Link to comment Share on other sites More sharing options...
yzerman Posted April 11, 2007 Share Posted April 11, 2007 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 Quote Link to comment Share on other sites More sharing options...
guts118 Posted April 12, 2007 Author Share Posted April 12, 2007 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 Quote Link to comment Share on other sites More sharing options...
per1os Posted April 12, 2007 Share Posted April 12, 2007 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.