[HELP] Searching Multiple Tables/Rows/Keywords (PHP+MySQL)

[iTpHo3NiX]

Ok so I'm working on a project for work and I'm stuck

Here's what I got:

login.php

Code: (php) [Select]

<?php

    // DB Connection
    require("common.php");
    
    // This variable will be used to re-display the user's username to them in the
    // login form if they fail to enter the correct password.  It is initialized here
    // to an empty value, which will be shown if the user has not submitted the form.
    $submitted_username = '';
    
    // This if statement checks to determine whether the login form has been submitted
    // If it has, then the login code is run, otherwise the form is displayed
    if(!empty($_POST))
    {
        // This query retrieves the user's information from the database using
        // their username.
        $query = "
            SELECT
                id,
                username,
                password,
firstname,
lastname,
phone,
email,
                salt
            FROM users
            WHERE
                username = :username
        ";
        
        $query_params = array(
            ':username' => $_POST['username']
        );
        
        try
        {
            $stmt = $db->prepare($query);
            $result = $stmt->execute($query_params);
        }
        catch(PDOException $ex)
        { 
            die("Failed to run query: " . $ex->getMessage());
        }
        
        // This variable tells us whether the user has successfully logged in or not.
        // We initialize it to false, assuming they have not.
        // If we determine that they have entered the right details, then we switch it to true.
        $login_ok = false;
        
        $row = $stmt->fetch();
        if($row)
        {
            // Using the password submitted by the user and the salt stored in the database,
            // we now check to see whether the passwords match by hashing the submitted password
            // and comparing it to the hashed version already stored in the database.
            $check_password = hash('sha256', $_POST['password'] . $row['salt']);
            if($check_password === $row['password'])
            {
                // If they do, then flip this to true
                $login_ok = true;
            }
        }
        
        // If the user logged in successfully, then we send them to the members page
        // Otherwise, display a login failed message and show the login form again
        if($login_ok)
        {
            // Preparing to store the $row array into the $_SESSION by
            // removing the salt and password values from it.
            unset($row['salt']);
            unset($row['password']);
            
            // This stores the user's data into the session at the index 'user'.
            // We will check this index on the members page to determine whether
            // or not the user is logged in.  We can also use it to retrieve
            // the user's details.
            $_SESSION['user'] = $row;
            
            // Redirect the user to the members page.
            header("Location: members.php");
            die("Redirecting to: members.php");
        }
        else
        {
            // Tell the user they failed
            print("Login Failed.");
            
            // Show them their username again so all they have to do is enter a new
            // password.  The use of htmlentities prevents XSS attacks.
            $submitted_username = htmlentities($_POST['username'], ENT_QUOTES, 'UTF-8');
        }
    }
    
?>
<h1>Login</h1>
<form action="login.php" method="post">
    Employee ID:<br />
    <input type="text" name="username" value="<?php echo $submitted_username; ?>" />
    <br /><br />
    Password:<br />
    <input type="password" name="password" value="" />
    <br /><br />
    <input type="submit" value="Login" />
</form>
common.php

Code: (php) [Select]

<?php

    $username = "asdf";
    $password = "asdf";
    $host = "localhost";
    $dbname = "asdf";

    $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');

    try
    {
        $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options);
    }
    catch(PDOException $ex)
    {
        die("Failed to connect to the database: " . $ex->getMessage());
    }

    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    if(function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc())
    {
        function undo_magic_quotes_gpc(&$array)
        {
            foreach($array as &$value)
            {
                if(is_array($value))
                {
                    undo_magic_quotes_gpc($value);
                }
                else
                {
                    $value = stripslashes($value);
                }
            }
        }
    
        undo_magic_quotes_gpc($_POST);
        undo_magic_quotes_gpc($_GET);
        undo_magic_quotes_gpc($_COOKIE);
    }

    header('Content-Type: text/html; charset=utf-8');

    session_start();
members.php

Code: (php) [Select]

