Build a Dynamic query in a script...

A

Anonymous

Guest
hi,

I'm having a problem with this script.

I want the user to be able to define some options before submintting a form and displaying the results.
This is the script that render the form:

Code: 
<?php
// if the value for $pn and $userLevel are present, then the form is displayed to define which issue to check
if(isset($_POST["pn"])) $pn=$_POST["pn"]; 
if(isset($_POST["issueNum"])) $issueNum=$_POST["issueNum"];
if(isset($_POST["issueType"])) $issueType=$_POST["issueType"];

// Display the form
if (isset ($pn) && isset ($userLevel)):
echo ('<br><strong><p class="titles">SEARCH OPTIONS</p></strong>');
echo ('<form name="form1" method="post" action="/module/issuetracks.php">
<table width="700" border="0" cellspacing="0" cellpadding="0">
  <tr class="bodypage">
    <td width="348" height="15"><div align="right">Issue Status:</div></td>
    <td width="14" height="15"> </td>
    <td width="336" height="15" class="textfieldSearch"><select name="issueStatus" class="textfields">
      <option value="Open" selected>OPEN</option>
      <option value="Closed">CLOSED</option>
    </select></td>
  </tr>
  <tr class="bodypage">
    <td height="15"><div align="right">Issue Type:</div></td>
    <td height="15"> </td>
    <td height="15" class="textfieldSearch"><select name="issueType" class="textfields">
      <option value="all" selected>ALL</option>
      <option value="bug">BUG</option>
      <option value="cr">CR</option>      
    </select></td>
  </tr>
  <tr class="bodypage">
    <td height="15"><div align="right">Issue Number:</div></td>
    <td height="15"> </td>
    <td height="15" class="textfieldSearch"><input name="issueNum" type="text" class="textfields" size="16"></td>
  </tr>
  <tr class="bodypage">
    <td height="15"><div align="right">Submitted on:</div></td>
    <td height="15"> </td>
    <td height="15" class="textfieldSearch"><input name="submitted" type="text" class="textfields" size="16">
       (DD/MM/YYYY)</td>
  </tr>
  <tr class="bodypage">
    <td height="15"><div align="right">Solved on (Closed Issues only):</div></td>
    <td height="15"> </td>
    <td height="15" class="textfieldSearch"><input name="solved" type="text" class="textfields" size="16">
       (DD/MM/YYYY)</td>
  </tr>
  <tr class="bodypage">
    <td height="15"> </td>
    <td height="15"> </td>
    <td height="15"> </td>
  </tr>
  <tr class="bodypage">
    <td height="15" colspan="3"><div align="right">
      <input name="Submit" type="submit" class="button" value="Search">
    </div></td>
    </tr>
</table>
	</form>');

endif;
?>
Now, when clicking 'submit', the datas should go to another page that is called a few lines below this form:

Code: 
<?php
// Get all the variables and fill in the script to execute.
if(isset($_GET["pn"])) $pn=$_GET["pn"]; 
if(isset($_GET["issueNum"])) $issueNum=$_GET["issueNum"];
if(isset($_GET["issueType"])) $issueType=$_GET["issueType"];

include ("extranet/module/issuetracks.php");
?>


What i espect is that the issuetracks.php, displays a result from the database, but it doesn't.
Here's the issuetracks.php:

Code: 
<?php
// Get the Variables from search form.
$issueNum = $_GET["issueNum"];
$issueType = $_GET["issueType"];
// get the value from $pn and set the right company name
$pn = $_GET["pn"];
// define options for $pn values	
switch ($pn) {
	case 3:
	$pname == "company1";
		break;
	
	case 4:
	$pname == "company2";
		break;
	
	case 5:
	$pname == "company3";
		break;
}
// Define the query. Need to insert the variables here.
$query = "SELECT * FROM `issuelist2` where `external_org` LIKE '".$pname."' AND problem_number='".$issueNum."' AND crstatus !='Closed' AND crstatus != 'User_Informed' AND crstatus != 'Rejected'";

$db = mysql_connect("100.000.00.0", "xxxxx", "xxxxxxxxxxx");
mysql_select_db("Support",$db);

$result = mysql_query($query);

if(mysql_num_rows($result)) 
{ 
//avoid do while statements. 
while ($myrow = mysql_fetch_array($result)) 
{ 
// variables for the Issue Comments   
   $issno = $myrow["problem_number"];  
   $sdes = $myrow["synops"]; 
    
   $bgcolor = (++$i & 1) ? '#F2F2F2' : '#F9F9F9'; 
echo ' 
<table width="700" class="bodypage" border="0" cellspacing="2" cellpadding="3"> 
   <tr> 
      <td bgcolor="'.$bgcolor.'"> 
         <strong>Customer:</strong> '.$myrow['external_org'].' 
         <br> 
         <strong>Issue Number:</strong> '.$myrow['problem_number'].' 
         <br> 
         <strong>Status:</strong> '.$myrow['crstatus'].' 
         <br> 
         <strong>Submitted On:</strong> '.$myrow['Issue_StartDate'].' 
         <br> 
         <strong>Priority:</strong> '.$myrow['external_priority'].' 
         <br> 
         <strong>Owner:</strong> '.$myrow['Submitted_id'].' 
         <br>  
         <br> 
         <strong>Workstream:</strong> '.$myrow['application'].' 
         <br> 
         <strong>Description:</strong> '.$myrow['desc'].' 
         <br>   
         <p align="right"><a href="module/windowcomments.php?id='.$issno.'&des='.$sdes.'" target="_blank">SEND A COMMENT ABOUT THIS ISSUE</a></p> 
      </td> 
   </tr> 
</table>'; 
   } 
} 
else { 
echo '<h5><font color="#003066">Sorry, there are no issues for the moment</font></h5>'; 
} 
?>

The first problem i have is how display the form only after the user click submit (like this the script issuetracks.php is called when the page is loaded) and i get nothing. Just the form. Non errors at all.

Then, is it possible to build a part of the query string with a variable? I need to sobstitute here in the query the part - !='Closed' AND crstatus != 'User_Informed' AND crstatus != 'Rejected' - that will have two options. How to render this one?

Thanks a lot...
 
Tonkpils said:
Code: 
echo ('<form name="form1" method="post" action="/module/issuetracks.php">
Click to expand...
here lies the problem, you are "posting" the values and you are using $_GET['varname'] to get the data, use $_POST['varname']

Hope this solves the problem! :D
 
Well... i did something more on the query string :
Code: 
$query = "SELECT * FROM `issuelist2` where `external_org` LIKE '".$pname."' AND problem_number=\"".$_POST['issueNum']."\" AND Issue_status=\"".$_POST['issueStatus']."\" AND  type=\"".$_POST['issueType']."\" AND Issue_StartDate=\"".$_POST['submitted']."\" AND Issue_EndDate=\"".$_POST['solved']."\",$db";
But i get the error : Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

I think is because as soon as the page is loaded, the issuetracks.php is included. I want it to be included only when the form is submitted. HOW????
 
Tonkpils said:
But i get the error : Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in
Click to expand...
This seems that your query is wrong and hence can't be executed and is not returnig a valid result resource
I recommend you that you break the string when the variable has to be entered, don't use back ticks
Code: 
$sql = "select * from sometable where id=" . $_POST['id'] . " and somecol='" . $_POST['someval'] . "';";
 
Code: 
// Connect to DB
$db = mysql_connect("", "", "");

// Select DB
mysql_select_db("Support",$db);

// Definie Variable to build text string before using this below.
$search_string = "'external_org' LIKE '".$pname."' AND problem_number='".$_GET['issueNum']."' AND Issue_status='".$_GET['issueStatus']."' AND  type='".$_GET['issueType']."' AND Issue_StartDate='".$_GET['submitted']."' AND Issue_EndDate='".$_GET['solved']."'";

// Query. Here's the variables should be filled (GEt or Post? Logically is Get, but i tried both and doesn't work...
$query = "SELECT * FROM 'issuelist2' where " .$search_string. ",$db";
Ok, this is it, then... But i still get the error. I'm sure the variables are poste to the page from the form. I wrotez this to check, before the script is executed:
Code: 
// for testing only: If variable are passed, i should see them (not for the moment)
echo "$pname";
echo '<br>';
echo "$pn";
echo '<br>';
echo "$issueType";
echo '<br>';
echo "$issueStatus";
echo '<br>';
echo "$submitted";
echo '<br>';
echo "$solved";
But now the problem is on the way to build the query... Ideas?[/quote]
 
Code: 
$search_string = "external_org LIKE '" . $pname . "' AND problem_number=" . $_GET['issueNum'] . " AND Issue_status=" . $_GET['issueStatus'] . " AND  type='" . $_GET['issueType'] . "' AND Issue_StartDate='" . $_GET['submitted'] . "'  AND Issue_EndDate='" . $_GET['solved'] . "';";

Are you sure this query works on the command prompt of MySQL??
Seems ok here :? [/code]
 
You must log in or register to reply here.
Back
Top