advanced search recordset HELP

Report · Feb 18, 2012

I have tried following the tutorial here http://help.adobe.com/en_US/dreamweaver/cs/using/WScbb6b82af5544594822510a94ae8d65-78aaa.html

the simple one works fine but i try and follow the advanced and it doesnt show how to make the correct SQL statement using cs5 in php

i am trying to search 3 different criteria from 3 text feilds and display either all or one of the seatch results.

the search page form is

<p>

</p>

</map>

</p>

</form>

the results page is

SELECT think_jobsearch.tk_job_title, think_jobsearch.tk_job_location, think_jobsearch.tk_job_salary

FROM think_jobsearch

then i dont know where to go from there? with regards to varialble

can anyone help?

Report · Feb 18, 2012

i have since tried this

SELECT think_jobsearch.tk_job_title, think_jobsearch.tk_job_location, think_jobsearch.tk_job_salary

FROM think_jobsearch

WHERE think_jobsearch.tk_job_title = JT AND think_jobsearch.tk_job_location = JL

and the variables are

NAME; JT

TYPE: Text

Default Value: %

Runtime Value: $_REQUEST["think_jobsearch.tk_job_title"]

Name JL:

Type: Text

Default Value: %

Runtime Value: $_REQUEST["think_jobsearch.tk_job_location"]

THis doesnt work. was just trying in hope

Report · Feb 19, 2012

>SELECT think_jobsearch.tk_job_title, think_jobsearch.tk_job_location, think_jobsearch.tk_job_salary

>FROM think_jobsearch

>WHERE think_jobsearch.tk_job_title = JT AND think_jobsearch.tk_job_location = JL

That won't work. You can't insert variables (JT and JL) directly into a text string. SQL can't evaluate those. You need to use a method using placeholder values that get replace using sprintf() or use concatenation.

>Runtime Value: $_REQUEST["think_jobsearch.tk_job_title"]

You have no REQUEST object variable with that name. That's the name of a database column. You need to use the name of the appropriate form field. Also, I believe you should not even be using _REQUEST anymore. Use $_GET instead.

>i am trying to search 3 different criteria from 3 text

>feilds and display either all or one of the seatch results.

Are all fields optional or required?

>THis doesnt work. was just trying in hope

You need to have a fundamental understanding of SQL and PHP. Trial and error just doesn't work.

Report · Feb 20, 2012

all three feilds are optional.

i was trying to follow the tutorial but it didnt show how using php,

Report · Feb 20, 2012

>all three feilds are optional.

OK. But now, how to you want to combine the logic? If a user enters 'Mananger' for job title, and 'Houston' for location, do you want to return all jobs in Houston and all jobs for Manager? Or, only Manager jobs in Houston?

>i was trying to follow the tutorial but it didnt show how using php,

As I have said before, DW behaviors will only get you so far. At some point, you need to learn the language you are working with. Learning even just the fundamentals would have saved you so much time by now. Why not stop development for a bit and pick up one of David Powers PHP books. Investing even just a few weeks will make you much more productive.

Report · Feb 20, 2012

bregent wrote:
Why not stop development for a bit and pick up one of David Powers PHP books. Investing even just a few weeks will make you much more productive.

I'm not aware that David covers advanced searches in any of his books bregent? Maybe I missed the section somewhere or havent got the one he does cover them in.

Report · Feb 20, 2012

You misunderstood Osgood. I'm not talking about how to learn to use DW advanced searches - I'm talking about learning the fundamentals of the programming language you are working with. If you want to be able to write or edit scripts, you need to be able to al least look at a script and understand what it is doing. Until you understand the syntax, function use, loops and branching, working with any scripting language will be a struggle. Having said that, I'm not sure which if any of Davids books are appropriate. But certainly a beginners book/tutorial for PHP is in order.

Report · Feb 20, 2012

well maybe i should do that. I will have to look at what book to get to learn the fundumentals. thanks

