search mysql using php

[ezeddie]

im building a search page for my page. this page will search my customer database by a field that can be chosen via a dropdown box in a form. im having problems when i try to output the data. if i just do SELECT * FROM $table1 then the entire table is outputted, so I know it is connecting properly. any guidance would be appreciated.

result.php

<?
//error message begins
$XX = "No Record Found, to search again please close this window";


//query details table begins
$query = mysql_query("SELECT * FROM $table1 WHERE $select LIKE '%$searchdb%'");
while ($row = @mysql_fetch_array($query))
{
$variable1=$row["customerID"];
$variable2=$row["customerFirst"];
$variable3=$row["customerLast"];
$variable4=$row["customerPrimaryPhone"];
$variable5=$row["customerEmail"];


//table layout for results
print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("<td>$variable4</td>");
print ("<td>$variable5</td>");
print ("</tr>");
}


//below this is the function for no record
if (!$variable1)
{
print ("$XX");
}
//end
?>
customerID, customerFirst, customerLast, customerPrimaryPhone, and customerEmail are my field names in my database

here is the form


<form method="post" action="result.php" target="_blank">
<div align="center">
<table>
<tr>
<td>
<p align="center">
<select name="select" size="1">
<option value="customerLast">Last Name</option>
<option value="customerPrimaryPhone">Telephone</option>
<option value="customerEmail">Email</option>
<option value="customerID">ID</option>
</select>
<input type="text" name="searchdb" size="25"> &nbsp;<br>
Search database: <input type="submit" value="Go!!" name="Go"></p>
</td>
</tr>
</table>
</div>
</form>

[charlesp]

Exactly what is it that you are trying to output, individual records, all records, partial records? What are you searching for? In what sort order do you want the output? I think there is an easier way to do what you want to do.

[ezeddie]

Exactly what is it that you are trying to output, individual records, all records, partial records? What are you searching for? In what sort order do you want the output? I think there is an easier way to do what you want to do.

im trying to output the customer information by individual record, as long as it meets the search entry. i have a drop down menu where you select what you want to search.

example: select "Email" option from drop down box and type in "123@abc.com" in the searchdb text field. When i click go i want it to search my database for all customers with email address "123@abc.com" and display each record

[charlesp]

Give me a little time to work on this and I will get back to you. Probably late tomorrow or Thursday. I think I can solve your problem. I'll see if I can build the script and form for you.

[ezeddie]

Give me a little time to work on this and I will get back to you. Probably late tomorrow or Thursday. I think I can solve your problem. I'll see if I can build the script and form for you.
thanks for the help, I will be awaiting your input.....for the time being i read up on a ajax tutorial and i got something kinda working if you cant get it to work right....not sure if I should rename the field names in the form or if that matters...

[charlesp]

Ezeddie,

Here is the script and form. I have included a connection script for your database if you want to use it. Put the connection script in a directory above your script ie., outside of public_html. You'll have to fill in the connection information.:

<?php // mysql_connect.php

// This file contains database access information
// This file also establishes a connection to MySQL and selects the database.
// Set the database access information as constants.

DEFINE ('DB_USER', '');
DEFINE ('DB_PASSWORD', '');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', '');

// Make the connection.

$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD)
or die ('Could not connect to MySQL: ' . mysql_error() );

// Select the database.

@mysql_select_db (DB_NAME) or die ('Could not select the database: ' . mysql_error() );

// Create a function for escaping data.

function escape_data ($data) {

// Address Magic Quotes.

if (ini_get('magic_quotes_gpc')) {

$data = stripslashes($data);

}

// Check for mysql_real_escape_string() support.

if (function_exists('mysql_real_escape_string')) {

global $dbc; // Need the connection.

$data = mysql_real_escape_string (trim($data), $dbc);

} else {

$data = mysql_escape_string (trim($data));

}
// return the escaped value.

return $data;

} // End function.

?>

Here is the code for your form:


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Customers</title>
</head>

<body>
<div>
<form action="result.php" method="post" name="customers" id="customers">
<table>
<tr>
<td><select name="customer" id="customer">
<option selected="selected"></option>
<option value="first_name">First Name</option>
<option value="last_name">Last Name</option>
<option value="phone">Phone Number</option>
<option value="email">Email</option>
<option value="custID">Customer ID</option>
</select>
<td><input type="text" name="searchdb" id="searchdb" size="25" />&nbsp;<br />
<tr>
<td><p>Search Database:</p></td>
<td><input type="submit" name="submit" id="submit" value="Go!" /></td>
</tr>
</table>
</form>
</div>
</body>
</html>


