Checkboxes and Queries help

[geniuscrew]

This is driving me krazy.

I have a table with some data which is put into an array, and displayed as checkboxes.

Now I have a separate table, which holds the data the user has selected (based on the checkbox options).

What would be the best way to display them both? IE, show the "options" and also show what the user has selected?

Many thanks

[AN-net]

you could left join the tables in a query:)

[geniuscrew]

Can you give me an example of how to Left Join properly? :) I did queries ages ago and can't remember how, and almost all interent resources I looked at are confusing :o


Cheers :D

[AN-net]

$DB_site->query("SELECT * FROM blah WHERE blah1='1234' LEFT JOIN blahothertable ON (blah.blah1.=blahothertable.blah6)");

i think thats correct but dont trust me 100% more like 80%;)

try phpfreaks.com or mysqlfreaks.com

[Velocd]

Left join will not work. That is, if you plan to show all the option fields. If you just want to grab the options that the user has "checked" in checkbox, then you can use a left join. You will have to make 2 queries.

Can you explain more about this table for holding the options? Such as, what fields it contains.

Also, are you selecting just one user from the user-table (e.g. $DB_site->query_first) or selecting more than one? (e.g. $DB_site->query)

[geniuscrew]

Appreciate the helps guys.

It's an abilities hack, for RPG boards and the likes.

It has 2 tables - rpgabilities and rpguserabilities.

rpgabilities - fields are abid (ability id - auto increments), name (name of ability), and description (describe what it does)

rpguserabilities - fields are id (unique, auto increments), username (name of user who is learning/has learnt the ability, name (name of ability), abexp (the exp the user has for that ability) and checked (yes no value for the checkbox).

Yup I will be using 1 user from the user table (where username = $bbuserinfo[name] i think) - it will show only the abilities of the member.

[geniuscrew]

*Bump* please help

Thanks :D

[Velocd]

You need to query the option table, then make a separate query for the user, then iterate through all the query options and when you come to one that the user has checked, you simply add the checked="checked" attribute to that checkbox tag. You can hold that bit of information in a variable, so to use it in a template.

I don't have time to write out the code for you, but I'll see what I can do later today if you still need help.

[geniuscrew]

I think I get what you mean - just don't understand how to do it.

It'd be great if you could provide some code to help, but I will try and do it myself too and see what i come up with. :D


Cheers again

[Velocd]

Your table setup for `rpguserabilities` is a bit ackward.

You have the field called `checked`, although shouldn't it be implicit that a user has an ability simply if the row exists? (since the row contains the ability name) ?

If I were you, I'd make the `rpguserabilities`contain the ability name for a field, but also the ability id (or just the ability id, incase you ever change the ability name you wouldn't want to have to update every row), and drop the `checked`.

Then you could use the following.

$abilities = $DB_site->query("
SELECT abid, name, description
FROM `abilities`");

$user = $DB_site->query_first("
SELECT id, username, name, abid, abexp
FROM `userabilities`
WHERE id=$bbuserinfo[userid]");

while ($ability = $DB-site->fetch_array($abilities))
{
if ($user['abid'] == $ability['abid'])
{
$is_checked = ' checked="checked"'; // to be used in template
// or substitute a template:
// eval('$is_checked = "'.fetch_template('template_is_checked') . '";');
}

eval('$option_row .= "'.fetch_template('template_option_row') . '";');
// this template contains the checkbox input information (name, description),
// and the $is_checked variable

unset($is_checked); // make sure to delete $is_checked
}

eval("dooutput(\"".gettemplate('template_options')."\");");
// output main template

[geniuscrew]

Thanks for all your help Velocd

Sorry I forgot to mention:

The way it will work is:

- The user ticks the checkboxes to show that he wants to learn that ability.
- He gets exp for all the abilities that are checked.

So just because it the ability is in the DB, doesn't mean he is learning it. Basically he can stop learning an ability too and put his "exp" towards a different 1.

Hope that better explains the situation.

Edited A field I left out in the rpguserabilities field is "learned" - it will update to "learned" once the required exp for that ability have been met (another field I forgot but I'm learning :D)


Thanks so much again

[geniuscrew]

I tried your method Velo, but I get all the boxes the user has checked and all the abilities from the rpgabilities table :/