[SOLVED] Problem using PHP for data

[spoco]

Background:

I have a MySQL database in which I have built in PHP a way to search and pull back data. This is for a school wishing to inventory their classroom computers.

 

I have five fields in which to search by:

School

Room

Operating Syatem

Projector Type

Whiteboard Type

 

If I search for any of the above fields and include School, it works fine. But if I want to search for any field in ALL schools, it pulls back nothing. In other words, I think I have written the code to require a school to be selected.

 

I want to be able to search, for instance, for Projector Type, and pull back all classrooms that have a certain type of projector, regardless of the other fields.

 

Here is the code in which I think the problem is. Thanks to the community in advance for your assistance.

 

BONUS PROBLEM: I want the search to encompass six different fields when searching for operating system. I think this is simply a syntax error. If I search for a school and an operating system that appears in the field 'operatingsystem' it works, but not any of the other fields.

 

<?php

$school=$_POST['school'];
$room=$_POST['room'];
$operatingsystem=$_POST['operatingsystem'];
$project=$_POST['projector'];
$whiteboard=$_POST['whiteboard'];

if($school=="ALL")
{
$where_school="";
$school_count="0";
}

else
{
$where_school=" WHERE `school` = '$school'";
$school_count="1";
}



if($room=="ALL")
{
$where_room="";
$room_count="0";
}

else
{
                   
			  if($school_count=="0") 
			  { 
			   
			   
			   $where_room=" WHERE `room` = '$room'";
			   }
			   
			   else
			   { 
			   
			   
			   $where_room=" AND `room` = '$room'";
			   }
			   
			   
$room_count="1";
}





if($project=="ALL")
{
$where_project="";
$project_count="0";
}

else
{
                   
			  if($school_count=="0" && $room_count=="0")
			  { 
			   
			   
			   $where_project=" WHERE `projector` = '$project'";
			   }
			   
			   else
			   { 
			   
			   
			   $where_project=" AND `projector` = '$project'";
			   }
			   
			   
$project_count="1";
}





if($whiteboard=="ALL")
{
$where_whiteboard="";
$whiteboard_count="0";
}

else
{
                   
			  if($school_count=="0" && $room_count=="0" && $project_count=="0")
			  { 
			   
			   
			   $where_whiteboard=" WHERE `whiteboard` = '$whiteboard'";
			   }
			   
			   else
			   { 
			   
			   
			   $where_whiteboard=" AND `whiteboard` = '$whiteboard'";
			   }
			   
			   
$whiteboard_count="1";
}













if($operatingsystem=="ALL")
{
$where_operatingsystem="";
$operatingsystem_count="0";
}

else
{
                   
			  if($school_count=="0" && $room_count=="0" && $project_count=="0" && $whiteboard_count=="0")
			  { 
			   
			   
			   $where_operatingsystem=" WHERE `operatingsystem`= '$operatingsystem' OR 'operatingsystem2'= '$operatingsystem' OR 'operatingsystem3'= '$operatingsystem' OR 'operatingsystem4'= '$operatingsystem' OR 'operatingsystem5'= '$operatingsystem' OR 'operatingsystem6' = '$operatingsystem'";
			   }
			   
			   else
			   { 
			   
			   
			   $where_operatingsystem=" AND `operatingsystem`= '$operatingsystem' OR 'operatingsystem2'= '$operatingsystem' OR 'operatingsystem3'= '$operatingsystem' OR 'operatingsystem4'= '$operatingsystem' OR 'operatingsystem5'= '$operatingsystem' OR 'operatingsystem6' = '$operatingsystem'";
			   }
			   
$operatingsystem_count="1";				   
}


?>

[w3evolutions]

Can you post the rest of the code, and the outcome of the entire SQL statement when you do ones of these searches? Please.

[Ken2k7]

Talk about SQL injection.

 

Do you even have a SELECT statement somewhere?

[w3evolutions]

Try making your code a little more structured, it helps to read. Also, mysql_real_escape_string() on $_POST vars.

 

foreach($_POST as $key=>$value) {
$_POST[$key] = mysql_real_escape_string($value);
}

$school			= $_POST['school'];
$room			= $_POST['room'];
$operatingsystem 	= $_POST['operatingsystem'];
$project 			= $_POST['projector'];
$whiteboard 		= $_POST['whiteboard'];

if($school == "ALL") {
$where_school = "";
$school_count = "0";
} else {
$where_school = " WHERE `school` = '$school'";
$school_count = "1";
}

if($room == "ALL") {
$where_room = "";
$room_count = "0";
} else {
if($school_count == "0")  { 
	$where_room = " WHERE `room` = '$room'";
} else { 
	$where_room = " AND `room` = '$room'";
}
    $room_count = "1";
}

if($project == "ALL") {
$where_project = "";
$project_count = "0";
} else {
if($school_count == "0" && $room_count == "0") { 
	$where_project = " WHERE `projector` = '$project'";
} else { 
	$where_project = " AND `projector` = '$project'";
}
$project_count = "1";
}

if($whiteboard == "ALL") {
$where_whiteboard = "";
$whiteboard_count = "0";
} else {
if($school_count == "0" && $room_count == "0" && $project_count == "0") { 
	$where_whiteboard = " WHERE `whiteboard` = '$whiteboard'";
} else { 
	$where_whiteboard = " AND `whiteboard` = '$whiteboard'";
}
$whiteboard_count = "1";
}

if($operatingsystem == "ALL") {
$where_operatingsystem = "";
$operatingsystem_count = "0";
} else {
if($school_count == "0" && $room_count == "0" && $project_count == "0" && $whiteboard_count == "0") { 
	$where_operatingsystem = " WHERE (`operatingsystem`= '$operatingsystem' OR 'operatingsystem2'= '$operatingsystem' OR" .
	" 'operatingsystem3'= '$operatingsystem' OR 'operatingsystem4'= '$operatingsystem' OR 'operatingsystem5'= '$operatingsystem'" .
	" OR 'operatingsystem6' = '$operatingsystem')";
} else { 
	$where_operatingsystem = " AND (`operatingsystem`= '$operatingsystem' OR 'operatingsystem2'= '$operatingsystem' OR" .
	" 'operatingsystem3'= '$operatingsystem' OR 'operatingsystem4'= '$operatingsystem' OR 'operatingsystem5'= '$operatingsystem'".
	" OR 'operatingsystem6' = '$operatingsystem')";
}
$operatingsystem_count = "1";               
}

[Ken2k7]

Also, mysql_real_escape_string() on $_POST vars.

 

mysql_real_escape_string on any user input vars whether they're GET or POST. That means, you should use that function when using $_GET, $_POST, $_REQUEST and if your PHP version is up-to-date, filter_input

[spoco]

Figured it out. I had inconsistent quotes. I've been looking at this for a couple of days.

[premiso]

mysql_real_escape_string on any user input vars whether they're GET or POST.

 

Not exactly. It should be used on any textual user input. If the input is suppose to be numeric, you should check for that and make sure it is the type that it should be.