When someone decides to list all members on my site or do a search with a lot of results and there was 1000's maybe 100's of 1000's

would the results be better by doing 1 mysql query, holding them in an array then showing them to the member

or doing several mysql_query ?

The reason i am asking is because i am not sure if there is a limit to the size an array can hold, maybe the max size a php file can hold set by the host by memory limit ?

this is a simple bit i did to show the x amount of members

PHP Code:

$counter = 1;
echo "<br><b><FONT size='4' color='$profdata[9]' face='arial'>Latest Members ( under construction )</font>";
echo "<table border='1' width='50%'>";
echo "<tr><td>picture</td>";
echo "<td>name</td>";
echo "<td>status</td>";
echo "<td>location</td></tr>";
while ( $counter <= 6 ) {
    echo "<tr><td>";
$result = mysql_query("SELECT * FROM Profiles WHERE ID = '$counter'");
$row = mysql_fetch_array( $result );
echo "<center><img src='"; echo $row['profilepic']; echo "' width='100' border='4' alt='profile picture'></center>";
    echo "</td><td>";
echo $row['login'];
    echo "</td><td>";
echo $row['asex'];
    echo "</td><td>";
echo $row['country'];
    echo "</td></tr>";
$counter = $counter + 1;
}
echo "</table> 


but what i need to know is

if i am doing a query on the database
and i want to scan a whole auto_increment table
i know i should start looking at ID 1 but how do i get the information what the "Next Autoindex" number is?

i can see it in php admin as number 5 but dont know how my php file should find it out so i know the number to search up to.

i will also need it when i show.. say the last 10 members to join

Thanks for your help!

Shaz x

is this it to find where the auto number counter is

SELECT COUNT(ID) ?


thanks

Sharon xx

ohhh i found a way hehe
i can just COUNT say the names and if there is say 100 then i know the auto number will be at 101 hehe

and i will do the searches in arrays but limit the number of results hehe

thanks

Shaz x

Originally Posted by Sharon_leic

ohhh i found a way hehe
i can just COUNT say the names and if there is say 100 then i know the auto number will be at 101 hehe

and i will do the searches in arrays but limit the number of results hehe

thanks

Shaz x

Yeah, its best not to try to load an array with thousands of items. It's technically possible, and in some cases the right solution, but whenever possible you should try to avoid huge flat datastructures. Your sql database has been optimized to handle lots of entries being accessed lots of times so its best not to reinvent the wheel.

Yeah, but in the same time, thousands of DB access will significantly slower your script, as each request goes

open db connection -> send query -> wait for reply -> open cursor for result -> close connection

It's all to be balanced. You are bound to the memory_limit setting of PHP, and it's default with PHP4 was 8Mo, it's now set to 128Mo by default in PHP5.2 and more recent.

Even an array of 800 entries will be checked more quickly than 800 requests, so I'd go in that way personally.
In particular, think of "indexed" arrays.
You don't have to use a numeric increment of 1 for each "cases", you can use any number / string.

For example, if a query search for every users that have a specific attribute with the same value, rather than having a multi-level array, you can use the user id as the index

PHP Code:

/*multi dimensional array*/
$ary[0]['user']='user1';
$ary[0]['id']=10;
$ary[1]['user']='user2';
$ary[1]['id']=14;
$ary[2]['user']='user3';
$ary[2]['id']=273;

/*indexed array*/
$r=mysql_query("select uid, username from users where whatever='$something'");
while($o=mysql_fetch_object($r)){
  $ary[$o->uid]=$o->username;
}
/*
And this will hold
$ary[10]='user1';
$ary[14]='user2';
$ary[273]='user3';
*/

//And to parse the array, use a foreach construct
foreach($ary as $key=>$user){
  echo "the user $key is $user<br/>";
}
/*
will output:
----------
the user 10 is user1
the user 14 is user2
the user 273 is user3
*/ 


To get the next auto_increment value, try http://dev.mysql.com/doc/refman/5.1/...le-status.html

For pagination, see my post at http://www.webmaster-talk.com/php-fo...l-results.html

I don't recommend loading all the results and showing the to the user for a couple of reasons:

1) The script may timeout.
2) The user's browser may timeout.
3) The user's browser may crash from too much data.

Hello!

thankyou all for you replies!
The pagenation thingy is something i was going to but when i do the search pages.

but for now i still can't work out how to find out how the current auto auto_increment value :-(

I looked at http://dev.mysql.com/doc/refman/5.1/...le-status.html

and even tried some of the code the people have posted but still got errors such as

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/html/site/count.php on line 8 ( and thats testing identical code that they had wrote )

all i need is to get the auto_increment value in a variable so i can use

echo $amount

and it shows what the auto_increment value is

i have tried with count (*) and a couple of other ways that are about the net but either get an error or it says something like resorce#3

thanks for any help

shaz x

After your mysql_query command, try

PHP Code:

echo mysql_error(); 


to get more information about what's going wrong.

tried that

