Mysql Count query returning "Query was empty"

Question

I am using the following script to try and count the rows in a table, the problem I keep getting is the error :

Query was empty Quiz Name

and a blank page. Im new to COUNT, so I think I might be making a mess of it :-S . My database layout is as follows :

itsnb_chronoforms_data_createquestions cf_id ,cf_uid,cf_created ,cf_modified, cf_ipaddress,    cf_user_id, quizID, questionID, quizquestion, quizanswer1, quizanswer2, quizanswer3, quizanswer4,     questionformat ,correctanswer

The script I am working on is :

    // Define Quiz Variables
    $quiz = $row['quizID'];
    $quizcfid = $row['cf_id'];
    $quizname = $row['quizname'];
    // Finish Define Quiz Variables  

    ///////////////////////////////////////////////////////////////////////////////////////////////
    // Make a MySQL Connection

    $query8 = "SELECT COUNT(*) as 'numberofquestions' FROM employees WHERE quizID='$quiz'"; 

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

    // Print out result
    while($row8 = mysql_fetch_array($result8)){
        echo 'There are '. $row8['COUNT(quizID)'] . ' questions';
    } 
    ///////////////////////////////////////////////////////////////////////////////////////////////

eric.itzhak · Accepted Answer · 2012-11-28 14:48:38Z

3

Your problem is that you are running the wrong query, you are running $query and not $query8

change to this :

$result8 = mysql_query($query8) or die(mysql_error());

in a second glance, you should also change to :

echo 'There are '. $row8['numberofquestions'] . ' questions';

as you set numberofquestion to be the count alias.

answered Nov 28, 2012 at 14:48

eric.itzhak

16.2k28 gold badges96 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Iain Simpson Over a year ago

Thanks, that has gotten rid of the error, but I now get There are questions echo'd out, it doesn't appear to be echoing the number of questions.

Iain Simpson Over a year ago

Thanks that's sorted now, I thought I had copied and pasted that correctly but apparently not........Its been a long day ! :-)

eric.itzhak Over a year ago

You welcome. i've browsed through your questions and you should start accpeting answers on your questions.

HenchHacker · Accepted Answer · 2012-11-28 14:49:00Z

2

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

should be

$result8 = mysql_query($query8) or die(mysql_error());

since i'm guessing the variable $query is empty which you were passing it before.

As for COUNT() stay away from COUNT(*), instead use COUNT(field_name) and for even faster results ensure that the field_name is in the index being used.

Count simply counts the total rows returned for the specified field.

Also, be carefull of using COUNT(*) in innodb as it will force a TABLE SCAN if not used with a WHERE clause on an index.

answered Nov 28, 2012 at 14:49

HenchHacker

1,6261 gold badge10 silver badges16 bronze badges

Comments

Nelson Benítez León · Accepted Answer · 2012-11-28 14:49:39Z

2

Replace your following line:

echo 'There are '. $row8['COUNT(quizID)'] . ' questions';

for this one:

echo 'There are '. $row8['numberofquestions'] . ' questions';

As numberofquestions is the name of the only field from your query result set.

answered Nov 28, 2012 at 14:49

Nelson Benítez León

51.1k8 gold badges70 silver badges84 bronze badges

Comments

Johnson_145 · Accepted Answer · 2012-11-28 14:49:51Z

2

To get the number of rows in your result use mysql_num_rows:

$numberOfRows = mysql_num_rows($result8)

answered Nov 28, 2012 at 14:49

Johnson_145

2,0721 gold badge21 silver badges30 bronze badges

Comments

v0d1ch · Accepted Answer · 2012-11-28 14:51:20Z

1

You have misstyped this

$result8 = mysql_query($query8) or die(mysql_error());

and also

echo 'There are '. $row8['numberofquestions'] . ' questions';

answered Nov 28, 2012 at 14:51

v0d1ch

2,7481 gold badge24 silver badges27 bronze badges

Comments

chenio · Accepted Answer · 2012-11-28 15:00:48Z

1

The sentence sql i see rigth. But i use this

$result8 = mysql_query($query) or die(mysql_error());
$count = mysql_fetch_row($result8);


echo 'There are '.$count[0].' '. questions';

Other Option:

 while($row8 = mysql_fetch_array($result8,MYSQL_ASSOC)){
        echo 'There are '. $row8['numberofquestions'] . ' questions';
    }

answered Nov 28, 2012 at 15:00

chenio

6123 gold badges11 silver badges27 bronze badges

Collectives™ on Stack Overflow

Mysql Count query returning "Query was empty"

6 Answers 6

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related