modify loop to allow for checkboxes to be repopulated

[markbelli]

I made a loop in order to display a html table where a user can choose various items.

 

In this table there are several Items, each item has it's own checkbox.

 

each checkbox value is unique and stored in the Users_and_items mysql table (UserID, Item_ID, Item_Fullname, Item_Picture are the columns)

 

If a user chooses an item, then it's UserID and the Item_ID get stored in a preferences mysql table (User_ID, Item_ID are the 2 columns)

 

Everything runs fine but I would like to repopulate the checkboxes now.

 

I thought about a solution which I need to integrate in the loop to make this happen:

 

 

 

<?php

include 'connector.php';

echo "<form action='processor.php' method='post'>";

echo "<table>";

//loop to create AvailableItems html Table , each Item gets listed with it's unique Item_ID

$query1 = "SELECT UserID, Item_ID, Item_Fullname, Item_Picture FROM Users_and_items";
$result1 = @mysql_query($query1);

$num=mysql_numrows($result1);

$i=0;
while ($i < $num) {

//creates a new table row after 3 items displayed
if($i>0 && ($i%3==0)) { echo "<tr>"; }

//gets all the table data for the loop sequentially from mysql row by row

$UserID=mysql_result		($result1,$i,"UserID");
$Item_ID=mysql_result	($result1,$i,"Item_ID");
$Item_Fullname=mysql_result	($result1,$i,"Item_Fullname");
$Item_Picture=mysql_result	($result1,$i,"Item_Picture");

// ### NEW CODE INSERTION POINT - SEE OTHER CODEBOX IN THIS FORUM POST ###

echo "

<td>
<input type='checkbox' name='selecteditems[]' value='$Item_ID' id='$Item_ID' />
<img src='$Item_Picture'><br>
$Item_Fullname
</td>

";

$i++;

//creates a new table row after 3 items displayed
if($i>0 && ($i%3==0)) { echo "</tr>"; }


}

echo "</table>";

echo "
<input type='hidden' name='PRIMARYID' value='$UserID' />
<input type='submit' name='formSubmit' value='OK' />
</form>

";


?>

 

// POSSIBLE SOLUTION, I think that it would run fine but I am a mysql beginner and don't know how to code it completely!

// ### THIS CODE MUST BE INSERTED IN THE LOOP (the insertion point is specified in the other codebox):


$query_repopulate_chkboxes  = "SELECT Item_ID FROM preferences WHERE UserID=$UserID"; // we get all the past user preferences
$result_repopulate_chkboxes =  @mysql_query($query_repopulate_chkboxes); // we store the preferences in a variable