this is the error i am getting

Access denied for user 'main_site_data'@'%' to database 'Profiles'
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/html/site/count.php on line 9


this is the code ( that i pinched of that page you posted )

PHP Code:

<?

include( "inc/db.php" );



$result = mysql_query("SHOW TABLE STATUS FROM Profiles;");
echo mysql_error();
while($array = mysql_fetch_array($result)) {
$total = $array[Data_length]+$array[Index_length];
echo '
Table: '.$array[Name].'<br />
Data Size: '.$array[Data_length].'<br />
Index Size: '.$array[Index_length].'<br />
Total Size: '.$total.'<br />
Total Rows: '.$array[Rows].'<br />
Average Size Per Row: '.$array[Avg_row_length].'<br /><br />
';
}

?>

although.. i won't need all this so once i got it working will chop it down

all i actually need is

it to return to me the amount of usernames in the database and then i will know what the current auto_increment value is

Profiles > username_field

but i am stuffed if i can work this out lol

i feel i am actually learning now and have got quite far with my experiment but this one little thing is really holding me back

Thanks

Shaz x

if someone can just tell me how

i can look in my database at

Profiles > username_field

and put the total number of usernames in a variable then that would be cool

i have 4 members so i want it to tell me there is 4.. and not the 4 usernames or something lol

thanks

Shaz x

this is the sort of thing i want

http://www.tizag.com/mysqlTutorial/mysqlcount.php

but instead i don't want the group

i just want it to echo the amount of usernames but can't work out how to change it without errors

http://dev.mysql.com/doc/refman/5.0/...ting-rows.html

SELECT COUNT(*) FROM `Profiles`; should do it

oops.

ok that again tells me

Resource id #3

instead of amount of users and this is what i can't work out..

with this code

<?
include( "inc/db.php" );
$query = "SELECT COUNT(*) FROM Profiles";
$result = mysql_query($query) or die(mysql_error());
echo $result;
?>

so can anyone tell me how to do it plz

thanks

shaz x

Your mysql error indicates that you have the wrong username password or that the user isn't give access to that database. Check your connection details.

Access denied for user 'main_site_data'@'%' to database 'Profiles'

Originally Posted by JeremyMiller

Your mysql error indicates that you have the wrong username password or that the user isn't give access to that database. Check your connection details.

I am guessing there is something there my host don't like and denying the connection as i have used "include( "inc/db.php" );" throughout my site and everything else is fine...

but if you can tell me the below i would be very much appreciative

thanks

shaz x

Originally Posted by Sharon_leic

ok that again tells me

Resource id #3

instead of amount of users and this is what i can't work out..

with this code

<?
include( "inc/db.php" );
$query = "SELECT COUNT(*) FROM Profiles";
$result = mysql_query($query) or die(mysql_error());
echo $result;
?>

so can anyone tell me how to do it plz

thanks

shaz x

I'm stuck if it's telling you there's a user access error and you believe there's not.

A couple of notes:

PHP Code:

$result = mysql_query("SHOW TABLE STATUS FROM Profiles;"); 


shouldn't have the ; in the string:

PHP Code:

$result = mysql_query("SHOW TABLE STATUS FROM Profiles"); 


echoing the result of a query is a resource. You have to fetch the results in order to get the value. So,

PHP Code:

<?php
include( "inc/db.php" );
$query = "SELECT COUNT(*) as row_count FROM Profiles"; 
$result = mysql_query($query) or die(mysql_error());
$total_rows_by_counting_instead_of_reading_table = mysql_fetch_object($result);
echo "Total Rows: ".$total_rows_by_counting_instead_of_reading_table->row_count;
?>

One side note. You keep wanting to count rows, but the total number of rows does not equal the next auto_increment -- what if a row is ever deleted. If you want the auto_increment, then read the table as I have provided.

Oh, something else just came to me, maybe your user isn't able to access tables. I always setup my users with full access to the database, so I never think about cases where that's not true. Not sure which type of access you need for SHOW TABLE STATUS, however, but you may want to look into that.

Ohhh JeremyMiller thankyou so much for that!

that row counting thing works grate and now i can carry on with what i was doing now i know how lol.. i put about 15 hours in trying to get it to count but couldn't work it out, have you ever been at the stage where you can't work it out and keep going to make coffee over and over again and getting more and more annoyed! lol

the other thing about the error, no idea why, i tried it without the ; but still the same error, not a problem though as your method works grate

thanks again, here's a virtual hug <//\\>

Shaz x

Originally Posted by Sharon_leic

have you ever been at the stage where you can't work it out and keep going to make coffee over and over again and getting more and more annoyed! lol

lol. I'm a programmer. There's no way of saying that and simultaneously be able to deny being stuck on a problem for an inordinate amount of time. My favorite is when it's a non-obvious missing semicolon or a typo in a variable -- elementary and yet take so much time.

Anyway, glad you got it. If you're counting rows, be sure that you never delete them or else you'll be off by the quantity deleted.