[RESOLVED] search parcial match.

[kproc]

HI below is code this is used to search a data base and display the results in a table. I information entered in the form does not have to be an exact match to the information in the database.

the problem that I'm is whe I do a seach it dispalys all the records.

any idea why

<? session_start(); // Start Session?>

<?
include 'db.php';
// get values from form
$s_firstname = $_POST['s_firstname'];
$s_lastname = $_POST['s_city'];
$s_city = $_POST['DOB'];
$s_prov = $_POST['s_prov'];

// set varibales stating particail matches are fine
$firstname_srch= $s_firstname."%";
$lastname_srch= $s_lastname."%";
$city_srch= $s_city."%";
$prov_srch= $s_prov."%";


// seaching data base for values
$query = ("SELECT * FROM users WHERE first_name LIKE '$fistname_srch' || last_name LIKE '$lastname_srch' || city LIKE '$city_srch' || province LIKE '$prov_srch'") or die ('Search error:' .mysql_error());

$s_msg .="<br><br>";
$s_msg .="<table width=90% align=center border=1><tr>";
$s_msg .="<td align=center> First Name</td>";
$s_msg .="<td align=center> Last Name</td>";
$s_msg .="<td align=center> City</td>";
$s_msg .="<td align=center> Province</td>";
$s_msg .="</tr>";

$result=mysql_query($query)or die("Get Search results Error: ".mysql_error());

while($r=mysql_fetch_assoc($result))
{

$firstname = $r['first_name'];
$lastname = $r['last_name'];
$city = $r['city'];
$province = $r['province'];

$s_msg .= "<tr>";

$s_msg .="<td>$firstname</td>";
$s_msg .="<td>$lastname</td>";
$s_msg .="<td>$city</td>";
$s_msg .="<td>$province</td></tr>";
}
$s_msg .= "</table>";

include 'SearchForm.php';

?>

</body>
</html>

[NogDog]

I don't know if this is the situation, but if any of the four search values are empty, then you'll get every row since you'll be matching on LIKE '%' for that field (which will match anything). Depending on what you're trying to do, changing the ORs to ANDs ("&&") would take care of this.

[kproc]

there are four fields to enter information into the first two are the only ones that require information. first name and last name. the other two are to help narrow down the search.

if I understand correctly where I have $variable. "&" I should put $variable. "&&"

thanks for the help

[NogDog]

Not exactly, where you have || you should use &&. Or, you could just use the word AND which makes it more intuitive to read your query.

[kproc]

thanks for the info. I tried that and it know returns nothing

the form is supposed to search the database and list all partial matches.

I believe my problem is the way that I'm setting up the variable I'm trying to you the wild card %. when I echo the variable it returns the variable value with % at then end, my thought is its thinkingn the % is part of the result

thank you

[NogDog]

See if this gives you the desired results:

include 'db.php';
// get values from form
$s_firstname = trim($_POST['s_firstname']);
$s_lastname = trim($_POST['s_city']);
$s_city = trim($_POST['DOB']);
$s_prov = trim($_POST['s_prov']);

// build where clause:
$where = "WHERE first_name LIKE '$s_firstname%' AND last_name LIKE '$s_lastname%'";
$where .= ($s_city != '') ? " AND city LIKE '$s_city%' ": "" ;
$where .= ($s_prov != '') ? " AND province LIKE '$s_prov%'" : "" ;

// create query string:
$query = "SELECT * FROM users $where";

// rest of code ...

[kproc]

thank you very much. It works excellent until I enter a province and city. then is displays no results.

[kproc]

to add where you user ? in your code does that mean end stop

[kproc]

all is good there were some dumb errors in my code

thank you

[kproc]

$where .= ($s_city != '') ? "

does the ? mean continue the if $s_city is not blank

[NogDog]

expression ? value1 : value2 means that if "expression" is true, return the value after the "?", otherwise return the value after the ":". So...

$where .= ($s_city != '') ? " AND city LIKE '$s_city%' ": "" ;

...means, If $_city is not an empty string, append " And city LIKE '$s_city%'" to the string stored in $where, otherwise append nothing (an empty string) to it. It's like doing an if and an elseif all in one command, and is called the "ternary operator (http://www.php.net/manual/en/language.operators.comparison.php#language.operators.comparison.ternary)".

[kproc]

And I drag this on :D
below is code that works excellent but I want to add a message if there is no result found

I have been moveing this piece around trying different variables but no luck


any help is great thank you


