PHP MySQLi table compare

Question

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 — Felk, Jun 08 '14 at 21:15
Using [`NOT IN()`](http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_not-in) query -> `SELECT gs.\`skillname\` FROM \`generalskill\` gs WHERE gs.\`skillname\` NOT IN (SELECT DISTINCT(rs.\`skillname\`) FROM \`rankedskill\` rs)` — Sean, Jun 08 '14 at 21:18
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 — Johnny Prescott, Jun 08 '14 at 21:22
LEFT OUTER JOIN WHERE IS NULL - http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ — hakre, Jun 08 '14 at 21:38
@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. — Johnny Prescott, Jun 09 '14 at 00:11
@DrizztDoUrden: If that was the answer, I link the reference question as it contains more information I'm able to leave here as an answer. — hakre, Jun 09 '14 at 08:22

potashin · Answer 1 · 2015-06-26T00:54:23.557

0

You can try with left join/is null :

select g.skillname 
from generalskill g
left join rankedskill r on r.skillname = g.skillname and r.? = g.?
where r.skillname is null

edited Jun 26 '15 at 00:54

answered Jun 08 '14 at 21:15

potashin

44,205
11
83
107

And you've downvoted because – ? – potashin Jun 08 '14 at 21:43
I didn't. Thanks for the answer. I finally used OUTER JOIN – Johnny Prescott Jun 09 '14 at 00:12
@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 http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ - It's also better to tell newer users of SQL about the plain JOIN stuff IMHO before jumping to derived tables. – hakre Jun 09 '14 at 08:33

PHP MySQLi table compare

1 Answers1