标签云

微信群

扫码加入我们

WeChat QR Code

PHP的mysqli表比较[复制]

This question already has an answer here:

Final Code I used for this. Thanks to @hakre.

Code(will real variable name):

        $sqlrequest = mysqli_query($conn,"SELECT COUNT(*) AS id FROM skillstable");
        $skillstablecount = mysqli_fetch_array($sqlrequest);

        $endfor = $skillstablecount['id'];

        $sqlrequest = mysqli_query($conn,
        "SELECT * FROM skillstable
            LEFT OUTER JOIN char_" . $infochar['name'] . "_skills
            ON skillstable.skillname = char_" . $infochar['name'] . "_skills.skillname
            WHERE char_" . $infochar['name'] . "_skills.skillname IS null;");

        for ($x=1; $x<=$endfor; $x++){
        array_push($nonrankedskill,mysqli_fetch_array($sqlrequest));
        }


        ?>

extracting information from array like this:

$nonrankedskill[rownumber][columnnumber];

i'm trying to figure out how to do this but my search have been unsuccessful so far.

I'm using php and MySQLi to compare two table a get the output of the difference between two table based on two column.

rankedskill:

id    skillname    subskill    rank
0     walk         slow        2
1     walk         fast        4
2     run                      1
3     jump                     7

generalskill:

id    skillname    othercolumn1
0     walk         something
1     fight
2     jump
3     dive
4     fly
5     run                        

Output wanted:

$nonrankedskill = array();

I want this variable to contain everything from the column "skillname" in tabel "generalskill" that is not contained in table "rankedskill". I should have at the end "fight", "dive" and "fly".

Could anyone refer me to some documentation on a way to achieve this? or explain me how to do it?


I would not separate those tables in the first place. It is much easier for you if you just have a true/false field ranked or so

2018年06月20日30分08秒

Using NOT IN() query -> SELECT gs.`skillname` FROM `generalskill` gs WHERE gs.`skillname` NOT IN (SELECT DISTINCT(rs.`skillname`) FROM `rankedskill` rs)

2018年06月20日30分08秒

I have to separate them because subskill are different from one character to another as well as the ranked point and misc point. with a lot of caracter, it will be really hard to manage. thanks anyway

2018年06月19日30分08秒

LEFT OUTER JOIN WHERE IS NULL - blog.codinghorror.com/a-visual-explanation-of-sql-joins

2018年06月20日30分08秒

hakre Thanks for the solution and the documentation. It was very well explained and work like a charm. You should put this as an answer so I will accept it.

2018年06月20日30分08秒

And you've downvoted because – ?

2018年06月19日30分08秒

I didn't. Thanks for the answer. I finally used OUTER JOIN

2018年06月19日30分08秒

Notulysses: You perhaps should take into account that this is Mysql. The DV is about the performance argument you make, in any case, you need to know about the concrete structure before doing the performance assumption (which you don't). However, in the general case (and that's for the -1), you should prefer LEFT JOIN instead of NOT EXISTS in Mysql. For an in-depth discussion, please see explainextended.com/2009/09/18/… - It's also better to tell newer users of SQL about the plain JOIN stuff IMHO before jumping to derived tables.

2018年06月20日30分08秒