Jump to content

Losing variable

smproph

By smproph
in PHP Coding Help

 Share

Recommended Posts

smproph Member

smproph

Posted
Posted

I am not sure If I am loosing my variable or if it is just getting cancelled out by the syntax.

The error I get is this:

Query string: "SELECT * FROM timeslip WHERE 1 and Initials=" ORDER BY "Date" ASC, "Cost" ASC

Failed with error: 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 '"SELECT * FROM timeslip WHERE 1 and Initials=" ORDER BY "Date" ASC, "Cost" ASC' at line 1

 

Initials should have the value of GEC at this moment

 

Here is my code:

$qry_str="SELECT * FROM timeslip WHERE 1 ";  

if($_POST['Initials']) 
{ 
   $Initials=$_POST['Initials'];  
   $qry_str.='and Initials="'.$Initials . '"'; 
} 

if($_POST['Identifier']) 
{ 
   $Identifier=$_POST['Identifier'];  
   $qry_str.='and Identifier LIKE "%'.$Identifier . '%"'; 
} 

if($_POST['Type']) 
{ 
   $Type=$_POST['Type'];  
   $qry_str.='and Type LIKE "%'.$Type . '%"'; 
} 
if($_POST['Terms']) 
{ 
   $Terms=$_POST['Terms'];  
   $qry_str.='and Terms LIKE "%'.$Terms . '%"'; 
} 
if($_POST['Memo']) 
{ 
   $Memo=$_POST['Memo'];  
   $qry_str.='and Memo LIKE "%'.$Memo . '%"'; 
}
if($_POST['date1']) 
{  
   $date1=$_POST['date1'];
   $date2=$_POST['date2'];
   $start=date('Y-m-d', strtotime($date1));
   $end=date('Y-m-d', strtotime($date2));
   $qry_str.="and Date >= $start and Date <= $end "; 
}
if($_POST['order1'] && $_POST['order2'])
{
$order1=$_POST['order1'];
$order2=$_POST['order2'];
$qry=$_POST['qry'];
$qry_str=' "'.$qry.'" ORDER BY "'.$order1 . '" ASC, "'.$order2 . '" ASC ';
}
elseif(isset($_POST['order1'])) {
     $order1=$_POST['order1'];
$qry=$_POST['qry'];
$qry_str='$qry ORDER BY $order1 ASC';
} else {
     if(isset($_POST['order2'])) {
          $order2=$_POST['order1'];
$qry=$_POST['qry'];
$qry_str='$qry ORDER BY $order2 ASC';
     }
}

if( !$result = mysql_query($qry_str) ) {
     echo "<br>Query string: $qry_str<br>Failed with error: " . mysql_error() . '<br>';  
}

 

Link to comment
Share on other sites
smproph Member

smproph

Posted
  • Author
Posted

I seem to be losing the value. Echo brings back nothing. My goal is to allow the user, after he searches from something to be able to order by two other categories. Here is the form with my code on top that I posted.

 

I don't see why I am losing the value since on the form, I took the entire SELECT statement that the server queried with and then sent it back to the page with just adding the Order by statement on the end of it.

 

$qry_str="SELECT * FROM timeslip WHERE 1 ";  

if($_POST['Initials']) 
{ 
   $Initials=$_POST['Initials'];  
   $qry_str.='and Initials="'.$Initials . '"'; 
} 

if($_POST['Identifier']) 
{ 
   $Identifier=$_POST['Identifier'];  
   $qry_str.='and Identifier LIKE "%'.$Identifier . '%"'; 
} 