if($somevariable == ????){
errorMsg .= 'No entry found, try again'

include'SearchForm.php';
exit();




<? session_start(); // Start Session?>

<?
include 'db.php';
// get values from form
$s_firstname = trim($_POST['s_firstname']);
$s_lastname = trim($_POST['s_lastname']);
$s_city = trim($_POST['s_city']);
$s_prov = trim($_POST['s_prov']);

if((!$s_firstname) || (!$s_lastname)) {
$errorMsg .= '<div style="width:325px" id= "formmessage">';

if(!$s_firstname){
$errorMsg .= "Retry Enter First Name.<br />";
}

if(!$s_lastname){
$errorMsg .= "Retry Enter Last Name.<br />";


}
$errorMsg .= '</div>';

include 'SearchForm.php';

exit(); // if the error checking has failed, we'll exit the script!
}


// build where clause:
$where = "WHERE first_name LIKE '$s_firstname%' AND last_name LIKE '$s_lastname%'";
$where .= ($s_city != '') ? " AND city LIKE '$s_city%' ": "" ;
$where .= ($s_prov != '') ? " AND province LIKE '$s_prov%'": "" ;

// create query string:
$query = ("SELECT * FROM users $where") or die ('Search error:' .mysql_error());

$s_msg .="<br><br>";
$s_msg .="<table width=90% align=center border=1><tr>";
$s_msg .="<td align=center> First Name</td>";
$s_msg .="<td align=center> Last Name</td>";
$s_msg .="<td align=center> City</td>";
$s_msg .="<td align=center> Province</td>";
$s_msg .="</tr>";

$result=mysql_query($query)or die("Get Search results Error: ".mysql_error());


while($r=mysql_fetch_assoc($result))


{

$firstname = $r['first_name'];
$lastname = $r['last_name'];
$city = $r['city'];
$province = $r['province'];

$s_msg .= "<tr>";

$s_msg .="<td>$firstname</td>";
$s_msg .="<td>$lastname</td>";
$s_msg .="<td>$city</td>";
$s_msg .="<td>$province</td></tr>";
}
$s_msg .= "</table>";

include 'SearchForm.php';

?>

</body>
</html>

[NogDog]

Probably the best way would be, after executing your query:

if(mysql_num_rows($result)) // one or more rows returned
{
// do your processing to output the results
}
else
{
// output your "no results found" message here
}

[kproc]

this is creating me som eimplementation challenges, could I do something like this

if(mysql_num_rows($result < 1)) // one or more rows returned

{
echo 'error';
}

[NogDog]

You can do whatever you want. :)

[kproc]

I guess I was not very clear, I made a statement instead of asking a question.

I tryed changing to the method I showed below but i get error message


Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\MyServer\xampp\htdocs\NewStuff\familypages\codesearch.php on line 52

[NogDog]

I didn't see this first time I looked at it; it should be:

if(mysql_num_rows($result) < 1)

[kproc]

this code works excellent untill I add this. when the code is exicuted it displays 'SeachForm.php' in the middle of the page (page is divided into three columns using css). when I deleted the $s_msg code and left just the exit() and include statment and samething happens. I viewed the code after it was displayed to make sure that there was not an extra div and thats not the issue.

without the if statment all the case's work great.

anything standing out that I'm missing


if(mysql_num_rows($result) < 1) {

$s_msg .= '<div style="width:325px" id= "formmessage">';
$s_msg .= 'Nothing has been found';
$s_msg .= '</div>';

include 'SearchForm.php';
exit();
}

case "users";

$where = "WHERE first_name LIKE '$s_firstname%' AND last_name LIKE '$s_lastname%'";
$where .= ($s_dob != '') ? " AND DOB LIKE '$s_dob%' ": "" ;

$query = ("SELECT * FROM $s_table $where") or die ('Search error:' .mysql_error());

$s_msg .="<table width=90% align=center border=1><tr>";
$s_msg .="<td align=center> First / Last Name</td>";
$s_msg .="<td align=center> Date of Birth</td>";
$s_msg .="<td align=center> Age</td>";
$s_msg .="</tr>";

$result=mysql_query($query)or die("Get Search results Error: ".mysql_error());


// one or more rows returned
if(mysql_num_rows($result) < 1) {

$s_msg .= '<div style="width:325px" id= "formmessage">';
$s_msg .= 'Nothing has been found';
$s_msg .= '</div>';

include 'SearchForm.php';
exit();
}

while($r=mysql_fetch_assoc($result)) {

$firstname = $r['first_name'];
$lastname = $r['last_name'];
$dob = $r['DOB'];
$id = $r['user_id'];

}


$age = round(dateDiff("/", date("m/d/Y", time()), $dob)/365, 0);

$s_msg .= "<tr>";
$s_msg .="<td><a href='SearchDetails.php?user_id=$id&table=$s_table'>$firstname $lastname</a></td>";
$s_msg .="<td align=center>$dob</td>";
$s_msg .="<td align=center>$age</td></tr>";
$s_msg .= "</table>";


include 'SearchForm.php';

break;

[/php]

[kproc]

I figured it out, all because the $age variable was outside the while tags {}