Tutorial :Unknown column 'building_requirements_count' in 'field list'



Question:

I'm make a little game in php with mysql. Now I have a problem with one of the sql query's I created. The idea is that the query checks if the user has enough materials.

I have a query that if I use it like this it works:

SELECT  (    SELECT COUNT(*)    FROM building_requirements    WHERE building_id = '1'  ) as building_requirements_count,  (    SELECT COUNT(*)    FROM user_materials, building_requirements    WHERE user_materials.material_id = building_requirements.material_id      AND user_id = '27'      AND building_id = '1'      AND (user_material_amount >= building_material_amount) = 1  ) as user_materials_count;  

But when I add one column that use the result of those subquery's it fails:

SELECT  (    SELECT COUNT(*)    FROM building_requirements    WHERE building_id = '1'  ) as building_requirements_count,  (    SELECT COUNT(*)    FROM user_materials, building_requirements    WHERE user_materials.material_id = building_requirements.material_id      AND user_id = '27'      AND building_id = '1'      AND (user_material_amount >= building_material_amount) = 1  ) as user_materials_count,   building_requirements_count = user_materials_count as enough_materials;  

I get the error:

#1054 - Unknown column 'building_requirements_count' in 'field list'  

Can someone explain to me why I can't use the results of the subquery here? And how I can fix this?


Solution:1

Cause the is no field called "building_requirements_count" in your table definition. You are not allowed to use self-defined fields here except for the WHERE part.

Why don't you use your self-defined fields in the WHERE section of your query?

EDIT: It would be easier for you to get each value seperate out of the DB and do the calculating stuff in PHP.

$result = mysql_query("SELECT COUNT(*) FROM building_requirements WHERE building_id = '1'");  if ($result) {    $row = mysql_fetch_row($result);    $building_requirements_count  = $row[0];  }  else {    $building_requirements_count = 0;  }    $query = "  SELECT COUNT(*)    FROM user_materials, building_requirements    WHERE user_materials.material_id = building_requirements.material_id      AND user_id = '27'      AND building_id = '1'      AND (user_material_amount >= building_material_amount) = 1";    $result2 = mysql_query($query);  if ($result2) {      $row = mysql_fetch_row($result2);    $user_material_count  = $row[0];  }  else {    $user_material_count = 0;  }    $enough_materials = ( $user_material_count >= $building_requirements_count) ? true : false;  


Solution:2

Where you have

building_requirements_count = user_materials_count as enough_materials;  

I think you may mean...

building_requirements_count - user_materials_count as enough_materials;  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »