?
Solved

Using PHP with MySQL to fill a Select Box and Pre-Select Items

Posted on 2008-11-15
7
Medium Priority
?
757 Views
Last Modified: 2013-12-13
Hey all, thanks in advance for any help you can provide.

I know i'm overlooking something silly here but I can't get my little piece of code to work for pre-selecting several items in a select box (listbox).  I've attached the code in question.  What I am trying to doo is fill the select box with category names (this part works), but I also want to pre-select some items based on whether a particular user has those categories assigned to them.  The user category assignment table just consists of CATID paired with USERID so i'm trying to do a lookup on both and then insert the "selected" attribute when they are found.

What happens with this code is that it selects everything instead of just the ones that match.  How can I check to see if a result is returned from the MySQL query without needing to pull the row etc?  

Thanks,
Nathan


<select name="lstCategories" size="20" multiple="multiple" id="lstCategories">
         <?php
	  
$sql = "SELECT * FROM $tableCategories ORDER BY CATNAME ASC";
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
 
if ($myrow = mysql_fetch_array($sql_result)) {
 
 
	  do 
	  {
		  $sqlCat = "SELECT * FROM $tableReviewerInfo WHERE USERID = $userID AND CATID = '".$myrow['CATID']."'";
		  $sql_resultCat = mysql_query ($sqlCat, $connection) or die ('request "Could not execute SQL query" '.$sql);
    	printf("<option value={$myrow['CATNAME']}");
		if ($catExists = mysql_fetch_array($sql_result))
		{
			printf(" selected");
		}
		printf(">{$myrow['CATNAME']}</option>");
	  } while ($myrow = mysql_fetch_array($sql_result));
}
  ?>
        </select>

Open in new window

0
Comment
Question by:ironwill96
  • 4
  • 2
7 Comments
 
LVL 6

Accepted Solution

by:
cr4ck3rj4ck earned 1500 total points
ID: 22967877
if ($catExists = mysql_fetch_array($sql_result))

should be

if ($catExists == mysql_fetch_array($sql_result))

Try that,
CJ
0
 
LVL 2

Author Comment

by:ironwill96
ID: 22968081
Hmm, that stops them from all being selected but it selects the item AFTER the one it should and then the loop stops working (it doesnt finish filling the select box).  Any ideas?

Thanks,
Nathan
0
 
LVL 2

Author Comment

by:ironwill96
ID: 22968092
Whoops, looks like it should also be $sql_resultCat

Trying that change now..
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 2

Author Comment

by:ironwill96
ID: 22968096
Yeah it was because I didn't have $sql_resultCat in my original check (it should remain = though, not ==).

Thanks for pointing me in the right direction!

Nathan
0
 
LVL 2

Author Closing Comment

by:ironwill96
ID: 31517116
Thanks for the help, I noticed the actual issue after going in to make your suggested change!
0
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 22968177
Change Line 15
                if ($catExists = mysql_fetch_array($sql_result))
To
                if ($catExists = mysql_fetch_array($sql_resultCat))
0
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 22968181
Try the above example FIRST

I also rewrote the code (not tested) below also...
<select name="lstCategories" size="20" multiple="multiple" id="lstCategories">
         <?php
          
$sql = "SELECT * FROM $tableCategories ORDER BY CATNAME ASC";
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
 
while ($myrow = mysql_fetch_array($result)){
                  $sqlCat = "SELECT * FROM $tableReviewerInfo WHERE USERID = $userID AND CATID = '".$myrow['CATID']."'";
                  $sql_resultCat = mysql_query ($sqlCat, $connection) or die ('request "Could not execute SQL query" '.$sql);
 
        printf("<option value={$myrow['CATNAME']}");
                if ($catExists = mysql_fetch_array($sql_resultCat))
                {
                        printf(" selected");
                }
                printf(">{$myrow['CATNAME']}</option>");
          }
}
  ?>
        </select>

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month17 days, 3 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question