PHP for searching SQL DB

Search one table field & then display results from multiple fields

[MelissaParken]

I would appreciate feedback from anyone who can help me with this issue. I have purchased mutliple programs to create the PHP code & can't get the result I am seeking.

The specifics:
I already have a server-hosted DB (VVMembers) containing the table MemberData.

Fields in table=MemberData are:

ID, DateReg, Type, NAME, PRACTICUM, EMAIL, PHONE, Cert1, Cert2, Cert3, Cert4, Cert5, (etc to Cert20)

I want site visitors to be able to search the DB=VVMembers by PRACTICUM (stored in table=MemberData in field=PRACTICUM)from a search box on my homepage.

I want the search results to display on a different webpage with the result displayed to look like this:

You searched for: "the PRACTICUM info the visitor entered into the search form"

<if not found> We're sorry, but we do not have an entry that matches your request.

<if found> The following is the information we have on file for that PRACTICUM requested:

NAME: "actual data in NAME field for that record"
EMAIL: "Actual data in EMAIL field for that record"
CONTACT PHONE: "Actual data in PHONE field for that record"
Certifications: "Actual data contained in all CERT fields (Cert1 through Cert20) listed one after another on separate lines"

I have searched high and low to find either scripting software to help me write this code or some basic scripts that i could edit/alter. The scripts I have been using don't display the results the way I want.

Any assistance would be deeply appreciated.

[edited by: MelissaParken at 5:09 pm (utc) on Feb 6, 2011]

[MelissaParken]

Correction -
the above contains a typo
<if found> The following is the information we have on file for that URL requested:

should read

<if found> The following is the information we have on file for that PRACTICUM requested:

[rocknbil]

Most beginning freelance coders should be able to whip something up for you fairly cheaply, have you considered outsourcing? Should only take a couple working hours.

[MelissaParken]

Yes, they probably could, but that wouldn't assist me in learning to code

[scooterdude]

delete mispost

[MelissaParken]

This is the script I started, but it does not achieve the result I desire:

For my search form:
<form name="form" action="search.php" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
</form>

For the search script:
<?php
$var = @$_GET['q'] ;
$trimmed = trim($var);
$limit=01;
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}
if (!isset($var))
{
echo "<p>We dont seem to have an entry that matches your search parameter!</p>";
exit;
}
mysql_connect("localhost","username","password");
mysql_select_db("database") or die("Unable to select database");
$query = "select * from the_table where 1st_field like \"%$trimmed%\"
order by 1st_field";
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";
if (empty($s)) {
$s=0;
}
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
echo "<p>You searched for: &quot;" . $var . "&quot;</p>";
echo "Results";
$count = 1 + $s ;
while ($row= mysql_fetch_array($result)) {
$title = $row["1st_field"];

echo "$count.)&nbsp;$title" ;
$count++ ;
}
$currPage = (($s/$limit) + 1);
echo "<br />";

I am a novice coder, and I know I'm missing something here... any help would be appreciated.

[rocknbil]

Well, OK, a couple things you are missing . . . .

$var = @$_GET['q'] ;

The @ suppresses any errors that is, if $_GET['q'] is not set, it won't tell you so and this will cause your entire app to fail. Remove @. Second, you should somehow filter this to accept only the data you will expect, uncleansed variables are the first point of entry for hacked web sites, especially those integrated with a database.

Next, the logic is a bit out of order. You say "please enter a search" and exit, but don't provide the tools for the user to do that - what should happen there is you output your search form before exiting. Then you go on to check for $var being set, and if it's not report that you don't have any entries, when you haven't even looked for them yet.

This combines with the problem you have in $_GET['q'] - if $_GET['q'] is not set, $var can't possibly be - so what you should be doing is checking for the set of $_GET['q'] before storing it in $var. All together, you can combine this entire top section into a single, user-friendly set of logics (I know logics plural is not a word, but you get my meaning . . .)

if (! isset($_GET['q']) or (isset($_GET['q']) and empty($_GET['q']))) {
echo "<p>Please enter a search.</p>";
// OUTUT YOUR SEARCH FORM HERE.
exit;
}
else {
// Accepting only letters, numbers, and spaces case insensitive, FOR EXAMPLE
$var = preg_replace('/[^a-z\d\s]+/i','',$_GET['q']);
if (empty($var)) {
echo "<p>Please enter a search.</p>";
// OUTUT YOUR SEARCH FORM HERE.
exit;
}
}

(TYPED ON THE FLY, may contain errors, not copy and paste code)

Next, 01 is an invalid limit clause. You can use 1, but it will only allow one result. Did you mean 10?

$limit=10;

What you are doing right: This is seldom seen in most PHP based database queries:

or die("Unable to select database");

As are the subsequent error traps on the next queries. If you are seeing these messages, you can extend it to find out why by adding this:

or die("Unable to select database" . mysql_error());

But be sure to remove that before deployment as it reveals things about your database structure.

Overall, I think something is screwy with your input, the above should help you figure it out. The mysql queries, I'd do them another way, but they should work.

As you can probably tell, there is a bit more to this than just "knowing the codes." :-)

For future posts, it will be helpful if you explain how it's not working and tell us a bit about your database structure.

[MelissaParken]

Hi rocnbil - thanks for the info, as I said I am new to PHP and though my ancient background is in programming (ACL, JCL, cobol, fortran, and several military languages, for some reason I can't get my head around the PHP stuff)...

Okay, here goes

It's a MySQL Database & structure is simple - single table with the following fields:
Fields in table=MemberData are:
ID, DateReg, Type, NAME, PRACTICUM, URL, EMAIL, PHONE, Cert1, Cert2, Cert3, Cert4, Cert5, (etc to Cert20)

The ID & URL fields are unique (no duplicates)

I am trying to get the code to let a site visitor type or copy/paste a URL into a simple search form

If the URL does not match a record in the DB, then receive a message stating such

If the URL does match a record, then what I am trying to get it to do is deliver a result on a new webpage that looks like this:

On your search of <search parameter URL entered by user into search form> resulted in the following match:

Member Name:<data from NAME field>
Member since: <data from DateReg field>
Member Type: <data from Type field>
Email: <data from EMAIL field>
Contact Phone: <data from PHONE field>
This member holds the following certifications:
<data from each Cert field (1-20) listed separately, one after the other each on a separate line & ignoring empty Cert fields that do not contain data)

What is happening is that I cannot get the data to appear in this format & can't get the Cert1-20 data to list line by line, omitting blank data fields)

For example, let's assume member 423 exists & the url http://www.423domain.com exists

when the user enters http://www.423domain.com into the search form, the result they should recieve should look like:

Member Name:John Smith
Member since: January 1, 2011
Member Type: Annual
Email: [e-mail]
Contact Phone: [phone]
This member holds the following certifications:
ASPPC (from Cert1 field)
MHRM (from Cert6 field)
CHRE (from Cert12 field)


But what's happening is the result in the certifications section shows nothing if Cert1 field is empty, or if they have an ASPPC but nothing in Cert2-Cert5 and they do have MHRM in Cert6, if Cert2-5 are empty, then it won't display the data from Cert6 (or thereafter). In COBOL, it's a string of simple if/then/else clauses... but I must be doing something wrong with the script, because it dies if Cert1 is empty or when Cert1 has data, but not Cert2, it dies. Each time the consecutive data field is empty it stops reading the remaining data fields.

I hope that helps clarify the issue I am having...

Thanks,
Melissa