Jump to content

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


Jim R

Recommended Posts

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)

1656381298_ScreenShot2020-03-29at11_39_17PM.png.feef46774b2c757439f9e4934a5fe02e.png

 

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) 
				";

 

Link to comment
Share on other sites

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 '+';
}

 

Link to comment
Share on other sites

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 "-"

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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. 

 

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.