Need help getting values from two tables

[LikeCoding]

					$query = "SELECT coins, price ".
				 "FROM members, avatar ".
					"WHERE user = '{$_SESSION['sess_user']}', name = '{$_POST['item']}'";

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


				// Print out the contents of each row into a table 
				while($row = mysql_fetch_array($result)){
					echo $row['coins']. " - ". $row['price'];
					echo "<br />";
				}

Hello hello people

I hope this forum is the right place for me. I try explain as good as I can.

I have this two tables that I want to pick out two rows and compare them. Right now I just write them out for testing. Anyhow... I have tried a lot of different syntax variants but I've come this far (see code).

 

I want to do "WHERE" statement on two tables, is that possible? If you are curious the tables are: avatar and members. That I going to do is take price (from avatar table) and subtract the value with coins from members.

 

ERROR: Column 'name' in where clause is ambiguous

[DaiLaughing]

You may need to learn about JOIN but I'm not clear what the data is you need and where "user" and "name" are coming from.  Can you describe what is in each table?

[LikeCoding]

MySQL client version: 5.0.51a

 

You may need to learn about JOIN but I'm not clear what the data is you need and where "user" and "name" are coming from.  Can you describe what is in each table?

 

I got two tables called: Members and avatar. In members I got both user and coins.

The other one is called Avatar with price and name columns.

 

--
-- Table structure for table `avatar`
--

This is how the avatar table looks like

  `id` int(11) NOT NULL auto_increment,
  `name` varchar(41) NOT NULL default '',
  `price` int(50) default NULL,
  `nick` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 

 

--
-- Table structure for table `members`
--

This is how the members table looks like

  `id` int(11) NOT NULL auto_increment,
  `user` varchar(30) NOT NULL default '',
  `pass` varchar(41) NOT NULL default '',
  `name` varchar(50) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `coins` int(25) default '0',
  `savatar` varchar(15) NOT NULL default 'bullet.gif',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

 

So I tell again:

I want to take the price value from the name in avatar and minus that with the coins value for the logged in user.

Also going to need somehow "check" if the users coins is more or equal with the price.

 

I've been searching for a long time how to complete this "buy"-script but I need some serious help.

[awjudd]

In your where clause, SQL doesn't know where to look (which table to look in) at the 'name' field because it is in both.  You will have to tell it to use either the avatar or the members table specifically.

 

~juddster

[LikeCoding]

Thanks a bunch! Now it maybe sounded simple but now I see the problem.

Got stuck with the "if user got points for the avatar name" place now. But I'll keep trying. Join is probably the right way to go..

 

					// Make a MySQL Connection
				// Construct our join query
				$query = "SELECT coins, price ".
				 "FROM members, avatar ".
					"WHERE user = '{$_SESSION['sess_user']}'";

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

				// Print out the contents of each row into a table 
				while($row = mysql_fetch_array($result)){
					echo $row['coins']. " - ". $row['price'];
					echo "<br />";

					if($row['coins']<10) { // if I change it to $row['price'] I guess it wont work so I better fix this^^
						echo "You need more cash!!";
						} else {
						echo "you have over 1 coins";
						}
				}

 

Anyway, I will probably make it. Thanks again

[DaiLaughing]

Although there is a more efficient way it might help to split what you want to do into four stages:

 

1) Use your MySQL query to get both the price and the number of coins the user has (you have this)

2) Use a PHP IF statement to see if the number of coins is more than the price

3) IF they have enough you can take the price from the new number of coins in PHP

4) Use an UPDATE query to change the number of coins in the database

 

This could all be done in one query but leave that for now.