MYSQL Two Tables Join Issue

Place for questions and answers for all newcomers and new coders. This is a free for all forum, no question is too stupid and to noob.

MYSQL Two Tables Join Issue

Postby Fireal » Sun Nov 26, 2017 3:46 pm

Hello again, I apologize if I am posting too much, I'm just getting stuck a bit more the deeper I get in to my game. Being a novice programmer I assumed this sort of stuff would happen. Anyways, here's my problem.

I am trying to display move names from a db. I have a $_GET['id'] stored in the variable $id. I am using this var to query a specific moveset from my moveset table. From there I need to output each move column (weak1,weak2,medium1,medium2,etc...) which has a moveid stored for its value and coresponds with the moveid in the moves table. Ultimately I want to just display the movename but have to pull the user profiles ID, get their moveset, then pull each moveid in their moveset, then match that moveid to the moveid in the moves table and in return pull the movename.

Here is my current code but it is only echoing the moveid:

Code: Select all
      echo "<h3>Moves:</h3>"; // List moves of wrestler
      $sql3 = "SELECT movesets.*, moves.* FROM movesets WHERE movesets.wrestlerid = '$id'";
       $result3 = mysqli_query($conn, $sql3);
      while($row3 = mysqli_fetch_assoc($result3)) {
         echo $row3["weak1"] . "," .
            $row3["weak2"] . "," .
            $row3["weak3"] . "," .
            $row3["weak4"] . "," .
            $row3["weak5"] . "," .
            $row3["medium1"] . "," .
            $row3["medium2"] . "," .
            $row3["medium3"] . "," .
            $row3["medium4"] . "," .
            $row3["medium5"] . "," .
            $row3["strong1"] . "," .
            $row3["strong2"] . "," .
            $row3["strong3"] . "," .
            $row3["strong4"] . "," .
            $row3["strong5"] . "," .
            
            "<br>";}
User avatar
Fireal
 
Posts: 11
Joined: Sun May 21, 2017 12:51 pm
Has thanked: 5 times
Been thanked: 0 time

Re: MYSQL Two Tables Join Issue

Postby hallsofvallhalla » Sun Nov 26, 2017 5:09 pm

So are you actually trying to echo the column names and then the corresponding Moveids?

BTW ther eis no such thing as posting too much. That is what these forums are for , ask away!
User avatar
hallsofvallhalla
Site Admin
 
Posts: 11998
Images: 13
Joined: Wed Apr 22, 2009 6:29 pm
Location: mobile, al
Has thanked: 11 times
Been thanked: 164 times
Blog: View Blog (3)

Re: MYSQL Two Tables Join Issue

Postby Fireal » Sun Nov 26, 2017 6:04 pm

I am just trying to echo the move names, but I am using the moveid's(which are both in the moveset and moves table) to get them to join in my query and spit out just the move names. The code I listed above spits out the moveid's based on the wrestlerid(id) from a form on a previous page. I'm assuming this is going to come down to an advanced sql query that my newbie brain can't think of right now. Maybe joining a bunch of tables?

Tables:
moves - which has the moveid and movename
moveset - has 20 columns for each move slot and each move slot has a int representing the moveid in the moves table. this table also has a column for wrestlerid to link a specific wrestler to the moveset

I'm also using $_POST['id'] from a form on the previous page (wrestler select screen) to get the wrestlerid selected by the user. Hope this helps a bit!

Also, glad to hear the posting isn't a big deal. I appreciate it!
User avatar
Fireal
 
Posts: 11
Joined: Sun May 21, 2017 12:51 pm
Has thanked: 5 times
Been thanked: 0 time

Re: MYSQL Two Tables Join Issue

Postby a_bertrand » Mon Nov 27, 2017 10:09 am

BTW NEVER EVER DO THAT!
"SELECT movesets.*, moves.* FROM movesets WHERE movesets.wrestlerid = '$id'";

Check out:
http://php.net/manual/en/mysqli-stmt.bind-param.php

Why? Because having values directly into your SQL opens the door to all kind of SQL injection hacks
Creator of Dot World Maker
Mad programmer and annoying composer
User avatar
a_bertrand
New Worlds Engine creator
 
Posts: 1535
Joined: Mon Feb 25, 2013 8:46 am
Has thanked: 15 times
Been thanked: 23 times

Re: MYSQL Two Tables Join Issue

Postby Jackolantern » Mon Nov 27, 2017 8:13 pm

Yep! Someone could DROP your whole database, or, even worse, inject their own values into it, making them an admin or worse.

And as Halls said, this is a free-for-all forum section. Normal rules of topic spamming do not apply here. People that want to help can come in and help, and people who would be annoyed by that can skip the section.
The indelible lord of tl;dr
User avatar
Jackolantern
 
Posts: 10893
Joined: Wed Jul 01, 2009 6:00 pm
Location: Houston, TX
Has thanked: 22 times
Been thanked: 92 times
Blog: View Blog (1)

Re: MYSQL Two Tables Join Issue

Postby hallsofvallhalla » Tue Nov 28, 2017 12:00 pm

I am still trying to understand the entire setup but sounds like you could just build an array from a first query that is all the move names then when you query the ids you could cross reference the array versus trying to figure it out in sql.
User avatar
hallsofvallhalla
Site Admin
 
Posts: 11998
Images: 13
Joined: Wed Apr 22, 2009 6:29 pm
Location: mobile, al
Has thanked: 11 times
Been thanked: 164 times
Blog: View Blog (3)

Re: MYSQL Two Tables Join Issue

Postby Chris » Tue Nov 28, 2017 5:08 pm

I would highly recommend using a good ORM pattern like active record or something of the sort. You will get really complicated joins and a lot of duplicate SQL queries, which can be easily solved using a good ORM. My personal favorite is the native Kohana ORM, Yii's CActiveRecord is also a nice class. I could also vouch for this one: http://phpactiverecord.org/

If you want to go the hard SQL route: https://stackoverflow.com/questions/101 ... h-3-tables
Fighting for peace is declaring war on war. If you want peace be peaceful.
User avatar
Chris
 
Posts: 1575
Joined: Wed Sep 30, 2009 2:22 pm
Has thanked: 4 times
Been thanked: 5 times
Blog: View Blog (2)

Re: MYSQL Two Tables Join Issue

Postby Jackolantern » Wed Nov 29, 2017 10:26 pm

I second at least using something like ActiveRecord. AR's learning curve is much smaller than an ORM and you can typically drop an AR library into any project, which isn't exactly true with most ORMs. Most ORMs require a fair bit of logic redesign if you are currently making queries directly in code.
The indelible lord of tl;dr
User avatar
Jackolantern
 
Posts: 10893
Joined: Wed Jul 01, 2009 6:00 pm
Location: Houston, TX
Has thanked: 22 times
Been thanked: 92 times
Blog: View Blog (1)


Return to Beginner Help and Support

Who is online

Users browsing this forum: No registered users and 1 guest

cron

x