从数据库中提取组

Alright i have a magic cards site and im trying to pull all of the cards from a certain set if you click on the set on the home page. www(dot)magiccards(dot)me the code on the first page is:

好吧,我有一个魔术卡网站,如果你点击主页上的设置,我试图从特定集中拉出所有卡。 www(点)magiccards(点)我在第一页的代码是:

    <?php

require("mysqlconnect.php");

$query = "SELECT COUNT(*) AS `Rows`, `set`,id FROM `magic_cards_copy`  GROUP BY `set` ORDER BY `set`";

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

// Print out result
while($row = mysql_fetch_array($result)){
    $setlink = $row[1];
    $setlink = str_replace(" ", "", $setlink);
    $setlink = strtolower($setlink);
    $setlink = preg_replace("/[^a-z0-9]./","",$setlink);
    $setlink .= "-c-$row[2]";
    $setlink .= ".html";

    $navigation .= "< href=\"$setlink\">$row[1]</a> <small><i>($row[0])</i></small>";
}

require("template.php");

?>

and the code on the page that comes is:

并且页面上的代码是:

    <?

require("mysqlconnect.php");

$cat=$_GET['cat'];

echo "Category: $cat<br>";

$query = "SELECT * FROM `magic_cards_copy` WHERE id = $cat ";

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
echo "Set: $row[1]<br>";

?>

how would have the code pull up the cards from each set? any help would be great. this is more of a practice site for me.

如何让代码从每一组中提取卡片?任何帮助都会很棒。这对我来说更像是一个练习网站。

5 个解决方案

#1

I would create a table that has two columns. The first column is the set-id (a unique identifier for all sets) and it would be indexed for fast lookups. The second column would be the id's for the cards. You would use this table for a JOIN on the table containing all unique cards

我会创建一个有两列的表。第一列是set-id(所有集合的唯一标识符),它将被编入索引以进行快速查找。第二列将是卡片的ID。您可以将此表用于包含所有唯一卡的表上的JOIN

BTW, sanitize all input

BTW,清理所有输入

$cat=$_GET['cat'];

is open to SQL injection attacks. You should also cast it to an integer.

对SQL注入攻击持开放态度。您还应该将其强制转换为整数。

#2

Read up on Database Normalization. What you ought to do is have one table of "sets" where you list each set with an ID number. Then in your cards database, instead of having the set name, you have an ID number corresponding to the set.

阅读数据库规范化。您应该做的是有一个“集合”表,您可以在其中列出每个集合的ID号。然后在您的卡片数据库中,您没有设置名称,而是拥有与该集合对应的ID号。

Example sets table:

示例集表:

id    set
------------------
1     Set One Name
2     Set Two Name

Example cards table:

示例卡表:

id    setid   card
-------------------------------------
1     1       Card One from First Set
2     1       Card Two from First Set
3     2       A card from Second Set

When you want to list sets, you simply select everything from the sets table. When you want to list cards from a set you select all cards where the set ID is whatever you're looking for.

如果要列出集合,只需从集合表中选择所有内容即可。当您想要从一组中列出卡片时,您可以选择所有卡片,其中所设置的ID就是您要查找的内容。

#3

Not really sure what your asking, but the first thing to look at seeing as this is a practice site is to

不太确定你的要求,但首先要看的是这是一个练习网站

$cat = mysql_real_escape_string($_GET['cat']);

at the very least to prevent SQL injection hacks. You should always practice security. PHP.not on SQL Injection

至少要防止SQL注入黑客攻击。你应该总是练习安全。 PHP.not on SQL Injection

#4

It's not clear from the question what you're trying to do - for example, what is the relationship between the two PHP scripts provided, and are you trying to pull the cards from ALL sets, or the cards from a GIVEN set? I'll try to answer both:

从问题中你不清楚你要做什么 - 例如,提供的两个PHP脚本之间的关系是什么,你试图从所有集合中拉出卡片,还是从GIVEN集合中取出卡片?我会尝试回答两个问题:

All cards in set $_GET['cat']:

set $ _GET ['cat']中的所有卡片:

<?php
$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'dbuser', 'dbpass');
$stmt = $dbh->prepare('SELECT cardname from magic_cards_copy where set = :set');
$stmt->bindParam(':set', $_GET['cat'], PDO::PARAM_STR);
$stmt->execute();

while ($card = $stmt->fetch(PDO::FETCH_ASSOC)) {
  // display card here
}

All cards in all sets:

所有套牌中的所有牌:

<?php
$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'dbuser', 'dbpass');
$query = $dbh->query('SELECT cardname from magic_cards_copy');

while ($card = $query->fetch(PDO::FETCH_ASSOC)) {
  // display card here
}

Note that I strongly recommend you use PDO, as it protects you from all sorts of foolishness.

请注意,我强烈建议您使用PDO,因为它可以保护您免受各种愚蠢。

#5

echo "Category: $cat<br>";

This is subject to a Cross-Site Scripting vulnerability. Since you're using this as a practice site, you should be aware of security flaws and how to avoid them. Start by reading the OWASP site.

这受跨站点脚本漏洞的影响。由于您将其用作练习网站,因此您应该了解安全漏洞以及如何避免这些漏洞。首先阅读OWASP网站。

As others have noted, you also have an SQL Injection flaw.

正如其他人所说,你也有一个SQL注入漏洞。

Also, here's output from your site:

此外,这是您网站的输出:

Category: 143345
Set: Alliances
You have an error in your SQL syntax; check the manual that corresponds to 
your MySQL server version for the right syntax to use near ''magic_cards_copy' 
WHERE category = 'Alliances' LIMIT 40' at line 1

Looks like you're using the wrong kind of delimiter around your table name. Use back-quotes, not single-quotes, around the table name.

看起来你在表名周围使用了错误的分隔符。在表名周围使用反引号而不是单引号。

You do need to create another table, to record the membership of each card in each set. This is sometimes called a "many-to-many table" or "intersection table." This is especially true because IIRC, some Magic cards can belong to multiple sets.

您需要创建另一个表,以记录每个组中每张卡的成员身份。这有时被称为“多对多表”或“交叉表”。这尤其正确,因为IIRC,一些魔术卡可以属于多组。

So here's how I'd do it:

所以我就是这样做的:

CREATE TABLE CardSets (
  set_id INT PRIMARY KEY AUTO_INCREMENT,
  set_name VARCHAR(40)
);

CREATE TABLE Cards (
  card_id INT PRIMARY KEY AUTO_INCREMENT,
  card_name VARCHAR(40)
  -- other card attributes, color, flavor text, etc.
);

CREATE TABLE CardSetManifest (
  set_id INT NOT NULL,
  card_id INT NOT NULL,
  -- other attributes of card specific to a given set, e.g. rarity
  PRIMARY KEY (set_id, card_id),
  FOREIGN KEY (set_id) REFERENCES CardSets(set_id),
  FOREIGN KEY (card_id) REFERENCES Cards(card_id)
);

So given a set_id you can get the count of cards in that set:

因此,给定一个set_id,您可以获得该组中的卡数:

SELECT set_id, COUNT(*) FROM CardSetManifest GROUP BY set_id;

Given a set_id you can get a list of the cards in that set:

给定一个set_id,您可以获得该集合中的卡片列表:

SELECT m.set_id, m.card_id, c.card_name
FROM CardSetManifest m JOIN Cards c USING (card_id);

#1