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
EmoticonEmoticon