<?php

    // First we execute our common code to connection to the database and start the session
    require("common.php");
    // At the top of the page we check to see whether the user is logged in or not
    if(empty($_SESSION['user']))
    {
        // If they are not, we redirect them to the login page.
        header("Location: login.php");
        
        // Remember that this die statement is absolutely critical.  Without it,
        // people can view your members-only content without logging in.
        die("Redirecting to login.php");
    }
    
    // Everything below this point in the file is secured by the login system

?>
Hello <?php echo htmlentities($_SESSION['user']['firstname'], ENT_QUOTES, 'UTF-8'); ?> <?php echo htmlentities($_SESSION['user']['lastname'], ENT_QUOTES, 'UTF-8'); ?>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Employee ID:&nbsp;<?php echo htmlentities($_SESSION['user']['username'], ENT_QUOTES, 'UTF-8'); ?>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="logout.php">Logout</a><br />
<hr>
<center><h1>Add an appointment</h1></center>
<form action="addapp.php" method="post">
    First Name:<br />
    <input type="text" name="appfname" value="" />
    <br /><br />
    Last Name:<br />
    <input type="text" name="applname" value="" />
    <br /><br />
    Phone:<br />
    <input type="text" name="appphone" value="" />
    <br /><br />
    Company:<br />
    <input type="text" name="appcompany" value="" />
    <br /><br />
    Date <font color=#FF0000>(MM/DD/YYYY)</font>:<br />
<input type="date" name="appdate" value="" />
    <br /><br />
    Does this user have the GMP, Confidentiality and Visitor Agreements signed?<br>
<input type="radio" name="appagree" value="yes">Yes
<input type="radio" name="appagree" value="no">No
    <br /><br />
Requests:<br />
<input type="text" name="apprequest" value="Send up/call on arrival">
    <br /><br />
Please Verify Your Employee ID:<br />
<input type="text" name="employeeid" value="<?php echo htmlentities($_SESSION['user']['username'], ENT_QUOTES, 'UTF-8'); ?>" />
    <input type="submit" value="Add" />
</form>
addapp.php

Code: (php) [Select]

<?php

    // First we execute our common code to connection to the database and start the session
    require("common.php");
    
    // This if statement checks to determine whether the registration form has been submitted
    // If it has, then the registration code is run, otherwise the form is displayed
    if(!empty($_POST))
    {
        // Ensure that the user has entered a non-empty First Name
        if(empty($_POST['appfname']))
        {
            die("Please enter a First Name.");
        }
        
        // Ensure that the user has entered a non-empty Last Name
        if(empty($_POST['applname']))
        {
            die("Please enter a Last Name.");
        }
// Ensure that the user has entered a non-empty Phone Number
if(empty($_POST['appphone']))
        {
            die("Please enter a Phone Number.");
        }
// Ensure that the user has entered a non-empty Date
if(empty($_POST['appdate']))
        {
            die("Please enter a Date.");
        }
// Ensure that the user has entered their employee ID
if(empty($_POST['employeeid']))
{
die("Please enter your Employee ID.");
}
        
        // This query is used to create a new user record in the database table.
        // Again, we are using special tokens (technically called parameters) to
        // protect against SQL injection attacks.
        $query = "
            INSERT INTO appointments (
                appfname,
                applname,
appphone,
appcompany,
appdate,
appagree,
                apprequest,
employeeid
            ) VALUES (
                :appfname,
                :applname,
:appphone,
:appcompany,
:appdate,
:appagree,
                :apprequest,
:employeeid
            )
        ";
        
        // Here we prepare our tokens for insertion into the SQL query.
        $query_params = array(
            ':appfname' => $_POST['appfname'],
            ':applname' => $_POST['applname'],
':appphone' => $_POST['appphone'],
':appcompany' => $_POST['appcompany'],
':appdate' => $_POST['appdate'],
':appagree' => $_POST['appagree'],
            ':apprequest' => $_POST['apprequest'],
':employeeid' => $_POST['employeeid']
        );
        
        try
        {
            // Execute the query to create the user
            $stmt = $db->prepare($query);
            $result = $stmt->execute($query_params);
        }
        catch(PDOException $ex)
        {
            die("Failed to run query: " . $ex->getMessage());
        }
        
        // This redirects the user back to the login page after they add an appointment
        header("Location: members.php");
        
        // Calling die or exit after performing a redirect using the header function
        // is critical.  The rest of your PHP script will continue to execute and
        // will be sent to the user if you do not die or exit.
        die("Redirecting to members.php");
    }
    
