[RESOLVED] Multiple tables in one SQL query.

Ark3typ3

So I've seen a lot of this:

 "SELECT t.`data`, s.`data` FROM `sqlData` t, `sqlData` s"

and I wanted to see if I could incorporate that into my sql queries....but I'm not sure how to manipulate it in PHP. I'm not even sure what this sort of query is called....

So my question is, first, what is it called? Second...how would I pull all the data and display it on my page? It seems that the standard mysql_query() and mysql_fetch_array() stuff doesn't work the same for this (I keep getting an invalid result message). Is there something special about this query?

Just getting the name of the query can at least get me on the right trail, but examples of code to manipulate this would be fantastic. As always, thanks for your help.

Ark3typ3

Ok, so I refined my searches and actually found the answer I was looking for. For future viewers, use mysql_fetch_object() instead of mysql_fetch_array()

Also, it didn't seem to have a specific name, it was just Mysql query with multiple tables.

laserlight

It looks like you are talking about a join when you say "multiple tables in one SQL query", but that should be no problem for mysql_query. As for mysql_fetch_object versus mysql_fetch_array: you should be able to use either of them. You probably merely used mysql_fetch_array incorrectly.

That said, have you considered using the MySQLi or PDO extensions instead?

bradgrafelman

Also note that there are several different types of joins available in MySQL (and many other DBMS's). The comma between two tables is analogous to a "LEFT JOIN". I personally never use the comma syntax and instead use "t1 LEFT JOIN t2 ON t1.foo=t2.bar" so that it's more apparent what is going on when reading the SQL query.

Ark3typ3

Well, it's probable that I was just using mysql_fetch_array() wrong, but here's the new one, it definitely works. The old one worked just fine using mysql_fetch_array() but I could only use one table otherwise I got a result error. It's all good in the hood now though 🆒

					
$queryB = "SELECT 
			m.typeID, 
			m.materialTypeID, 
			m.quantity, 
			t.typeName,
			eg.icon
		FROM 
			invTypeMaterials m, 
			invTypes t,
			eveGraphics eg
		WHERE 
			m.typeID = '$typeID[0]' 
		AND t.typeID = m.materialTypeID
		AND eg.graphicID = t.graphicID";			
$resultB = mysql_query($queryB);
while ($row = mysql_fetch_object($resultB)) {
	if($counter%2==0){
		echo "<tr class=rowcolor>";
	} else {
		echo "<tr>";
	}						
	echo "	<td width=16px><img src='http://website.com/eveimg/icons/16_16/icon".$row->icon.".png' /></td>
			<td><a href='javascript:CCPEVE.showMarketDetails(".$row->materialTypeID.")'>" .$row->typeName. "</a></td>
			<td>".($row->quantity * $qty)."</td>
		</tr></br>";
	$perfectBuild[] = $row->quantity * $qty;
	$counter++;
}