Printing opposite results from the same query (or printing outside a while loop?)

[Jim R]

I have 64 rows of players and want each User to be able to choose to bookmark an player, as well as unbookmark it too.

I have a bookmark table set up, and each time a User decides to bookmark(+) or unbookmark(-) an player a row is created in the data table.   (Matching the player ID and User ID) That's working fine.  

A query reads the most recent row for each player to determine if there is a + or - button next to each player.  Testing the query and adding some dummy data, that part of it works too.  However, the issue is the initial state, which I want to be a +.  Once I go live, the table will be empty, nothing for the query to return/produce.

How do I create an initial state of a + with no rows and have it not used or swapped out when Users start clicking + or -?

 

  $query = "SELECT b.id, bookmark,playerID,userID,p.id 
                FROM a_player_bookmark b
                LEFT JOIN a_players p
                ON '". $id ."' = playerID
                WHERE userID = '". $userID ."'&&'". $id ."' = playerID
                ORDER BY b.id desc
                LIMIT 1
                ";

    $results = mysqli_query($con,$query);
    echo mysqli_error($con);
    while($row = mysqli_fetch_assoc($results)) {    

    echo $row['bookmark'];

        if($row['bookmark'] == 0) {
            echo '-'; // this is where a form goes to remove a bookmark
        }       
        else {
            echo '+'; // this is where a form goes to add a bookmark
        }

    }   

I tried to get it with CASE, but I don't think that's the right path.  I also looked at UNION.  I was able to get it to produce an initial state of +, but it still printed the already made up sample data too (so I have three instances of ++ or +-).

 (Players 2, 4 and 4)

 

Here is the what the UNION query looked like:

	$query = "(SELECT b.id, bookmark,playerID,userID,p.id 
				FROM a_player_bookmark b
				LEFT JOIN a_players p
				ON '". $id ."' = playerID
				
				WHERE userID = '". $userID ."'&&'". $id ."' = playerID
				ORDER BY b.id desc
				LIMIT 1)
			UNION
				(SELECT b.id, bookmark,playerID,userID,p.id 
				FROM a_player_bookmark b
				LEFT JOIN a_players p
				ON '". $id ."' = playerID
				
				WHERE userID = '". $userID ."'
				ORDER BY p.id desc
				LIMIT 1) 
				";

 

[kicken]

Your query only returns one row, so there is no need for a loop.  Just attempt to fetch that one row and see if any results come back.  If so, check them.  If not, use your default.

$results = mysqli_query($con,$query);
$row = mysqli_fetch_assoc($results);
if ($row && $row['bookmark']){
  echo '-';
} else {
  echo '+';
}

 

[Barand]

Sounds like the solution is a simple LEFT JOIN

player P                                                                  bookmark B
+-------------+------------------+                                       +-------------+
|  player_id  |   name           |                                       |  player_id  |
+-------------+------------------+                                       +-------------+
|      1      |   Curly          |                                       |      2      |
|      2      |   Larry          |                                       |      4      |
|      3      |   Mo             |                                       +-------------+
|      4      |   Abbott         |            
|      5      |   Costello       |            
+-------------+------------------+            




                                       player P LEFT JOIN bookmark B
                                        +-------------+------------------+-------------+
                                        | P.player_id |   P.name         | B.player_id |
                                        +-------------+------------------+-------------+
                                        |      1      |   Curly          |    NULL     |
                                        |      2      |   Larry          |      2      |
                                        |      3      |   Mo             |    NULL     |
                                        |      4      |   Abbott         |      4      |
                                        |      5      |   Costello       |    NULL     |
                                        +-------------+------------------+-------------+

Where no bookmark matches, you have NULL values for the bookmark data. So now you you know whether to put a "+" or a "-"

[Jim R]

6 hours ago, kicken said:

Your query only returns one row, so there is no need for a loop.  Just attempt to fetch that one row and see if any results come back.  If so, check them.  If not, use your default.

$results = mysqli_query($con,$query);
$row = mysqli_fetch_assoc($results);
if ($row && $row['bookmark']){
  echo '-';
} else {
  echo '+';
}

 

That's pretty damn close.  For some reason it's not recognizing the last row of the data table, or let's say I have a + where there should be a -.

 

I'm going to play around with what @Barand  suggested too.  I have a LEFT JOIN, but it's bookmark Left Join players instead of what Barand has. 

[Jim R]

5 hours ago, Barand said:

Where no bookmark matches, you have NULL values for the bookmark data. So now you you know whether to put a "+" or a "-"

Left joining as you had it didn't change anything, unless there was something in the output I needed to change.  My query had a left join too. 

 

[Jim R]

7 hours ago, kicken said:

Your query only returns one row, so there is no need for a loop.  Just attempt to fetch that one row and see if any results come back.  If so, check them.  If not, use your default.

$results = mysqli_query($con,$query);
$row = mysqli_fetch_assoc($results);
if ($row && $row['bookmark']){
  echo '-';
} else {
  echo '+';
}

 

Upon further review, it's reversing the results, and reversing the +/- logic changes the rows that haven't had any action on them yet.  

[Jim R]

Never mind.  I'm pretty sure @kicken output worked.  I might've gotten my wires crossed.  

 

Thank you to both of you for your time.