Minimum Spanning Tree

[wbrigg]

So i have a table of "Galaxies".

 

The columns are ID, Name, x, y, z

 

And a Table of "Wormholes"

 

The columns are ID, Start, End, Length, MST, Active

 

I already have bits of PHP to populate the Galaxies Table (it just pus random numbers into x,y,z. ID auto increments)

 

I also have PHP which fills the Wormholes table with entries such that there is a "Wormhole" from each Galaxy, to every other Galaxy. It also fills in the Length information (just pythag.)

 

 

I have this:

//Make lengths array
$query = mysql_query("SELECT ID FROM Wormholes ORDER BY length ASC");
$wh_count = mysql_num_rows($query);
$length = mysql_fetch_array($query);	
echo($length['id']);

//Print shortest WH and it's stats.
$WH = mysql_fetch_array(mysql_query('SELECT * FROM Wormholes WHERE ID='.$length[0]));
echo "Shortest Wormhole: ".$WH['ID']."<br />";
echo "Start: ".$WH['Start']."<br />";  
echo "End: ".$WH['End']."<br />";
echo "Length: ".$WH['Length']."<br />";

//Add shortest WH to the MST
$update = 'UPDATE Wormholes SET MST=1 WHERE ID='.$length[0];
$result = mysql_query($update);
$mst = array($WH['Start'],$WH['End']);

 

To find the shortest Wormhole and designate it as where my Minimum Spanning Tree will Start from. And it works fine. The problem i have is the next bit;

 

Then i wrote this bit:

$selectWHs1 = mysql_fetch_array(mysql_query('SELECT ID From Wormwholes WHERE Start ='.$mst.' && End != '.$mst.'ORDER BY Length ASC'));
$selectWHs2 =mysql_fetch_array(mysql_query( 'SELECT ID From Wormwholes WHERE Start !='.$mst.' && End = '.$mst.'ORDER BY Length ASC'));
$newshort = min($selectWHs[0],$selectWHs2[0]);
$update = 'UPDATE Wormholes SET MST=1 WHERE ID='.$newshort;
$WH = mysql_fetch_array(mysql_query('SELECT * FROM Wormholes WHERE ID='.$newshort));
$mst = array_unique(array_push($mst, $wH['Start'], $wH['End']));

 

Which i planned to repeat with until there are no Rows returned from the $selectWHs1 or $selectWHs2 queries. I want it to find rows where either the Start OR the End is in the array $mst. but i'm guessing i've gone about it the wrong way.

 

These are the errors i get:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\UG\MST.php on line 59

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\UG\MST.php on line 60

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\UG\MST.php on line 63

 

Warning: array_unique() [function.array-unique]: The argument should be an array in C:\xampp\htdocs\UG\MST.php on line 64

 

Warning: array_count_values() [function.array-count-values]: The argument should be an array in C:\xampp\htdocs\UG\MST.php on line 70

Number of entries in $mst array: Wormholes in the Minimum Spanning Tree: 1

 

I'm guessing they all stem from the problem with $selectWHs1 and $selectWHs2 not being arrays.

 

 

Help?

[BlueSkyIS]

you have to use mysql_query first to get a result resource, then use mysql_fetch_array to pull an array from it.

[wbrigg]

As in i have to write

$selectWHs1 = mysql_query('SELECT ID From Wormwholes WHERE Start ='.$mst.' && End != '.$mst.'ORDER BY Length ASC');
$selectWHs1 = mysql_fetch_array($selectWHs1);

 

 

instead of

$selectWHs1 = mysql_fetch_array(mysql_query('SELECT ID From Wormwholes WHERE Start ='.$mst.' && End != '.$mst.'ORDER BY Length ASC'));

 

 

?!

 

Really? i've done it like this before

[BlueSkyIS]

ah, didn't see that mysql_query in there. the problem is probably that mysql_query is failing. i suggest that you break stuff out so you can get any errors and debug easier:

 

$sql = "SELECT * FROM some_table";
$result = mysql_query($sql) or die(mysql_error(). " error in $sql");
$selectWHs1 = mysql_fetch_array($result);

[wbrigg]

I tried that already, it didn't make a difference. I'm almost positive it's to do with the way i've done this:

 

WHERE Start ='.$mst.' && End != '.$mst

 

To try and get all the rows where the Start and not the End are in the array $mst

[BlueSkyIS]

this error means you are trying to use mysql_fetch_array() on a failed query:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\UG\MST.php on line 59

 

if you echo out the error caused in mysql_query, it will give you a clue where the error in your SQL is.

 

