[SOLVED] Trouble with mysql & php

[killah]

I am currently in a rough situation.

 

I am not a mysql person that know's all mysql function's because i do not know where to go to look for them.

 

To my problem:

 

I am wanting to select multiple category's from the database. How ever. I got that part to work.

 

Each category has multiple upgrade's. These upgrades are in a select drop down list form. My problem lies within the query to select these upgrades and to display the dropdown list.

 

WHY?

 

Because in the upgrades list i have a field name called `upHOUSES` in a form of an array. For example:

 

1,4,9,5,2

 

I would like to restrict the upgrades from showing if my house id is not in there. how ever the problem also lies here. I tried NOT IN() mysql function but it just won't work. WHY?

 

Because it's trying to go like this

 

`upHOUSES` NOT IN(`oHOUSE`)

 

Meaning

 

`1,6,29,3` NOT IN(`4`)

 

I also use a foreach function to display the drop down's.

 

Here is the code im using

 

<?php //Added to color code the rest

function customize()
{
global $ir,$h;
$ID = abs(@intval($_GET['ID']));
$its_house = mysql_query("SELECT oOWNER,oID,oUPGRADES,oHOUSE FROM `house_owned` WHERE `oID` = ".$ID);
$its = mysql_fetch_assoc($its_house);
if(mysql_num_rows($its_house) == 0)
{
	echo 'Invalid entry.';
	$h->endpage();
	exit;
}
if($its['oOWNER'] != $ir['userid'])
{
	echo 'You do not own this house.';
	$h->endpage();
	exit;
}
if( !isset($_POST['buy_upgrades']) )
{
	echo '
	Here is a list of upgrades you can buy for your house.
	<br><br>
	<b>House Upgrades</b><br>
	<form action="'.$_SERVER['PHP_SELF'].'?x=customize&ID='.$its['oID'].'" method="post">
		<table border="0" class="table" cellspacing="0" cellpadding="0" width="80%">
			<tr>
				<th>Facility & Upgrades</th>
				<th>Upgrade</th>
			</tr>';
			//Upgrades for house
			$errno_upID      = $its['oUPGRADES'];
			$errno_house     = $its['oHOUSE'];
			$fetch_upgrades  = mysql_query("SELECT upNAME,upPRICE,upWILL,upCATEGORY,upPTYPE FROM `house_upgrades` WHERE `upID` NOT IN(".$errno_upID.") AND `upTYPE` = 1") or die(mysql_error());
			$fetch_categorys = mysql_query("SELECT catNAME,catID FROM `house_upcategorys` ORDER BY `catID` ASC") or die(mysql_error());

			while($fu = mysql_fetch_assoc($fetch_upgrades))
			{
				$soc[] = $fu;
			}

			if(mysql_num_rows($fetch_categorys) == 0)
			{
				echo '
			<tr>
				<td colspan="2">No Categorys</td>
			</tr>
		</table>';
			}
			else
			{
				while($fc = mysql_fetch_assoc($fetch_categorys))
				{
					echo '
			<tr>
				<td>'.$fc['catNAME'].'</td>
				<td>
					<select name="fu[]">';
						foreach($soc as $v)
						{
							if($soc['upCATEGORY'] == $fc['catID'])
							{
								echo '<option value="'.$v['upID'].'">'.$v['upNAME'].'</option>';
							}
						}	echo '
					</select>
				</td>
			</tr>';
				}
			}	echo '
			</tr>
		</table>
	</form>';
}
}

 

Thank's in advaced 

[btherl]

Can you modify your code to do this:

 

$upgrades_sql = "SELECT upNAME,upPRICE,upWILL,upCATEGORY,upPTYPE FROM `house_upgrades` WHERE `upID` NOT IN(".$errno_upID.") AND `upTYPE` = 1";
echo "== $upgrades_sql<br>";
$fetch_upgrades = mysql_query($upgrades_sql) or die(mysql_error());

 

The idea is just to show the query you are running.  If it's not clear how to fix the problem after seeing the query, post the output here for us to look at.  You can do the same for the other queries too.

[killah]

== SELECT upNAME,upPRICE,upWILL,upCATEGORY,upPTYPE FROM `house_upgrades` WHERE `upID` NOT IN(10) AND `upTYPE` = 1

[killah]

Ok. I tried a left join. But i get an error.

 

<?php

				$socket = mysql_query
				("
					SELECT
						up.upNAME,up.upPRICE,up.upKEEP,up.upWILL,up.upID,up.upHOUSES,up.upPTYPE,up.upCATEGORY,
						o.oUPGRADES,o.oHOUSE
					FROM 
						`house_upgrades` `up`
					LEFT JOIN 
						`house_owned` `o`
					ON 
						`o.oHOUSE` NOT IN(`up.upHOUSES`)
					WHERE
						`o.oID` = ".$ID."
					AND
						`up.upCATEGORY` = ".$cats['catID']
				) or die(mysql_error());

?>

 

Error that produced was:

 

Unknown column 'o.oID' in 'where clause'

 

On every single left join i use it give's me that error. Why?

[btherl]

I'm not sure you can join like that.  Putting values in a "string array" makes things difficult in SQL, as SQL is just not designed like that.  Take a look at SQL anti-patterns, the section about "Storing multivalued attributes in strings"

 

As for how to deal with it, you can either alter your database structure as explained in that document above, or you can do the join in php (which means as you had it before).

 

Your specific error though is that the column oID does not exist in the "o" table (which is house_owned).

 

 

 

[killah]

I know what my error mean's. But why? The oID is in the house_owned table.

 

CREATE TABLE IF NOT EXISTS `house_owned` (

  `oID` bigint(255) NOT NULL auto_increment,

  `oHOUSE` int(25) NOT NULL default '0',

  `oOWNER` int(25) NOT NULL default '0',

  `oWILL` int(25) NOT NULL default '0',

  `oUPGRADES` varchar(255) collate latin1_general_ci NOT NULL,

  `oUPKEEP` int(25) NOT NULL default '0',

  `oRENTED` int(25) NOT NULL default '0',

  `oIMAGE` varchar(255) collate latin1_general_ci NOT NULL default '',

  PRIMARY KEY  (`oID`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=76 ;

 

Now tell me oID does not exist

[killah]

B Bump

U Up

M My

P Post

[btherl]

I'm no mysql expert, but should it be `o`.`oID` rather than `o.oID`?  And same for the other columns.

[killah]

Figured that out already. I have also solved my solution with the above mysql query.

[btherl]

You're welcome.