Building MYSQL select statements using PHP

[spivey]

I have a search form that queries a four-table database: one parent table, three child tables. The form (for an incident report database) has many search options: date range, name of person involved, location, whether police where called, etc. No searches are required: users could use all search functions at once, or only one.

Data from all tables is to be gathered based on the primary key of the parent table.
Rather than have separate queries for each function run and then sort out the massive result sets, I thought I could use conditional logic in mysql's 'CASE/THEN/When' construct to sort out which search functions had been used.

On the mysql board, someone suggested that I let php handle the conditions. How, then, would I construct code that passes only the variables used in one session into a SELECT statement?

Thanks
spivey

[rulian]

You can search accross all your tables if they care keyed correctly by doing a select on your parent table and using JOINS to extract the matching rows from your children table.

I think what they are talking about in the PHP is if your query is going to be coded differently based on your user's input the you use your form variables and values to determine which sql to run or construct it dynamically
for example, lets say you are querying different tables depending on a drop down they select

$SQL="SELECT * FROM "; 
swich($_POST["tselect"])
{
case "one": /// never reveal your table names to users
$SQL.=" `mytable1` WHERE `id` = '".$FORMIDAFTERSECURITYCHECK."'";
break;
case "TWO": 
$SQL.=" `mytable2` WHERE `something` = '".$FORMIDAFTERSECURITYCHECK."'";
break;
}

OR 

$SQL="SELECT * FROM"; 
if ($_POST["blah"] == "boo") $SQL.="`thistable`" ;
else $SQL.="`thattable`"; 
$SQL .=" WHERE `this` = 'that'"; 


echo $SQL; 
//// OUTPUTS: "select * from `thistable` WHERE `this` = 'that'; 

[spivey]

Thanks for your help.

I am working on what you have given me, but I am not sure about the "break" statements. If I enter a query for Case One: a date range search, I want to be able to refine it with CASE Two, a search for "location". If I break after each case, it will run individual queries. But if I don't break, will I end up with gobs of results, which is the problem I was hoping to avoid anyway?

Thanks

Spivey

[rulian]

then you cannot use switch,
just use nested if statements, it's more manual work but it will give you the flexibility you need, just be sure to properly document your if's to not get them tangled

if ($_POST["one"] == "this")
{
	if ($_POST["two"] =="that") // if post['one'] = this and post[two] ='that'
		{
			/// code
		}
	else /// ends $_POST["two"] == "that";  

		{
			//code
		}
}
else //// ends $_POST["one"] == this 
{
	if ($_POST["two"] == "this"]) // if post['one'] !== "this" and post['two'] == 'this'
		{
			//code
		}
	else 
		{
			// code
		}
}

[spivey]

This is what I have so far, but something ain't quite right:

$query = "select incid from ";
    if ($input) {
        if ($startmonth && $endmonth && $startyear && $endyear) {
            $query .= " incident where month(date) between '{$startmonth}' AND '{$endmonth}' AND year(date) between '{$startyear}' AND '{$endyear}'";
        }  elseif ($startmonth && $startyear &! $endmonth &! $endyear) {
            $query .= "incident where month(date) = '{$startmonth}' and year(date) = '{$startyear}'";
            } elseif (!$startmonth &! $startyear ||! $endmonth ||! $endyear) {
                $startmonth = null;
                $startyear = null;
                $endmonth = null;
                $startmonth = null;
            } 
        if ($inctype) {
            $query .= "inctype from inctype where incid = '{$inctype}'";
        } else {
            $inctype = null;
        }
        if ($police) {
            $query .= "incident where police = 'Yes'";
        } else {
            $police = null;
        }
        if ($location) {
            $query .= " location where location = '{$location}'";
        } else {
            $location = null;
        }
        if ($treatment) {
            $query .= " incident where treatment = '{$treatment}' ";
        } else {
            $treatment = null;
        }
        if ($lastname) {
            $query .= " patrons where patrons.lastname = '{$lastname}'";
        } else {
            $lastname = null;
        }
        if ($author) {
            $query .= " incident where author = '{$author}' ";
        } else {
            $author = null;
        }
        if ($gender) {
            $query .= " incident where gender = '{$gender}' ";
        } else {
            $gender = null;
        }

}

