Whats wrong with my SQL statement. ...

[iPixel]

This used to work on PhP 4 and no longer does ... whats wrong.

[code]
$sql="SELECT J.JOB_NAME, J.JOB_NUM, J.STATE, J.SIZE, JT.TYPE_ID, JT.TYPE, CON.CONFIG_ID, CON.CONFIG, JS.SCOPE_ID, JS.SCOPE, J.DATE,  J.DOLLARS , J.SQFT, J.ADDRESS, J.CITY, J.ZIP

FROM JOBS J, CONFIGURATION CON, JOB_SCOPE JS, JOB_TYPE  JT

WHERE $job_name $job_number $job_state $job_type $job_config $job_scope $from_year $to_year $price JT.TYPE_ID=J.TYPE_ID AND J.STATE=J.STATE AND J.CONFIG_ID=CON.CONFIG_ID AND J.SCOPE_ID=JS.SCOPE_ID";
[/code]


The variables in the WHERE clause contain info like :

[code] $job_name="";
$job_name=$_REQUEST['jobnametxt'];
if ($_REQUEST['jobnametxt'])
  {
      $job_name=" job_name LIKE '%$job_name%' AND";
      $url_variables .= "&jobnametxt=$_REQUEST[jobnametxt]";
  }
[/code]

Basically its a dynamically built query ... but for some reason it never finds anything...

[Barand]

Remove all those variables from the where clause

[code]<?php
$sql="SELECT J.JOB_NAME, J.JOB_NUM, J.STATE, J.SIZE,
JT.TYPE_ID, JT.TYPE,
CON.CONFIG_ID, CON.CONFIG,
JS.SCOPE_ID, JS.SCOPE, J.DATE, 
J.DOLLARS , J.SQFT, J.ADDRESS, J.CITY, J.ZIP
FROM JOBS J
INNER JOIN CONFIGURATION CON ON J.CONFIG_ID=CON.CONFIG_ID
INNER JOIN JOB_SCOPE JS ON J.SCOPE_ID=JS.SCOPE_ID
INNER JOIN JOB_TYPE JT ON JT.TYPE_ID=J.TYPE_ID";
?>[/code]

[iPixel]

Its not the variables .. i use them in other sql statements and they run and find data just fine... i thought its something with my SELECT or FROM parts.

The variable turns into

job_name LIKE '%$job_name%' AND blah blah blah... so that isnt the problem.. its something with my J.fieldname or tablename J, tablename2 CON  etc.

[redarrow]

if Barand advise you take it to hart ok trust me.

[iPixel]

In that case how do i place the variables in ... its a dynamic sql query ... i wont know what the query is supposed to look like untill someone fills in the info and hits search.
each of those $variables has a condition, a condition that is determined by the user.
Unless u can tell me how to do this another way the variables must stay.

Here is what the query looks like once i run the search with data.
[code=php:0]
SELECT J.JOB_NAME, J.JOB_NUM, J.STATE, J.SIZE, JT.TYPE_ID, JT.TYPE, CON.CONFIG_ID, CON.CONFIG, JS.SCOPE_ID, JS.SCOPE, J.DATE, J.DOLLARS , J.SQFT, J.ADDRESS, J.CITY, J.ZIP

FROM JOBS AS J, CONFIGURATION AS CON, JOB_SCOPE AS JS, JOB_TYPE AS JT

WHERE job_name LIKE '%Dummy Job%' AND J.job_num = '2' AND J.state = 'NY' AND JT.type = 'AMERICAN' AND CON.config = 'D.P.' AND JS.scope = 'DELIVERED' AND date >= '2006-06-06' AND date <= '2006-06-06' AND DOLLARS >= 500000 AND JT.TYPE_ID=J.TYPE_ID AND J.STATE=J.STATE AND J.CONFIG_ID=CON.CONFIG_ID AND J.SCOPE_ID=JS.SCOPE_ID ORDER BY JOB_NUM DESC
[/code]

Now whats wrong with that ?

[Barand]

My apologies, I hadn't read the second bit of code properly.

The full query looks OK, although the "j.state=j.state" is redundant. (Plus I always prefer to seaprate the join conditions from the search conditions) Have you tried

$result = mysql_query($sql) or die(mysql_error());

To see if there are any errors reported?

If no errors then are you sure you have data which matches all the criteria?

[iPixel]

LoL Barand... u must know how when things go wrong its always the simplest thing that messes you up... i was in a hurry in recreating these tables that i completely forgot to enter all the neccessary data, hence the no result i was getting.

Anyways... thanks for all the help. and yea i removed the redundant things from the query.

Thanks again
iPixel