trying to count entries in a database

Question

I'm trying to count entries in a database based on 2 basic criteria. It is returning a blank result, even though there are results to be found. Anyone have any idea what I am doing wrong here? I have tried it so many different ways and they all return no result. (If I enter the query directly in phpmyadmin it returns a result.)

$sql = "SELECT count(*) as total_count from orderOption3Detail WHERE orderDate='$orderDate' AND studentID='$studentID'"; 
$numericalResult = mysql_query($sql, $con);
$row = mysql_fetch_object($numericalResult);
$totalOrders1 =  $row->total_count;
echo "My orders:" . $totalOrders1;

**warning** your code may be vulnerable to sql injection attacks! — Daniel A. White, May 15 '13 at 01:21
Enable proper error reporting, and see the syntax error caused by the missing `=` between `$totalOrders` & `$row->total_count`. — Wrikken, May 15 '13 at 01:22
please dont use mysql_* function see this http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php/14110189#14110189 — NullPoiиteя, May 15 '13 at 01:55

score 0 · Answer 1 · answered May 15 '13 at 01:56

As others stated, make sure you sanitize variables before they go into query.

$sql = "SELECT * FROM orderOption3Detail WHERE orderDate = '" . $orderDate . "' AND studentID = '" . $studentID . "'";

$sql_request_data = mysql_query($sql) or die(mysql_error());
$sql_request_data_count = mysql_num_rows($sql_request_data);

echo "Number of rows found: " . $sql_request_data_count;

That's all you need.

Eduardo Vieira · Answer 2 · 2013-05-15T20:01:06.637

Edited: providing full code corrected:

$con=mysqli_connect($db_host,$db_user,$db_pass,$db_name); // Check connection 
if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } //global option 1 
$sql = "SELECT count(*) as total_count from orderOption3Detail WHERE orderDate='$orderDate' AND studentID='$studentID'"; 
//echo $sql; 
$numericalResult = $con->query($sql); 
$row = mysqli_fetch_object($numericalResult); 
echo $row->total_count; //echo (int) $row->total_count;

Please test this and let me know. Good luck! ----- End Editing ----

Have you tested assigning values directly as a test in your SQL string, like:

$sql = "SELECT count(*) as total_count from orderOption3Detail WHERE orderDate='05/23/2012' AND studentID='17'";

Also, did you check if the date's format is correct, reading that $orderdate variable and testing it in PHPMyAdmin?

Did you read the $sql with values inserted and test in PHPMyAdmin and worked?

Also, check the connection to assure there is no problem there.

One more thing, sorry. You seem to be using the wrong syntax in your mysql_query statement. That way works for mysqli_query, and the parameters would be inverted. Try only:

$numericalResult = mysql_query($sql);

Provided you made the connection and database selection previously, like in:

$connection=mysql_connect($db_host, $db_username, $db_password);
if (!$connection)
          {
            $result=FALSE;
            die('Error connecting to database: ' . mysql_error());
          }

     // Selects database
     mysql_select_db($db_database, $connection);

Best wishes,

The database connection is good. It is checked further up. I threw the SQL query into phpmyadmin and it returns a proper result...just returns a zero here. In regards to the syntax error ($numericalResult = mysql_query($sql);) maybe that has something to do with my problem because when I change it to that, it breaks the code. It seems to want the connection info, $con, to be there... Here is the full cod — kayleegirl, May 15 '13 at 13:33
Full Code: $con=mysqli_connect($db_host,$db_user,$db_pass,$db_name); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } //global option 1 $sql = "SELECT count(*) as total_count from orderOption3Detail WHERE orderDate='$orderDate' AND studentID='$studentID'"; //echo $sql; $numericalResult = mysql_query($sql, $con); $row = mysql_fetch_object($numericalResult); echo $row->total_count; //echo (int) $row->total_count; — kayleegirl, May 15 '13 at 13:36
Ah, It is a simple mistake. You are making the connection using mysqli, so you must make all further statements using mysqli too, like mysqli_query, etc. I am mobile now, but will correct your full code later. Cheers! — Eduardo Vieira, May 15 '13 at 14:16
Answer edited to provide you with the full code. It is the block in the beginning. I hope this helps. If not let me know. Cheers. — Eduardo Vieira, May 16 '13 at 03:28

trying to count entries in a database

2 Answers2