Selecting 2 columns from multiple tables.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
brownfreelance
Forum Newbie
Posts: 1
Joined: Sat May 29, 2010 7:50 pm

Selecting 2 columns from multiple tables.

Post by brownfreelance »

Hello,

I have 6 tables that have the same structure. (ie. each table has country, abbrev and filename, the columns i want). I'm trying to set up a form that will search through and return all results into a sing UL. Right now I have:

Code: Select all

$str = $_REQUEST["str"];
//
$sql = "SELECT 'country', 'filename' FROM europe, asia, africa, northamerica, southamerica, oceania WHERE '*.country' LIKE '%".$str."%' or '*.abbrev' LIKE '%".$str."%'";
//
$res = mysqli_query($link, $sql);
if($res) {
	print "Things and stuff.";
	print "<ul id=\"countryList\">";
	while($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
		$name = $newArray["country"];
		$file = $newArray["filename"];
		print "<li><img src=\"/Flags/".$file."\" width=\"24\" height=\"24\" align=\"absmiddle\" />&nbsp;&nbsp;".$name."</li>";
	}
	print "</ul>";
} else {
	printf("error:%s\n", $mysqli_error($link));	
}
mysqli_close($link);
The above code returns no errors but returns no list items as well. I'm new to databases so... What am I doing wrong.

Thanks in advance for any help,
Scott.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Selecting 2 columns from multiple tables.

Post by mikosiko »

brownfreelance wrote:I have 6 tables that have the same structure
brownfreelance wrote:I'm new to databases so... What am I doing wrong
Just to start.... your database design..... why 6 tables if all of them have the same structure?... I will have only one table adding an additional field to properly qualify the countries .... and associate a reference table for that field..... that will simplify your queries in the future. One alternative to the second table is denormalization and include the qualification field values directly in the country table ...

in the code that your show your SELECT have several errors,,, you should read a little more about how to use this sentence.

hope this help
Post Reply