[SOLVED] Name Could be in any field

[WebGuyRI]

Newbee making progress but really stuck on this. Looked all over and could not get anything to work.

 

The database contains a list of names and a little info, each name could be in any one of 6 fields. Looking to have the user type one name in the query and it would give them the full line of info no mater which field the name is in.

 

Now when the person types the search name it only returns the line that has that name in the first place.  Have tried a number of combinations using name1 name2 etc but can not get it working.

 

$yourfield = "name";

$name = "name";

 

if ($_POST['name'] == "" ) {

    echo 'There was an error in the data you submitted. Please go back and fill in all the required fields.';

    die();

}

 

mysql_connect($hostname, $username, $password) or DIE("Unable to connect to MySQL server $hostname");

"  Connected to MySQL server<br>";

$selected = mysql_select_db($dbname) or DIE("Could not select requested db $dbname");

 

print "Connected to database $dbname<br>";

 

$name = mysql_real_escape_string($_POST['name']);

 

$query = "SELECT * FROM $usertable WHERE name='$name'";

 

$result = mysql_query($query) or DIE("Could not Execute Query on table $usertable");

if ($result) {

 

    print "Query successful on table $usertable<br><br>";

    print "Your Query returned:<br>";

    while ($row = mysql_fetch_array($result)) {

      print $row['name'].", ".$row['name2'].", ".$row['name3'].", ".$row['name4'].", ".$row['name5'].",".$row['name6'].", ".$row['Misc'].", ".$row['volumn'].", ".$row['city'].", ".$row['state']."<br>";

        }

 

    }

 

mysql_close;

?>

 

Any help would be appreciated

 

Thanks

[GingerRobot]

There's no easy way to do that...you need to search each field. For example:

 

SELECT * FROM yourtable WHERE field1='somevalue' OR field2 = 'somevalue' ...

 

But, why do you have 6 fields storing names in them? Chances are, there's a better way to store you data.

[WebGuyRI]

Thanks for the reply will give it a try.

 

As for why I have the names all those fields. This is a database of Cemetery headstones and the names are on the headstones. Which is why they are in different places on each line. The persons who created the database put it the way they found it on the stones. Looking at about 55,000 lines of info.

 

Will let yo know after I try..thanks

[WebGuyRI]

Tried the new code and variations of it but still nothing. I get the data line with the search name in the first place but it is not finding the name in the list of names.

[GingerRobot]

So you mean that if a there is, say, "john" in the name2 column of a given row and you search for "john", then it won't find it, but it will if "john" is in the name1 column of a given row?

 

Can you post your updated code?

[.josh]

$query = "SELECT CONCAT('',name,name2,name3,name4,name5,name6) as n FROM $usertable HAVING n LIKE '%$name%'";

[WebGuyRI]

Sorry for the delay but tried so many versions did not remember what actually happened.

 

I used this code

$name = mysql_real_escape_string($_POST['name']);

 

$query = "SELECT * FROM $usertable WHERE name='$name'"; (this works for the first field)

 

Tried this

$query = "SELECT * FROM $usertable WHERE name='$name' OR name2='$name'";  which gave me a blank screen

 

$result = mysql_query($query) or DIE("Could not Execute Query on table $usertable");

if ($result) {

 

    print "Query successful on table $usertable<br><br>";

    print "Your Query returned:<br>";

    while ($row = mysql_fetch_array($result)) {

      print $row['name'].", ".$row['name2'].", ".$row['name3'].", ".$row['name4'].", ".$row['name5'].",".$row['name6'].", ".$row['name7'].", ".$row['name8'].", ".$row['name9'].", ".$row['name10']."<br>";

        }

 

Very sorry but not really up on PHP Mysql but like it. Thanks for your patience.

[WebGuyRI]

$query = "SELECT CONCAT('',name,name2,name3,name4,name5,name6) as n FROM $usertable HAVING n LIKE '%$name%'";

 

Tried it and got this

Your Query returned:

, , , , ,, , , ,

, , , , ,, , , ,

, , , , ,, , , ,

, , , , ,, , , ,

, , , , ,, , , ,

 

Thanks for jumping in..

 

Would it help if I post the link to see the results and is this allowed?

[.josh]

okay the reason why you are getting a bunch of commas is because in you didn't change the part of your code where you are outputting the results.  Instead of this:

 

       print $row['name'].", ".$row['name2'].", ".$row['name3'].", ".$row['name4'].", ".$row['name5'].",".$row['name6'].", ".$row['name7'].", ".$row['name8'].", ".$row['name9'].", ".$row['name10']."<br>";

 

do this:

 

  print $row['n'] . "<br/>";

 

Yes, you can post links to your code, but don't ask to have them removed later, because we won't.

[WebGuyRI]

Thank you very much that print code worked great.

One small issue with that code is will I be able to add text in between the results? with the old code for one field I was putting text like Volume Number . Death etc.

 

Also if I can not add text this at lease is a major step forward.

 

Thanks again this is a great site for help.

[.josh]

I don't really understand what you mean by adding text between results. Do you mean you want to print other stuff out in each loop iteration? Just put it in there...if that's not what you mean, post code example  of what you were doing before.

[WebGuyRI]

The text I wanted to put in is not a problem. I will add it to the database.

 

Thanks again