Ausghostdog Ausghostdog - 2 years ago 96
SQL Question

three table inner join with empty table for searching

my database is structured as below
four tables

at present the character, groups and vault tables all have data. The games tables is empty however.

Is it possible to write an inner join on the games table that will allow for a php search box to get data from the other three tables even though the games table has no data in it?

Is there another way of doing this that I might not know about?

For example I want to write a php search script for a webpage that searches on the games tables. If a user enters 'Allistair Tenpenny', it will pull the data from the characters table and display it in the search page with the characters name and their history, same with if some one searches a vault it will display the data from the vaults table.

From what I have read of inner joins the data on each joined table must match for it to display. Is there another way to approach this?

charactor table
vault table

Answer Source

No inner join is necessary to get the data you want. You can simply use php to use SQL to search the appropriate table based on the user input.

If there are multiple search fields on your page, just name the submit buttons differently, then have PHP check for the existence of each submit button's POST data from the form, then perform the appropriate search. An example form might be:

<form action="" method="post">
    Search Character Name:<input type="text" name="charactername">
    <input type="submit" name="charsubmit" value="Search">
<form action="" method="post">
    Search Vault Name:<input type="text" name="vaultname">
    <input type="submit" name="vaultsubmit" value="Search">

Your PHP code can then be structured as:

if (isset($_POST['charsubmit']))
    $stmt = $db->prepare("SELECT * FROM character_table WHERE character_name = ':mydata'");
elseif (isset($_POST['vaultsubmit']))
    $stmt = $db->prepare("SELECT * FROM vault_table WHERE vault_name = ':mydata'");


Using prepared statements like this is a good way to prevent SQL injection attacks, thus ensuring user entered data is NEVER put directly into a SQL statement.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download