# Database Discussions > MySQL >  query

## lukeurtnowski

I have a form with;
list box where multiple selections can be made, name  is medivas_poc
group of 3 checkboxes where a priority is selected; A, B, and C, names are prioritya ....
2 combo boxes so a date range can be selected, name is startdate and enddate,
lastly I have a combo box so a certain company can be selected, name is c_name
The query so I could find the results from  all the selections made is this (soo far)
Select * from company, activity where medivas = $medivas_poc and priority in ($prioritya, $priorityb, $priorityc) and a_date between ($startdate, $enddate) and c_name = $C_name 

Is this right and how do I change it so it will work even when multiple selections could be made from the first list box?


Thanks.

----------


## lukeurtnowski

ok I think I made a query for the above form,
select * from company, activity
where c_id = a_c_id and  
medivas = $poc and 
priority in ($prioritya, $priorityb, $priorityc) and 
a_date between ($startdate, $enddate) and 
c_name = $C_name

But I get this error?
You have an error in your SQL syntax near 'medivas (2) and priority in (A, , ) and a_date between (2005-01-01, 2005-01-' at line 3
I can't figure ouuuut where the  error lies, any ideas?

----------


## oraclable

Syntax error:

replace :"a_date between ($startdate, $enddate)"
by: "a_date between $startdate and $enddate"

----------


## lukeurtnowski

OK, made the change, when the page runs, this is returned

Warning: Supplied argument is not a valid MySQL result resource in /home/domains/lukeu/lukesplace.com/Padilla/report_generator.php on line 19

heres my php where The problem must lie.


```

<?php
$hostname = "10.0.0.127";
$username = "luke";
$password = "blues";
$database = "test";
$connection = mysql_connect($hostname , $username , $password)
or die("cannot make connection");
$db = mysql_selectdb($database , $connection)
or die("cannot find database");
$sql="select * from company, activity
where c_id = a_c_id and  
medivas = $poc and 
priority in ($prioritya, $priorityb, $priorityc) and 
a_date between $startdate and $enddate and 
c_name = $C_name";
//$query=mysql_query($sql) or die($sql);
echo $sql;
$numofrows = mysql_num_rows($query);
?>


```

If you want to see what I'm trying to do, its at
http://www.lukesplace.com/Padilla/createreport.php

----------


## oraclable

Did you set these parameters?
$startdate and $enddate .
If yes : try to test the string by replacing$startdate and $enddate by some date values.

----------


## lukeurtnowski

ok,  ill try  that

----------


## lukeurtnowski

done, heres all the page returned.
select * from company, activity where c_id = a_c_id and medivas = test medivas (1) and priority in (A, , ) and a_date between 2005-01-01 and 2005-02-03 and c_name =

This seems to be the result of echoing thee $sql variable, do you see what the problem, the problem seems to be with the $C_name variable, is that right?


```

$sql="select * from company, activity
where c_id = a_c_id and  
medivas = $poc and 
priority in ($prioritya, $priorityb, $priorityc) and 
a_date between 2005-01-01 and 2005-02-03 and 
c_name = $C_name";
$query=mysql_query($sql) or die($sql);
echo $sql;
$numofrows = mysql_num_rows($query);
?> 


```

Thanks

----------


## lukeurtnowski

