Jump to content

Archived

This topic is now archived and is closed to further replies.

iPixel

Whats wrong with my SQL statement. ...

Recommended Posts

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...

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 ?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.