When I run this with an incident type chosen, I get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inctype from inctype where incid = 'Array'incident where police = 'Yes' location' at line 1

This error message seems to combine three elements, which strikes me as weird.

Also, if I run it without selecting an incident type, the problem just moves to the next item.

Spivey

[rulian]

Hmm... ,
If you are simply trying to search across your table(s) you dont have to query each table seperately.

I'm not sure how your setting up or filtering those variables you are using but if ($variable) will execute if a variable is set and will produce an variable not found error on it not being set.
if you want to check for variable instances use if (isset($var)) or if you want to check if a variable is empty use if (empty($var)) and vice versa, variable not set, if (!isset($var))

also, if you query different tables with different schemas yoru return can be greatly varied which will make you displaying the results even more chaotic.

post yoru table schema, and idealy what you would like to see come out of them, lets check if your tables are up to the task of delivering what you want them to do.
post your form here as well if you can

also, you got that error because multiple conditions in your if statements were true and added to the sql values that should not have been together

[foyer]

I had a similar situation on a project I worked on recently. The code was ridiculously long and confusing. I'm not sure if you have the option to do this or not. But I just created a new table. With all the values that people would search. That way, it was 1 table and contained all the search items.

Each time a new item was added to the DBs I just ran a function that updated the search DB with the new item information. I know it's not to much help but it's another option you could try.

[nemonoman]

The problems you are finding are problems that want solving by intelligent programming. In the case of spivey, you need to look carefully at the queries being generated by your PHP code: First debug the SQL that's causing mysql to burp, then debug the PHP that is generating the SQL bug.

In the case of foyer: you are simpy saying it's easier to give up than debug.

Finding answers to challenging data problems is quite satisfying. Which is lucky -- people used to pay programmers the big bux, but now satisfaction in a job well done seems to be the reward.

[spivey]

My database is populated by an online form, filled out by staff in event of an incident. The functionality that I am trying to provide is the ability to conduct analysis of incidents through analysis. If staff want to know about all of the incidents in a certain location, of a certain type, involving a particular patron, etc, they can collect those reports easily.

And I need all of the data for any given report, so I cannot distill it down to a new table.

Here are my tables:

create table [B]incident [/B](
incid int not null auto_increment primary key,
date date not null,
time time not null,
lid int not null,
otherpatrons varchar(255),
incdec text(255) not null,
police char(1),
tpa time,
inctypid int not null,
treatment varchar(100),
othertreat varchar(100),
transport varchar(100),
othertrans varchar(100),
actions text,
nonstaffinv text,
comments text,
staffinv text,
author varchar(30) not null)
Type=innodb;

create table [B]location[/B] (
lid int not null auto_increment primary key,
location varchar(25),
incid int not null,
index loc_data(lid),
foreign key (incid) REFERENCES incident(incid) ON DELETE CASCADE)
type=innodb;

create table [B]inctype[/B] (
inctypid int not null auto_increment primary key,
inctype varchar(100),
incid int not null,
index inct_data(itid),
foreign key (incid) REFERENCES incident(incid) ON DELETE CASCADE)
type=innodb;

create table [B]patrons[/B] (
pid int not null auto_increment primary key,
incid int not null,
lastname varchar(30), 
firstname varchar(30),
street varchar(50),
city varchar(30),
state char(2),
zip int(5),
gender char(1),
age varchar(15),
height varchar(15),
weight varchar(15),
hair varchar(20),
eyes varchar(10),
race varchar(15),
other text,
foreign key (incid) references incident(incid) on delete cascade)
type=innodb;

So, my thinking is, since everything is based on the primary key of the incident table (incid), my first select statement needs to factor in the variables related to the various search functions available to the user (hence the conditional logic in building the query).

Once I have the incids for any search, I can use the foreach and separate queries for the different tables, to display the results.

Part of the problem that I have is, some of the queries are returning arrays: some incident reports can involve more than one incident type and more than one location.