$sql = 'SELECT ID From Wormwholes WHERE Start ='.$mst.' && End != '.$mst.'ORDER BY Length ASC';
$result = mysql_query($sql) or die(mysql_error(). " error in $sql");
$selectWHs1 = mysql_fetch_array($result);

 

shortcuts might look neat, but they hose you if you've got an error in the code.

[wbrigg]

it told me i had an error in the syntax of the MySQL code near "ORDER BY"

 

which is where i thought the error was. i just don't know the syntax to write what i wanna write.

[wbrigg]

I echo'd the MySQL query:

 

SELECT ID From Wormwholes WHERE Start =Array AND End != Array ORDER BY Length ASC

 

so the $mst isn't coming up as a list of entries, just stating what it is.

 

 

if i made it so the mysql was

 

SELECT ID From Wormwholes WHERE Start =$a,$b,$c,$d AND End !=$a,$b,$c,$d ORDER BY Length ASC

 

would it select all the rows where the columns where $a OR $b OR $c OR etc.

 

or would i have to make it so it printed

 

SELECT ID From Wormwholes WHERE Start =$a OR $b OR $c OR $d AND End !=$a OR $b OR $c OR $d ORDER BY Length ASC

 

Or would all the ORs and ANDs interfere with one another?

[BlueSkyIS]

it told me i had an error in the syntax of the MySQL code near "ORDER BY"

 

which is where i thought the error was. i just don't know the syntax to write what i wanna write.

 

what did the SQL look like that caused the error?

[wbrigg]

'SELECT ID From Wormwholes WHERE Start ='.$mst.' AND End != '.$mst.'ORDER BY Length ASC'

 

 

All i want to do is make it select a new "wormhole" where either the start or the end is shared with one of the current ones, and arrange them in order of length.

[BlueSkyIS]

no, i mean what is the output from this line?

 

$result = mysql_query($sql) or die(mysql_error(). " error in $sql");

[wbrigg]

Unknown column 'Array' in 'where clause' error in SELECT ID FROM Wormholes WHERE Start =Array AND End != Array ORDER BY Length ASC

[BlueSkyIS]

instead of inserting a single value into the SQL, you are inserting an array. you need to reference a specific element of the array, not the entire array. something more like...

 

$sql = "SELECT ID From Wormwholes WHERE Start ='".$WH['Start']."' AND End != '".$WH['End']."' ORDER BY Length ASC";

 

[wbrigg]

is there any way i can reference it against all the elements of the array?

 

 

i think i'm going to try adding another column and have a little bit of code which runs through all the rows changing it to a 1 if it has either, and a zero if neither or both.

 

then have WHERE specialcolumn=1 in the mysql.

[GingerRobot]

I've only scanned the above posts, but sounds like you need the IN clause. For example:

 

$array = array('tom','dick','harry');
$sql = "SELECT * FROM yourtable WHERE name IN '".join("','",$array)."' ORDER by...";

[wbrigg]

it's annoying when the answer is insultingly simple  :'(

 

is there a NOT IN version?

[BlueSkyIS]

yes, you just posted it. 

 

$array = array('tom','dick','harry');
$sql = "SELECT * FROM yourtable WHERE name NOT IN '".join("','",$array)."' ORDER by...";

[wbrigg]

$selectWHs1 = mysql_fetch_array(mysql_query('SELECT ID From Wormwholes WHERE Start IN '.$mst.' AND End NOT IN '.$mst.' ORDER BY Length ASC'));

 

That's not working though...

 

i need to be able to do both checks at the same time or it's useless to me.

[wbrigg]

okay i'm going about it a different way now;

 

$h=0;
While($h<= $galaxycount-1){
$i=0;
While($i+1<= count($mst)){
$j=$i+1;
While($j+1<= count($mst)){
	$whinmst = mysql_query('UPDATE Wormholes SET check=1 WHERE Start='.$mst[$i].' AND WHERE End!='.$mst[$j]);
	$j=$j+1;
}
$i=$i+1;
}
$WH = mysql_fetch_array(mysql_query('SELECT ID FROM Wormholes WHERE check=1 ORDER BY Length ASC'));
mysql_query('UPDATE Wormholes SET MST=1 WHERE ID='.$WH[0]);
$startend = mysql_fetch_array(mysql_query('SELECT Start,End FROM Wormholes WHERE ID='.$WH[0]));
$mst = sort(array_unique(array_push($mst, $startend['Start'], $startend['End'])));
mysql_query('UPDATE Wormholes SET check=0');
$h=$h+1;
}

 

$mst starts off as an array with the start and finish of the first WH in the Minimum spanning tree.

 

It still doesn't work though  ??? help ???