Sql query with two rows

Not really sure if this should go in php or what, but I got the following sql query that returns two rows and three records.

SELECT addons.value, addons.name FROM ships

INNER JOIN customships ON customships.shipID = ships.id

INNER JOIN addons ON addons.id = customships.partID 

INNER JOIN members ON members.id = ships.userid AND ships.userid = 1

I need to use each record differently from the others. How can I do that ?

The data would be something like weapons = 20, shields = 200 and speed = 200, but with the same row name for each of them I dont know how to separate it. Is there a way I can do that in the query or is this simply a php question ?

Thanks

You’ve lost me. What’s the difference between rows and records?

Please give an example of the data in the four tables, and the end result the query should give you? That should make things a bit clearer :wink:

1 Like

Sorry I mean columns lol :slight_smile: The above query gives me this result

value name
250 Warpdrive
250 Shield
250 Laser

I need to use those three separately in my “combat script”. The values will be different depending on what the users are buying on the market.

The customships would look like this

ID shipID partID

  1.       15             2
    
  2.       15             7
    
  3.       15             1
    

addons contains all the parts and ships contains the ship and its names and hitpoints. Customships is supposed to connect all the parts to a ship.

I wanted to try get them all in one query, but I may have to use three queries instead. Should my database structure be different or is this ok ?

And the query result is ok?
Then in your script all you have to do is loop through the query result set and get the values, right? Each row has a unique “name” value, so you should be able to decide what to do with each value?

yes, the query is okay.

Ok then I’d say it’s a PHP question. I’ve moved the thread.
So what problem are you having with the PHP part? :wink:

I was thinking I needed an array for this, but not sure how to put it into an array. Or if Im wrong I need some help to find the best solution.

btw I also posted in the php forum, could you please delete the duplicate ? Thanks :slight_smile:

Ok done

Assuming you’re using PDO , doing a fetchAll would return an array with all the data.

I’ve got this, should return the data. Either its to early in the morning or I’m missing some basic knowledge here lol

		$dbQuery = "SELECT departments.type AS type, addons.value AS value, addons.name AS name FROM ships
					INNER JOIN customships ON customships.shipID = ships.id
					INNER JOIN addons ON addons.id = customships.partID 
					INNER JOIN departments ON departments.id = addons.department
					INNER JOIN members ON members.id = ships.userid AND ships.userid = 1";
		
		$data = getContent($dbQuery);
			foreach($data as $row) {
				
				$type = $row['type'];
				$value = $row['value'];
				$name = $row['name'];
			}

what does getContent() do?

its a function to get the data from the database. I currently got this code and using an example I found, but when I print it just gives me a one letter character :grimacing:

		$dbQuery = "SELECT departments.type AS type, addons.value AS value, addons.name AS name FROM ships
					INNER JOIN customships ON customships.shipID = ships.id
					INNER JOIN addons ON addons.id = customships.partID 
					INNER JOIN departments ON departments.id = addons.department
					INNER JOIN members ON members.id = ships.userid AND ships.userid = 1";
		
		$yourArray = array();
		$index = 0;
		$data = getContent($dbQuery);
			foreach($data as $row) {
				
				$yourArray[$index] = $row;
				$index++;
			}
		
		echo $row[2][value];

Try doing a var_dump of $data and see what the function returns?

Sarray(3) { [0]=> array(6) { ["type"]=> string(7) "Engines" [0]=> string(7) "Engines" ["value"]=> string(3) "250" [1]=> string(3) "250" ["name"]=> string(17) "Elmont Hyperdrive" [2]=> string(17) "Elmont Hyperdrive" } [1]=> array(6) { ["type"]=> string(7) "Shields" [0]=> string(7) "Shields" ["value"]=> string(3) "250" [1]=> string(3) "250" ["name"]=> string(12) "Basic shield" [2]=> string(12) "Basic shield" } [2]=> array(6) { ["type"]=> string(6) "Weapon" [0]=> string(6) "Weapon" ["value"]=> string(3) "250" [1]=> string(3) "250" ["name"]=> string(27) "Single pulse laser Mark III" [2]=> string(27) "Single pulse laser Mark III" } }

Ok looks good.
But I think what you really wanted to echo in your script is

$yourArray[2][value];

?

Although I believe the loop to transfer the data from $data to $yourArray is quite useless, because $data is already an array and you’re just creating a duplicate?
If you do a var_dump of $yourArray I think you’ll see it’s identical?

1 Like

yes I did and it is :smiley: lol

Wow, thanks. It was that simple lol. I learn something new all the time :smile: I can actually just remove the whole foreach loop too

		$dbQuery = "SELECT departments.type AS type, addons.value AS value, addons.name AS name FROM ships
					INNER JOIN customships ON customships.shipID = ships.id
					INNER JOIN addons ON addons.id = customships.partID 
					INNER JOIN departments ON departments.id = addons.department
					INNER JOIN members ON members.id = ships.userid AND ships.userid = 1";
		

		$data = getContent($dbQuery);

			foreach($data as $row) {
				
				$type = $row['type'];
				$value = $row['value'];
				$name = $row['name'];
			}
			echo "<p>".$type. " - " .$value. " - " .$name."</p>";

			echo $data[2][value];
			echo $data[2][name];
			echo $data[2][type];
			
			//var_dump($data);

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.