PHP and SQL Query - not sure if join should be used

[Drabin]

Here is the code:

$query = "SELECT fab_loot.name, 
		  fab_loot.class, fab_loot.raids_att, fab_loot.raid_bwl, 
		  fab_loot.raid_mc, fab_loot.raid_zg, fab_loot.raid_other FROM `".FABLOOT_MEMBERS."`, 
		  fab_items.name, fab_items.raid_bwl_nonepic_item, fab_items.raid_bwl_epic_item, 
		  fab_items.raid_mc_nonepic_item, fab_items.raid_mc_epic_item,
		  fab_items.raid_zg_coin, fab_items.raid_zg_bijou, fab_items.raid_zg_primal, fab_items.raid_zg_nonepic_item,
		  fab_items.raid_zg_epic_item, fab_items.raid_other_item, fab_items.total_items FROM `".FABLOOT_ITEMS."` WHERE fab_loot.name = fab_items.name";

// Add custom primary and secondary ORDER BY definitions
// Add custom primary and secondary ORDER BY definitions
if (isset($_GET['s'])) {
	$switchString = ($_GET['s'])?$_GET['s']:'';
} else {
	$switchString = '';
}
switch ($switchString) {
	case 'name':
		$query .= " ORDER BY fab_loot.name ASC";
		break;
	case 'class':
		$query .= " ORDER BY fab_loot.class ASC, name ASC";
		break;
	case 'raids_att':
		$query .= " ORDER BY fab_loot.raids_att DESC";
		break;
	case 'raid_zg':
		$query .= " ORDER BY fab_loot.raid_zg DESC";
		break;
	case 'raid_zg_coin':
		$query .= " ORDER BY fab_items.raid_zg_coin ASC";
		break;
	case 'raid_zg_bijou':
		$query .= " ORDER BY fab_items.raid_zg_bijou ASC";
		break;
	case 'raid_zg_primal':
		$query .= " ORDER BY fab_items.raid_zg_primal ASC";
		break;
	case 'raid_zg_nonepic_item':
		$query .= " ORDER BY fab_items.raid_zg_nonepic_item ASC";
		break;
	case 'raid_zg_epic_item':
		$query .= " ORDER BY fab_items.raid_zg_epic_item ASC";
		break;
	case 'total_items':
		$query .= " ORDER BY fab_items.total_items DESC";
		break;
	default:
		$query .= " ORDER BY fab_loot.name ASC";
}

$result = mysql_query($query) or die(__LINE__.":".mysql_error());
if ($sqldebug) {
	print ("<!--$query-->");
}

The error I'm having is:

58:You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `fab_items` WHERE fab_loot.name = fab_items.name ORDE

All I'm trying to do is grab information from both tables and order them by name. However, I'm trying to grab "all" of the information. So, I may be doing something incorrect.

For instance:

Let's say I want to grab all names and items from table1 and all names and items from table2 but list them based on what names are matched to them, how would I do this?

Table1:

Joe
John
Mary

Item1
Item2
Item3

Table 2:

Joe
John
Mary

Item 4
Item 5
Item 6

List them as:

Joe Item 1 Item 4
John Item 2 Item 5
Mary Item 3 Item 6

etc.

[devinemke]

your SQL syntax is incorrect. why not simple do:

SELECT * FROM fab_loot, fab_items WHERE fab_loot.name = fab_items.name

[Drabin]

Okay that works great! Thanks.

Now for another question.

After modifying my sql and working out the kinks in the two tables, I'm having a small sort issue which probably is easy to some folks, but I'm having some problems with it.

Here's the full code for the list page:

<?php

// Establish our connection and select our database
$link = mysql_connect($db_host, $db_user, $db_passwd) or die ("Could not connect to desired database.");
mysql_select_db($db_name) or die ("Could not select desired database.");

$query = "SELECT * FROM fab_loot, fab_items WHERE fab_loot.name = fab_items.name";

// Add custom primary and secondary ORDER BY definitions
// Add custom primary and secondary ORDER BY definitions
if (isset($_GET['s'])) {
	$switchString = ($_GET['s'])?$_GET['s']:'';
} else {
	$switchString = '';
}
switch ($switchString) {
	case 'name':
		$query .= " ORDER BY fab_loot.name ASC";
		break;
	case 'class':
		$query .= " ORDER BY fab_loot.class ASC, name ASC";
		break;
	case 'raids_att':
		$query .= " ORDER BY fab_loot.raids_att DESC";
		break;
	case 'raid_zg':
		$query .= " ORDER BY fab_loot.raid_zg DESC";
		break;
	case 'raid_zg_coin':
		$query .= " ORDER BY fab_items.raid_zg_coin ASC";
		break;
	case 'raid_zg_bijou':
		$query .= " ORDER BY fab_items.raid_zg_bijou ASC";
		break;
	case 'raid_zg_primal':
		$query .= " ORDER BY fab_items.raid_zg_primal ASC";
		break;
	case 'raid_zg_nonepic_item':
		$query .= " ORDER BY fab_items.raid_zg_nonepic_item ASC";
		break;
	case 'raid_zg_epic_item':
		$query .= " ORDER BY fab_items.raid_zg_epic_item ASC";
		break;
	case 'total_items':
		$query .= " ORDER BY fab_items.total_items DESC";
		break;
	default:
		$query .= " ORDER BY fab_loot.name ASC";
}

$result = mysql_query($query) or die(__LINE__.":".mysql_error());
if ($sqldebug) {
	print ("<!--$query-->");
}