?>
My MySQL Tables:

appointments

Code: (mysql) [Select]

CREATE TABLE IF NOT EXISTS `appointments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `appfname` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `applname` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `appphone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `appcompany` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `appdate` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `appagree` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `apprequest` char(255) COLLATE utf8_unicode_ci NOT NULL,
  `employeeid` char(20) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `appphone` (`appphone`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
users

Code: (mysql) [Select]

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `firstname` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `lastname` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` char(64) COLLATE utf8_unicode_ci NOT NULL,
  `salt` char(16) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Ok so what I want to accomplish is to set up a search form where my officers have a search.php that is just a form that asks for the First and Last name of the appointment (appfname, applname) and then display all of the appointments information (appfname, applname, appphone, appcompany, appdate, appagree, apprequest) and then also display the user that requested the appointment which is why the members appointment includes their employeeid which is actually their username so I can search the users table and display their contact information.

So pretty much I search and it displays their information like the following:

Appointment Information:
FirstName LastName
Company
Phone#
Date

Employee Requesting Appointment Information:
FirstName LastName
Phone#
EmployeeID (username value in the users table)

So I need to search and display information from 2 tables in a single database and all the rows within that database from a search of 2 of the keywords from (table appointments) row=['appfname']; and row=['applname'] and then the employee id associate with that appointment row=['employeeid'] and then row=['employeeid'] I can use to search the users table for row=['username'] which will be the same value...

I'm not even sure how to go about doing this.

[RedBullAddicted]

I am not a sql expert but as far as I know you can use a simple join in you select statement and join the tables on the employeeid/username or you can create a view (thats what I would prefer). Something like:

Code: (mysql) [Select]

CREATE VIEW app_employee_view AS
SELECT appointments.id, appointments.appfname ..., users.id, users.username ...
FROM appointments
INNER JOIN users
ON appointments.employeeid=users.username

After you create that view you can query it the way you query all other tables. If this is not what you were asking for, sorry.. lol. I just woke up and my brain is still sleeping

Cheers,
RBA[/code]

[Schalla]

Code: [Select]

SELECT * FROM appointments app LEFT JOIN users usr ON app.employeeid=usr.id WHERE app.appfname LIKE '%<VALUE>%' AND app.afflname LIKE '%<VALUE>%'

Maybe you want to use instead of like, equal (=), that's your choice. However, your database/column naming is not really ideal.


I would suggest to use always singular and also use prefix.


e.g. table user


user_id, user_name and so on.


The table appointment got then not employeeid, but instead user_id, since it is a direct relation.

[iTpHo3NiX]

Code: [Select]

SELECT * FROM appointments app LEFT JOIN users usr ON app.employeeid=usr.id WHERE app.appfname LIKE '%<VALUE>%' AND app.afflname LIKE '%<VALUE>%'

I'm confused with this code ?.?

I made a few changes (ie db is now named employees, the other appointments)

@RBA
So that code preps the database correct? Now how would I search the table for first and last name of the appointment in order to display that so kind of how the code would work in my brain:

search.php (display)

Please Enter
First Name: [INPUTBOX]
Last Name: [INPUTBOX]
Search

^This is the first and last name of the person that is visiting that the employee added^

And then it will search the columns to ensure that appointments.appfname and appointments.applname are in the same row and then dump the appointments.employeeid from the same row into a variable that then queries the employees database to pull the requesting employee then display the employees information stored in the db and then the information from the appointment information

Hope that makes sense... Also if there is a better suggestion in the way to go about it...

So would running this be necessary:
jointables.sql

Code: (mysql) [Select]

CREATE VIEW app_employee_view AS
SELECT appointments.id, appointments.appfname, appointments.applname, appointments.appphone, appointments.appcompany, appointments.appdate, appointments.appagree, appointments.apprequest, appointments.employeeid, employees.id, employees.employeeid, employees.firstname, employees.lastname, employees.phone, employees.email
FROM appointments
INNER JOIN employees
ON appointments.employeeid=employees.employeeid
updated table
table_employees

Code: (mysql) [Select]

CREATE TABLE IF NOT EXISTS `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employeeid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `firstname` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `lastname` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` char(64) COLLATE utf8_unicode_ci NOT NULL,
  `salt` char(16) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employeeid` (`employeeid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-----
EDIT
-----

I've been working on a search function (haven't added sqli protection just yet)

search.php

Code: (PHP) [Select]

<?PHP

    // First we execute our common code to connection to the database and start the session
    require("common.php");

    // We will use this SQL query to locate the first and last name entered in the search
    //is there
    $query = "
        SELECT
            `id`, `appfname`, `applname`, `appphone`, `employeeid`
        FROM appointments
        WHERE
            appfname='" . $_POST['appfname'] . "' IN (appfname, applname, appphone, employeeid)
        AND
            applname='" . $_POST['applname'] . "' IN (appfname, applname, appphone, employeeid)
    ";
?>

<h1>Search Appointments</h1>
<form action="search.php" method="post">
    First Name:<br />
    <input type="text" name="appfname" value="" />
    <br></br>
    Last Name:<br />
    <input type="text" name="applname" value="" />
    <br></br>
    <input type="submit" value="Search" />
</form>
After use the form I get the following PHP Notice's:

PHP Notice:  Undefined index: appfname in search.php on line 13
PHP Notice:  Undefined index: applname in search.php on line 15

Then once I get the search function working, I still need to do the results page where I can also query the requesting employee based off of the employeeid in the associated row...

Sorry le n00b at mysql with PHP xD

[Schalla]

....What's confusing with the SQL Statement above? It selects the Appointment and the connected user data using a search on the two names?

<VALUE> should be replaced with the search value, ofc. Creating a view is a total overkill for that task.......

Above the $_POST Values are empty, that's why the notices are popping up.

Code: (php) [Select]

<?php

if(!empty($_POST)){
     //Action here
}



[RedBullAddicted]

I don't think its overkill to create a view for that task. But again, I am not a sql expert. I prefer to create them to have more simple queries to create. As the view already combines the two needed tables and it provides the possibility to query it as if it was a normal table it gets a lot more easy to work with it. But thats just my opinion Regarding Performance there shouldn't be any difference.

[Schalla]

Performance wise that won't be an difference, true! Views are great for tons of tasks,
however, they don't punish bad database design.

As example, if the database would be proper designed, he could only write:

Code: [Select]

SELECT <FIELDS> FROM appointment JOIN user USING (user_id) WHERE....
Views, however, just is a way of saying "Yeahhh well, for every relation I will create a view instead of using a proper database layout". Also, that way
he doesn't learn proper SQL syntax. Wrapping every query who got a join into a view is not really
the sense of a database.

That is at least my opinion. Better design proper and use 3 words more in your query then bloating your database.

[RedBullAddicted]

Yes Schalla, I totally agree with you on this one. No matter what you do a proper and well thought out design of the database and the tables is the most important thing. I also agree with your statement that creating views for every single "query" you want to do is an overkill. In this case I guess what DeepCopy tries to do here is one of the main functionalities of his application and in that case it would be ok to create a view for it? As I said before I am not a sql expert and I tried to find some resources on the webs that can explain what you do in which case. Tbh there are a lot of discussions about that topic but it seems to me that its all a matter of taste except for the obvious things you already mentioned. Maybe you can give me some infos on when to use a view or a normal query with joins.

@DeepCopy: Guess that went a bit off topic.. sorry for that. Do you still need help on something or did you figure it out in the mean time?