Jump to content


Photo

Whats wrong with my SQL statement. ...


  • Please log in to reply
6 replies to this topic

#1 iPixel

iPixel
  • Members
  • PipPipPip
  • Advanced Member
  • 496 posts

Posted 06 July 2006 - 07:45 PM

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

$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";


The variables in the WHERE clause contain info like :

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

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 July 2006 - 08:00 PM

Remove all those variables from the where clause

<?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";
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 iPixel

iPixel
  • Members
  • PipPipPip
  • Advanced Member
  • 496 posts

Posted 06 July 2006 - 08:02 PM

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.

#4 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 06 July 2006 - 08:05 PM

if Barand advise you take it to hart ok trust me.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#5 iPixel

iPixel
  • Members
  • PipPipPip
  • Advanced Member
  • 496 posts

Posted 06 July 2006 - 08:10 PM

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

Now whats wrong with that ?

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 July 2006 - 09:11 PM

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?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 iPixel

iPixel
  • Members
  • PipPipPip
  • Advanced Member
  • 496 posts

Posted 10 July 2006 - 09:14 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users