Here is the script (so far). It will search your database according to the criteria from the select list. But I haven't figured out how to display a message when there is now matching data in the db. Maybe you have some ideas. I'll keep working on it but see if this is what you want:


<?php
include('mysql_conn.php');

$select = $_POST['customer'];
$searchdb = $_POST['searchdb'];

// make the query
$query = "SELECT custID, first_name, last_name, phone, email
FROM customers
WHERE $select LIKE '%$searchdb%'";
$result = @mysql_query($query); // run query


if ($result) { // If it ran OK, display the records.

// Table header.
echo '<table align="center" cellspacing="10" cellpadding="15">
<tr>
<td align="left"><b>CustomerID</b></td>
<td align="left"><b>First Name</b></td>
<td align="left"><b>Last Name</b></td>
<td align="left"><b>Phone</b></td>
<td align="left"><b>Email</b></td></tr>';


// Fetch and print all the records.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<tr><td align="left">' . $row['custID'] . '</td>
<td align="left">' . $row['first_name'] . '</td>
<td align="left">' . $row['last_name'] . '</td>
<td align="left">' . $row['phone'] . '</td>
<td align="left">' . $row['email'] . '</td></tr>';
}

} else {
echo "There are no matches in the database.";
}
echo '</table>';

?>

[charlesp]

I forgot to tell you about the 'include' file it should be 'include('../mysql_conn.php');' instead of 'include('mysql_conn.php');' without the quotes.:D

[charlesp]

ezeddie,
Try replacing the previous result.php script with this one.

<?php
include('./mysql_conn.php');

$select = $_POST['customer'];
$searchdb = $_POST['searchdb'];

// make the query
$query = "SELECT custID, first_name, last_name, phone, email
FROM customers
WHERE $select LIKE '%$searchdb%'";
$result = @mysql_query($query); // run query

$num = @mysql_num_rows($result);

if ($num > 0) {
echo "<p style=\"text-align: center\"><strong>There are currently $num of results.</strong></p>";
} else {
echo "<p style=\"text-align: center\"><strong>There are no matches in the database.</strong></p>";
}

// if ($result) { // If it ran OK, display the records.

// Table header.
echo '<table align="center" cellspacing="10" cellpadding="15">
<tr>
<td align="left"><b>CustomerID</b></td>
<td align="left"><b>First Name</b></td>
<td align="left"><b>Last Name</b></td>
<td align="left"><b>Phone</b></td>
<td align="left"><b>Email</b></td></tr>';


// Fetch and print all the records.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<tr><td align="left">' . $row['custID'] . '</td>
<td align="left">' . $row['first_name'] . '</td>
<td align="left">' . $row['last_name'] . '</td>
<td align="left">' . $row['phone'] . '</td>
<td align="left">' . $row['email'] . '</td></tr>';
}

echo '</table>';

?>

[charlesp]

ezeddie,
I have improved on your script so that it displays an error message if the user doesn't select a search parameter or enter a name, phone#, or email address. You can test it by going to http://www.thruthewindow.com/customers.htm. Search for first name Charles or Sally, and last name Brown, Morreson.

if You would like to have it I will post the code.:D

[SummerLuvSammie]

I would like to do a similar search, but only have the drop down box,

for example,

I have 3 tables called "Surrey" "London" "Newcastle", these all have the following fields, "Institute" "Location" "Subject" .


If i had three search criteria which were, "Subject" "Location" and "Institute"

I would like a drop down box for each, so the Subject dropdown box would list various subjects from art to film etc. If for example the user selected art, i would like to search all my table's subject field.??? does this make sense?? :confused:

[charlesp]

I am not sure if I understand or not. I think I do and if do it can probably be done with 'joins.' It seems like there should be a more efficient way to set up your database. But I don't know without knowing all the information you want stored. Then how would you like your form to be? A drop down list for tables and fields? How would you like the results displayed?:confused:

[SummerLuvSammie]

Hi, can i just say i:o really appreciate your help with this.

ok. I think i will try and keep it as simple as possible so i will just have one table called institutions.

I will have the following 5 fields in my table,
"Institution" "Location" "Course" "Entry Qualification" "Special Features"

I would like to have three search criteria which are,

"Institution" "Location" "Course"

So if the user decided to search by Location, I would like the dropdown to display different areas of the Uk, For example, South West, South East, North, etc.

It would then list the institutions in that area.

I hope this isnt so confusing,

[charlesp]

