Jump to content

Whats wrong with my SQL statement. ...


iPixel

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...
Link to comment
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]
Link to comment
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.
Link to comment
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 ?
Link to comment
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?
Link to comment
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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.