Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

[b]Need correct sql syntax for php script?[/b]

Status
Not open for further replies.
dscrugham

dscrugham

Programmer
Apr 11, 2007
7
0
0
US
Pervasive 8.6
PHP v4.3.1
Apache v1.3.33

The following syntax works if I hardcode in the string value. However, if I use a variable, I get no results. I've tried using wildcards, i.e. %, both before and after the variable but get the same results. If I replace 'SOME NAME HERE' with '$sup_name', no results come back. I have confirmed that $sup_name has a valid name. Any suggestions?

[blue]$sup_name[/blue] = trim($supervisor);

$dir_query = "SELECT lastname, firstname, ssn FROM soinc.upempl WHERE STATUS <> '3' AND SUPERVSR = '[red]SOME NAME HERE[/red]'
UNION
SELECT lastname, firstname, ssn FROM topllc.upempl WHERE STATUS <> '3' AND SUPERVSR = '[red]SOME NAME HERE[/red]'";
$dir_result = odbc_exec($connect_soinc, $dir_query);
$dir_row = odbc_fetch_row($dir_result);
 
Sort by date Sort by votes
The "." operator is how string concatenation is done in PHP. So your statement would look something like:
Code: 
$dir_query = "SELECT lastname, firstname, ssn FROM soinc.upempl WHERE STATUS <> '3' AND SUPERVSR = '" . $sup_name . "' UNION  SELECT lastname, firstname, ssn FROM topllc.upempl WHERE STATUS <> '3' AND SUPERVSR = '" . $sup_name . "'";

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
 
Upvote 0 Downvote
Thanks Mirtheil but when I try that I echo out the following for the $dir_query:

SELECT lastname, firstname, ssn FROM soinc.upempl WHERE STATUS <> '3' AND SUPERVSR = 'JOHN SMITH' UNION SELECT lastname, firstname, ssn FROM topllc.upempl WHERE STATUS <> '3' AND SUPERVSR = 'JOHN SMITH'

This is exactly the same result as if I were to use the following and gives the same results, nothing.
Code: 
$dir_query = "SELECT lastname, firstname, ssn FROM soinc.upempl WHERE STATUS <> '3' AND SUPERVSR = '$sup_name' UNION  SELECT lastname, firstname, ssn FROM topllc.upempl WHERE STATUS <> '3' AND SUPERVSR = '$sup_name'";

Although if I hardcode 'JOHN SMITH' in the origianl sql statement, it returns records...
 
Upvote 0 Downvote
WHat happens if you take the SQL statement that is generated when you use the variable and is copied into the PCC (or ODBCTest or something similar) and run?
If the query is the same, you should be getting the same results.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
 
Upvote 0 Downvote
I just tried it with a very simple statement and was able to get a variable substitution to work. DOes the following return records? It did for me.

Code: 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>PHP Sample</TITLE>
</HEAD>
<BODY>
<?php
$conn=odbc_connect("Demodata","","",""); 
$simple = "ACC 101";
$sql="select * from class where name = '$simple'";

echo $sql;
$rs=odbc_exec($conn,$sql);  
echo "<table border=1>\n";
$numfields = odbc_num_fields($rs);
for($i=1;$i<=$numfields;$i++){
	$fn=odbc_field_name($rs,$i);
	echo "<th>$fn</th>";
}
echo "\n";
while(odbc_fetch_row($rs)){ 
	echo "<tr>\n";
	for($i=1;$i<=$numfields;$i++){
	   $fv=odbc_result($rs,$i);
	   echo "<td>$fv</td>";
	}   
	echo "</tr>\n";
} 
echo "</table>\n";
echo "<p>Number of Fields: $numfields</p>\n";
?>
</BODY>
</HTML>

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
 
Upvote 0 Downvote
Mirtheil-
Your script does work but only if I hardcode the string value in the sql statement. If I put the variable in, it doesn't populate the table. I have never encountered this situation so I am scratching my head. Still working on it, I'll post back when/if I get it worked out. Thanks for the help.
 
Upvote 0 Downvote
The only difference in my environment would be that I'm using PHP 5.2.1. I have tried with multiple versions of PSQL and didn't see a difference.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dgillz
  • Locked
  • Question
DDF Builder for Actian fka Pervasive SQL
Replies
3
Views
185
mirtheil
mirtheil
profoundhypnotic
  • Locked
  • Question
how to select min and max value and the date for each
Replies
1
Views
149
mirtheil
mirtheil
michellepace
  • Locked
  • Question
Basic SQL: Add column to select statement (ie value from another table)
Replies
1
Views
132
michellepace
michellepace
michellepace
  • Locked
  • Question
Source control for pervasive? (git)
Replies
1
Views
123
mirtheil
mirtheil
Tabled
  • Locked
  • Question
Periodically run an SQL update statement 1
Replies
9
Views
216
mirtheil
mirtheil

Part and Inventory Search

Sponsor

Back
Top