creating a 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.

pkghost

i could be wrong (you should check out the syntax at www.mysql.com) but i think you can use or statements in where statements. soo.. it might look like this

SELET * FROM table WHERE (var=val OR var=val2 OR var=val3) AND var2=val4 AND...

to be honest i have no idea if this is even possible. i would check the site, they have very thorough manuals and reference documents

Sxooter

Yes, ors are legal in a where clause. But, if you have a list of variable size, you can use an in() clause:

$thingstoget = array("a","b","d","g","k","r");
$query = "select * from table where somefield in ('";
$query.= implode("','",thingstoget);
$query.= "')";

pkghost

sxooter: that won't put a comma after the last variable will it? i've been using a for loop (slightly modified) to do that...

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

Sxooter

I'm gonna bet your problem is in here somewhere:

($prioritya, $priorityb, $priorityc) and
a_date between $startdate and $enddate and
c_name = $C_name"

It's quite possible that either $C_name needs to be quoted with single quotes, or that the priority entries do.

And no, pkghost, it will no put an extra , at the end, which is why I use implode so much for stuff like that.

lukeurtnowski

OK, I put actual dates in there to see if that works , and changed a few variables and this is what I got (which seems to be the echo()) of the $sql variable.

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

mrhappiness

lukeurtnowski wrote:
a_date between 2005-01-01 and 2005-02-03

equals:

a_date between 2003 and 2000

a_date between '2005-01-01' and '2005-02-03'

should be better

same for medivas...

everything that's not a number has to be enclosed by '

lukeurtnowski

beautiful, thank you, that returned some records,. Now do I replace 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!