if($_POST['Type']) 
{ 
   $Type=$_POST['Type'];  
   $qry_str.='and Type LIKE "%'.$Type . '%"'; 
} 
if($_POST['Terms']) 
{ 
   $Terms=$_POST['Terms'];  
   $qry_str.='and Terms LIKE "%'.$Terms . '%"'; 
} 
if($_POST['Memo']) 
{ 
   $Memo=$_POST['Memo'];  
   $qry_str.='and Memo LIKE "%'.$Memo . '%"'; 
}
if($_POST['date1']) 
{  
   $date1=$_POST['date1'];
   $date2=$_POST['date2'];
   $start=date('Y-m-d', strtotime($date1));
   $end=date('Y-m-d', strtotime($date2));
   $qry_str.="and Date >= $start and Date <= $end "; 
}
if($_POST['order1'] && $_POST['order2'])
{
$order1=$_POST['order1'];
$order2=$_POST['order2'];
$qry=$_POST['qry'];
$qry_str=' "'.$qry.'" ORDER BY "'.$order1 . '" ASC, "'.$order2 . '" ASC ';
}
elseif(isset($_POST['order1'])) {
     $order1=$_POST['order1'];
$qry=$_POST['qry'];
$qry_str='$qry ORDER BY $order1 ASC';
} else {
     if(isset($_POST['order2'])) {
          $order2=$_POST['order1'];
$qry=$_POST['qry'];
$qry_str='$qry ORDER BY $order2 ASC';
     }
}

