Searching through MySQL DB, snd another question.

Daegalus

I have 2 Questions. I'm making a site for Yugioh, and im going to include a Card Database Search and Spoiler Lists. I have my database fully loaded with every card there is.

1st Q:
For the card search I want to search in multiple tables. I made a table for each set in yugioh with roughly 150 cards in each set. I want so that when someone searches for a card, it searches in every table in the database. I've got the basic part done:

$name = "name";
$type = "type";
$attribute = "attribute";
$subtype = "subtype";
$level = "level"
$atk = "atk";
$def = "def";
$rarity = "rarity";
$cardtext = "cardtext";


mysql_connect (localhost,****,*****) or die ( mysql_error ());
mysql_select_db (cards)or die ( mysql_error ());


?>

But I have no clue on how to make it so that it searches every table.

2nd Q:
Since I'm going to make a Spoiler List (a list with every card in the set) I wanna use my database to do it, instead of rewriting all that information.

Heres what I have, and i have no clue why it wont work. You can check the live version at http://yc.servegame.com/spoiler.php
(you can add ?set=SDY, or ?set=IOC at the end to test it.) When I check the source it only has this:

Heres part of the code: (the first 3 lines are HTML)

<a href="spoiler.php?set=DCR">DCR</a> - 
					<a href="spoiler.php?set=IOC">IOC</a>
					<br /> <br />
					<?php
					mysql_connect (localhost,***,***) or die ( mysql_error ());
					mysql_select_db (cards)or die ( mysql_error ());
					$row = mysql_query('SELECT number,name,type,attribute,subtype,level,atk,def,rarity,cardtext FROM ' . $set . ' WHERE 1')
					echo <b>$set</b><br /> $row
					?>

I have tried it with "SELECT * FROM '$set' WHERE 1" But that still didnt work, and I read on a forum, that if you specify the columns, it improves Efficiency.

Sgarissta

Personally I think you could have solved all of this much easier if you had just added a column to each card's record that indicated what "set" it was in. Then to search for all cards, you only query 1 table. And to find all cards in a given set, its just a matter of adding a WHERE clause.

Daegalus

I do have that kind of column on all the cards. Each card has a Number. For the set LOD the cards have LOD-001, LOD-002, etc. (those are the official numbers given by Konami)

Daegalus

also the cards are seperated by set into different tables.

LOD has its own table, DCR has its own table, etc.

(sorry double post, had to put something in here)

[Edit] Also, When I the query is done, I want to show the whole row, not only that column.

Sgarissta

Originally posted by Daegalus
also the cards are seperated by set into different tables.

LOD has its own table, DCR has its own table, etc.

(sorry double post, had to put something in here)

[Edit] Also, When I the query is done, I want to show the whole row, not only that column.

I fail to see the need for the separate tables if you have a column listing the set number.

Daegalus

ok, if i merge them together, how am i going to make the spoiler lists when i cant search for part of a number? cause not all of them are LOD-000, they all have different numbers.

Daegalus

So can anyone help me out on how I can search with Wildcards. Cause I need to search only the Letter part and not the numbers.