Report · Feb 23, 2012

i am looking online for sql fundumentals but my first question was i was trying to follow the online tutorial on the Adobe website but it was not very helpful. This is surely a common search practice now for website so thought that Adobe would have made it a bit easier.

Report · Feb 23, 2012

have you tried this combination:

SELECT tk_job_title, tk_job_location, tk_job_salary

FROM think_jobsearch

WHERE tk_job_title = %s AND job_location = %s

Plus what are your variable setup?

If I go by the tutorial you are reffering to I get something like below:

$varJobTitle_conJobResults = "%";

if (isset($_GET["JobTitle"])) {

$varJobTitle_conJobResults = $_GET["JobTitle"];

}

$varLocation_conJobResults = "%";

if (isset($_GET["Location"])) {

$varLocation_conJobResults = $_GET["Location"];

}

in the above my form field names are 'JobTitle' and 'Location' and the variables are 'varJobTitle' and 'varLocation'

Admittedly I too have been looking for an 'advanced search' tutorial' just to do for the exercise and I'm afraid I have never come across one or come across one in any of the phps books I've got. I'm with you on this one.

Also I'd keep the name of the form fields the same as the name of your database columns. Less confusion

Report · Feb 23, 2012

my variables are whats confusing me i have set up these settings in dreamweaver advanced

NAME; JT

TYPE: Text

Default Value: %

Runtime Value: $_REQUEST["think_jobsearch.tk_job_title"]

Name JL:

Type: Text

Default Value: %

Runtime Value: $_REQUEST["think_jobsearch.tk_job_location"]

thanks very much, i will try what you have suggested

Report · Feb 23, 2012

>Default Value: %

The default value is the value supplied if the user does not enter anything. The wildcard character only works with the SQL like predicate.

>Runtime Value: $_REQUEST["think_jobsearch.tk_job_title"]

Runtime Value tells DW where to get the value to use in the SQL WHERE clause at runtime. As mentioned earlier, this should be the name of the form field where the data is coming from, not the database columns.

And you still haven't answered my earlier question below which is critical if you want to get the right results:

"OK. But now, how to you want to combine the logic? If a user enters 'Mananger' for job title, and 'Houston' for location, do you want to return all jobs in Houston and all jobs for Manager? Or, only Manager jobs in Houston?"

Report · Feb 23, 2012

If a user enters 'Mananger' for job title, and 'Houston' for location, do you want to return all jobs in Houston and all jobs for Manager? Or, only Manager jobs in Houston?"

all the jobs for manager and all the jobs in houston

also they can jobs search jobs in houston and leave others blank then it will return all jobs in houston

OR

can they do both so if they leave the others blank it will just show results for the one searched criteria

Report · Feb 23, 2012

>all the jobs for manager and all the jobs in houston

Then the conditions in the WHERE clause need to be separated with an OR, not an AND. Use Osgoods SQL as an example, but change the AND to an OR:

SELECT tk_job_title, tk_job_location, tk_job_salary

FROM think_jobsearch

WHERE tk_job_title = %s OR job_location = %s

>can they do both so if they leave the others blank

>it will just show results for the one searched criteria

Sure. One way is to set the default values in the advanced recordset editor to a value that will never occur in the data.

Default Value: XXXX

Report · Feb 24, 2012

ok but what are the variables i should use, ?

$varJobTitle_conJobResults = "%";

if (isset($_GET["JobTitle"])) {

$varJobTitle_conJobResults = $_GET["JobTitle"];

}

$varLocation_conJobResults = "%";

if (isset($_GET["Location"])) {

$varLocation_conJobResults = $_GET["Location"];

}

if i am using the advanced recordset builder in dreamweaver or should i not use that?

Report · Feb 24, 2012

Open the 'advanced' record set behaviour panel for the results page.

Type into the sql box:

SELECT tk_job_title, tk_job_location, tk_job_salary FROM think_jobsearch

