(Newbie Question) Can I have 2 SELECT in 1 query? Need to select another column.

[bambambanana]

Hello. First I want to say I'm glad to see a very helpful forum such as this. I really hope I can benefit from by fellow users knowledge

Thank you in advance for all replies

 

Well, I downloaded a script made for my database. Except I'd like to mod the script a bit more.

 

The script goes into my SQL database, reads the table and then displays the items in my website page.

 

It's set to read the "character_items" table and display all items that have "enchantlvl" over 4. That's fine. But the problem

I'm having is too many users have items over enchantlvl 4, so I'd like to divide and organize the script a bit more.

 

Here is the code:

 

	$query_R_items = "SELECT * FROM character_items LEFT JOIN 

armor ON character_items.item_id = armor.item_id WHERE 

character_items.enchantlvl > 4 and NOT armor.item_id IS NULL ORDER BY 

enchantlvl DESC LIMIT 0,{$oe_num}";break;
default : exit;

 

In the "armor" table, there is a column called "type". And it has different values like helm, gloves, boots, cloak, amulet etc.

I want to divide the top 20 enchantlvls (that's what it is, a top 20) but have 1 page for each of the values in the "type" category

in the "armor" table. Currently it does not distinguish what is in the "type" column and just ignores the column in the "armor" table.

 

I'd like to select the column "type" in the "armors" database. I'd still like to show enchantlvl over 4 and above only. The only way I can think of doing it is to add another SELECT * to the query but I tried and it did not work.

 

I'd appreciate the help. Here is the full code if its necessary. Thank you all.

 

<?php
include("setup.php");

if (isset($_POST['sw'])){
$sw = (get_magic_quotes_gpc()) ? $_POST['sw'] : addslashes($_POST

['sw']);
}else{
exit;
}

$filename = "OE_{$sw}.html";

if (!file_exists($filename) or filemtime($filename) < time() - 

$renewal_time){

mysql_select_db($database_l1jdb, $l1jdb);
switch ($sw){
case "weapon":
	$query_R_items = "SELECT * FROM character_items LEFT JOIN 

weapon ON character_items.item_id = weapon.item_id WHERE 

character_items.enchantlvl > 6 and NOT weapon.item_id IS NULL ORDER BY 

enchantlvl DESC LIMIT 0,{$oe_num}";break;
case "armor":
	$query_R_items = "SELECT * FROM character_items LEFT JOIN 

armor ON character_items.item_id = armor.item_id WHERE 

character_items.enchantlvl > 4 and NOT armor.item_id IS NULL ORDER BY 

enchantlvl DESC LIMIT 0,{$oe_num}";break;
default : exit;
}

$R_items = mysql_query($query_R_items, $l1jdb) or die(mysql_error());
$row_R_items = mysql_fetch_assoc($R_items);
$totalRows_R_items = mysql_num_rows($R_items);

$rank = 0;
$rank_check = "";

$html = "<table width=\"600\"><tr><td>\n";
$html .= "<table style=\"font-family:Verdana;font-size:10;color:#ff9933

\"><tr>\n";

if ($totalRows_R_items > 0){
do {
	$rank++;
	$item_name = "+" . $row_R_items['enchantlvl'] . " " . 

$row_R_items['item_name'];
	if ($rank_check == $row_R_items['enchantlvl']){
		$html .= "<td align=\"right\"><strong><big 

style=\"font-size:10;color:#ff9933\">{$rank2}</big></strong></td>";
	}else{
		$rank2=$rank;$html .= "<td 

align=\"right\"><strong><big style=\"font-size:10;color:orange\">{$rank2}

</big></strong></td>";$rank_check=$row_R_items['enchantlvl'];
	}
	$html .= "<td><img src=\"http://l1.izst.com/L1J-

JPDB/inv_gfx/{$row_R_items['item_invgfx']}.png\" align=\"absmiddle\"> 

<strong><big style=\"font-size:10;color:orange\">{$item_name}

</big></strong></td>";

	if (($rank % ($oe_num / 2)) == 0){
		$html .= "</tr>\n";
		$html .= "</table>\n";
		$html .= "</td><td>\n";
		$html .= "<table style=\"font-family:Verdana;font-

size:10;color:orange\"><tr>\n";
		$html .= "</tr><tr>\n";
	}else{
		$html .= "</tr><tr>\n</tr><tr>\n";
	}

} while ($row_R_items = mysql_fetch_assoc($R_items));
}

$html .= "</table>\n";
$html .= "</td></tr></table>\n";

file_put_contents($filename,$html);

echo $html;

mysql_free_result($R_items);

}else{
include($filename);
}
?>

[bambambanana]

So far all I could come up with is

 

SELECT * FROM armor WHERE armor.type = helm

 

 

but I can't seem to fit that in there..

 

[aschk]

Give us the table layout, examples of the data in them, and then an example of what you want out from the information.

I'm sure that'll allow us to more easily assess what it is you need.

[bambambanana]

database: l1jdb

 

table: character_items

 

it reads the "enchantlvl" column from that database and displays all values 4 and higher.

it sorts it decending.

 

I need it to read a column from the "armor" table. The column is "type" and the value for it to read is "helm"

 

So now the code I posted in the first post should not only read the enchantlvl from character_items, it should access the "armor" database

and display every "helm" in the "type" column.

 

I need to do that but still make sure it displays the "enchantlvl" column without canceling each other out.

 

Thank you.

 

http://www.game-masters.org/viewpage.php?page_id=12

 

If you head there and click "armor" you can see what I am talking about. Sorry if it makes you log in.

It's a top 20 ranking. But as you can see everything is tied at 1.

 

You can notice there are helmets, shields, boots and stuff in that section. I'd like to divide it up. So instead of just an "armor" and "weapon" section of the webpage I will have "boots, helm, gloves, shields etc" All similar to the armor top 20, but more specific.

 

Thank you.

[aschk]

Ok, to give us the table layout (which you haven't thus far) please post the output of

DESCRIBE character_items

 

Then also do a

SELECT * FROM character_items LIMIT 0,10;

 

And post that here too.

[bambambanana]

Sorry, I'm new to this.

 

Here is when I run DESCRIBE query through Navicat:

 

id	int(11)	NO	PRI	0	
item_id	int(11)	YES			
char_id	int(11)	YES	MUL		
item_name	varchar(255)	YES			
count	int(11)	YES			
is_equipped	int(11)	YES			
enchantlvl	int(11)	YES			
is_id	int(11)	YES			
durability	int(11)	YES			
last_used	varchar(255)	YES			
upgradelvl	int(11)	YES		0	

 

 

And when I run SELECT * FROM character_items LIMIT 0,10;

 

SELECT
character_items.id,
character_items.item_id,
character_items.char_id,
character_items.item_name,
character_items.`count`,
character_items.is_equipped,
character_items.enchantlvl,
character_items.is_id,
character_items.durability,
character_items.upgradelvl,
character_items.last_used
FROM
character_items
LIMIT 0,10

 

278416901	20282	278416890	Ivory Tower Ring	1	1	0	0	1		0
283520176	40089	278558182	Scroll of Resurrection	6	0	0	0	0		0
282824276	20028	282824265	Ivory Tower Leather Helm	1	0	5	0	1		0
286586555	40486	286587698	Volcanic Ash	1	0	0	0	0		0
278428294	35	278428292	  Ice Dagger	1	1	0	0	1		0
280077465	40126	278412484	Scroll of Identify	30	0	0	1	0		0
278331085	20013	278323411	Helmet of Magic: Speed	1	0	0	0	0		0
286961922	20173	286961914	Ivory Tower Gloves	1	1	5	0	1		0
278999321	200002	280159748	Dice Dagger	1	0	0	0	0		0
279119959	20317	278398871	Ogre Belt	1	1	0	0	0		0

 

[aschk]

Ok, what i can't see from that table is how you categorise the items.

i.e. which items are cloak, helm etc.

How do you distinguish this information? Can you use DESCRIBE on the armor table too please?

 

Then explain what you want from the tables, and give a sample dataset. i.e an example of what you want to see.