help with cleaning up code

[squishypentagon]

Ok, i have this code that i want to print out the total number of times the defined username appears in my database with the defined date.  Right now all it prints out is the echo at the end but with no numbers in the result.  I just need help with getting it to display the number.

 

<?php
//select a database to work with
$selected = mysql_select_db("nwacompu_totals",$dbhandle)
  or die("Could not select examples");
  
$username = "bayyari";
$date = "3-16-2011";

$query = "SELECT COUNT(*) FROM totals WHERE date = '$date' AND username = '$username'"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(username)'] ." ". $row['date'] ." items.";
echo "<br />";
echo $result;
}


?>

 

what is printed is this

 

There are items.

Resource id #2Resource id #3

 

which makes no sense to me.  Any help is very much appreciated.

[KevinM1]

You can't use a MySQL function on a fetched result.  Also, $result itself isn't a row of data you can directly manipulate.  It's a resource which must be used by one of the fetch functions (like mysql_fetch_assoc) in order to actually retrieve data.

 

If you need to count results on the db, do it in your query.

[squishypentagon]

ok, well i messed around with the code some more and came up with this.  i thought it was working, but no matter how many results i manually put in the database it only says i have 1 result when i run the query:

 

<?php

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
echo "Connected to MySQL<br />";

//select a database to work with
$selected = mysql_select_db("nwacompu_totals",$dbhandle)
  or die("Could not select examples");
  
  
$search = mysql_query("SELECT COUNT(*) FROM totals WHERE timeid ='l1' AND date = '3-16-2011'");
$total_records = mysql_num_rows($search);
echo $total_records;



?>

[KevinM1]

You're confusing what the database's COUNT function is doing with what PHP's mysql_num_rows is doing.  mysql_num_rows gives the count of how many rows your query will return.  Your query will only return one row, which has one value - the number of rows in the 'totals' table that fit your criteria.

 

So, long story short, the result that's being echoed is correct.

[nicholasolsen]

 

Easy fix i think... Replace your $query with this:

$query = "SELECT * FROM totals WHERE date = '$date' AND username = '$username'"; 

 

You dont need the COUNT(*) when youre using num_rows..

 

Hope this help you out.

 

[squishypentagon]

 

Easy fix i think... Replace your $query with this:

$query = "SELECT * FROM totals WHERE date = '$date' AND username = '$username'"; 

 

You dont need the COUNT(*) when youre using num_rows..

 

Hope this help you out.

 

I'm not sure what you are telling me to replace. 

[nicholasolsen]

 

Easy fix i think... Replace your $query with this:

$query = "SELECT * FROM totals WHERE date = '$date' AND username = '$username'"; 

 

You dont need the COUNT(*) when youre using num_rows..

 

Hope this help you out.

 

I'm not sure what you are telling me to replace.

 

Replace this line:

$query = "SELECT COUNT(*) FROM totals WHERE date = '$date' AND username = '$username'"; 

 

With this:

$query = "SELECT * FROM totals WHERE date = '$date' AND username = '$username'";

 

The only thing different is that I left the COUNT outside the query.. This function doesnt need to be embeded into the query since youre using num_rows to count the results in the end.

[nicholasolsen]

 

Easy fix i think... Replace your $query with this:

$query = "SELECT * FROM totals WHERE date = '$date' AND username = '$username'"; 

 

You dont need the COUNT(*) when youre using num_rows..

 

Hope this help you out.

 

I'm not sure what you are telling me to replace.

 

Replace this line:

$query = "SELECT COUNT(*) FROM totals WHERE date = '$date' AND username = '$username'"; 

 

With this:

$query = "SELECT * FROM totals WHERE date = '$date' AND username = '$username'";

 

The only thing different is that I left the COUNT outside the query.. This function doesnt need to be embeded into the query since youre using num_rows to count the results in the end.

 

 

Sorry, my mistake. Didnt see you updated the codesnippet.

 

 

Replace this:

$search = mysql_query("SELECT COUNT(*) FROM totals WHERE timeid ='l1' AND date = '3-16-2011'");

 

With this:

$search = mysql_query("SELECT * FROM totals WHERE timeid ='l1' AND date = '3-16-2011'");

[DavidAM]

Using "SELECT *" and then mysql_num_rows() to get a count, is a bad idea. Understand that the database is going to send all the data from every row that matches the query to the PHP client. This takes up bandwidth, memory and time. It is MUCH more efficient to use COUNT(*) and then retrieve the value that was counted. For instance:

 

$sql = "SELECT COUNT(*) FROM myTable WHERE someCondition = 'whatever'";
$res = mysql_query($sql);
if ($res !== false) {
  $row = mysql_fetch_row($res);
  echo "There are " . $row[0] . "entries.";
}

Note: Since we are only expecting a single row to be returned, it is not necessary to use a while loop to process the results.

 

The code in your original post was not too far off from that. However, inside the loop you were referring to $row['COUNT(username)'] and $row['date']. These are not selected in your query, so they do not exist and are output as an empty string. If you are looking for the result for a single user and a single date, the original code would be something like this:

$username = "bayyari";
$date = "3-16-2011";

$query = "SELECT COUNT(*) FROM totals WHERE date = '$date' AND username = '$username'"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row[0] ." ". $date ." items.";
echo "<br />";
echo $result;
}

[squishypentagon]

Using "SELECT *" and then mysql_num_rows() to get a count, is a bad idea. Understand that the database is going to send all the data from every row that matches the query to the PHP client. This takes up bandwidth, memory and time. It is MUCH more efficient to use COUNT(*) and then retrieve the value that was counted. For instance:

 

$sql = "SELECT COUNT(*) FROM myTable WHERE someCondition = 'whatever'";
$res = mysql_query($sql);
if ($res !== false) {
  $row = mysql_fetch_row($res);
  echo "There are " . $row[0] . "entries.";
}

Note: Since we are only expecting a single row to be returned, it is not necessary to use a while loop to process the results.

 

The code in your original post was not too far off from that. However, inside the loop you were referring to $row['COUNT(username)'] and $row['date']. These are not selected in your query, so they do not exist and are output as an empty string. If you are looking for the result for a single user and a single date, the original code would be something like this:

$username = "bayyari";
$date = "3-16-2011";

$query = "SELECT COUNT(*) FROM totals WHERE date = '$date' AND username = '$username'"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row[0] ." ". $date ." items.";
echo "<br />";
echo $result;
}

 

that worked perfectly!  thank you!