WHERE tk_job_title LIKE var_tk_job_title OR tk_job_location LIKE var_tk_job_location OR tk_job_salary LIKE var_tk_job_salary

Click + on the Variables panel and add:

Name: var_tk_job_title

Type: choose 'text' from the drop down

Default value: %

Runtime value: $_GET["tk_job_title"]

click + again and add:

Name: var_tk_job_location

Type: choose 'text' from the drop down

Default value: %

Runtime value: $_GET["tk_job_location"]

click + again and add:

Name: var_tk_job_salary

Type: choose 'text' from the drop down

Default value: %

Runtime value: $_GET["tk_job_salary"]

Click ok. That will automatically create your variable.

IMPORTANT: Amend your form field 'names' to match the variable names (as shown below)

<p>

</p>

</map>

</p>

</form>

Report · Feb 25, 2012

ok that brilliant but have tried it but it doesnt return any results where i know there should be, i take it the results should just be

<?php echo $row_Recordset1['tk_job_title']; ?>

placed on the page?

Report · Feb 25, 2012

You need to show us the complete code you are currently having problems with.

Report · Mar 09, 2012

<?php require_once('Connections/jobs.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

$var_tk_job_title_Recordset1 = "%";

if (isset($_GET["tk_job_title"])) {

$var_tk_job_title_Recordset1 = $_GET["tk_job_title"];

}

$var_tk_job_location_Recordset1 = "%";

if (isset($_GET["tk_job_location"])) {

$var_tk_job_location_Recordset1 = $_GET["tk_job_location"];

}

$var_tk_job_salary_Recordset1 = "%";

if (isset($_GET["tk_job_salary"])) {

$var_tk_job_salary_Recordset1 = $_GET["tk_job_salary"];

}

mysql_select_db($database_hostprop, $hostprop);

$query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary FROM think_jobsearch WHERE tk_job_title LIKE %s OR tk_job_location LIKE %s OR tk_job_salary LIKE %s", GetSQLValueString($var_tk_job_title_Recordset1, "text"),GetSQLValueString($var_tk_job_location_Recordset1, "text"),GetSQLValueString($var_tk_job_salary_Recordset1, "text"));

$Recordset1 = mysql_query($query_Recordset1, $hostprop) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

$totalRows_Recordset1 = mysql_num_rows($Recordset1);

?>

then the results should diplay here

<tr>

<td>tk_job_title</td>

<td>tk_job_location</td>

<td>tk_job_salary</td>

</tr>

<?php do { ?>

<tr>

</tr>

<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

</table>

Report · Mar 09, 2012

ok that does seem to work but when running test when they search for security officer only that job title is returned. even if there is a security guard also as a job. what do i have to to for the search to pick out any word in the search?

Report · Mar 09, 2012

>what do i have to to for the search to pick out any word in the search?

Please clarify this by supplying examples of the data, examples of what the user might enter, and what results you would want based on that criteria.

Report · Mar 10, 2012

example: in the tk_job_title the user may type in security or security guard. Currently the results will either show only the exact search result either security or security guard. i want it to display the results for both all job with security in the and guard.

or in the tk_job_location the user may type in Orlando or Orlando Florida, again i want results to display all locations in Orlando and all Results in Florida

or tk_job_salary. again they may put in 30,0000 or Any and it will show results for both

Report · Mar 10, 2012

Standard SQL doesn't work like that. You either need to parse the search expression into separate words and then build a dynamically SQL statement, or alter your database to enable Full-Text Searching.

Report · Mar 11, 2012

so what you are saying is the way i have it currently set up it wont work like that?

>alter your database to enable Full-Text Searching.

this is something i do within the database itself? or is this done on the MySQL statement?

Report · Mar 11, 2012

>this is something i do within the database itself?

Yes, you would need to alter the database tables.

>or is this done on the MySQL statement?

There are many types of SQL statements. It would not be done in a SELECT statement, if that's what you mean.