Page 1 of 1

Multiple Tables, 1 Query

Posted: Mon Aug 14, 2006 2:00 am
by waradmin
I have done reading and am a bit lost as to the syntax of the query.

I have 2 tables:

Character
----------------------------------------------
weapon_id | health
----------------------------------------------

Weapon
----------------------------------------------
weapon_id | damage_min | damage_max
----------------------------------------------

And I want to be able to get the weapon the character has, and their health, and pull up the weapon min and max damage numbers and assign them to a variable by doing $health = $row->health; $damage_min = $row->damage_min; etc...

Thanks in advance, I have been reading and reading and It just makes NO sense at all to me.

-Steve

Posted: Mon Aug 14, 2006 2:13 am
by RobertGonzalez

Code: Select all

SELECT c.`health`, w.`damage_min`, w.`damage_max` 
FROM `Character` c 
INNER JOIN `Weapon` w 
ON c.`weapon_id` = w.`weapon_id`;

Posted: Mon Aug 14, 2006 11:22 am
by waradmin
I tried that and recieve no results. (I did change the C to p because I changed the table name from Characters to players, and weapons is lowercase, however this is my function and nothing is being returned:

Code: Select all

function calculate_damage() {
	include "includes/config.php";
	include "includes/mysql.class.php";
	$mysqldb = new mysql("$DBHOST","$DBUSER","$DBPASS","$DBNAME");
	$mysqldb->connect();
	$mysqldb->select();
	$mysqldb->query("SELECT p.`health`, w.`damage_min`, w.`damage_max` FROM `players` p INNER JOIN `weapons` w ON p.`weapon_id` = w.`weapon_id`");
	while ($row = $mysqldb->fetchObject())
	$weapon_ldamage = $row->damage_min;
	$weapon_hdamage = $row->damage_max;
	$resists        = '100' - '56';
	$resists        = ".". $resists;
	$damage_initial = rand($weapon_ldamage, $weapon_hdamage);
	$damage = $damage_initial * $resists;
	$round = explode(".", $damage);
	$damage = $round[0];
	
	echo "Calculations: $damage_initial x $resists = $round[0] <br />";
	echo "Damage Through: $damage <br />";
	$health_current = $row->health;
	$health = $health_current - $damage;
	if($health < '0') {
		echo "You have died <br />";
	}
	
	echo "Current Health: ";
	if($health > '0') {
		echo "<font color=green>$health</font>";
	}
	else
	{
		echo "<font color=red>$health</font>";
	}
	mysql_close();
}
Thanks for all of your help, its much appreciated.

-Steve

Posted: Mon Aug 14, 2006 12:06 pm
by RobertGonzalez
Have you tried running that query in phpMyAdmin? I can't see if there is an error detection going on, so the only thing I can think of is to run it in the db admin utility to see what is returned by the query. One of two things will happen. 1) It will be a bad query that has errors in syntax or 2) the syntax is right and built in a way that the result set is empty.