CODEWITHSUNDEEP
phpmysqlarrays
frosty
frosty

Reputation: 2659

Getting columns from more than 2 tables and then inserting them into an array

I wrote some code to try and fetch multiple columns from different tables and join them together before inserting them into an array. It works when I was doing it on single tables, and two different tables, but when I tried to do it for three tables, suddenly I'm getting more results than I'm suppose to. A lot more. Please take a look:

include('connect.php');

$arrayX = "pieces.pieceID,playerDeck.amount,playerPieces.amount/pieces, playerDeck,playerPieces/where playerDeck.playerName = 'playerName' and playerPieces.playerName = 'playerName' and pieces.name = playerDeck.name = playerPieces.name";

$arrayX = explode('/', $arrayX);
$column = $arrayX[0];
$table = $arrayX[1];
$where = $arrayX[2];

$myArray = explode(',', $column);

global $connect;
$fetch = mysqli_query($connect,"SELECT $column FROM $table $where");
$count = mysqli_num_rows($fetch);

while($row=mysqli_fetch_array($fetch,MYSQLI_NUM)){

  $count --;
  $arrayCount = count($myArray);

  while($arrayCount > 0){
  $arrayCount--;
  $array[$count][$arrayCount]= $row[$arrayCount];
  }

}

$count = count($array);
echo $count." rows";

Expected output:

32 rows

Actual output:

31744 rows

31,744/32 = 922. Which means I got 922 of the same copies of the rows I needed. I have no idea just what I did wrong to get 922 copies, nor how is that actually even possible. If anyone can figure out just what I did wrong, please point it out. Thank you very much.

Upvotes: 0

Views: 77

Answers (3)

Gobea Alcoba Gonzalo
Gobea Alcoba Gonzalo

Reputation: 59

Cartesian product?

You try writing the query with "Inner Join".

Just like that:

pieces p INNER JOIN playerDeck pd
ON p.name = pd.name
INNER JOIN playerPieces pp
ON pd.name = pp.name
WHERE pd.playerName = 'playerName' AND
pp.playerName = 'playerName'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522050

Please run the following query:

SELECT COUNT(*) FROM
pieces p INNER JOIN playerDeck pd
ON p.name = pd.name
INNER JOIN playerPieces pp
ON pd.name = pp.name
WHERE pd.playerName = 'playerName' AND
pp.playerName = 'playerName'

If you get 31744 rows, the you will know that the logic in your query is not quite what you expected.

Upvotes: 1

frosty
frosty

Reputation: 2659

After seeing c4pone's answer, before it was deleted, I tried to edit that part where it said it was breaking it? And then it worked. 

$arrayX = "pieces.pieceID,playerDeck.amount,playerPieces.amount/pieces,playerDeck,playerPieces/where playerDeck.playerName = 'playerName' and playerPieces.playerName = 'playerName' and pieces.name = playerDeck.name and pieces.name = playerPieces.name";

Upvotes: 0

Related Questions