Page 1 of 1

SQL Query Error

Posted: Mon Nov 07, 2005 5:09 am
by dashwood75
feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


Can you help.

I am trying to do a query pulling the users name from a login box, but get a sql query error due to space in name.

is there a way to resolve this?

Here is my code;

Login Page:

Code: Select all

<?
session_start();
header("Cache-control: private"); //IE6 Fix

echo "<form method=post action=test1.php>";
echo "<table width=20% align=center>";
echo "<tr><td>Username:<td><input type=text name=userid align=middle>";
echo "<tr><td colspan=2 align=center><input type=submit value=Login></td></tr>";
echo "</table></form>";
?>
Results Page:

Code: Select all

<? 
session_start(); 
header("Cache-control: private"); //IE 6 Fix 
// Get the user's input from the form 
   $name = $_POST['userid']; 
// Register session key with the value 
   $_SESSION['userid'] = $name; 
// Display the sssion information: 
?>
<table id="t1" class="sortable" width="100%" align="center">
<tr><th>Work Order</th><th>Problem</th><th>Requestor</th><th>Date Due</th><th>Call Type</th><th>Technician Assigned</th><th>Status</th></tr>
<?
include './includes/dbconn.php';
/*Query Database for specific data*/
$cnt = 0;
$sql= "select * from tasks where COMPLETED IS NULL and PRIORITY !='Project' and PRIORITY !='Internal' and PRIORITY !='Training' and PRIORITY !='Low' and PRIORITY !='Medium' and PRIORITY !='High' and PRIORITY !='0' and PRIORITY !='1' and PRIORITY !='2' and REQUEST = ".$_SESSION['userid']." order by DUEDATE";
$rs = mssql_query($sql);
  if (!$rs)
   { exit("Error in SQL");
   }
/*While row of data is detected continue processing query*/
 while ($row = mssql_fetch_assoc($rs))
  {
  	  /*Declare variables*/
 	  $color = "#eeeeee";
	  $today = date('d M Y H:i:s');
	  $todayover = strtotime($today);
	  $todaydue = date('d M Y');
	  $wo=$row ['WO_NUM'];
	  $task=$row ['TASK'];
	  $request=$row ['REQUEST'];
	  $reqdate=$row ['REQDATE'];
	  $rqdate=strtotime($reqdate);
	  $rdate=date('d M Y',$rqdate);
	  $due=$row ['DUEDATE'];
	  $dudate=strtotime($due);
	  $ddate=date('d M Y',$dudate);
	  $priority=$row ['PRIORITY'];
	  $respons=$row ['RESPONS'];
	  $status=$row ['STATUS'];
	  $progress=$row ['LOOKUP1'];
	  $cnt ++;
/*If fields meet the statements change bgcolour of field*/
  if ($dudate<$todayover){
	  $lookup = $status;
	  if ($status = "Overdue"){
      $color = "#CC6666";}
	  }
	  else {
	  $lookup = $progress;}
  if ($ddate == "$todaydue"){
  	  $color = "#99CCCC";}
  echo "<tr bgcolor=\"".$color."\"><td align=center><img src=images/icon_post.gif><a href=call.php?worder=$wo target=_blank>$wo</a></td>";
  echo "<td>$task</td>";
  echo "<td>$request</td>";
  echo "<td>$ddate</td>";
  echo "<td>$priority</td>";
  echo "<td>$respons</td>";
  echo "<td>$lookup</td></tr>";
  }
  echo "<div align=center><h3>CURRENT OPEN CALLS: $cnt</h3></div>";
  echo "</table>";
?>
It is for a web front end to our helpdesk application, but the query throws this error :evil: :

Code: Select all

Warning: mssql_query() [function.mssql-query]: message: Line 1: Incorrect syntax near 'Brown'. (severity 15) in E:\Apache2\htdocs\Support\test1.php on line 29
Warning: mssql_query() [function.mssql-query]: Query failed in E:\Apache2\htdocs\Support\test1.php on line 29
Error in SQL
FYI The name entered was Lee Brown..

so how can I do the query with the spaces?

Advice appreciated as this is my first journey into the world of php...


feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Mon Nov 07, 2005 5:21 am
by n00b Saibot
change

Code: Select all

REQUEST = ".$_SESSION['userid']."
to

Code: Select all

REQUEST = [".$_SESSION['userid']."]

Posted: Mon Nov 07, 2005 5:36 am
by dashwood75
That changed the error to this:


Warning: mssql_query() [function.mssql-query]: message: Invalid column name 'Lee Brown'. (severity 16) in E:\Apache2\htdocs\Support\test1.php on line 29
Warning: mssql_query() [function.mssql-query]: Query failed in E:\Apache2\htdocs\Support\test1.php on line 29
Error in SQL


headache is building trying to learn php and sql!

:?

Posted: Mon Nov 07, 2005 5:43 am
by Chris Corbyn

Code: Select all

REQUEST = '".$_SESSION['userid']."'
Single quotes around the variable before you escape out.

Note:

This works too (I find it clearer, as long as I use a decent editor that recognizes {$varname} as being escaped, so highlights it)...

Code: Select all

$sql= "select * from tasks where COMPLETED IS NULL and PRIORITY !='Project' and PRIORITY !='Internal' and PRIORITY !='Training' and PRIORITY !='Low' and PRIORITY !='Medium' and PRIORITY !='High' and PRIORITY !='0' and PRIORITY !='1' and PRIORITY !='2' and REQUEST = '{$_SESSION['userid']}' order by DUEDATE";

Posted: Mon Nov 07, 2005 5:50 am
by n00b Saibot
Invalid column name 'Lee Brown'
Aargh! I think it has been long time since I ran a query thru SQL Server :?

Posted: Mon Nov 07, 2005 5:52 am
by dashwood75
the only reason I am using Sql Server 2000 is because the damn helpdesk package requires it... :evil: 8O

Posted: Mon Nov 07, 2005 5:53 am
by Chris Corbyn
n00b Saibot wrote:
Invalid column name 'Lee Brown'
Aargh! I think it has been long time since I ran a query thru SQL Server :?
That interested my though....

Evidently you can also use [column] to mean the same thing as `column` (backticks)....

EDIT | Hmmm... no you can't I just tested it. What a strange error :?

Man... I should be working :P !!

Posted: Mon Nov 07, 2005 5:55 am
by n00b Saibot
d11wtq wrote:Evidently you can also use [column] to mean the same thing as `column` (backticks)....
yeah! i really like to know all the alternate routes to a thing :wink:

Posted: Mon Nov 07, 2005 5:59 am
by dashwood75
d11wtq wrote:

Code: Select all

$sql= "select * from tasks where COMPLETED IS NULL and PRIORITY !='Project' and PRIORITY !='Internal' and PRIORITY !='Training' and PRIORITY !='Low' and PRIORITY !='Medium' and PRIORITY !='High' and PRIORITY !='0' and PRIORITY !='1' and PRIORITY !='2' and REQUEST = '{$_SESSION['userid']}' order by DUEDATE";
This one worked!! Cheers for the help guys is appreciated :D