smproph Posted January 17, 2011 Share Posted January 17, 2011 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>'; } Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/ Share on other sites More sharing options...
Maq Posted January 17, 2011 Share Posted January 17, 2011 Echo out the POST to see what the value is. Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1160814 Share on other sites More sharing options...
smproph Posted January 17, 2011 Author Share Posted January 17, 2011 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> Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1160828 Share on other sites More sharing options...
Maq Posted January 17, 2011 Share Posted January 17, 2011 If you're are echoing $_POST['Initials'] and it's blank then the issue is with the form that you're passing it from. Check that you're actually passing a value for that particular field. Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1160848 Share on other sites More sharing options...
smproph Posted January 17, 2011 Author Share Posted January 17, 2011 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 '; } Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1160868 Share on other sites More sharing options...
smproph Posted January 17, 2011 Author Share Posted January 17, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1160918 Share on other sites More sharing options...
leitning_strike Posted January 17, 2011 Share Posted January 17, 2011 I don't see a input with the name 'Initials' in your form anywhere Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1160970 Share on other sites More sharing options...
smproph Posted January 17, 2011 Author Share Posted January 17, 2011 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> Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1160997 Share on other sites More sharing options...
BlueSkyIS Posted January 17, 2011 Share Posted January 17, 2011 I don't see a input with the name 'Initials' in your form anywhere Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1161004 Share on other sites More sharing options...
smproph Posted January 17, 2011 Author Share Posted January 17, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1161012 Share on other sites More sharing options...
Maq Posted January 17, 2011 Share Posted January 17, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1161014 Share on other sites More sharing options...
smproph Posted January 17, 2011 Author Share Posted January 17, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1161033 Share on other sites More sharing options...
Maq Posted January 17, 2011 Share Posted January 17, 2011 Try this: $qry_str.=" and Initials LIKE '%{$Initials%}'"; Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1161035 Share on other sites More sharing options...
smproph Posted January 17, 2011 Author Share Posted January 17, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/224732-losing-variable/#findComment-1161043 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.