[RESOLVED] 3 questions, need help please.

[dirkadirka]

Just a disclaimer, I've read through alot of searched threads to find my answers but nothing I've tried has worked.

I'm just gonna start with the easy question and once that's answered (if it is 🙂 I'll move on from there.

Ok, I have a DB with about 1500 entries and I built a real basic search function for it...currently I just have a drop down box with 3 different DB columns listed like Last_Name, Title, Location...then a submit box.

Ok, the Title field is where my first problem is...the users have been entering things like DON/ADOS...or whatever...my search only finds the first letters or more that you enter....so if you enter DO you'd get that record, but if you entered ADOS you would not.

this is what the code is for that part...

$query_Recordset1 = sprintf("SELECT * FROM resumes WHERE JOB_TITLE LIKE '%s%%' ORDER BY LAST_NAME ASC", $colname_Recordset1);

now I've tried '%%s%%' and that just pulls up every record with even one of the submitted letters...I've also tried a ton of other things that usually just end in database errors.

basically I want to be able to search for partial matches anywhere in the field, not just starting at the beggining...also if you enter a % before you do the actual search it will actually work like I'd like it too...my end users don't want to do that though. Thanks for any help...I have more...haha

[leatherback]

Hi,

did you try:

... like '%$s%' ...

[dirkadirka]

That just pulled up every record. also had an error but it still displayed the reusults.

[leatherback]

Hi,

It shouldn't.
What is the error, and what is the actual querystring that is passed to the dabatase?
In your code you have placed just the letter s. Can is be that each record contains an s?

J.

[dirkadirka]

Hey, thanks for the help...

the error is at the top of the page and says

Notice: Undefined variable: s in results.php on line 41

hmm, this does work like its posted as a search for the first part of a word or the whole word, but now changed gives me that error.

also, no, it doesn't pull up all records with an S in it before, and after with your change it pulls up every record and they don't all have an S in them.

this is some more surrounding code if that helps...

if (isset($_GET['select'])) {
  $type_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['select'] : addslashes($_GET['select']);
}

if (isset($_GET['textfield'])) {
  $colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['textfield'] : addslashes($_GET['textfield']);
}
mysql_select_db($database_mydb, $mydb); 

if ($type_Recordset1 == 'JOB_TITLE')
{
$query_Recordset1 = sprintf("SELECT * FROM resumes WHERE JOB_TITLE LIKE '%$s%' ORDER BY LAST_NAME ASC", $colname_Recordset1);
} 

[leatherback]

The error it is giving is because I assumed you placed a typo with just an s: I though you used the variable $s to pass the searchterm. But if it was just testing, place the plain S back in.

I am not familiar with the shorthand you are using for the database query, so I cannot check whether that is correct. The querystring:
SELECT * FROM resumes WHERE JOB_TITLE LIKE '%$s%' ORDER BY LAST_NAME ASC

looks ok. I prefer testing using variables in locations where variables should be, so try:

set the variable first for now. I assume you will later retrieve this from a user textbox input

$s = 's';

$query_Recordset1 = sprintf("SELECT * FROM resumes WHERE JOB_TITLE LIKE '%$s%' ORDER BY LAST_NAME ASC", $colname_Recordset1);

[dirkadirka]

No the textbox input variable is $colname_Recordset1

Honestly I'm not sure why the s is there as some of this code was formatted for me by a code generator, though I've adapted it to work like I want...I was trying to find options for this on php.net and mysql.com but couldn't see anything but the wildcard % and the constant _

I've tried just sticking '%$colname_Recordset1%' in there but that doesn't work at all.

That code snippit before is how I'm getting the textbox info placed into $colname_Recordset1 as far as I can tell...hehe

[dirkadirka]

well I tried adding the $s = 's'; before the query and I get an error that says too few arguements in the query...

so I added

$query_Recordset1 = sprintf("SELECT * FROM resumes WHERE JOB_TITLE LIKE '%$s%%' ORDER BY LAST_NAME ASC", $colname_Recordset1);

the extra % in the %$s%% and then the program works exactly like it did before, it only finds the first part of a search.

[dirkadirka]

here's some code I do after the fact if it helps...

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $mydb) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

is the limit %d, %d causing my problems?

well i guess that doesnt make any sense since this is just for counting the matching records...im grasping at straws here...

why would I get a too few arguement error with '%$s%'? in the original solution u suggested?

[dirkadirka]

I guess thats another question, the s in the '%s%%' is never declared as a variable in my script and yet it works fine for beggining matches...I took that to mean that s was some kind of option that has to deal with the query , sprintf, or like....so I was also assuming there were more options or switches...like one that would give me partial matches like I need.

I couldn't find anything like that on php.net or mysql.com or through searches here however...

[leatherback]

Hi,

How about we for now just toss out the sprintf, shall we? Just looked up the command, and all it really does is format a string, which you do not need to create a query, in my opinion (I have build 15+ database driven sites by now, and never used this command....)

so:

$s = 's';   // set the variable, so we know we are looking for the letter s