if( !$result = mysql_query($qry_str) ) {
     echo "<br>Query string: $qry_str<br>Failed with error: " . mysql_error() . '<br>';
 echo "Hi ".$Initials;  
} 
else { 
$result=mysql_query($qry_str); 
$count=mysql_num_rows($result); 
if ($count>0){


?>

<form action="" method="post">
<input type="hidden" value="<?=$qry_str?>" name="qry"/>
Order Results By
<select name="order1" >
		<OPTION value="<?=$order1?>"><?=$order1?></OPTION>
		<OPTION value="Identifier">Identifier</OPTION>
                <OPTION value="Type">Type</OPTION>
            	<OPTION value="Terms">Terms</OPTION>
                <OPTION value="Date">Date</OPTION>
            	<OPTION value="Cost">Cost</OPTION>
              
</select> 
Then
<select name="order2" >
<OPTION value="<?=$order2?>"><?=$order2?></OPTION>
		<OPTION value="Identifier">Identifier</OPTION>
                <OPTION value="Type">Type</OPTION>
            	<OPTION value="Terms">Terms</OPTION>
                <OPTION value="Date">Date</OPTION>
            	<OPTION value="Cost">Cost</OPTION>
              
</select> 
<input type="submit" name="submit">
</form>

 

Link to comment
Share on other sites
smproph Member

smproph

Posted
  • Author
Posted

I am getting a value on $_POST['Initials']. My search works great. It is just after the user searches and gets to the results page you have two drop down menus on top. Those drop down menus is what you can order by. When a user selects two categories to Order By and clicks submit, the form on the results page just sends to the same page whatever the user selected and I add those variables to the end of the Select Statement and use Order By. I am sending the Select statement as a hidden input with the form so whatever the user originally searched for on the previous page is not lost.

 

That's why I have :

 

<form action="" method="post">
<input type="hidden" value="<?=$qry_str?>" name="qry"/>
Order Results By
<select name="order1" >
		<OPTION value="<?=$order1?>"><?=$order1?></OPTION>
		<OPTION value="Identifier">Identifier</OPTION>
                <OPTION value="Type">Type</OPTION>
            	<OPTION value="Terms">Terms</OPTION>
                <OPTION value="Date">Date</OPTION>
            	<OPTION value="Cost">Cost</OPTION>
              
</select> 
Then
<select name="order2" >
<OPTION value="<?=$order2?>"><?=$order2?></OPTION>
		<OPTION value="Identifier">Identifier</OPTION>
                <OPTION value="Type">Type</OPTION>
            	<OPTION value="Terms">Terms</OPTION>
                <OPTION value="Date">Date</OPTION>
            	<OPTION value="Cost">Cost</OPTION>
              
</select> 
<input type="submit" name="submit">
</form>

 

And then my IF statement determines if they just posted that Order By data:

if($_POST['order1'] && $_POST['order2'])
{
$order1=$_POST['order1'];
$order2=$_POST['order2'];
$qry=$_POST['qry'];
$qry_str=' "'.$qry.'" ORDER BY "'.$order1 . '" ASC, "'.$order2 . '" ASC ';
}

 

 

Link to comment
Share on other sites
smproph Member

smproph

Posted
  • Author
Posted

Maybe there is just a better way for me to do this. I have it where search results pull up after they search and then on the Searchresults page they can order by 2 different fields. Is there a better way other than making it a form on the Searchresults page and submitting their results. Because by putting the SELECT statment in a hidden field on the Form I am some how losing the value of what they originally searched by.

Link to comment
Share on other sites
smproph Member

smproph

Posted
  • Author
Posted

From

if($_POST['Initials']) 
{ 
   $Initials=$_POST['Initials'];  
   $qry_str.=' and Initials LIKE "%'.$Initials . '%"'; 
} 

if($_POST['Identifier']) 
{ 
   $Identifier=$_POST['Identifier'];  
   $qry_str.=' and Identifier LIKE "%'.$Identifier . '%"'; 
} 

if($_POST['Type']) 
{ 
   $Type=$_POST['Type'];  
   $qry_str.=' and Type LIKE "%'.$Type . '%"'; 
} 
if($_POST['Terms']) 
{ 
   $Terms=$_POST['Terms'];  
   $qry_str.=' and Terms LIKE "%'.$Terms . '%"'; 
} 
if($_POST['Memo']) 
{ 
   $Memo=$_POST['Memo'];  
   $qry_str.=' and Memo LIKE "%'.$Memo . '%"'; 
}
if($_POST['date1']) 
{  
   $date1=$_POST['date1'];
   $date2=$_POST['date2'];
   $start=date('Y-m-d', strtotime($date1));
   $end=date('Y-m-d', strtotime($date2));
   $qry_str.=' and Date >= $start and Date <= $end '; 
}

 

This takes data from the form on the previous search page

<table width="100%" class="tablefont">
<tr><td align="center">Search Timeslips<br/><img src="images/time.png" height="60px"></td>
<td align="center">Search Tasks<br/><img src="images/task.png" height="60px"></td>
    <td align="center">Search Clients<br/><img src="images/client.png" height="60px"></td></tr>
    <tr><td><br/></td></tr>
<tr>
<td valign="top">
<table class="tablefont" align="center"> 
<form action="searchresults.php" method="post" name="search">   
            <tr>
<td align="right">Initials</td><td><SELECT NAME=Initials>
<OPTION value="">Select</OPTION>
          		<OPTION value="AAC">AAC</OPTION>
                <OPTION value="AJP">AJP</OPTION>
            	<OPTION value="EJG">EJG</OPTION>
                <OPTION value="FAH">FAH</OPTION>
            	<OPTION value="GEC">GEC</OPTION>
               <OPTION value="IJC">IJC</OPTION> 
               <OPTION value="KHW">KHW</OPTION> 
               <OPTION value="LH">LH</OPTION> 
               <OPTION value="VRT">VRT</OPTION>
            </SELECT></td>
</tr>
<tr><td align="right">Identifier</td><td><input type="text" name="Identifier" id="course" /></td></tr>
<tr><td align="right">Type </td><td>
        <SELECT NAME=Type>
        <option value="">Select</option>
           <OPTION value="Attendance">Attendance</OPTION>
		<OPTION value="Correspondence">Correspondence</OPTION>
		<OPTION value="Intake">Intake</OPTION>
		<OPTION value="IPC">IPC</OPTION>
		<OPTION value="Meeting">Meeting</OPTION>
		<OPTION value="Mess Done">Mess Done</OPTION>
		<OPTION value="Mess New">Mess New</OPTION>
		<OPTION value="Note">Note</OPTION>
		<OPTION value="OPC">OPC</OPTION>
		<OPTION value="Research">Research</OPTION>
		<OPTION value="Review">Review</OPTION>
		<OPTION value="RPC">RPC</OPTION>
            </SELECT></td> </tr>
<tr><td align="right">Memo</td> <td><input type="text" name="Memo" /></td> </tr>
<tr><td align="right">Terms</td><td>
           <SELECT NAME=Terms>
           <option value="">Select</option>
          		<OPTION value="Appt">Appt</OPTION>
            	<OPTION value="BK13">BK13</OPTION>
                <OPTION value="BK7">BK7</OPTION>
            	<OPTION value="Cont">Cont</OPTION>
                <OPTION value="FRT">FRT</OPTION>
            	<OPTION value="HRD">HRD</OPTION>
                <OPTION value="HRB">HRB</OPTION>
            	<OPTION value="HRT">HRT</OPTION>
                <OPTION value="Intake">Intake</OPTION>
            	<OPTION value="NC">NC</OPTION>
          
            </SELECT></td></tr>
<tr align="center"><td colspan="2">Date Range</td></tr>
<tr><td align="right">
<script language="JavaScript">
new tcal ({
	// form name
	'formname': 'search',
	// input name
	'controlname': 'date1'
});

</script>
<input placeholder="12/12/2012" type="text" name="date1"  size="10"/>
        </td>
            <td align="right">
           <script language="JavaScript">
new tcal ({
	// form name
	'formname': 'search',
	// input name
	'controlname': 'date2'
});

</script>
    <input placeholder="12/13/2012" type="text" name="date2"  size="10"/>
		</td>
            </tr>
            <tr><td><div id="calendar"></div></td></tr>
<tr><td align="right"><INPUT TYPE="button" VALUE="Empty Fields" onClick="parent.location='search.php'"></td>
<td align="left"> <input type="submit" name="submit" value="Search" /></td></tr>

</form>            
         </table>

 

This part of the IF's:

if($_POST['order1'] && $_POST['order2'])
{
$order1=$_POST['order1'];
$order2=$_POST['order2'];
$qry_str=$_POST['qry'];
$qry_str=' '.$qry_str . ' ORDER BY "'.$order1 . '" ASC, "'.$order2 . '" ASC ';
}
elseif(isset($_POST['order1'])) {
     $order1=$_POST['order1'];
$qry=$_POST['qry'];
$qry_str='$qry ORDER BY $order1 ASC';
} 
else {
     if(isset($_POST['order2'])) {
          $order2=$_POST['order1'];
$qry=$_POST['qry'];
$qry_str='$qry ORDER BY $order2 ASC';
     }
}

if( !$result = mysql_query($qry_str) ) {
     echo "<br>Query string: $qry_str<br>Failed with error: " . mysql_error() . '<br>';
 echo $qry_str;

} 
else { 

$result=mysql_query($qry_str); 
$count=mysql_num_rows($result); 
if ($count>0){
$newqry ='SELECT * FROM timeslip WHERE 1 and Initials LIKE "%GEC%" and Type LIKE "%IPC%" and Terms LIKE "%HRT%"';
echo $newqry;

?>

 

takes from this form

<form action="" method="post">
<input type="hidden" value="<?=$newqry?>" name="qry"/>
Order Results By
<select name="order1" >
		<OPTION value="<?=$order1?>"><?=$order1?></OPTION>
		<OPTION value="Identifier">Identifier</OPTION>
                <OPTION value="Type">Type</OPTION>
            	<OPTION value="Terms">Terms</OPTION>
                <OPTION value="Date">Date</OPTION>
            	<OPTION value="Cost">Cost</OPTION>
              
</select> 
Then
<select name="order2" >
<OPTION value="<?=$order2?>"><?=$order2?></OPTION>
		<OPTION value="Identifier">Identifier</OPTION>
                <OPTION value="Type">Type</OPTION>
            	<OPTION value="Terms">Terms</OPTION>
                <OPTION value="Date">Date</OPTION>
            	<OPTION value="Cost">Cost</OPTION>
              
</select> 
<input type="submit" name="submit">
</form>

 

 

 

Link to comment
Share on other sites
smproph Member

smproph

Posted
  • Author
Posted

It is in the 2nd window of code

SELECT NAME=Initials>
<OPTION value="">Select</OPTION>
          		<OPTION value="AAC">AAC</OPTION>
                <OPTION value="AJP">AJP</OPTION>
            	<OPTION value="EJG">EJG</OPTION>
                <OPTION value="FAH">FAH</OPTION>
            	<OPTION value="GEC">GEC</OPTION>
               <OPTION value="IJC">IJC</OPTION> 
               <OPTION value="KHW">KHW</OPTION> 
               <OPTION value="LH">LH</OPTION> 
               <OPTION value="VRT">VRT</OPTION>
            </SELECT>

 

I have gotten to the point too where it does have the variable and all the information. It is just that the MYSQL syntax is canceling it out. It has something to do with my Quotes. Can't seem to figure out a clean way to write it to where it is not canceling out

 

Link to comment
Share on other sites
Maq Prolific Member

Maq

Posted
Posted

It's not MySQL because when you echoed the query you said it was:

 

SELECT * FROM timeslip WHERE 1 and Initials=" ORDER BY "Date" ASC, "Cost" ASC

 

the variable has nothing in it.  MySQL hasn't touched it at this point.

Link to comment
Share on other sites
smproph Member

smproph

Posted
  • Author
Posted

Well I changed some of the IF statements and I am getting this

 

Query string: SELECT * FROM timeslip WHERE 1 and Initials LIKE ''%GEC%'' and Type LIKE ''%IPC%'' and Terms LIKE ''%HRT%'' ORDER BY "Date" ASC, "Cost" ASC

Failed with error: 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 '%GEC%'' and Type LIKE ''%IPC%'' and Terms LIKE ''%HRT%'' ORDER BY "Date" ASC, "C' at line 1

 

It is showing the Initials. It's just the " ' " are killing me some how

Link to comment
Share on other sites
smproph Member

smproph

Posted
  • Author
Posted

I've changed it to:

if($_POST['Initials']) 
{ 
   $Initials=$_POST['Initials'];  
   $qry_str.=" and Initials LIKE '%{$Initials}%'"; 
} 

if($_POST['Identifier']) 
{ 
   $Identifier=$_POST['Identifier'];  
   $qry_str.=" and Identifier LIKE '%{$Identifier}%'"; 
} 

if($_POST['Type']) 
{ 
   $Type=$_POST['Type'];  
   $qry_str.=" and Type LIKE '%{$Type}%'"; 
} 
if($_POST['Terms']) 
{ 
   $Terms=$_POST['Terms'];  
   $qry_str.=" and Terms LIKE '%{$Terms}%'"; 
} 
if($_POST['Memo']) 
{ 
   $Memo=$_POST['Memo'];  
   $qry_str.=" and Memo LIKE'%{$Memo}%'"; 
}
if($_POST['date1']) 
{  
   $date1=$_POST['date1'];
   $date2=$_POST['date2'];
   $start=date('Y-m-d', strtotime($date1));
   $end=date('Y-m-d', strtotime($date2));
   $qry_str.=" and Date >= $start and Date <= $end "; 
}
if($_POST['order1'] && $_POST['order2'])
{
$order1=$_POST['order1'];
$order2=$_POST['order2'];
$qry_str=$_POST['qry'];
$qry_str=" '{$qry_str}' ORDER BY '{$order1}' ASC, '{$order2}' ASC ";
}

 

I am getting this as an error:

 

Query string: 'SELECT * FROM timeslip WHERE 1 and Initials LIKE ''%GEC%'' and Type LIKE ''%IPC%'' and Terms LIKE ''%HRT%''' ORDER BY 'Date' ASC, 'Cost' ASC

Failed with error: 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 ''SELECT * FROM timeslip WHERE 1 and Initials LIKE ''%GEC%'' and Type LIKE ''%IPC' at line 1

 

I can see already right before ORDER BY there are 3 '''. Not sure why it is adding them. I am not sure if it is doubling the " ' " or not.

 

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.