k, made some chjnanges (I hope you don't mind), but now this is what I get...
select * from company, activity where c_id = a_c_id and medivas = test medivas (1) and priority in (A, , ) and a_date between 2005-01-01 and 2005-02-03 and c_id = 1
here's the php i changed...


```

$sql="select * from company, activity
where c_id = a_c_id and  
medivas = $poc and 
priority in ($prioritya, $priorityb, $priorityc) and 
a_date between 2005-01-01 and 2005-02-03 and 
c_id = $c_id";
$query=mysql_query($sql) or die($sql);
echo $sql;
$numofrows = mysql_num_rows($query); 



```

(the last line of the select statement)

----------


## lukeurtnowski

beautiful, thank you, that returned some records, (I put quootes around the variables).  Now I replaced the dates with the variables $startdate and $enddate? (which works, thank you)

Also, heres the php code where I display the results,


```

    print "<TD><a href=showactivity.php?num=$result[c_id]&start=$GET_[startdate]&end=$GET[enddate]".$result['c_id']."</TD>"; 
    print "<TD>".$result['c_city'].", ".$result['c_state']."</TD>"; 
    print "<TD>".$result['c_desc']."</TD>"; 
    print "<TD>".$result['rationale']."</TD>"; 
    print "<TD>".$result['contact_name']."</TD>"; 
    print "<TD>".$result['contact_phone']."</td>"; 
    print "<TD>".$result['contact_title']."</TD>"; 
    print "<TD ALIGN=CENTER><b>".$result['priority']."</B></TD>"; 
    print "<TD>".$result['medivas']."</td>"; 



```

All the records print like the should except for the first one, do you know why?
Thanks soo much!

----------


## oraclable

let me see you While loop?

----------


## lukeurtnowski

k here it is...



```

while ($result = mysql_fetch_array($query)) { 
    if($i++ % 2) { 
        $bgcolor = "PowderBlue"; 
    } else { 
        $bgcolor = "white"; 
    } 
    print "<tr bgcolor=\"$bgcolor\">"; 
    print "<TD><a href=showactivity.php?num=$result[c_id]&start=$GET_[startdate]&end=$GET[enddate]".$result['c_id']."</TD>"; 
    print "<TD>".$result['c_city'].", ".$result['c_state']."</TD>"; 
    print "<TD>".$result['c_desc']."</TD>"; 
    print "<TD>".$result['rationale']."</TD>"; 
    print "<TD>".$result['contact_name']."</TD>"; 
    print "<TD>".$result['contact_phone']."</td>"; 
    print "<TD>".$result['contact_title']."</TD>"; 
    print "<TD ALIGN=CENTER><b>".$result['priority']."</B></TD>"; 
    print "<TD>".$result['medivas']."</td>"; 
    print "</TR>\n"; 
}
    ?> 


```

----------


## lukeurtnowski

Let me explain my logic here, the first <td> should be only the name of the company (c_name) which would link to another page which would grab the c_id, startdate, and enddate variable.

----------


## oraclable

Try this:
print "<TD><a href=showactivity.php?num=$result[c_id]&start=$GET_[startdate]&end=$GET[enddate]".$result['c_id']."> link_name</a></TD>";

----------


## oraclable

That intead..
print "<TD><a href=showactivity.php?num=$result[c_id]&start=$GET_[startdate]&end=$GET[enddate]>" .$result['c_id']."</a></TD>";

----------


## lukeurtnowski

Genius, thanks soo much.  Now what I have to do is create a page for thee link which would displat the activity info, based on the $startdate, $enddate, and its coresponding comppany.   But this is nnot going to be a problem (I dont think) so I should be able to hanndle it fine, but it is ok if i get help from you un into any trouble?

----------


## lukeurtnowski

k, I  have a question.  In  a combo box, I have the user select a company name and that name is then sent to the query.  What I am trying to do is if no  selection is made, the query would still run as if all the selections were made, returning all the companies which  fall into that category.  Do you  know how to do this?  I created an <option>ALL</option>, but ccant figure out what its value should be?  Help?

----------


## oraclable

Let's assume that the company name combo's default value is 'all'

if ($C_name=="all"){
  $company_name_cond=" and 1=1 ";
}
esle{
   $company_name_cond=" and c_name = $C_name "; 
}
$sql="select * from company, activity 
where c_id = a_c_id and   
medivas = $poc and 
priority in ($prioritya, $priorityb, $priorityc) and 
a_date between $startdate and $enddate ".$company_name_cond;

----------


## lukeurtnowski

amazing, what dont you  know?

----------


## lukeurtnowski

K,  Now, the guy wants me to do away with selecting  a  certain  company and want me to sort the list by prority, do  you  know how I would then link  to the other page...(somewhat like this?)


```

    print "<TD ALIGN=CENTER><a href=showactivity.php?priority=$prioritya, $priorityb, $priorityc&start=$startdate&end=$enddate>".$result['c_name']."</TD>"; 


```

then should my select statement on the next page be
select c_name, c_state, c_city, contact_name, contact_phone, a_desc, a_next, medivas, 
DATE_FORMAT(a_date, '%m/%d/%Y') as a_date_formatted from company, activity
where c_id = a_c_id and  
priority = '$priority' and 
a_date between '$start' and '$end'

----------


## oraclable

I am not realy clear.. 

You dont have to put priority in the search condition.

print "<TD ALIGN=CENTER><a href=showactivity.php?&start=$startdate&end=$endda  te>".$result['c_name']."</a></TD>"; 

And the next page query should be some like:
select c_name, c_state, c_city, contact_name, contact_phone, a_desc, a_next, medivas, 
DATE_FORMAT(a_date, '%m/%d/%Y') as a_date_formatted from company, activity
where c_id = a_c_id and 
a_date between '$start' and '$end'
and c_name = '*$company_name_chosen'*
oder by priority asc

----------


## lukeurtnowski

ok, sounnd good, thanks, i'll try  that (your right.....I bet you were a boy genius or something like that)  Dop you do this for a living?

----------

