How do I query all records => 250 and total?


#1

Trying total all records that are => 250


#2

i dont know exactly what you mean but i will give it a try :slight_smile:

to take all records from a table you have to use this code:
[php]
$query = mysql_query("SELECT * FROM your_table"); // you cand add a order or query condition

$query = mysql_query(“SELECT * FROM your_table WHERE table_id=‘variable_id’ ORDER BY table_id DESC LIMIT 250”); // this query selects all values from your table WHERE table_id equals your variable and ORDER all results by your table_id in DESCending ORDER from the biggest to the lowest value of table_id //
[/php]

the you can print the query out with a while() loop,
if you don`t know what a while() loop is check out php.net :slight_smile:


#3

Very easy to do, just use a WHILE loop. For example, assuming you are pulling all users’ first and last name:

First you have to pull the table_id value from the database using $_GET.

[php]

<?php if (!isset($_GET['table_id'])) { $variable_id = '1'; } else { $variable_id = preg_replace('#[^0-9]#i', '', $_GET['table_id']); // filter everything but numbers for security ( avoid SQL injections). } ?>

[/php]
Then assuming you have two table columns called fname and lname, query the db, as you have in your code as follow:

[php]

<?php $query = mysql_query("SELECT * FROM your_table where table_id='$variable_id' ORDER BY `table_id`") or die (mysql_error()); while ($row = mysql_fetch_array($query)){ $firstname = $row['fname']; //pull fname field in the DB $lastname = $row['lname']; //pull lname field in the DB echo $firstname.' '.$lastname.'
'; } ?>

[/php]

That’s it… all users First and last names will be printed as such:

  1. John Doe
  2. Jane Doe etc…

Get rid of DESC LIMIT 250 in your query as this will limit your output to to 50 rows, should your list exceed 250. The WHILE loop will stop once the the last row in the database has been matched and printed.

The
tag sends each new user into a new line.


#4

For example:
Record 1 has a column with a int of 250
Record 2 has a column with a int of 100
Record 3 has a column with a int of 75
Record 4 has a column with a int of 300
Record 5 has a column with a int of 275

I want to count how many records have => 250. Based on the example above, should return 3.


#5

well you could do what jj-dr said, and query each of the values that meet that requirement and then in the while loop, just use a counter instead of echoing the values in the fetched array. that way you could get your counter.

[php]$result = mysql_query(“SELECT * FROM your_table WHERE table_id='variable_id”);
$count = 0;
while($values = mysql_fetch_array($result))
{
$count++;
}
echo $count;[/php]

I’ve also read about COUNT() that could also help you? sorry I am fairly new to mysql and php but I would assume it would be something like:

[php]$result = mysql_query(“SELECT COUNT(‘table_id’) FROM your_table WHERE table_id='variable_id”);[/php]

I hope some of this helps you.


#6

sorry for the spam, but you could also use mysql_num_rows($result) after you query the specified amount of data and that should give you the number of rows from the query result.