How to do this? :-S

[Adam93]

Hi,

I'm wanting to make a badge system. I have a table in my database called 'badges', which contains all the badge information and image address;

 

 

And then a table called 'user_badges', which holds the information of badges assigned to users;

 

 

So, I'm wanting to do a query on the member's profile page which will display each badge assigned to them. I thought I could do through mysql_fetch_array, and echo 'base' as an <img> for each corresponding badgeid and user id, I don't know if it's possible to link them or if it's too complicated? 

 

 

[jcbones]

How I would do it is 2 tables:

2. badges

3. user_badges (which would be a two column table of: user_id, badge_id

 

Then:

UN-TESTED

SELECT

  name, description, base 

FROM

  badges

WHERE

  id

IN (

   SELECT

     badge_id 

   FROM
     user_badges
   WHERE
     user_id = $current_user
 );

[Adam93]

 

How I would do it is 2 tables:

2. badges

3. user_badges (which would be a two column table of: user_id, badge_id

 

Then:

UN-TESTED

SELECT

  name, description, base 

FROM

  badges

WHERE

  id

IN (

   SELECT

     badge_id 

   FROM
     user_badges
   WHERE
     user_id = $current_user
 );

 

Can't seem to get it to work. I've tried;

<?php
$qry="SELECT * FROM `badges` WHERE `badgeid`=`badgeid` FROM `user_badges` WHERE `userid`='$bobba'";

$result=mysql_query($qry);
$getbadge = mysql_query($qry) or die(mysql_error());
   
if($result) {
while ($row = mysql_fetch_array($getbadge)) {
$imgs = explode(",",$row['base']);
foreach ($imgs as $badgeurl)
		  	{
		  	echo "<img src='".$badgeurl."''>";	
		  	}
} 
}
?>

But obviously that isn't a valid query, but that's what I'm trying to achieve, if you get me? :-S

[jcbones]

EDIT: Chromium hates me.  I'll be back with FF.

 

I gave you an idea with a specific query (that works).  

Step 1:

Get it working in your database, before you try to code anything in PHP.

So, load up your database software (phpmyadmin), or enter the console manually.

 

Step 2:

Create a table called user_badges, here is the schema.

CREATE TABLE IF NOT EXISTS `user_badges` (
  `user_id` int(11) NOT NULL,
  `badges_id` int(11) NOT NULL
)

Edited September 7, 2014 by jcbones

[jcbones]

Continuation of above post.

 

When you have the table built like above:

Populate the user_badges table with the id from the user table in the user_id column, and the id from the badges table in the badges_id column (NOT the badgesid column).

 

To clarify, you will not need to use the badgesid column from the badges table to tie any tables together.

 

Step 3.

When you get the desired results from the database, you can return to PHP to code your script.