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.
Post Reply
Fireal
Posts: 11
Joined: Sun May 21, 2017 5:51 pm

MYSQL Two Tables Join Issue

Post by Fireal »

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
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

Re: MYSQL Two Tables Join Issue

Post by hallsofvallhalla »

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!
Fireal
Posts: 11
Joined: Sun May 21, 2017 5:51 pm

Re: MYSQL Two Tables Join Issue

Post by Fireal »

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
a_bertrand
Posts: 1537
Joined: Mon Feb 25, 2013 1:46 pm

Re: MYSQL Two Tables Join Issue

Post by a_bertrand »

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
Jackolantern
Posts: 10893
Joined: Wed Jul 01, 2009 11:00 pm

Re: MYSQL Two Tables Join Issue

Post by Jackolantern »

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
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

Re: MYSQL Two Tables Join Issue

Post by hallsofvallhalla »

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
Chris
Posts: 1580
Joined: Wed Sep 30, 2009 7:22 pm

Re: MYSQL Two Tables Join Issue

Post by Chris »

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
Jackolantern
Posts: 10893
Joined: Wed Jul 01, 2009 11:00 pm

Re: MYSQL Two Tables Join Issue

Post by Jackolantern »

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
Post Reply

Return to “Beginner Help and Support”