$query_Recordset1 = "SELECT * FROM resumes WHERE JOB_TITLE LIKE '%$s%' ORDER BY LAST_NAME ASC limit $startRow_Recordset1, $maxRows_Recordset1";
$Recordset1 = mysql_query($query_limit_Recordset1, $mydb) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);  

[dirkadirka]

Beauty Leatherback!!! Yeah, I suppose that was the danger of using a code generator...but, it sure is helping me learn..

yeah, switching that made it work perfectly.

Now, there are 2 other main questions I was looking for help with.

1) I currently have 5 options for users to search through but only one option/column at a time...I'd like to make it so they could use multiple options at once and have looked for some code through searches here to help me make that happen but couldn't find anything simple enough to make that happen...Alot of search's I've done here have brought up people needing help with site search engine kind of help. (even a basic example that I could expand upon would be greatly helpful)

2) I also would like my users to be able to start a new email message in outlook/outlook express that either inserts the text of a resume they are viewing into the body of the email (all text, no html) or add it as an attachment to the email.

If anyone knows where I could get solid info on either of these 2 subjects I would be very greatful...seems like most searches I do for this information brings me stuff that doesn't match closely enough for me to learn what I need to learn.

Thanks again for all the help Leatherback!

[leatherback]

1 => You can search multiple field simlutaniously:

select * from table where (field1 = somevalue) and (field2 = somevalue) or((field3 = somevalue) and(field4 <> somevalue))

2=> No clue. I do not use ms outlook, since i think it is a horrible program :-). I am not sure whether you can. But why do you need it to start in outlook? Why not use an online form, where they punch in the email addresses?

J.

[dirkadirka]

alright, I should have been more specific on the multiple term search...

right now I'm working with a new submit form and naming my text entries more readable...

so the new result page thats doing the searching takes in the search variables through the URL such as

varFN=Bob&varLN=Marley&varCity=LA&varState=&varJobTitle=Musician&Search=Search

notice I left the varState empty....

now since I won't know how many variables they are entering, could be 1, could be 3....what would the logic look like then? I mean, would I have to create some large if,else,then statement to take care of every possible combination of the 5 categories?

Heh, well thats what like 120 combinations? Ok, so there must be an easier way right?

Thanks again for the help leather, you are saving my bacon and teaching me at the same time! 🙂

[leatherback]

Hi,

You can use the fact that this data is arriving in an array:

// fill this array with the acceptable labels for the search string

$allowed_terms = array(
0 => 'varFN',
1 => 'varLN',
);

// now loop through the get array
Foreach($_GET as $key => value))
  {
   if((in_array($key, $allowed_terms)) && ($value <> ""))  // If the variable is allowed
     {        

      $querystring .= and ($key = '$value')
      }
  }

Please note that this is the general technique. You should do some string testing to make sure the data coming in is not part of sql statement etcetc. But I will leave that to you! 😃

[dirkadirka]

Im starting to get the idea but am a little confused by the code you posted...in the array $allowed_terms we'll be setting an id # for each variable...ok.

The loop format as such I've never seen that way...trying to work my way through the logic.

Foreach($_GET as $key => value))  // so $key is a variable defined by what? and value?
  {
   if((in_array($key, $allowed_terms)) && ($value <> ""))  // I understand this but $key variable again               

     {                                                                                   

      $querystring .= and ($key = '$value')  // what does this do?  is this where I'd enter  a normal query?
      }                                                 

  } 

Just for kicks I entered all my fields and tried this and got...

Parse error: parse error, expecting T_VARIABLE' or'$'' in the Foreach line

I'll be looking this stuff up and experimenting but any further help would be awesome.

[dirkadirka]

err wait, ok so in the Foreach arguement there is supposed to be a $ before value right?

so Foreach($_GET as $key => $value))

well that fixed the first error, now it says Parse error: parse error in the For Each line...

[leatherback]

Hi,

THe $_GET variable, which holds all the variables passed through the url, is an array. THis has the general structure: key => value. So in the foreach, you tell the script: I am using an array, and in this array I call the key: $key, and the value: $value. You can then use the value for each key with $key, and the value of the value with $value.

The error is because of my typo, there is a bracket too many at the end. And yes. There should have been a $

The querystring .= just adds sections to the variable querystring.

You could place this command after the script:

echo $querystring;

[leatherback]

Uch, what a messy coed I posted:

// show the get array:
print_r($_GET);

foreach($_GET as $key => $value) 
  {
   if((in_array($key, $allowed_terms)) && ($value <> ""))
     {                                                                                   

      $querystring .= " and ($key = '$value')  ";
      }                                                 

  } 

echo $querystring;

[dirkadirka]

AH HAH...so the querystring gave me this from my earlier example

and (varFN = 'Bob') and (varLN = 'Marley') and (varCity = 'LA') and (varJobTitle = 'Musician')

soo....my query should look like...errr wait, so I have something that looks like the query I'd want to run, but how do I call the columns?

$query_Recordset1 = "SELECT * FROM resumes WHERE ?? LIKE '%$querystring%' ORDER BY LAST_NAME";

heh, definetly headed in the right direction...guess I don't know enough about creating queries still.

Leatherback, you are the best 🙂