9 Replies - 341 Views - Last Post: 31 January 2013 - 01:12 PM Rate Topic: -----

#1 snig08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 22-January 13

Completion Table with PHP and MySQL

Posted 30 January 2013 - 01:07 PM

I need a table that lists challenges and then to the right lists "completed" or "uncompleted"
Currently I have 3 tables. the first is Members, in this table I have Member_Names.
my 2nd table is called challenges. It has a Challenge_Name column and a Challenge_Description.
In my 3rd and final table called completed_challenges_junction I have a Member_Names and a Challenge_Name.
This lists who has completed what challenge.

Now for the hard part.

In my php I need a way to get the vales from the challenges table on the left and then I need it to look through the completed_challenges_junction to see if that member name appears then display complete. I can get it to display the values of the challenges table like I want it to but I can't get it to show complete or not. Also in my sql statements I have been using WHERE Member_Names='$_SESSION[username]' so I have no problem searching for the specific user.
Any help is much appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: Completion Table with PHP and MySQL

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3317
  • View blog
  • Posts: 11,201
  • Joined: 12-December 12

Re: Completion Table with PHP and MySQL

Posted 30 January 2013 - 02:27 PM

The following may work but I have no idea :)

SELECT Members.Member_Names, challenges.Challenge_Name, 
SELECT CASE completed_challenges_junction.Member_Names 
WHEN NULL THEN 'Not Complete' ELSE 'Complete' END AS `Completed` 
FROM (Members LEFT JOIN completed_challenges_junction ON Members.Member_Names = completed_challenges_junction.Member_Names) LEFT JOIN challenges ON completed_challenges_junction.Challenge_Name = challenges.Challenge_Name


However, it will not show a challenge if that challenge does not appear at least once in the junction table. If you wish to include all challenges (whether or not they appear in the junction table) then you would need a further subquery within the expression, I believe.
Was This Post Helpful? 0
  • +
  • -

#3 snig08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 22-January 13

Re: Completion Table with PHP and MySQL

Posted 30 January 2013 - 02:43 PM

I did actually come up with some sql code that was very similar to that. My code displayed all the challenges and even a NULL value where no one had earned it, the only problem was that it would bring back other members completed challenges instead of a specific value. Here is the code I used a while back:
SELECT DISTINCT challenges.Challenge_Name, challenges.Challenge_Description, completed_challenges_junction.Member_Names FROM challenges LEFT JOIN completed_challenges_junction ON challenges.Challenge_Name=completed_challenges_junction.Challenge_Name
WHERE Member_Names='snig08' OR challenges.Challenge_Name=challenges.Challenge_Name

At the minute I have given up with joining the tables and I was using a few complex php statements that was leading me no where. Do you think I could use my query and then in php use something like an if statement to get rid of other members?
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3317
  • View blog
  • Posts: 11,201
  • Joined: 12-December 12

Re: Completion Table with PHP and MySQL

Posted 30 January 2013 - 02:56 PM

Whichever way you cut it you are still going to have to join the tables. It is much better, and more efficient, if you do most of the work in the SQL statement.

However, if you can get the data-rows that you need then, when looping through them in the PHP, you could check for a null value (is_null) and insert 'Complete' or 'Not complete' into the relevant column-cells.

[The design I suggested previously would have been better because you would have a field in the junction table named 'Completed' which could be either true or false - then the joins would not need to be so complicated. But anyway..]
Was This Post Helpful? 0
  • +
  • -

#5 snig08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 22-January 13

Re: Completion Table with PHP and MySQL

Posted 30 January 2013 - 04:00 PM

Here is the php code I currently have, it displays all of the information but also displays it more than once.
$sql="SELECT DISTINCT challenges.Challenge_Name, challenges.Challenge_Description, completed_challenges_junction.Member_Names FROM challenges LEFT JOIN completed_challenges_junction ON challenges.Challenge_Name=completed_challenges_junction.Challenge_Name
WHERE Member_Names='$_SESSION[myusername]' OR challenges.Challenge_Name=challenges.Challenge_Name";
$tablecontents=mysql_query($sql);
$table = "<table border='1px' bordercolor='#000000' width='750px' align='center''><tr><th>Challenge Name</th><th>Challenge Description</th><th>Completion Status</th></tr>";
while($row=mysql_fetch_array($tablecontents)){

		
		if($row[Member_Names]==$_SESSION[myusername]){
		$table .= "<tr><td>$row[Challenge_Name] </td><td> $row[Challenge_Description]</td><td>COMPLETED</td><tr>";
		}
		if($row[Member_Names]!=$_SESSION[myusername]){
		$table .= "<tr><td>$row[Challenge_Name] </td><td> $row[Challenge_Description]</td><td>UNCOMPLETED</td><tr>";			
			}
	
	if($row[Member_Names]==NULL){
		$table .= "<tr><td>$row[Challenge_Name] </td><td> $row[Challenge_Description]</td><td>UNCOMPLETED</td><tr>";
		}

	//else if($row[Member_Names]!=$_SESSION[myusername]){
	//$table .= "<tr><td>$row[Challenge_Name] </td><td> $row[Challenge_Description]</td><td>UNCOMPLETED</td><tr>";
	//}
	//if($row[Member_Names]==NULL||$row[Member_Names]!=$_SESSION[myusername]){
	//$table .= "<tr><td>$row[Challenge_Name] </td><td> $row[Challenge_Description]</td><td>UNCOMPLETED</td><tr>";
	//	}
	
	}
	$table.="<table>";
	echo $table;


