nesting a query within a query

[ali_p]

I have a mysql database that i am using for a job search. I have a table called jobseekers that contains the jobseekers name, email, query string ( by this i mean when they did an initial search i saved the string and put it in a field. So the contents here could look like this SELECT * FROM jobs where 1 and (division='A' or division='B') and (sector='AD') and (location='DC') order by division, for example.) My reasoning here is so that i can use this field to query the jobs database for the individual.

So now here is my problem, i want to send the jobseekers the jobs that match their query, by using the query field in the jobseekers table. The second table i want to query is where the jobs are . Say i have 20 rows in this table the query field just reads as one. i'll stop here, here is my code

$sql = "SELECT *
		FROM jobseeker";
$result = mysql_query($sql,$conn)or die(mysql_error());
	while ($row = mysql_fetch_array($result)) {
	//give a name to the fields
	$name = $row['name'];
	$email=$row['email'];
	$query=$row['query'];
	$salary_from=$row['salary_from'];
	$salary_to=$row['salary_to'];
	$keyword=$row['keyword'];
	$last_sent=$row['last_sent'];


$qry2 = "$query";
//echo  "$qry2";

$qry2 .="and upload_date > $last_sent";

$rslt = mysql_query($qry2,$conn);
// Set a variable to display results on screen later
$display_block = "";

//query to retrieve division



while ($row2 = mysql_fetch_array($rslt)) {

$job_key=$row2['job_key'];
$salary=$row2['salary'];
$display_block .="<tr><td>$job_key<br></td></tr>";



}

My second query is not giving me the desired results because , i think, my query string field is printing out as all one one as opposed to individual queries.

I think i'm close but at the same time i know i'm way off, i'm going around in circles could anyone help ... please

[dyntryx]

Well, if I understand correctly, you're gathering results from one query, then slightly modifying the query and getting other results (more current ones, maybe?).

To me it looks like your second query is simply malformed. Say, this is your first one:
SELECT * FROM jobs where 1 and (division='A' or division='B') and (sector='AD') and (location='DC') order by division

Then you're using .="and upload_date > $last_sent" to append to it.

But, that would result in:
SELECT * FROM jobs where 1 and (division='A' or division='B') and (sector='AD') and (location='DC') order by divisionand upload_date > $last_sent

And that clearly just isn't a well-formed query. So, I would suggest adding some code to separate the query by " order ". Then you'll have two strings...
SELECT * FROM jobs where 1 and (division='A' or division='B') and (sector='AD') and (location='DC')
AND
by division
After that, append and upload_date > $last_sent to the first one, but make sure you include a space!
Like this:
$first_str .=" and upload_date > $last_sent";

Then make your final string by doing this:
$final_str = $first_str . " order " . $second_str;

Not that I ever test most things like this before posting, but that's how I'd do it. If I'm answering the wrong question or you need further help, just post again. But, I hope that made some sense and helped you out. 🙂

[QUICK EDIT]
By the way, if I was on the right track, but you need some help modifying the code, let me know and I'll do it for you along with providing a short explanation.
[/QUICK EDIT]

[ANOTHER QUICK EDIT]
If I did that right, the final query I was trying to form was:
SELECT * FROM jobs where 1 and (division='A' or division='B') and (sector='AD') and (location='DC') and upload_date > $last_sent order by division

Just thought that might clear up what I was doing.
[/ANOTHER QUICK EDIT]