5 Replies - 11821 Views - Last Post: 11 March 2009 - 06:48 AM Rate Topic: -----

#1 Mystic-Wolf  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 10-January 09

Returning Multiple Rows from a MySQL Database

Posted 26 February 2009 - 07:31 PM

Heya Everyone, I've been reading through the turorials here now for a while as well as searching the forums whenever a problem has come up. I have had one problem for a while now though and no amount of tutorials, books or posts I have read have been able to clear this one up for me.

Basically I have a database that so far has information for users and videos submitted by the users. I want to give users the ability to view all of the videos they have submitted which are stored in the database table vids. There are likely to be multiple results for this which is why I need to know how to handle multiple rows. So far what I have read has gone over my head and though I can return the first row (thanks to this Dream In Code post). What I really want to be able to do is display the rows neatly in the page so would be nice if they could be made into variables that I can then call later in my HTML. The relevant code I have so far is as follows:

	$user_id = $_SESSION['id'];
	
	$query = "SELECT * FROM vids WHERE user_id = $user_id";
	$result = mysql_query($query) or die(mysql_error());
	$row = mysql_fetch_assoc($result)


Then just for the sake of testing this I have later in the script (where the HTML is):

<?php print_r($row); ?>


If anyone can help it would be greatly appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: Returning Multiple Rows from a MySQL Database

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6052
  • View blog
  • Posts: 23,487
  • Joined: 23-August 08

Re: Returning Multiple Rows from a MySQL Database

Posted 26 February 2009 - 07:46 PM

Have you read the PHP manual page on mysql_fetch_assoc?
Was This Post Helpful? 1
  • +
  • -

#3 lemonacode  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 23
  • Joined: 02-February 09

Re: Returning Multiple Rows from a MySQL Database

Posted 26 February 2009 - 07:52 PM

Hello, to display multiple rows in a database try tizag
edit-------------
got beaten to it :P
but yeah a while loop should do the trick

This post has been edited by lemonacode: 26 February 2009 - 07:56 PM

Was This Post Helpful? 1
  • +
  • -

#4 Mystic-Wolf  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 10-January 09

Re: Returning Multiple Rows from a MySQL Database

Posted 27 February 2009 - 05:09 PM

Thanks for the help JackofAllTrades and lemonacode. I had looked up the entry for mysql_fetch_assoc a long time ago before I took on this project though had long forgotten most of what it said. I should of took a look at that entry again but didn't think to do so, thanks for that.

That tizag page was quite wonderful and answered some of my questions in a very simple to understand way. I managed to echo all the rows after reading that and understand how to format them all for proper output. Only thing that concerns me is all of the formatting and outputting being done within the loop. Is that very common? The most basics of what I have read with regards to php stress that it is advisable and good coding practice to keep the php seperate from the html but I do not understand how that could be possible with regards to echoing multiple rows from the database. If it is, is there some technique that I am just missing here?

Thanks again guys.
Was This Post Helpful? 0
  • +
  • -

#5 lemonacode  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 23
  • Joined: 02-February 09

Re: Returning Multiple Rows from a MySQL Database

Posted 27 February 2009 - 07:50 PM

View PostMystic-Wolf, on 28 Feb, 2009 - 09:09 AM, said:

Thanks for the help JackofAllTrades and lemonacode. I had looked up the entry for mysql_fetch_assoc a long time ago before I took on this project though had long forgotten most of what it said. I should of took a look at that entry again but didn't think to do so, thanks for that.

That tizag page was quite wonderful and answered some of my questions in a very simple to understand way. I managed to echo all the rows after reading that and understand how to format them all for proper output. Only thing that concerns me is all of the formatting and outputting being done within the loop. Is that very common? The most basics of what I have read with regards to php stress that it is advisable and good coding practice to keep the php seperate from the html but I do not understand how that could be possible with regards to echoing multiple rows from the database. If it is, is there some technique that I am just missing here?

Thanks again guys.

Pleasure to help, in respect to JackOfAllTrades i didnt realise he posted before me, sorry about that Jack.
With formatting in a while loop there are alot of techniques to format your data, so that you can achieve a table or just a layout of data. Have a look at for loops or foreach loops and these two functions mysql_affected_rows() mysql_result() hope that helps you form a technique. :)
Was This Post Helpful? 0
  • +
  • -

#6 Mystic-Wolf  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 10-January 09

Re: Returning Multiple Rows from a MySQL Database

Posted 11 March 2009 - 06:48 AM

Sorry for my delayed response, time has been short as of late and I haven't had much time for this project. That is brilliant though, I have it displaying how I want even if I am not really comfortable with all that HTML in the loop. I have run into another problem though that I should of forseen but was too focused on the previous one. I should imagine that this problem is easier to solve then the last one but just can't see how it would work. On top of displaying the information for the vids in this application I need to add up certain values and produce an end profit to the users for their videos. I have managed to do this alright except I do not know how to get a total of the profit that I have being returned for each row.

Here is my code:

			<table cellpadding='10'>
				<tr>
					<td><b>Video Name</b></td>
					<td><b>Video Length</b></td>
					<td><b>Video Sales Rate</b></td>
					<td><b>Commission</b></td>
					<td><b>Sales</b></td>
					<td><b>Profit</b></td>
				</tr>
				<?php			
					while($row = mysql_fetch_array($result)){
					echo "<tr>
							<td>".$row['name']."</td>
							<td>".$row['length']."</td>
							<td>"."$".$row['rate']."</td>
							<td>".$row['percentage']."</td>
							<td>".$row['sales']."</td>";
							$profit = (($row['rate'] * $row['percentage']) * $row['sales']);
							echo "<td>"."$".$profit."</td>
						</tr>";
					}
				?>
		</table>



So I have the php multiplying the rate by the percentage by the sales to get the variable $profit then am outputting $profit. What I want to do from here is add each $profit in all the rows to get a total. I've done some Google searches for finding sums in this manner but couldn't find an easier way to do it then making individual MySQL queries to get the sums in the columns then doing the multiplication again to get the final result. There must be an easier way especially since I am already returning all the values that I need to add together via that loop, I just don't understand how to add them together.

Thanks again for the advice, I am glad I took this project on as I have learned a great deal already.

Cheers,

MW
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1