-
query
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.
-
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?
-
Syntax error:
replace :"a_date between ($startdate, $enddate)"
by: "a_date between $startdate and $enddate"
-
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 Code:
<?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
-
Did you set these parameters?
$startdate and $enddate .
If yes : try to test the string by replacing$startdate and $enddate by some date values.
-
-
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?
PHP Code:
$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
-
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...
PHP Code:
$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)
-
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,
PHP Code:
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!
-
let me see you While loop?
-
k here it is...
PHP Code:
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";
}
?>
-
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.
-
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>";
-
That intead..
print "<TD><a href=showactivity.php?num=$result[c_id]&start=$GET_[startdate]&end=$GET[enddate]>" .$result['c_id']."</a></TD>";
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|