php COUNT help

Hi guys i would appreciate some help here

this is what i want to be able to do

i would like to split my table depending on results shown in row citizenship so that all grouped citizenship seperated into tables instead of showing them in 1 big table

here is my code


<?php $con=mysqli_connect("localhost","dbuser","password","dbname"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $result = mysqli_query($con,"SELECT * FROM `data` ORDER BY citizenship ASC"); $num_rows = mysqli_num_rows($result); global $vbulletin; echo " There are $num_rows BANNED Citizens "; while($row = mysqli_fetch_array($result)) { $color3 ="#FF9"; $color ="#036"; $fontsize ="2"; echo ""; echo ""; echo ""; echo ""; echo ""; } echo "
CitizenID Citizen Name Citizenship
".$row['citid']."" . $row['citname'] . "" . $row['citizenship'] . "
"; mysqli_close($con); [/php]

If you are trying to limit the results to say, 20 per row, what you are looking for is a modification of the following two rows:

[php]$result = mysqli_query($con,“SELECT * FROM data ORDER BY citizenship ASC”);
$num_rows = mysqli_num_rows($result);[/php]

The MySQL LIMIT keyword/operand is what you are after. It is the last element of a query and allows you to limit the number of results by skip-take parameters. The syntax is as follows:

LIMIT number
LIMIT from, number

The first version allows you to get number results from the set that your query matched, whereas the second skips from rows before doing so.

Effectively, you would add a GET parameter to allow the user to select a page, and then use a LIMIT like the following: LIMIT variable*20, 20 to get 20 results from a page, for example.

Caveat: sanitize the user input. For page counts, it is trivial: typecast to int before using the variable. This is done as such:

[php]$var = (int)$_GET[‘var’];[/php]

the problem with that is

i have 30 + citizenships each citizenship ranges from 20 - 100 records in fieldname citizenship
i want to archive seperating them into different tables not limiting them

Okay. In this case, your other tool for this is the WHERE clause, which allows you to filter your results. Same principle - the clause takes a conditional.

For example:
[php]SELECT * FROM data WHERE citizenship=“French” ORDER BY citizenship ASC[/php]

sorry not sure if you understand

so this example it shows 2 citizenshship but i dont want them showing on the same table
does anyone know how to split the result dependant on $row[‘citizenship’]

so that the same headers appear for each citizenship but as separate tables

Do you still want to show all nationalities on the same page, just as different tables? If so, as your query is sorted by citizenship already, all you need to do is something like this:

$dCitizenship = false;
while($row = mysqli_fetch_array($result))
if ($row[‘citizenship’] !== $dCitizenship) {
// Print headers here
$dCitizenship = $row[‘citizenship’];

This will output new headers every time the citizenship value of the new row is not the last visible citizenship. If you’d like multiple pages (one per citizenship), see my post about WHERE.

Why was this reported? Or was it already taken care of?

Dealt with. I screwed up on BBCode, which made one of my posts unreadable, and cannot edit my own posts, so I reported it for a mod to fix.

Sponsor our Newsletter | Privacy Policy | Terms of Service