if $Item_ID of the html table that is being created by the php loop
is contained in $result_repopulate_chkboxes (which is the list of all the past user's chosen items)

then echo the function with the checkbox input checked='checked' ELSE 
output the normal echo (which leaves the checkbox unchecked).

While I think that this is  a feasible solution,  I am having difficulties to code this section and implement it in the loop since I am a beginner with php. Any help is appreciated!

[Dhanus]

Hi, Markbelli

 

I am not sure what the database tables actually look like, but I take it what you want to achieve is to have the script look into the 'preferences' table, and, if a record with the same UserID and Item_ID exists, check the checkbox in the display list. If that is correct, here is how I would do that.

 

First, I would modify the query to look like this:

 

 

$query1 = "SELECT UserID, Item_ID, Item_Fullname, Item_Picture, (SELECT COUNT(*) FROM preferences WHERE Users_and_items.UserID = preferences.UserID AND Users_and_items.Item_ID = preferences.Item_ID) AS is_checked FROM Users_and_items";

 

Please keep in mind that I am typing this on the fly, so I have not tested it, but if the tables work in the way I deduce from the context, the result should now have an extra field called is_checked, which should be the count of the item's occurrences in the 'preferences' table (ideally, either 1 or 0).

 

Following your coding style, you should be able to do the following to make it a boolean variable you can work with:

 

$is_checked = (mysql_result($result1,$i,"is_checked") > 0) ? true : false;

 

right where you have the // ### NEW CODE INSERTION POINT - SEE OTHER CODEBOX IN THIS FORUM POST ### line

 

So then, it should become easy to work with, assuming the rest of the script works just fine:

 

echo "

 

<td>

<input type='checkbox' name='selecteditems[]' value='$Item_ID' id='$Item_ID'";

 

if($is_checked) echo " checked='checked'";

 

echo " />

<img src='$Item_Picture'><br>

$Item_Fullname

</td>

 

";

 

So the checkbox looks checked.

 

Please let me know if it worked, or if you have any questions

 

Dhanus

[markbelli]

Hello Dhanus, first of all thanks for the reply!

 

When a user makes a choice, it's user id + the item id are stored in the preferences table.

 

So if there is a row in that table, it means that a checkbox has been checked (so I don't think that the checked 0 or 1 boolean value is needed, since the presence of a row in that table means that the choiche was made = 1)

 

What I need to do is:

SELECT item_id FROM Preference table WHERE UserId=CurrentUserId

 

and I can call the query result ---> $result[]

 

Now I got all the preferences of a certain user in the variable $result and what I want to do is very simple:

 

IF the current checkbox id ($Item_ID) that is being created by the loop equals any row in the $result array,  , then write checked, otherwise don't write anything.

 

but I am a beginner and I am not sure how to code it! any ideas?

[Dhanus]

So, basically, you want to make an array (a list) of all of the IDs from the preference table, and each time the script is drawing a checkbox, check its ID to see if it exists in the array. And if it does, check the checkbox. Well, no problem.

 

You could do it this way:

 

$res = mysql_query("SELECT item_id FROM preferences WHERE userid = '$userid'"); // The query

$list_of_ids = array(); // initialize the array

 

if(mysql_num_rows($res)) // if any results exist

{

    while($row = mysql_fetch_object($res)) // loop through the results and add them to the array

    {

        $list_of_ids[] = $row->item_id;

    }

}

 

Then, you can check any results against this array in the following way:

 

if(in_array($itemid, $list_of_ids) echo ' checked="checked"';

 

This checks whether the current checkbox ID exists in the array of IDs from preferences. The only problem with this approach is that the script is placed inside the loop, which causes it to execute a MySQL query each time the loop is executed. That is why I think it should come from the original query (which is possible in MySQL), instead of from a separate one.

 

Let me know if this works, and we can do a bit of optimization.

[markbelli]

thanks for the answer, there was just a missing ) in the last sentence, here is the complete one:

 

if(in_array($itemid, $list_of_ids) echo ' checked="checked"';

 

Do you confirm that if I do the sql query BEFORE the loop and then I just insert this:

 

if(in_array($itemid, $list_of_ids) echo ' checked="checked"';

 

php won't access mysql at every loop passage since it will just work with the array data?

 

thanks!

[Dhanus]

Well, the problem with doing that would be where the $userid value would actually come from. However, now that I check your script again, there's something that sort of doesn't click for me.

 

Are you trying to show ALL of the items stored in the database, and then only check the checkboxes the CURRENT USER (let's say, somebody who has logged in) chose in the past? Because if that is the case, then the $userid value should NOT come from the item table, but from the session array itself ($_SESSION), (or cookies, or whatever you're using for sessions).

[markbelli]

you are right, for testing purposes the userid value is stored in the same table, but in reality the user value will be retrieved by a different mysql query and stored as a variable, the rest stays the same (as previously described).

 

So with this loop, would php just get the array data or would it execute the query at every loop passage if I insert only this part into the loop:

 

if(in_array($itemid, $list_of_ids) echo ' checked="checked"';

 

 

 

[Dhanus]

To answer your question, yes. If you include only THAT code in the loop (and the MySQL query outside of the loop), the MySQL query will only be executed once. What will be executed every loop passage is a check against the data that's been PREVIOUSLY pulled out of the database.

However, you need a different method to identify the current user. Would you need help with that as well? Given it'd be a rather easy thing to do, which would make your script fully functional

 

Dhanus

 

PS: However, here's a correction to my syntax:

if(in_array($itemid, $list_of_ids)) echo ' checked="checked"';