[RESOLVED] Look at my join please

phpsupergod

I've been screwing around with this now for hours and I keep getting the same error. I know it is basically a join issue. I just need a new set of eyes.

$result = mysql_query("SELECT states.state,colleges.state_id
			FROM states,colleges
			WHERE states.id=colleges.state_id and
		    colleges.state_id={$_GET['cid']}");

And the error I'm getting is
mysql_fetch_array(): supplied argument is not a valid MySQL result resource in

I'm using php version 4.4.6 and mysql 4.1.22

cgraz

Have you tried mysql_error()? Is it outputting anything?

phpsupergod

cgraz wrote:
Have you tried mysql_error()? Is it outputting anything?

Yes, I have, It has given me "query was empty" error.

cgraz

Is $_GET['cid'] set? What does this print?

<?php

$sql = "SELECT states.state
	  , colleges.state_id
	 FROM states
	   , colleges 
	WHERE states.id = colleges.state_id 
	   AND colleges.state_id = {$_GET['cid']}";

print $sql;

$result = mysql_query($sql) or die(mysql_error());

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

}

?>

(just up to the print line). The rest is to make sure on mysql_fetch_array() you've got $result as your parameter.

phpsupergod

SELECT states.state , colleges.state_id FROM states , colleges WHERE states.id = colleges.state_id AND colleges.state_id =

Thats what I get.

I think, I'm setting a wrong join...

cgraz

The join isn't the problem. Look at what the code I gave you outputted. There is no value for $_GET['cid'].

phpsupergod

Hmmm. Basically what I want to do is have a list of all states displayed. when someone clicks on any particular state, it will pass the query string to the url and display college information for that state. It is essentially a college directory.

essentially the navigation of the site would be something like

/states
/states/colleges/
/states/colleges/info
etc..

Sorry, it's been about 2 years (long story) since I have touched php/mysql ..and I'm really really rusty. Really appreciate the help.

cgraz

Here's an example. You'll have to modify this to suit your needs.

List states

$query = mysql_query("SELECT id, state FROM states ORDER BY state");

while( $row = mysql_fetch_object( $query ) )
{
	print '<a href="stateDetail.php?stateID=' . $row->id . '">' . $row->State . "</a><br />\n";
}

stateDetail.php (listing of colleges in that state)

if( !isset( $_GET['stateID'] ) )
{
	print 'No State Selected';
}
else
{
	$id = (int) $_GET['stateID'];

// Substitute c.name for whatever the name of your field is in your table that holds 
// The name of the college
$query = mysql_query("
		SELECT c.id
		  , c.name 
		FROM states AS s 
		INNER
		   JOIN colleges AS c 
		   ON c.state_id = s.id 
		WHERE s.id = " . $id . "
		ORDER
		   BY c.name"
);

if( mysql_num_rows($query) == 0 )
{
	print 'No Colleges listed in this state';
}
else
{
	while( $row = mysql_fetch_object( $query ) )
	{
		print '<a href="collegeDetail.php?collegeID=' . $row->id . '">' . $row->name . "</a><br />\n";
	}
}
}

you can make collegeDetail.php based on these examples.

None of this is tested and I likely made a mistake somewhere. Just sift through it and play around - you should get the general concept from this.

phpsupergod

Wow, you rock. The code was almost perfect you only forgot one closing brace!

Thanks a lot, really appreciate it.