nemonoman, I'm totally down for learning some intelligent programming and the joy of problem solving; that's why I'm here! 🙂 I have been working on debugging the SQL, and maybe that is my principal failure: I'm just not doing it right. I'll keep at it.

thanks all for the help.

spivey

[spivey]

I have figured out that my problem with my queries is, that while individual queries work just fine, it is assembling multiple queries into one select statement that is fouling things up. So, here goes. I have shortened my list of many variables just to these two for the example. "inctype" refers to "incident type". "Police" is for incidents where the cops were called.

$select = 'select distinct incid from ';
    $query = '';


  if (isset($inctype)) {
        $query .= " inctype where inctype = '{$inctype}' ";

    } else {
        $inctype = null;
    }
    print_r($inctype);




    if ($police) {
        $query .= " incident where police = 'Yes'";
    } else {
        $police = '';
    }

$select .= $query;

$result = mysql_query($select) or die(mysql_error() . '<br />'. $select);

echo "Records for your date query" . "<br />";
    while ($data = mysql_fetch_array($result)){
        print_r($data);
       echo " {$data['incid']} <br />";
    }

If I run the "if" statements for the two variables separately, I get my list of incids. I understand that the way I have set this up, I have created an invalid query.

How do I put multiple, and differing queries together in a way that also allows for variables to not be used?

[bobby44]

Hi Spivey,

I am trying the same thing I think as well. Trying to run a query on a table that has multiple possible conditions to it. I suspect that an inner join may be the answer to our problems and am looking into this. I'll let you know if I get a result. Would you be kind enough to do the same for me also on my post on this forum.

Checking out this post may help:
http://www.thescripts.com/forum/thread685716.html

Many thanks and good luck.

[spivey]

Hi, Bobby44.

I think my problem goes beyond the join issue:

I am having trouble building the queries. I need to have variables on which of three tables to choose from.

Here is my tanged mess of php code attempting (but sorely failing) to accomplish my goal:

<?php

//print_r($_POST['Submit']['incidenttype']);
$input = $_POST['Submit'];

if (isset($_POST['location'])) {
   $location = $_POST['location'];   

} else {
   $location = null;      

}

if (isset($_POST['incidenttype'])) {
   $inctype = $_POST['incidenttype'];   

} else {
   $inctype = null;      

}

if (isset($_POST['incidenttype']['other_incident'])) {
    $other_inc= $_POST['incidenttype']['otherinc'];
} else {
    $other_inc = null;
}


if (isset($_POST['treatment'])) {
	$treatment = $_POST['treatment'];
} else {
	$treatment = null;
}

if (isset($_POST['treatment']['other_treatment'])) {
    $othertreat = $_POST['treatment']['other_treatment'];
} else {
    $othertreat = null;
}
if (isset($_POST['treatment']['hospital'])) {
    $hospital = $_POST['treatment']['hospital'];
} else {
    $hospital = null;
    }
if(isset($_POST['police'])) {
    $police = $_POST['police'];
} else {
    $police = null;
}
/*
if(isset($_POST['date'])) {
    $date = $_POST['date'];
} else {
    $date = '';
}*/
if(isset($_POST['author'])) {
    $author = $_POST['author'];
} else {
    $author = null;
}

if (isset($_POST['startmonth'])) {
$startmonth = $_POST['startmonth'];
} else {$startmonth = null;
}

if (isset($_POST['startyear'])) {
    $startyear = $_POST['startyear'];
} else {
    $startyear = null;
}
if (isset($_POST['endmonth'])) {
    $endmonth = $_POST['endmonth'];
} else {
    $endmonth = null;
}

if (isset($_POST['endyear'])) {
    $endyear = $_POST['endyear'];
} 
else {
    $endyear = null;
}

if ($input) {
$gender = ($_POST['gender']);
$lastname = ($_POST['lastname']);
}
//Database connect
$hostname = "localhost";
$database = "db";
$username = "user";
$password = "password";

$con = mysql_connect($hostname,$username,$password);


if (mysql_select_db($database, $con)) {
echo "Successful connection<br />";
}