Your first explanation made more sense. The idea behind databases is NOT to store redundant information. You should have specific information in one table, related information in another table and join the fields from the different tables to retrieve the information you want. For example, have a table called 'locations', the have a field listing all the locations, you would probably want also a field called 'locationID', and other fields related to location. Then have another table called institutions that lists all of the 'institutions' in that table. The you could select out of each table only the fields that you are interested in. It's hard for me to say how to lay out your database without having all of the information.

In all of your tables you will want a field called 'somethingID' to set as your primary key as these are handy when creating 'joins' and other search parameters.

I hope this helps. I'll be glad to help you with your project but I only have a couple of weeks before school starts. After that I will not have much free time.
Charlesp

[SummerLuvSammie]

ok, i will explain to you what information i have and how it is to be searched.

The project is basically about what courses are offered by different institutions around the uk. So i have the folowing information,

Institution Courses Location Other info
The university of Kent Graphics North Flexible study programme
The university of Suffolk Film East Rigid study programme
The university of Derby Art South Flexible study programme

Now, i would have thought that i would have had a table for each institution? (So i would have three tables) Is that wrong?

Now i would like the user to be able to search by one of three criteria, institution, course or location.

But i would like a dropdown/combo box to display these options.

So, if the user decided they wanted to find institutions in a certain location, then if they selected the location dropdown/combobox, they would be presented with all the regions, such as, north, east, south and west.

I hope this clears things up,lol,

Once again many thanks for you help,

[SummerLuvSammie]

Heres the information layed out better,

Institution -------------Courses ----Location ---Other info
The university of Kent ----Graphics ----North ------Flexible study programme
The university of Suffolk --Film --------East -------Rigid study programme
The university of Derby--- Art ---------South ------Flexible study programme

[charlesp]

So you want 3 drop-down/ combo boxes with different search parameters, but the query will be for only one of the parameters, am I right. The form is easy but the php code will take some doing. I think I can do it but it will take some time.:D

Here is code for the form, is this what you want????

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<form action="" method="post" name="institutes" id="institutes">
<table>
<tr>
<td><select name="location" id="location">
<option selected="selected">Location</option>
<option value="north">North</option>
<option value="south">South</option>
<option value="east">East</option>
<option value="west">West</option>
</select>
</td>

<td><select name="institution" id="institution">
<option selected="selected">Institution</option>
<option value="school_a">School A</option>
<option value="school_b">School B</option>
<option value="school_c">School C</option>
<option value="school_d">School D</option>
</select>
</td>

<td><select name="course" id="course">
<option selected="selected">Course</option>
<option value="art">Art</option>
<option value="film">Film</option>
<option value="literature">Litature</option>
<option value="history">History</option>
</select>
</td>
<tr>
<td><input type="submit" name="submit_l" id="submit_l" value="Search" /></td>
<td><input type="submit" name="submit_i" id="submit_i" value="Search" /></td>
<td><input type="submit" name="submit_c" id="submit_c" value="Search" /></td>
</tr>
</table>
</form>
</body>
</html>


You can dress it up with CSS.

[SummerLuvSammie]

Yeah, You got it!!!!!!!!!

lol

:) :) :) :)

[charlesp]

OK. It will take me probably a couple of days to write the code. I can only work on it part-time and I will have to build a small database to use for testing.:D

[SummerLuvSammie]

:) You are an absolute star!:)

I hav been looking all over the web for help with this and hav had no luck.

Incase you are wondering, i am a beginner when it comes to php but im at an intermediate level with html and css.

I have built a successful website using mysql and php, so i am familiar with it, (that mite surprise you), i must admit though i had no idea about joins, and i am now gonna do some much needed research in to designing tables.

Can't wait to hear back from you.

[charlesp]

SummerLuvSammie,

As I map out the DB this project is looking very complex and sorry to say I might not be able to come up with a solution for you. But I'll work at it for a few days and see what comes of it. What you want done is do able, I'm just not sure I can do it. It might be easier to do with one table but with one table but I don't think the different search criteria will work the way you want. Only one way to find out and that is to try it.:D

[charlesp]

SummerLuvSammie,

I am very near a resolution for you search form using only one table. I expect to have this complete today, hopefully before I go to work.:D

[charlesp]

I have the search form done for location and institutes but not for courses.
I think that courses will have to be done by pattern matching using regular expressions. That is something I know nothing about. But I'll have a go of it. And the form works on my server but not BH. I am not sure what the problem is there.:o

[charlesp]

Sammie,

I uploaded my files again to BH and the search form works on BH servers. You can see what I have done so far by clicking on this link ... edited

