标签云

微信群

扫码加入我们

WeChat QR Code

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年08月18日36分44秒

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

2018年08月18日36分44秒

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年08月18日36分44秒

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

2018年08月18日36分44秒

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年08月18日36分44秒

And you've downvoted because – ?

2018年08月18日36分44秒

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

2018年08月18日36分44秒

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年08月18日36分44秒