$tableHeader = '<table cellpadding="2" cellspacing="2" class="membersList">';
$borderTop = '<tr><th colspan="9" class="rankbordertop"><span class="rankbordertopleft"></span><span class="rankbordertopright"></span></th></tr>';
$tableHeaderRow = '<tr>
	<th class="rankbordercenterleft"><div class="membersHeader"><a href="?s=name">'.$wordings[$roster_lang]['name'].'</a></div></th>
	<th class="membersHeader"><a href="?s=class">'.$wordings[$roster_lang]['class'].'</a></th>
	<th class="membersHeader"><a href="?s=raids_att">'.$wordings[$roster_lang]['raids_att'].'</a></th>
	<th class="membersHeader"><a href="?s=raid_zg">'.$wordings[$roster_lang]['raid_zg'].'</a></th>
	<th class="membersHeader"><a href="?s=raid_zg_coin">'.$wordings[$roster_lang]['raid_zg_coin'].'</a></th>
	<th class="membersHeader"><a href="?s=raid_zg_bijou">'.$wordings[$roster_lang]['raid_zg_bijou'].'</a></th>
	<th class="membersHeader"><a href="?s=raid_zg_primal">'.$wordings[$roster_lang]['raid_zg_primal'].'</a></th>
	<th class="membersHeader"><a href="?s=raid_zg_nonepic_item">'.$wordings[$roster_lang]['raid_zg_nonepic_item'].'</a></th>
	<th class="membersHeader"><a href="?s=raid_zg_epic_item">'.$wordings[$roster_lang]['raid_zg_epic_item'].'</a></th>
	<th class="rankbordercenterright"><div class="membersHeaderRight"><a href="?s=total_items">'.$wordings[$roster_lang]['total_items'].'</a></div></th>
</tr>';
$borderBottom = '<tr><th colspan="9" class="rankborderbot"><span class="rankborderbotleft"></span><span class="rankborderbotright"></span></th></tr>';
$tableFooter = '</table>';

print($tableHeader);

// Counter for row striping
$striping_counter = 0;
if (isset($_GET['s'])) {
	$current_sorting = $_GET['s'];
} else {
	$current_sorting = '';
}

$last_value = '';
while($row = mysql_fetch_array($result)) {
	// Adding grouping dividers
	if ($current_sorting == 'class') {
		if ($last_value != $row['class']) {
			if ($striping_counter != 0) {
				print($borderBottom);
			}
			print('<tr><th colspan="8" class="membersGroup" id="'.$current_sorting.'-'.$row['class'].'">'.$row['class']."</th></tr>\n");
			print ($borderTop);
			print ($tableHeaderRow);
			$striping_counter = 0;
		}
		$last_value = $row['class'];
	} else {
		if ($striping_counter == 0) {
			print($borderTop);
			print($tableHeaderRow);
		}
	}


// Striping rows
	print('<tr>');

// Increment counter so rows are colored alternately
	++$striping_counter;

// Echoing cells w/ data
	print('<td class="rankbordercenterleft"><div class="membersRow'. (($striping_counter % 2) +1) .'">');
	if ($row['server']) {
		print('<a href="char.php?name='.$row['name'].'&amp;server='.$row['server'].'">'.$row['name'].'</a>');
	} else {
		print($row['name']);
	}
	print('</div></td>');
	print('<td class="membersRow'. (($striping_counter % 2) +1) .'">'.$row['class'].'</td>');
	print('<td class="membersRow'. (($striping_counter % 2) +1) .'">'.$row['raids_att'].'</td>');
	if ($row['raid_zg']) {
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">'.$row['raid_zg'].'</td>');
	} else {
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">&nbsp;</td>');
	}
	if ($row['raid_zg_coin']) {
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">'.$row['raid_zg_coin'].'</td>');
	} else {
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">&nbsp;</td>');
	}
	if($row['raid_zg_bijou']) { 
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">'.$row['raid_zg_bijou'].'</td>');
	} else { 
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">&nbsp;</td>');
	}
	if ($row['raid_zg_primal']) {
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">'.$row['raid_zg_primal'].'</td>');
	} else {
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">&nbsp;</td>');
	}
	if ($row['raid_zg_nonepic_item']) {
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">'.$row['raid_zg_nonepic_item'].'</td>');
	} else {
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">&nbsp;</td>');
	}
		if ($row['raid_zg_epic_item']) {
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">'.$row['raid_zg_epic_item'].'</td>');
	} else {
		print('<td class="membersRow'. (($striping_counter % 2) +1) .'">&nbsp;</td>');
	}
	print('<td class="rankbordercenterright"><div class="membersRowRight'. (($striping_counter % 2) +1) .'">'.$row['total_items'].'</div></td>');
	print("</tr>\n");
}
print($borderBottom);
print($tableFooter);
mysql_free_result($result);
?>

Here's the issue I'm having.

Using the example of "Joe"

In Table 1:

Joe
1
1
1

In table 2:

Joe
item 1
item 2
item 3

Joe
item 4
item 5
item 6

When the page is read it does the following:

Joe 1 1 1 Item 1 item 2 Item 3
Joe 1 1 1 Item 4 item 5 item 6

However, I want it to appear like this:

Joe 1 1 1 Item 1 Item 2 Item 3
Item 4 Item 5 Item 6

Where the name is only shown once, the data in table 1 is only shown once, and it sorts the data from table 2 in the same column as the other items from the same name.