Select Statement for multiple tables

[Daminious]

Hello, I have a problem. I am creating an administration area for a members only area of a website. Within this administration area I have pages deticated for specific functions, such as search members, create members, exc.

Right now I am working on the search members function. I want the admin to be able to search all members of the database for multiple fields. For example, I want the admin to have the choose to search for Username, User_ID, Area Code, Phone Number, City, State, exc. I store these values in their respected tables. I have created a form for the user to fill out. This form allows the user to input the fields they wish to search for. My problem is creating a query that allows for any of the data to be searched for that runs across multiple tables.

Table: Customer_Authorization
Primary Key: User_ID, Secret_Question_ID
Fields:
User_ID
Username
Password
E-Mail
Question_ID
Secret_Question_Answer
Member_Type
Created_Date
Modified_Date

Table: Customer_Information
Primary Key: Customer_Information_ID
Foreign Key: User_ID
Fields:
Customer_Information_ID
User_ID
First_Name
Last_Name
Created_Date
Modified_Date

Table: Address_Information
Primary Key: Address_Information_ID
Foreign Key: User_ID
Fields:
Address_Information_ID
User_ID
House_Number
Street
City
State
Zip
Created_Date
Modified_Date

Table: Phone_Information
Primary Key: Phome_Information_ID
Foreign Key: User_ID, Phone_Type_ID
Fields:
Phone_Information_ID
User_ID
Area_Code
Phone_Number
Phone_Type_ID
Created_Date
Modified_Date

Table: Phone_Type_Information
Primary Key: Phone_Type_ID
Fields:
Phone_Type_ID
Phone_Type_Description




Here is the form I am wanting to use to search for the above values:




<form action="admin_search.php" method="post" name="member_edit" id="member_edit">
<br />
<table width="96%" border="0" align="center" cellpadding="1" cellspacing="1">
<tr>
<td width="28%">User ID:</td>
<td width="72%"><input name="User_ID" type="text" id="User_ID" size="15" maxlength="100" /></td>
</tr>

<tr>
<td>Username:</td>
<td><input name="Username" type="text" id="Username" size="30" maxlength="86" /></td>
</tr>
</table>
<br />
<br />
<table width="96%" border="0" align="center" cellpadding="1" cellspacing="1">
<tr>
<td width="28%">First Name:</td>
<td width="72%"><input name="First_Name" type="text" id="First_Name" size="30" maxlength="100" /></td>
</tr>
<tr>
<td>Last Name:</td>
<td><input name="Last_Name" type="text" id="Last_Name" size="30" maxlength="86" /></td>
</tr>
<tr>
<td>E-Mail Address: </td>
<td><input name="EMail" type="text" id="EMail" size="30" maxlength="100" /></td>
</tr>
<tr>
<td>Phone Number: </td>
<td><input name="Area_Code" type="text" id="Area_Code" size="3" maxlength="3" />
-
<input name="Phone_Number" type="text" id="Phone_Number" size="7" maxlength="7" /></td>
</tr>

<tr>
<td>Birthday (YYYY/MM/DD):</td>
<td><input name="Birthday_Year" type="text" id="Birthday_Year" size="4" maxlength="4" />
-
<input name="Birthday_Month" type="text" id="Birthday_Month" size="2" maxlength="2" />
-
<input name="Birthday_Day" type="text" id="Birthday_Day" size="2" maxlength="2" /></td>
</tr>
</table>
<br />
<table width="96%" border="0" align="center" cellpadding="1" cellspacing="1">
<tr>
<td width="28%">House Number:</td>
<td width="72%"><input name="House_Number" type="text" id="House_Number" size="5" maxlength="5" /></td>
</tr>
<tr>
<td>Street:</td>
<td><input name="Street" type="text" id="Street" size="30" maxlength="100" /></td>
</tr>
<tr>
<td>City: </td>
<td><input name="City" type="text" id="City" size="30" maxlength="100" /></td>
</tr>
<tr>
<td>State: </td>
<td><input name="State_Menu" type="text" id="State_Menu" size="2" maxlength="2" /></td>
</tr>
<tr>
<td>Zip:</td>
<td><input name="Zip" type="text" id="Zip" size="5" maxlength="5" /></td>
</tr>
</table>
<br />
<br />
<table width="96%" border="0" align="center" cellpadding="1" cellspacing="1">
<tr>
<td><input name="submitok" type="submit" id="submitok" value="Search Members Database" /></td>
</tr>
</table>
</form>

My questions is, how do I write a query that will search the above tables for the information submitted in the form, being the information is contained in multiple tables? I am reading about unions, but they are not working for me. I am also reading about joins, but how do I write a join from dynamic data?

I am using MYSQL version 5.0.19, and I am using PHP 5.1.2 to access the data. Thanks.

Daminious

[Sid3335]

I'm no expert on sql but if you want to access the Customer_Information and the Address_information can't you just do:


SELECT Customer_Information.First_Name, Address_information.House_Number FROM
Customer_Information, Address_information
WHERE
Address_information.User_ID = Customer_Information .User_ID


or is this inefficient.

[Daminious]

I guess my problem is the dynamic nature of my request.

I want the user to pick what they want to search for, and in any amount they wish to search for. If they want to search for the house number and the username, it does for both of those, and not anything else. If the user wants to search for Username, E-mail address, phone number, and street number, it does just for these items. I do not know how to do this without building specific queries for each possibility, something I do not want to do, and should not need to.