/*

if (isset($startyear) && isset($endyear)) {

while ($startyear > $endyear && $endyear != '') {
die('Your starting year is larger than your end year');
}
}


*/  

if (isset($location)) {
$table = 'location';
$field = 'location';
$var = '{$location}';
}

if (isset($startyear) || isset($gender) || isset($police) || isset($author) || isset($treatment)){
    $table= 'incident';
    $field = 'startmonth' || 'startyear' || 'endmonth' || 'endyear' || 'police' || 'author' || 'treatment'|| 'gender';
    $var = array($startmonth || $startyear || $endmonth || $endyear || $police || $author || $treatment || $gender);
}

if (isset($inctype)) {
    $table= 'inctype';
    $field = 'inctype';
    $var = '{$inctype}';
}

if (isset($lastname)) {
    $table= 'patrons';
    $field = 'lastname';
    $var = '{$lastname}';
} 

$query = "SELECT distinct incid from ";

if(is_array($table)) {
  $query .= implode(',', $table);
} else {
  $query .= $table;
}

$query .= ' WHERE ';

if (empty($startmonth) && isset($startyear) && empty($endmonth) && empty($endyear)){
-            $query .= "year(date) = '{$startyear}' ";
    }
      elseif(isset($startmonth) && isset($startyear) && empty($endmonth) && empty($endyear)){ 
-                $query .= "month(date) = '{$startmonth}'  and year(date) = '{$startyear}' ";
}
      elseif (isset($startmonth) && isset($startyear) && isset($endmonth) && empty($endyear)){
-            $query .= "month(date) between '($startmonth)' AND '{$endmonth}' and year(date) = '{$startyear}' ";
            }
     //below works
elseif(isset($startmonth) && isset($startyear) && isset($endmonth) && isset($endyear)) {-                $query .= "month(date) between '($startmonth)' AND '{$endmonth}' AND year(date) BETWEEN '{$startyear}' AND '{$endyear}' ";
            }             else {
-                $startmonth = '';
-                $startyear = '';
-                $endmonth = '';
-                $startmonth = '';
    }

    if (is_array($field)) {
        $query .= implode(',', $field);
    }
    else {
        $query .= $field;
    }

    if($lastname){
      $query .= ' LIKE '%$var%' ';
    } else {
    $query .= " = $var";
    }



echo $query;
$rs = mysql_query($query);
if ($rs)
{ echo "Records for your date query" . "<br />";
//return with assoc array, you can change to MYSQL_NUM or MYSQL_BOTH
while ($tmp=mysql_fetch_array($rs, MYSQL_ASSOC))
{
$result[] = $tmp;
echo " {$result} <br />";
}
mysql_free_result($rs);
}


/*     

            foreach ($data as $id) {
//incident table query
        $query1 = "select * from incident where incident.incid = '{$id}'";

    $result1 = mysql_query($query1) or die(mysql_error(). '<br />' . $query1);

       $data1 = mysql_fetch_array($result1);
           print_r($data1);
        }


//patron table query
           $query2 = "select * from patrons where patrons.incid = all (select incid from incident where incid = '{$id}')";

    $result2 = mysql_query($query2) or die(mysql_error(). '<br />' . $query2);

       $data2 = mysql_fetch_array($result2);
            //print_r($data2);

//location table query     

                $query3 = "select location from location where location.incid = all (select incid from incident where incid = '{$id}')";

    $result3 = mysql_query($query3) or die(mysql_error(). '<br />' . $query3);

       $data3 = mysql_fetch_array($result3);
            //print_r($data3);

//incident type table query     

                $query4 = "select inctype from inctype where inctype.incid = all (select incid from incident where incid = '{$id}')";

    $result4 = mysql_query($query4) or die(mysql_error(). '<br />' . $query4);

       $data4 = mysql_fetch_array($result4);

        if($data3 = null) {
        echo "No patron information available for your search.";
       }
       if($data4 = null) {
        echo "No incident type available for your search.";
       }
       echo "{$data1['date']}" . "<br />" . "{$data1['time']}" . "<br />" . "{$data1['otherpatrons']}";



 */    

?>