在mysql的单个列中创建包含结果的视图

Question

I would like to create a view that has a single column. If my two tables looked like the following:

Table 1: ID | Name 1 | Joe

Table 2: ID | Address 1 | 123 BlueBerry St.

The view would look like: View: newcolumn | Joe 123 Blueberry St.

The reason I do this is because I have an autocomplete text box that searchs a single column with the following php / jquery code:

$query = $db->query("SELECT value FROM countries WHERE value LIKE '$queryString%' LIMIT 10");
if($query) {
    while ($result = $query ->fetch_object()) {
    echo '<li onClick="fill(\''.$result->value.'\');">'.$result->value.'</li>';
     }
     }

If there is a better way than putting all values into a single column for this solution, please let me know as well.

Answer 1

You view wouldn't be able to be searched over anyways. Do a table join and LIKE query on all the columns you want to match.

Like

SELECT value FROM table1 INNER JOIN table2 USING (id) WHERE table1.col1 LIKE '%:query%' AND table2.col1 LIKE '%:query%' LIMIT 10

And PLEASE don't use PHP variables in your query string. Use PDO instead