0

I am a newbie to mysql, and originally I was going to create an enum datatype for my table but I found this question Create enum in SQL Server, and used it as a reference as that is what I needed. However when I tried to grab the data using the following sql statement it failed:

  $sql = "
  SELECT users.userID
       , users.firstName
       , users.lastName
       , membersrank.rank
       , memberstyle.style
    FROM users 
    JOIN membersrank 
      ON membersrank.rankID = memberships.rankID  
    JOIN memberstyle 
      ON memberstyle.styleID = memberships.styleID  
    JOIN memberships 
      ON users.userID = memberships.userID
  ";

The prepare statement failed when I ran it. I assumed something is wrong with the sql statement.

$stmt = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt, $sql)) {
  header("location: /index.php?error=stmtFailed");
  exit();
}
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);


These two tables below are the "user_rank" table referenced from the link.

Table: membersrank          Table: memberstyle
| rankID   | rank     |     | styleID  | style        |  
| -------- | -------- |     | -------- | ------------ |
| 0        | Guest    |     | 0        | N/A          |
| 1        | Member   |     | 1        | Casual       |
| 2        | Admin    |     | 2        | Competitive  |

Table: memberships    (is it the table, 'users', from the link)

| membershipID | userID   | rankID   | styleID  | 
| ------------ | -------- | -------- | -------- |
| 0            | 1        | 1        | 1        |   
| 1            | 2        | 2        | 1        |  
| 2            | 3        | 1        | 0        |  


Result im trying to achieve:

| userID       | firstName  | lastName | rank     | style    |     
| ------------ | ---------- | -------- | -------- | -------- |
| 0            | John       | Doe      | Member   | Casual   |  
| 1            | Jane       | Doe      | Admin    | Casual   |    
| 2            | Joe        | Smith    | Member   | N/A      |

How do I go about this and what is the proper sql statement? (if need the unlisted table 'users' is required as info then is as you see in the my result table without the rank and style columns.)

Thanks in advance!

Brandon
  • 159
  • 1
  • 1
  • 13
  • _"It failed."_ - how exactly? Wrong result set returned? An error? – El_Vanja Jan 07 '21 at 16:25
  • I used prepare statement and it didnt pass. $stmt = mysqli_stmt_init($conn); if(!mysqli_stmt_prepare($stmt, $sql)) { exit() } doesn't continue etc. updated in post – Brandon Jan 07 '21 at 16:32
  • I believe it's an issue of `JOIN` order. You're trying to join on `memberships.rankID` before you even joined on the `memberships` table. Move your last `JOIN` to the top. – El_Vanja Jan 07 '21 at 16:39
  • For future reference, read [this tutorial](https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param) to see how to find out the actual error with your statement. – El_Vanja Jan 07 '21 at 16:41
  • the prepare statement still failed, after changing join order. – Brandon Jan 07 '21 at 16:47
  • Then you have other issues as well, because with that fix, the [query works](http://www.sqlfiddle.com/#!9/4b9c5a/1). – El_Vanja Jan 07 '21 at 16:51

0 Answers0