Keep in mind that I still have to work things out for the courses search. Right now when you select a course it brings up all of the records in the database. Also I want to get it to where one school has more than one course. That's what you want isn't it? Or will one school have only one course?

Anyway I made a small table for courses and I am going to try to get it to work with joins.:D

[charlesp]

Now the search form does not work on my computer or BH. I don't understand as I did not change anything on th BH server.:confused:

[seabee]

Hey, it's working fine here.

[seabee]

I'd like to query that database, but would like to use the option of querying one, two, or all three drop-downs. On a large database, you may want to search for all located in the North, with Institution School C, with an Art course. I'm a little confused on how to make 2 of 3 searches or all three...

[charlesp]

Yes, I had to re-create the database, then the form worked fine. It somehow got corrupted.

I don't know how to get the form where it could use all three parameters, either.
It would probably involve three different tables, using joins and maybe sub-selects. But I know it can be done.

[intenziti]

Hi there,

I came upon this thread while looking for code for a project I am completing for university. I have made a form with a drop down menu so that the user can query the database about which course is available in each month. However I keep getting this error message

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /Applications/xampp/xamppfiles/htdocs/paul/month_drop_down.php on line 41

The php code I actually got from this page: http://www.webmasterworld.com/forum88/4831.htm I have tried to adapt it to suit my purposes and it seems that all my database information is correct but for the likes of me I cannot work out why the php code is not processing the form data from the drop down menu.

Would anyone here please be able to have a quick look at my code and see if there is anything that may be stopping it from functioning?

There are two fields in my table which are called 'month' and 'course', the table itself is called months.

Any help would be greatly appreciated!

Form code:

<form name="month_search" method="post" action="month_drop_down.php"><table width="600" align="center" border="0" cellspacing="1" cellpadding="2">
<tr><td width=300><select name="month" id="selCourse" style="width: 200px;"><option value="Select month to attend..." selected>Select month to attend...</option><option value="jan">January</option><option value="feb">February</option><option value="mar">March</option><option value="apr">April</option><option value="may">May</option><option value="jun">June</option><option value="jul">July</option><option value="aug">August</option><option value="sep">September</option><option value="oct">October</option><option value="nov">November</option><option value="dec">December</option></select></td><td><input type="submit" name="search" value="Search"/></td>
</tr>

</table></form>


PHP code:

<?php

// get variable after selecting something from the dropdown with name 'month'
$select = $_POST['search'];

// if something has been chosen
if (!empty($select)) {

// get the chosen value
$month = $_POST['month'];

// select the type from the database
// database connection details (change to whatever you need)
$HOST = 'localhost';
$DATABASE = 'sworphe_paul';
$USER = 'sworphe_paul';
$PASSWORD = 'sworphe_paul';

// connect to database
if(!$conn=mysql_connect('localhost','sworphe_paul' ,'sworphe_paul')) {
echo("<li>Can't connect to $HOST as $USER");
echo("<li>mysql Error: ".mysql_error());
die;
}

// select database
if (!mysql_select_db($DATABASE,$conn)) {
echo("<li>We were unable to select database $DATABASE");
die;
}

// if everything successful create query
// this selects all rows where the type is the one you chose in the dropdown
// * means that it will select all columns, ie name and type as i said above
$sql_query = "SELECT * FROM months WHERE type='$month'";

// get the data from the database
$result = mysql_query($sql_query,$conn);

// output data
while (($deails = mysql_fetch_assoc($result))) {
// print out the name
echo('Courses available in '.$details['month'].' - ');
// print out the type
echo('Type: '.$details['course'].'<br>');
}

// close mysql connection
mysql_close($conn);

}
?>


Thank you so much in advance! :confused:

[Jimi_l]

Hi All,

I need something Soooo close to this it hurts and I have been working on it for days.

I don't need drop down menus, just three blank fields for users to enter data in. Something like this-

<h2>OEM Database Search:</h2>
<form name="form1" method="post" action="<?=$PHP_SELF?>">
<p>Make
<input type="text" name="textfield">
<br>
Model
<input type="text" name="textfield2">
<br>
Part
<input type="text" name="textfield3">
<br>
<input type="submit" name="Submit" value="Search">
</p>
</form>

The connect part seems pretty straightforward, using .php and adding it below the form.

mysql_connect("localhost", "USER HERE", "PASSWORD HERE") or die(mysql_error());
mysql_select_db("OEM") or die(mysql_error());

This is where I fall apart. I have been trying to get the data from these three fields to search ALL the tables and rows in a single database called "oem" and then display the results.

Can anyone here help a guy with bleeding eyes out?

Thanks,

Jim