Counting row results

PHP
1

I cant seem to get a count of rows in a query. I have tried numerous and various methods, but cant get a result.

This is one of the sets of experimentaions. The query works, it produces a result of the items checked, however getting a count of rows is eluding me.

The LIMIT also does not work.


mysql_select_db($database_assess, $assess_remote);
$fid="SELECT name, count(*) FROM counties WHERE name = 'checked' LIMIT 5";
$result1=mysql_query($fid, $assess_remote);
$row = mysql_fetch_array($result1);
$row_count = mysql_num_rows($result1);
$fetch=mysql_fetch_row($result1);
echo 'id'.$row['firm_id'].'<br />';
echo 'row count'.$row_count.'<br />';
$count=count($result1);
echo 'count'.$count;
echo 'fetch'.$fetch;

In the above I get this result:

id
row count1
count1fetchnum_results

I have the original query in the same page as this:



mysql_select_db($database_assess, $assess_remote);
$query_county_result = "SELECT * FROM counties WHERE name ='checked' LIMIT 5";
$result=mysql_query($query_county_result, $assess_remote);
$row = mysql_fetch_array($result);
$row_count = mysql_num_rows($result);

I also have this in fishing for something to work:


////////////////////////////this is working\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
if ( !empty($_POST['county']))
  foreach ( $_POST['county'] AS $id => $name )
    echo 'You have selected '. " {$name}".'<br />';
///////////////////above working, generates list of values(text)\\\\\\\\\\\\\\\\\\\\\\\\	


echo 'count_result()'.count($result1).'<br />';
echo 'count_row()'.count($row).'<br />';
echo 'count_row_count()'.count($row_count).'<br />';
echo 'id()'.count($id).'<br />';
echo 'count_fid()'.count($fid).'<br />'.'<br />'.'<br />';
echo 'fetch'.$fetch.'<br />'.'<br />'.'<br />';
echo 'this is where the for starts';

for($i=0; $i<$_POST['counties']; $i++){

echo 'result1()'.count($id).'<br />';
echo 'row()'.count($row).'<br />';
echo 'row_count()'.count($row_count).'<br />';
echo 'fid'.count($fid).'<br />';
echo 'fetch'.$fetch;

echo 'checked'.$name.'<br />'.'<br />';
}

These are the results: (atually the block starting with result1()1
I get 15 times)

You have selected Aroostook County
You have selected Cumberland County
You have selected Franklin County
count_result()1
count_row()2
count_row_count()1
id()1
count_fid()1

fetch

this is where the for startsresult1()1
row()2
row_count()1
fid1
fetchcheckedFranklin County

result1()1
row()2
row_count()1
fid1
fetchcheckedFranklin County

result1()1
row()2
row_count()1
fid1
fetchcheckedFranklin County

This is coming from a dynamically created checklist. The code for it is


 <?php $st=$_SESSION['name'];
//echo 'There are '."$totalRows_Recordset1" . ' Counties in' . " $choice_name";

if ( isset($_POST['submit']) ) { // if form is submitted, process it


print "<form action=\\"counties-result.php\\" method=\\"POST\\">\
";



if ($Recordset1) {
print "<table width=200 border=0>\
";

print "<th>&nbsp; </th>\
";


print "</tr>\
";
//create table
$i = 0;
while ( $row = mysql_fetch_array($Recordset1) ) {
$i++;
print "<tr>\
";

print "<td><input type=\\"checkbox\\" name=\\"county[$i]\\" value=\\"$row[name]\\">";



//echo "<td>{$row['state_id']}</td>\
";

echo "<td>{$row['name']}</td>\
";

echo "</tr>\
";

}//end while
print "</table>\
";
} else {
echo("<P>Error performing query: " .
mysql_error() . "</P>");

}
print "<input type=\\"hidden\\" name=\\"counties\\" value=\\"$i\\"/>\
";
print "<input type=\\"submit\\" name=\\"submit\\" value=\\"Go\\"/>\
";
}?>

<?php $insquery = "CREATE TABLE 'countchoice'"?>
<?php $checked= $_SESSION['checked']; ?>

I have started a new query using the above query, the only thing that is changed is the WHERE is pointing to a bit a data in the DB and not a qeary result.

Anyone be able to help me on this?

Thank you

Gary

2 

$row = mysql_fetch_array($result1); 
$row_count = mysql_num_rows($result1);

swap these line around, find your row count before you start getting data from the array.

