picking strings out of sql

kilbey1

Not sure if this is exactly what I want to do (the title). But basically, what I have here is a dynamically generated SQL query based on whatever the user enters in a form (example: firstname and lastname will give a SQL statement constructed to include the table 'placement_candidate').

This works great, so when the user fills out a form like this:

Technology: C
First name: Joe
Last name: Blow

I get this:

SELECT placement_primarytech.PrimaryTechnology, placement_candidate.FirstName, placement_candidate.LastName FROM placement_primarytech, placement_candidate WHERE placement_primarytech.PrimaryTechnology LIKE "%c%" AND placement_candidate.FirstName = "joe" AND placement_candidate.LastName = "blow"

$sSelect is part of this code that generates the 'SELECT' portion of the sql statement.

Now, when I get here:

	if($sSelect) {
     	     $sSelect .= 'placement_candidate.CandidateID';
	     echo $sSelect;
	}

I can tack on 'placement_candidate.CandidateID' to the SELECT portion of my sql.

What I really want to do here is only attach it IF 'placement_candidate' exists at all in my SELECT (and FROM) portions.

Pseudo code:
If 'placement_candidate' exists ANYWHERE in the SELECT (or From, it doesn't matter), add placement_candidate.CandidateID.
If 'placement_primarytech' exists ANYWHERE in the SELECT (or from), add placement_primarytech.TechnologyID
if BOTH exist, then add placement_candidate.CandidateID AND placement_primarytech.TechnologyID

How do I squeeze this out of the SQL statement I constructed?

EDIT: things I've looked into:
SQL Query Caching

olaf

Hallo,

use a if, elseif and else clause with different queries to solve your problem...

kilbey1

Not quite following you. The query is already generated dynamically; so now, I have depending on what it dynamically generated, I've got dynamically generate what will and won't appear.

Basically, on quick calculation, here are some of the queries I could come up with and their combinations:

SELECT placement_candidate.FirstName FROM placement_candidate WHERE ... ( first name form only)
SELECT placement_candidate.LastName FROM placement_candidate WHERE ... (last name form only)
SELECT placement_candidate.FirstName, placement_candidate.LastName FROM placement_candidate WHERE ... (first and last name fields)
SELECT placement_candidate.FirstName, placement_candidate.LastName, placement_primarytech.PrimaryTechnology FROM placement_candidate, placement_technology FROM ... (first, last names and technology)
SELECT placement_candidate.LastName, placement_primarytech.PrimaryTechnology FROM placement_candidate, placement_primarytech WHERE ... (last name, technology)
SELECT placement_candidate.LastName, placement_candidate.State FROM placement_candidate WHERE (last name, state)
[/list=1]

etc. etc. This would become really cumbersome very quickly.

AstroTeg

kilbey1, I think I was working with you with a similar query a few posts ago.

You took my WHERE clause idea of putting it in an array and really ran with it (cool).

Although, I usually leave the SELECT and FROM clauses alone because even though the results page may not need all the data, its just easier to leave the data in there and if I need it I can always update my HTML templates to include the data. I think by doing this, you save yourself the hassle of trying to identify if the query needs that field or not (and you can save that for when it comes to displaying the data).

kilbey1

That'd be fine, except the 1 table in question - candidate - will have thousands of entries. All people looking for jobs. 🙂

Not a mySQL guru, but I would think if you constructed the query like so:

SELECT placement_candidate.FirstName FROM placement_candidate, placement_primarytech (etc.)

...would slow down the query quite a bit. No? Will mySQL skip over placement_primarytech if you are not 'select'-ing anything from it?

kilbey1

Addition:

or what if the person decided to just select a skill? The Query would then be:

SELECT placement_primarytech.PrimaryTechnology FROM placement_candidate, placement_primarytech WHERE ...

Would the Query go through thousands of users, selecting nothing until it went through the other table?

Actually, this isn't quite complete. Then I need to dig down deeper and, based on this info, do another query matching the technology with another table... placement_candidatetech ... which is a cross-reference table matching candidateIDs with the technology they have, then take the ID from that table and match it to CandidateID. 🙂

AstroTeg

If you're not doing a lot of heavy ORDER BY or GROUP BY statements against the query, you may not be taking a huge performance hit. You will get dinged for adding the 2nd table, but because it doesn't look like your query does a lot with the data other than bring it in, it may not be bad - even for a couple thousand records. Now if it was a couple million, then thats a different story.

Quick test would be to run some time trials and see how it it takes for each query. For the queries I've done, I usually end up needing most of the data anyways from several tables. In your case, it may make sense just to bounce off the one table instead of two. If thats the case, in your code to test if it needs the 2nd table, have it also add onto the SELECT clause at the same time. That might help.

leatherback

Hey Kilbey,

Not completely sure if I understand your question corretly.. IF you are just tryint o find wether these statements exist in your query.. Couldn't you just store the query in a temp. variable, and then do a stringmatch on it? Perhaps break up the query by 'where' to get the sub-query-part-thingies?

Justa thought, IF you decide you still want to rebuild the query..

kilbey1

Maybe I'm barking up the wrong tree. Leatherback, what you suggest is a possibility. What functions do I look into, to go about this?

Here's the code I worked out (thanks to AstroTeg!):

function results() {

//set up SELECT statement
$aSelect = array(); 
if($_POST['skill'] != '') 
  $aSelect[] = 'placement_primarytech.PrimaryTechnology'; 
if($_POST['namefirst'] != '') 
  $aSelect[] = 'placement_candidate.FirstName'; 
if($_POST['namelast'] != '') 
  $aSelect[] = 'placement_candidate.LastName'; 
if($_POST['region'] != '') 
  $aSelect[] = 'placement_candidate.Region'; 
if($_POST['country'] != '') 
  $aSelect[] = 'placement_candidate.State'; 

//set up FROM portion of sql statement
$aFrom = array(); 
if($_POST['skill'] != '') 
  $aFrom[] = 'placement_primarytech'; 
if($_POST['namefirst'] || $_POST['namelast'] || $_POST['region'] || $_POST['country'] != '') 
  $aFrom[] = 'placement_candidate'; 

//set up WHERE portion of sql statement
$aWhere = array(); 
if($_POST['skill'] != '') 
  $aWhere[] = 'placement_primarytech.PrimaryTechnology LIKE "%' . $_POST['skill'] . '%"'; 
if($_POST['namefirst'] != '') 
  $aWhere[] = 'placement_candidate.FirstName = "' . $_POST['namefirst'] . '"'; 
if($_POST['namelast'] != '') 
  $aWhere[] = 'placement_candidate.LastName = "' . $_POST['namelast'] . '"'; 
if($_POST['region'] != '') 
  $aWhere[] = 'placement_candidate.Region = "' . $_POST['region'] . '"'; 
if($_POST['country'] != '') 
  $aWhere[] = 'placement_candidate.State =  "' . $_POST['end_date'] . '"'; 

//build SELECT clause
$sSelect = 'SELECT ' . implode(', ', $aSelect);

// build FROM clause
$sFrom = 'FROM ' . implode(', ', $aFrom);

// build where clause 
$sWhere = 'WHERE ' . implode(' AND ', $aWhere);  

// Select, Where and From clause done - add it to the SQL skeleton 
$sql = "" . $sSelect . " " . $sFrom . " ". $sWhere;

echo $sql . "<P>";

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

//$num_rows = mysql_num_rows($result);

if($sFrom) {
	echo "FROM exists";
}

}
?>

When this function is spit back from a user-inputted form (let's say they enter:
Technology: C
Last Name: Blow

I get this SQL:
SELECT placement_primarytech.PrimaryTechnology, placement_candidate.LastName FROM placement_primarytech, placement_candidate WHERE placement_primarytech.PrimaryTechnology LIKE "%c%" AND placement_candidate.LastName = "blow"

BEAUTIFUL!

Now, I've got to take this query, based on what the user has input, and "drill down". Using the info we have (primarytech = c, lastname = blow), construct a query matching to the appropriate table.

Primarytech exists ONLY? look at candidatetech table now (has candidate IDs with primarytech IDs) and pull up a list of candidates with C programming skills. (further reduced into another function, multipleresults() ).

candidate exists ONLY? (easy, just display candidate info)

Primarytech + candidatelastname? query ok as is.

primarytech + candidatefirstname? query ok as is

candidate firstname and candidate last name? query ok as is

primarytech + state? search in primarytech AND candidate and match 'em up

primarytech + region? search in primarytech AND candidate and match 'em up

region and state? get list of users in these areas

skill and state? search primarytech (C), search candidatetech table where ID = 1 (C technology), take ID from candidatetech table and find those ids in candidate table

AstroTeg

Why not update this chunk of code:

    $aFrom = array(); 
    if($_POST['skill'] != '') 
      $aFrom[] = 'placement_primarytech'; 
    if($_POST['namefirst'] || $_POST['namelast'] || $_POST['region'] || $_POST['country'] != '') 
      $aFrom[] = 'placement_candidate';

to:

    $aFrom = array(); 
    if($_POST['skill'] != '') 
      {
       $sSelect = ", placement_primarytech.PrimaryTechnology";
      $aFrom[] = 'placement_primarytech'; 
       }  // end if(...
    if($_POST['namefirst'] || $_POST['namelast'] || $_POST['region'] || $_POST['country'] != '') 
      $aFrom[] = 'placement_candidate';

Then update this:

$sSelect = 'SELECT ' . implode(', ', $aSelect);

to be:

$sSelect = 'SELECT ' . implode(', ', $aSelect) . $sSelect;

This is just one way to do it. Not sure its all that graceful, but I think it might work.

kilbey1

Is this using the original idea, or the updated one where I break down select/from/where?

AstroTeg

Originally posted by kilbey1
Is this using the original idea, or the updated one where I break down select/from/where?

The break down one where you put everything into arrays (or the code you just posted before my last post).

kilbey1

I'd better lay this out a bit further.

We have the function "result()"... this is the code we set up. At this function, I have echoed out:

	$num_rows = mysql_num_rows($result);

if($num_rows == 0) {
	echo "nothing returned";
} elseif($num_rows > 1) {
	echo "$num_rows rows, therefore, more than 1 result";
} else {
	echo "just 1, display result";
}

if no result = try again.
If 1 result = display info.
if > 1 result = multipleresults();

Now, entering 'c' and 'blow' pulls back 4 results (4 technologies with c in their name). So it echoes out:

4 rows; therefore, more than 1 result.

In here, I'm really going to put the function: multipleresults(). This should construct a query based on whatever was selected in the previous, narrow it down, and group by Skill Set.

other possibilities for multipleresults():
State + skill: group by State
State + name: group by state

EDIT: I think that's why I thought it would be easiest to already know what SQL I constructed, and construct my next statement based on this.

kilbey1

Maybe something like this (i'm getting a headache!):

function multipleresults() {
$sql = $sql2;
echo $sql2;
if($sql2 CONTAINS 'this_phrase') {
    $sSelect = "blah";
}
//let's construct another dynamic query like before
}

But I don't know how to write this up correctly. Where does PHP have a 'contain' function (I know MySQL handle it)? How do I put the generated '$sql' into a temporary variable?

kilbey1

boy, am I a dumbass. Been staring at the computer too long. I had the variable outputting in the wrong IF statement. (sigh)

This does it. Now to search the string, and (hopefully) generate another dynamic query.

	if($num_rows == 0) {
		echo "nothing returned";
	} elseif($num_rows > 1) {
		echo "$num_rows rows, therefore, more than 1 result";
		$sql2 = "$sql";
		echo $sql2;
	} else {
		echo "just 1, display result"; 
                )