the table that is produced on the website is this:

Challenge Name Challenge Description Completion Status
All Pro Get 2 head shots with a single bullet. COMPLETED
Noob Tube Time Kill 3 or more enemies with a single shot from an under barrel grenade launcher. UNCOMPLETED
Noob Tube Time Kill 3 or more enemies with a single shot from an under barrel grenade launcher. COMPLETED
Medic +1 Revive a downed team mate UNCOMPLETED
Medic +1 Revive a downed team mate UNCOMPLETED


once again thanks for any help!
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3317
  • View blog
  • Posts: 11,201
  • Joined: 12-December 12

Re: Completion Table with PHP and MySQL

Posted 30 January 2013 - 04:37 PM

You need to use if.. else if as you only want one table-row per database-row.

If the member-name is null then this is true:
if($row[Member_Names]!=$_SESSION[myusername]){

but this is also true:
if($row[Member_Names]==NULL){

so you will get two rows output.

Why are you checking the session-username within the loop, but outputting the same data anyway?? If you want to output a row for each row returned from the database then just:

if (is_null($row[Member_Name])) {
    // uncompleted
else {
    // completed
}


but your WHERE clause is incorrect, so I'm not sure what specific output you want:
challenges.Challenge_Name=challenges.Challenge_Name"

.. this is always true.

I think you should either delete the WHERE clause entirely, or just select rows for the current user - depending on your intent.
Was This Post Helpful? 0
  • +
  • -

#7 snig08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 22-January 13

Re: Completion Table with PHP and MySQL

Posted 31 January 2013 - 01:52 AM

right I will try again today to get this working but either way I dont think I can display entries if another user has completed the challenge. I had an idea, Could I not use a 2nd query to retreive the challenge names and descriptions from the challenges table after showing the completed challenges, but missing out any challenge names that have already appeared from the first query?
Was This Post Helpful? 0
  • +
  • -

#8 snig08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 22-January 13

Re: Completion Table with PHP and MySQL

Posted 31 January 2013 - 04:49 AM

Here is my latest progress and I think that this just needs some fine tqeaking to get it working:
$sql="SELECT DISTINCT challenges.Challenge_Name, challenges.Challenge_Description, completed_challenges_junction.Member_Names FROM challenges LEFT JOIN completed_challenges_junction ON challenges.Challenge_Name=completed_challenges_junction.Challenge_Name
WHERE Member_Names='$_SESSION[myusername]'";
$tablecontents=mysql_query($sql);
$sql2="SELECT DISTINCT Challenge_Name, Challenge_Description FROM challenges";
$tablecontents2=mysql_query($sql2);
$table = "<table border='1px' bordercolor='#000000' width='750px' align='center''><tr><th>Challenge Name</th><th>Challenge Description</th><th>Completion Status</th></tr>";
while($row=mysql_fetch_array($tablecontents)){

		
	if($row[Member_Names]==$_SESSION[myusername]){
		$table .= "<tr><td>$row[Challenge_Name] </td><td> $row[Challenge_Description]</td><td>COMPLETED</td><tr>";
		}
		
	if($row[Member_Names]==NULL){
		$table .= "<tr><td>$row[Challenge_Name] </td><td> $row[Challenge_Description]</td><td>UNCOMPLETED</td><tr>";
		}

		
	}
	while($row2=mysql_fetch_array($tablecontents2)){
	if($row[Challenge_Name]!=$row2[Challenge_Name]){
	$table .= "<tr><td>$row2[Challenge_Name] </td><td> $row2[Challenge_Description]</td><td>UNCOMPLETED</td><tr>";
	}
	}
	$table.="<table>";
	echo $table;
	
}


All I need is to miss out on the 2nd while loop if the challenge name has already been printed.

Here is the output on the website:

Challenge Name Challenge Description Completion Status
Noob Tube Time Kill 3 or more enemies with a single shot from an under barrel grenade launcher. COMPLETED

All Pro Get 2 head shots with a single bullet. UNCOMPLETED

Noob Tube Time Kill 3 or more enemies with a single shot from an under barrel grenade launcher. UNCOMPLETED

Medic +1 Revive a downed team mate UNCOMPLETED

Here is my latest progress and I think that this just needs some fine tqeaking to get it working:
$sql="SELECT DISTINCT challenges.Challenge_Name, challenges.Challenge_Description, completed_challenges_junction.Member_Names FROM challenges LEFT JOIN completed_challenges_junction ON challenges.Challenge_Name=completed_challenges_junction.Challenge_Name
WHERE Member_Names='$_SESSION[myusername]'";
$tablecontents=mysql_query($sql);
$sql2="SELECT DISTINCT Challenge_Name, Challenge_Description FROM challenges";
$tablecontents2=mysql_query($sql2);
$table = "<table border='1px' bordercolor='#000000' width='750px' align='center''><tr><th>Challenge Name</th><th>Challenge Description</th><th>Completion Status</th></tr>";
while($row=mysql_fetch_array($tablecontents)){

		
	if($row[Member_Names]==$_SESSION[myusername]){
		$table .= "<tr><td>$row[Challenge_Name] </td><td> $row[Challenge_Description]</td><td>COMPLETED</td><tr>";
		}
		
	if($row[Member_Names]==NULL){
		$table .= "<tr><td>$row[Challenge_Name] </td><td> $row[Challenge_Description]</td><td>UNCOMPLETED</td><tr>";
		}

		
	}
	while($row2=mysql_fetch_array($tablecontents2)){
	if($row[Challenge_Name]!=$row2[Challenge_Name]){
	$table .= "<tr><td>$row2[Challenge_Name] </td><td> $row2[Challenge_Description]</td><td>UNCOMPLETED</td><tr>";
	}
	}
	$table.="<table>";
	echo $table;
	
}


All I need is to miss out on the 2nd while loop if the challenge name has already been printed.

Here is the output on the website:

Challenge Name Challenge Description Completion Status
Noob Tube Time Kill 3 or more enemies with a single shot from an under barrel grenade launcher. COMPLETED

All Pro Get 2 head shots with a single bullet. UNCOMPLETED

Noob Tube Time Kill 3 or more enemies with a single shot from an under barrel grenade launcher. UNCOMPLETED

Medic +1 Revive a downed team mate UNCOMPLETED
Was This Post Helpful? 0
  • +
  • -

#9 snig08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 22-January 13

Re: Completion Table with PHP and MySQL

Posted 31 January 2013 - 12:46 PM

Guys today I have made great progress on getting this working, I can get it to print out all of the values from the challenges table and then state complete if a username and a challenge name appear in a junction table.
My last problem is showing uncomplete because now it just simply shows nothing in my table. If no one can help and I don't figure it out soon I think I will leave it how it is. Here is my current code:
$sql="SELECT DISTINCT challenges.Challenge_Name, challenges.Challenge_Description, completed_challenges_junction.Member_Names FROM challenges LEFT JOIN completed_challenges_junction ON challenges.Challenge_Name=completed_challenges_junction.Challenge_Name
WHERE Member_Names='$_SESSION[myusername]'";
$tablecontents=mysql_query($sql);
$sql2="SELECT DISTINCT Challenge_Name, Challenge_Description FROM challenges";
$tablecontents2=mysql_query($sql2);
$table = "<table border='1px' bordercolor='#FFFFFF' cellpadding='5' bgcolor='771012' cellspacing='3' width='750px' align='center''><tr><th><div style='font-size:20px;'>Challenge Name</div></th><th><div style='font-size:20px;'>Challenge Description</div></th><th><div style='font-size:20px;'>Completion Status</div></th></tr>";

while($row=mysql_fetch_array($tablecontents2)){
	mysql_data_seek($tablecontents, 0);
	$table .= "<tr><td>$row[Challenge_Name] </td><td> $row[Challenge_Description]</td>";
	for($row2;$row2=mysql_fetch_array($tablecontents);)/>{
		if($row[Challenge_Name]==$row2[Challenge_Name]){
		$table .= "<td>COMPLETED</td><tr>";
		}
	}
	
	//$table .= "<td>UNCOMPLETED</td><tr>";

	}
	$table.="<table>";
	echo $table;
}

Was This Post Helpful? 0
  • +
  • -

#10 snig08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 22-January 13

Re: Completion Table with PHP and MySQL

Posted 31 January 2013 - 01:12 PM

JUST FIXED IT!!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1