First Attempt: SQL Joins and Arrays

[geudrik]

This is my first attempt at a sql join, referencing multiple tables for varing bits of data. 

 

$sid = $_GET['id'];

 

I am trying to:

* Get rental cost, station name, repo efficiency, repo station take FROM staStations WHERE solarSystemID='$sid'

* Get itemName FROM eveNames WHERE itemID='$sid'

* Reference rows: staOperationServices.operationID = staStations.operationID

* Get multiple rows of: serviceName FROM staServices WHERE serviceID = staServiceOperations.serviceID

 

I will include table structure below.

 

 

staStations

CREATE TABLE IF NOT EXISTS `staStations` (
  `stationID` int(11) NOT NULL,
  `security` smallint(6) default NULL,
  `dockingCostPerVolume` double default NULL,
  `maxShipVolumeDockable` double default NULL,
  `officeRentalCost` int(11) default NULL,
  `operationID` tinyint(3) unsigned default NULL,
  `stationTypeID` smallint(6) default NULL,
  `corporationID` int(11) default NULL,
  `solarSystemID` int(11) default NULL,
  `constellationID` int(11) default NULL,
  `regionID` int(11) default NULL,
  `stationName` text,
  `x` double default NULL,
  `y` double default NULL,
  `z` double default NULL,
  `reprocessingEfficiency` double default NULL,
  `reprocessingStationsTake` double default NULL,
  `reprocessingHangarFlag` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`stationID`),
  KEY `constellationID` (`constellationID`),
  KEY `corporationID` (`corporationID`),
  KEY `operationID` (`operationID`),
  KEY `regionID` (`regionID`),
  KEY `solarSystemID` (`solarSystemID`),
  KEY `stationTypeID` (`stationTypeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

staOperationServices

CREATE TABLE IF NOT EXISTS `staOperationServices` (
  `operationID` tinyint(3) unsigned NOT NULL,
  `serviceID` int(11) NOT NULL,
  PRIMARY KEY  (`operationID`,`serviceID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

staServices

CREATE TABLE IF NOT EXISTS `staServices` (
  `serviceID` int(11) NOT NULL,
  `serviceName` text,
  `description` text,
  PRIMARY KEY  (`serviceID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

eveNames

CREATE TABLE IF NOT EXISTS `eveNames` (
  `itemID` int(11) NOT NULL,
  `itemName` text,
  `categoryID` tinyint(3) unsigned default NULL,
  `groupID` smallint(6) default NULL,
  `typeID` smallint(6) default NULL,
  PRIMARY KEY  (`itemID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

 

Currently, I get the following output from my page...

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /xxxx/xxxxxxx/xxxxxxxxxx/tools.php/sinfo.php on line 44
Array ( )

 

Line 44 is...

while ($row = mysql_fetch_assoc($result))
{

 

Below is the entire page, with SQL Join included...

error_reporting(E_ALL);
ini_set('display_errors','On');


@define('IN_SPAAZZ', true);
include('config.php');
database(1);

// This is our system ID
$sid = $_GET['id'];

// And here it is... one giant ass sql join...
$sql = "
	SELECT
		staStations.officeRentalCost, staStations.stationName, 
		staStations.reprocessingEfficiency, staStations.reprocessingStationsTake,
		eveNames.itemName
		staServices.serviceName

	FROM
		staStations

	FULL JOIN
		eveNames, staOperationServices, staServices

	ON
		staStations.solarSystemID = '$sid', eveNames.itemID = '$sid', 
		staOperationServices.operationID = staStations.operationID,
		staServices.serviceID = staOperationServices.serviceID

	ORDER BY
		staStations.stationName

   ";


$result = mysql_query($sql);

// Array for all formatted data...
$master = array();

while ($row = mysql_fetch_assoc($result))
{
$staName         = $row['staStations.stationName'];
$staRentalCost   = $row['staStations.officeRentalCost'];
$staRepoEffic    = $row['staStations.reprocessingEfficiency'];
$staRepoTake     = $row['staStations.reprocessingStationsTake'];

$eveSystemName   = $row['eveNames.itemName'];

$staServiceNames = array($row['staServices.serviceName']);

foreach($row as $key)
{
	array_push($master, $staName, $staRentalCost, $staRepoEffic, $staRepoTake, $eveSystemName, $staServiceNames);
}
}

print_r($master);







database(0);

 

Where am I going wrong with my join or is it something stupid in the actual PHP that I'm missing?  For referencing this many tables at once, should I be using a different method of retrieving data, say from one table at a time?  (note: I tried that, and it's a pain...)

 

Thanks!

[ignace]

if ($result = mysql_query($sql)) {

 

This makes sure the query succeeded. Your query on the other hand is wrong and so is:

 

	$staName = $row['staStations.stationName'];

 

Which should be simply:

 

	$staName = $row['stationName'];

[kickstart]

Hi

 

You are also missing a comma after eveNames.itemName in the list of columns you are selecting.

 

Also Full joins are not supported ( I think), and Mysql will just treat FULL as being an alias for the table staStations.

 

You ON clauses appear muddled, and they should be seperated with ANDs or ORs like a WHERE clause, not with commas.

 

All the best

 

Keith

[geudrik]

Alright, tried again.  Put $result into the IF, and my else { mysql_error(); }

 

Updated my query, using AND's, added in missing semi colon.  Still getting the same error: mysql_fetch_assoc() is not a valid res

 

 

Updated Code...

<?php

error_reporting(E_ALL);
ini_set('display_errors','On');


@define('IN_SPAAZZ', true);
include('config.php');
database(1);

// This is our system ID
$sid = $_GET['id'];

// And here it is... one giant ass sql join...
$sql = "
	SELECT
		staStations.officeRentalCost, staStations.stationName, 
		staStations.reprocessingEfficiency, staStations.reprocessingStationsTake,
		eveNames.itemName,
		staServices.serviceName

	FROM
		staStations

	FULL JOIN
		eveNames, staOperationServices, staServices

	ON
		staStations.solarSystemID = '$sid' 
		AND eveNames.itemID = '$sid'  
		AND staOperationServices.operationID = staStations.operationID
		AND staServices.serviceID = staOperationServices.serviceID

	ORDER BY
		staStations.stationName

   ";


if($result = mysql_query($sql)) {} else { mysql_error(); }

// Array for all formatted data...
$master = array();

while ($row = mysql_fetch_assoc($result))
{
$staName         = $row['stationName'];
$staRentalCost   = $row['officeRentalCost'];
$staRepoEffic    = $row['reprocessingEfficiency'];
$staRepoTake     = $row['reprocessingStationsTake'];

$eveSystemName   = $row['itemName'];

$staServiceNames = array($row['serviceName']);

foreach($row as $key)
{
	array_push($master, $staName, $staRentalCost, $staRepoEffic, $staRepoTake, $eveSystemName, $staServiceNames);
}
}

print_r($master);







database(0);
?>

[ignace]

When I said:

 

if ($result = mysql_query($sql)) {

 

I meant:

 

if ($result = mysql_query($sql)) {
    while ($row = mysql_fetch_assoc($result)) {

 

Copy and paste your query and paste it in phpMyAdmin a join takes one table not multiple

[geudrik]

Shoot, didn't know you could only join one additional table.  Guess it's back to the drawing board to write a few more queries

[ignace]

Shoot, didn't know you could only join one additional table.  Guess it's back to the drawing board to write a few more queries

 

Have you tried executing your query in phpMyAdmin or any other db interface?