3

Thank you for your reply, I think we are getting somewhere.

When I swapped the 2 lines, I got a result of 1, when it should have been 5. I have altered the code in the two queries, and I am now getting 1@5times and 0@5times.

Code


mysql_select_db($database_assess, $assess_remote);
$fid="SELECT name, count(*) FROM counties WHERE name = 'value=$i' LIMIT 5";
$result1=mysql_query($fid, $assess_remote);
$row_count1 = mysql_num_rows($result1);
$row = mysql_fetch_array($result1);

$fetch=mysql_fetch_row($result1);

 foreach ( $_POST['county'] AS $id => $name )
echo $row_count1.'<br />';

This results in
1
1
1
1
1

And this


mysql_select_db($database_assess, $assess_remote);
$query_county_result = "SELECT * FROM counties WHERE name ='checked' LIMIT 5";
$result=mysql_query($query_county_result, $assess_remote);
$row_count = mysql_num_rows($result);
$row = mysql_fetch_array($result);
 foreach ( $_POST['county'] AS $id => $name )
echo $row_count;

Gets me
00000

So we are getting the 5 results, but not as a number.

Any thoughts?

Again, thank you for all of your help.

Gary

4

Sorry I didnt look at your query before, you cant have count() without GROUPing the results, the count function returns a number of each records in each group of the GROUP BY statement. So forget your top script.

For the bottom one you are not counting though the results because the database array has 5 entries, you are counting though because $_POST[‘country’] has five entries.

add this line after the query

if (!$result){echo mysql_error();}

I suspect that your query is failing

5

Again thank you.

The query is working. I put your script on and recieved no error messages.

The first script, with the count() is returning a 1
(x the correct number):

1
1
1
1
1
while the other was returning a 0.
00000

I am trying to simply add them and / by 1 to get my number, but that seems to want to fight me too.

Can you think of why the mysql_num_rows is not working?

Gary

6

Well your queries are different for each script for a start, and you still cant use count() without a GROUP BY, just because you get a result that seems to be working doesnt mean it is.

How many results do you get if you put the command below directly into the command line or mysqlphpadmin

SELECT * FROM counties WHERE name =‘checked’ LIMIT 5;

7

I get the number of checkboxes I have checked. It ignores the LIMIT.

So in the latest experiment I have on screen, I have 5 check boxes checked, and text listing all five of my choices.

The query works if I change the WHERE clause from ‘checked’ to ‘$checked’ to ‘$i’.

$checked is a session variable
$i is the value from the checkbox form.

Gary

8

There are no checkboxes in the database, I want you to enter the query directly into the mysql database command line, not through your PHP script. If you are on a hosted server you will use myphpadmin then select to run an SQL statement, copy and paste the query into the box and hit RUN. Then post what you get back.

And Ill repeat again, your screen output when running the script has nothing to do with the database query


foreach ( $_POST['county'] AS $id => $name ) 
   echo $row_count;

Will output as many values of $row_count as there are variables in the array $_POST[‘county’]. Thats not an indication that the query is working correctly. Do you understand ?

Another thing to try, replace your query for

SELECT * FROM counties

then run and see what you get

9

Ok, I think I am starting to understand. So my queries were irrelevant, I was getting the data printed to the screen by the foreach loop. That would explain why the num_rows did not work.

My query as written will not work in phpmyadmin because the WHERE clause it will not understand. (WHERE name = ‘checked’).

I altered the sql in the phpmyadmin page to this


SELECT * FROM counties WHERE `state_id` = '39' AND name ='chester county'

Which worked fine.

So where does that leave me as far as counting the number of checkboxes checked.

Do you suggest that I INSERT the checked boxes into the DB, then call the results in a queary? or is there a way to get a total count from the foreach.

I suppose, (and I’m asking your opinion) that since I will need to write a script that works as an inventory control (only 3 per county), it would make more sense to re-think and INSERT then SELECT?

Thank you for pointing out my confusion…I was chasing the wrong ghost.

Gary

10

Voila :wink: thats why my_sql_num wasnt returning a valid number, it wasnt an error in the query per-se but the query returned no rows, hence you were printing 0.

So where does that leave me as far as counting the number of checkboxes checked.

You dont need a database just to find out how many select boxes were clicked you can do that by counting how many array items are in the $_POST variable


$checkboxes = count($_POST['county']);
11

Mandes

Thank you for showing me the error of my ways. Your solution worked perfect.

Gary