grabbing data from mysql and printing efficency

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 788 Views - Last Post: 01 September 2011 - 01:51 PM Rate Topic: -----

#1 polska03  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 302
  • Joined: 28-November 09

grabbing data from mysql and printing efficency

Posted 01 September 2011 - 12:25 PM

Hi, so I am grabbing grabbing about 1000 rows, each row containing 3 columns and then putting that information from the databse into a nice list to be presented. The problem is it takes about 2 minutes for everything to load. I am running the xampp on a machine that is a quad-core i5 and 4gb ram. This is the code that is running in a loop to retrieve the 1000 rows, is there anyway to make it considerbly faster??
<ul>
			<?php
			while ($row = mysql_fetch_assoc($q)) {
				?>	<li>
					<div>
						<div style="float:left;width:70px"><img src="images/user.pic" width="50"></div>
						<div style="float:left;width:70px;"><input type="checkbox"  name="user[]" value=<?php echo $row["user_id"];?>/><?php echo $row['fname']." ".$row['lname']; ?></div>
					</div>
				</li>
				<?php
				}
				?>


Is This A Good Question/Topic? 0
  • +

Replies To: grabbing data from mysql and printing efficency

#2 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1004
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:00 PM

Do you know if it's query execution or the actual looperation that takes time?

Grabbing 1000 rows is absolutely nothing, if you can show us more code (ie. your query) this would be great.
Was This Post Helpful? 0
  • +
  • -

#3 polska03  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 302
  • Joined: 28-November 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:19 PM

I have no idea unforunatly. When I loaded about 20 users, it loaded normally, but when I loaded a 1000, it pretty much freezes firefox and crashes. This is my full code.

<?php

$con=mysql_connect("localhost","root","");
mysql_select_db("website");

$q="SELECT user_id,fname,lname FROM user";
$q=mysql_query($q);

?>
	
	<div>
	    <ul>
			
<?php
			while ($row = mysql_fetch_assoc($q)) {
				?>	<li>
					<div>
						<div style="float:left;width:70px"><img src="images/profile.pic" width="10"></div>
						<div style="float:left;width:70px;"><input type="checkbox"  name="friend[]" value=<?php echo $row["user_id"];?>/><?php echo $row['fname']." ".$row['lname']; ?></div>
					</div>
				</li>
				<?php
				}
				?>
					
			</ul>
	
		</div>


Was This Post Helpful? 0
  • +
  • -

#4 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1004
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:21 PM

That shouldn't crash anything, unless you don't have any RAM (which you do, or perhaps it's full?).

It's probably just attempting to output too much data at once, and Firefox is generically crap anyway, have you tried another browser?
Was This Post Helpful? 0
  • +
  • -

#5 polska03  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 302
  • Joined: 28-November 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:23 PM

I shouldnt say crash because after it loads its fine. Yes I tried it with firfox, chrome and opera
Was This Post Helpful? 0
  • +
  • -

#6 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1004
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:28 PM

Well put it this way - The problem is not your code, it's your browser / computer.

Although I just tried outputting your snippet a thousand times and it works fine..

This post has been edited by RudiVisser: 01 September 2011 - 01:30 PM

Was This Post Helpful? 0
  • +
  • -

#7 polska03  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 302
  • Joined: 28-November 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:29 PM

so I just tried this and it seems to work. I put limit on the end the query statement to 1000 of the query and now it works(and yes there is only 1000 rows in the database)
Was This Post Helpful? 0
  • +
  • -

#8 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1004
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:31 PM

That's not possible!

Try a SELECT COUNT(*) FROM user.
Was This Post Helpful? 0
  • +
  • -

#9 polska03  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 302
  • Joined: 28-November 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:32 PM

outputed 30009.
Was This Post Helpful? 0
  • +
  • -

#10 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1004
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:33 PM

Then there's not 1000 rows in there, that would probably explain your problem :bananaman:

This post has been edited by RudiVisser: 01 September 2011 - 01:34 PM

Was This Post Helpful? 3
  • +
  • -

#11 polska03  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 302
  • Joined: 28-November 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:35 PM

in my user table each user has a user_id (PK) and it starts at 1 and goes all the way to 1000, so shouldnt there be only 1000 rows. I am now very confused.

This post has been edited by polska03: 01 September 2011 - 01:36 PM

Was This Post Helpful? 0
  • +
  • -

#12 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1004
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:37 PM

Primary key doesn't really mean too much as they get reused after you hit 18446744073709551615 anyway :D

But seriously, a SELECT MAX(user_id) FROM users returns 1000? That's very strange.
Was This Post Helpful? 1
  • +
  • -

#13 polska03  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 302
  • Joined: 28-November 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:43 PM

I was just counting how many rows in the table by the primary key since they are labeled 1-1000. no that statement returns 30054 for some reason.
Was This Post Helpful? 0
  • +
  • -

#14 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1004
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:46 PM

In that case the primary keys are not going from 1-1000 they're going from 1-30054.

How exactly are you looking at these IDs? If you're just looking at the first row and the last row then this could be wrong as rows arn't selected in order of the primary key.
Was This Post Helpful? 1
  • +
  • -

#15 polska03  Icon User is offline

  • D.I.C Regular

Reputation: 5
  • View blog
  • Posts: 302
  • Joined: 28-November 09

Re: grabbing data from mysql and printing efficency

Posted 01 September 2011 - 01:49 PM

AHHHHH I figured it out what a newbie I am. THERE is 30000 records in the table. I am using mysql workbench and noticed there is a 1000 row limit when you select something, so only 1000 rows will show up in the ide when you press SELECT * FROM user so I thought there was 1000 users. But when you use it with php all the rows show up. Thanks for your help though!!

This post has been edited by polska03: 01 September 2011 - 01:51 PM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2