×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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?

        $sup_name = trim($supervisor);

        $dir_query = "SELECT lastname, firstname, ssn FROM soinc.upempl WHERE STATUS <> '3' AND SUPERVSR = 'SOME NAME HERE'
                      UNION
                      SELECT lastname, firstname, ssn FROM topllc.upempl WHERE STATUS <> '3' AND SUPERVSR = 'SOME NAME HERE'";
        $dir_result = odbc_exec($connect_soinc, $dir_query);
        $dir_row = odbc_fetch_row($dir_result);

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

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
http://www.mirtheil.com

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

(OP)
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...

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

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
http://www.mirtheil.com

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

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
http://www.mirtheil.com

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

(OP)
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.

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

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
http://www.mirtheil.com

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

One more thing, I went to www.php.net and found that PHP 4 is being discontinued.  I did try with 4.4.7 and it worked the same.  

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close