I can make a specific query that joins tables and such, its the dynamic nature that I an confused with. Thanks.

Daminious

[russell]

dynamically create your query in php (the following code is sample and not exactly match your form)

<?
$uid = $_POST("user_id");
$lname = $_POST("name");

if(strlen($uid)) {
$sql = "select * from tableNameHere where uid = " . $uid;
}

if(strlen($name )) {
$sql = "select * from tableNameHere where lname= " . $lname ;
}
//etc..
?>
you can build on it as much as needed by appending more to $sql based on the post data. If modifying the WHERE clause, a useful technique is to SELECT * FROM tableName WHERE 1=1 then append AND conditions to the query.

[Daminious]

I had a specific query for each item I was searching for, I tried to combine them in a union, it failed.

I tried to append them to a larger query, that failed as well. Half the time it gave the wrong results.

This is the code I had when I tried the Union method, I never got it to display data in PHP, in phpmyadmin, it works half the time, the other half if displayed wrong data.


$Query_Array; //Set the Query Array, it will hold the form's post values of the fields the user wants to search for

if ($_POST['User_ID'] != "") //If User ID is not blank, add it to the Query Array
{
$User_ID = $_POST['User_ID'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM customer_authorization WHERE User_ID = '$User_ID'";
}

if ($_POST['Username'] != "") //If Username is not blank, add it to the Query Array
{
$Username = $_POST['Username'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM customer_authorization WHERE Username = '$Username'";
}

if ($_POST['First_Name'] != "") //If First_Name is not blank, add it to the Query Array
{
$First_Name = $_POST['First_Name'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM customer_information WHERE First_Name = '$First_Name'";
}

if ($_POST['Last_Name'] != "") //If Last_Name is not blank, add it to the Query Array
{
$Last_Name = $_POST['Last_Name'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM customer_information WHERE Last_Name = '$Last_Name'";
}

if ($_POST['EMail'] != "") //If EMail is not blank, add it to the Query Array
{
$EMail = $_POST['EMail'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM customer_authorization WHERE EMail = '$EMail'";
}

if ($_POST['Area_Code'] != "") //If Area_Code is not blank, add it to the Query Array
{
$Area_Code = $_POST['Area_Code'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM phone_information WHERE Area_Code = '$Area_Code'";
}

if ($_POST['Phone_Number'] != "") //If Phone_Number is not blank, add it to the Query Array
{
$Phone_Number = $_POST['Phone_Number'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM phone_information WHERE Phone_Number = $Phone_Number";
}

//If All Birthday Fields are not blank, combine them and add to Query Array
if ($_POST['Birthday_Year'] != "" and $_POST['Birthday_Month'] != "" and $_POST['Birthday_Day'] != "")
{
$Birthday = $_POST[Birthday_Year] . "-" . $_POST[Birthday_Month] . "-" . $_POST[Birthday_Day];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM birthday WHERE Birthday = '$Birthday'";
}

if ($_POST['House_Number'] != "") //If House_Number is not blank, add it to the Query Array
{
$House_Number = $_POST['House_Number'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM address_information WHERE House_Number = '$House_Number'";
}

if ($_POST['Street'] != "") //If Street is not blank, add it to the Query Array
{
$Street = $_POST['Street'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM address_information WHERE Street = '$Street'";
}

if ($_POST['City'] != "") //If City is not blank, add it to the Query Array
{
$City = $_POST['City'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM address_information WHERE City = '$City'";
}

if ($_POST['State_Menu'] != "") //If State_Menu is not blank, add it to the Query Array
{
$State = $_POST['State_Menu'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM address_information WHERE State_Codes_ID = '$State'";
}

if ($_POST['Zip'] != "") //If Zip is not blank, add it to the Query Array
{
$Zip = $_POST['Zip'];
$Query_Array[count($Query_Array)] = "SELECT User_ID FROM address_information WHERE Zip = '$Zip'";
}


$query_count = count($Query_Array);
$main_query = "";
for($i = 0; $i < $query_count; $i++) //Loop through all queries in the array
{
if ($main_query == "")
{
$main_query = $Query_Array[$i];
}
else
{
$past = $main_query;
$main_query = $past . " UNION " . $Query_Array[$i];
}
}
$result=mysql_query($main_query);
echo ($result);
echo ($main_query);


I am still not sure how to do this. Let me know if my syntax or something else is the problem. Thanks.

Daminious

[audux]

hope this one helps

SELECT T1.flds_you_wish_to_include,
T2.flds_you_wish_to_include,
T3.flds_you_wish_to_inlude,
T4.flds_you_wish_to_include,
T5.flds_you_wish_to_include
FROM T1 INNER JOIN
T2 ON T1.userid = T2.userid INNER JOIN
T3 ON T1.userid = T3.userid INNER JOIN
T4 ON T1.userid = T4.userid INNER JOIN
T5 ON T1.userid = T5.userid
WHERE T1.userid = 'returned value' OR
T3.city = 'returned value' OR
T3.state = 'returned value' OR
T4.area_code = 'returned value' OR
T5.phone_no = 'returned value'

you may that one,
replace the tables, fields and values to meet your needs

i haven't created the table, so i haven't check it for errors, anyway this is my idea on how you could link those tables and return the records on any value you have.

i replace the name of your tables on my query to shorten my post, anyway T1 - T5 was based on the order on how you post your tables here.

may you find it useful.
good day!