Welcome! Log In Create A New Profile

Advanced

spinning smiley sticking its tongue out YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?

Posted by Anonymous User 
Announcements Last Post
Announcement SoC Curricula 09/30/2017 01:08PM
Announcement Demarcation or scoping of examinations and assessment 02/13/2017 07:59AM
Announcement School of Computing Short Learning Programmes 11/24/2014 08:37AM
Announcement Unisa contact information 07/28/2011 01:28PM
Hi there,
please just give me a yes or no. I am getting mad with searching and not finding any solution, so perhaps there is none...

Let me explain what I intend to do:
When establishing form sheets - this is HTML - I use also drop down lists.
My intention is to fill these lists with content from a table, so to avoid entering the option values right in the script which requires manual update whenever the table content changes:
ex: coursename from the table course

Unfortunately, I can nowhere find any hint, neither in manual, textbook nor Internet that this is possible.

Please just tell me whether this is possible or not before I continue searching.

Thanks & Regards
Iris
Anonymous User
confused smiley Re: final prac: possible to fill drop down lists automatically from tables?
October 15, 2008 02:57PM
hi, Iris

Its easy just write:

<select name="name">
<option>
$query = "Select * from table";
then loop through your query
therefore echo the results.
<option>
</select>

I hope this helps cool.

Mamog
Hi Mamog,
Thanks for your support.

I am trying to figure it out, the problem I have is the loop since I have to find a way to tell him to execute until there is no more entry in the field coursename.

Still on search...

Iris
Anonymous User
confused smiley Re: final prac: possible to fill drop down lists automatically from tables?
October 15, 2008 04:16PM
Hi, Iris

Ja you just say:

while($row = msyql_fetch_array($result))
{
//then for value return cid;
//then for display return cname;

//therefore echo out your options
}

Cool Iris.thumbs up
avatar
Mac
confused smiley Re: final prac: possible to fill drop down lists automatically from tables?
October 16, 2008 07:24AM
Quite correct.

echo "<SELECT NAME=whatever><OPTION SELECTED value='Select'>Select a whatever</OPTION>";

$db=mysql_connect();
mysql_select_db("whatever", $db);
$query=mysql_query("SELECT whatever FROM whatever ORDER BY whatever", $db)
or die(mysql_error());
$numrows = mysql_numrows($query);
$i = 0;
while ($i<$numrows) {
$whatever= mysql_result($query,$i,whatever);
echo "<OPTION>$whatever</OPTION>";
$i++;
}

echo "</select>";
avatar
Mac
confused smiley Re: Re: final prac: possible to fill drop down lists automatically from tables?
October 16, 2008 07:31AM
I can take it further:

$db=mysql_connect();
mysql_select_db("whatever", $db);
$query=mysql_query("SELECT whatever FROM whatever ORDER BY whatever", $db)
or die(mysql_error());
$numrows = mysql_numrows($query);

if($numrows>0) { //there is whatever available to put into this drop list - show dropdown

echo "<SELECT NAME=whatever><OPTION SELECTED value='Select'>Select a whatever</OPTION>";


$i = 0;
while ($i<$numrows) {
$whatever= mysql_result($query,$i,whatever);
echo "<OPTION>$whatever</OPTION>";
$i++;
}

echo "</select>";

}
else { //there are no whatever availabe therefore
echo "No choices currently available"";
}


//note the position of the <select> </select> in both examples - they are outside the loop since you only want to repeat the drop down choices, i.e. <option></option> which is then inside the loop.
You can't have the <select> inside the loop.
Thanks Mamog, Thanks Mac!

I really worked hard on it today and I found the solution before I saw your messages. Anyhow it is a good feeling to solve something by yourself. Have a look on page 171 in the textbook, there is an example.

Have fun with PHP
Iris
spinning smiley sticking its tongue out Re: YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?
October 20, 2008 01:32AM
am struggling with the dropdown in the reg form. got something that kinda works but not completely satifactory. have tried to make sense of the 2 solutions above but none of it making any sense to me whatsoever. i can't even see how the they are related to one another. so far, i have this for the dropdown:

<p>
<select name="course">

<?php
$result = mysql_query("SELECT * FROM course"winking smiley;
while ($row = mysql_fetch_array($result)) {
echo $row[0], "<br>";
?>
<option value="<?php echo $cname; ?>" >
<?php
}
?>
</option>
</select>
</p>


problem is, it keeps leaving out the first entry in the course table every time. so if my course table entries are this:

course N 18
Course B 7
Course C 8
Course D 9
Course F 15

my dropdown looks like this:

Course B
Course C
Course D
Course F

also, i am able to add/delete entries to the course table, but it won't reassign the empty auto increment numbers to a new entry, so hence the jump in cid's. and when i delete an earlier entry, it will insert a new entry into that position. is this correct? (ie. entry 'Course N' is now first in the cue where 'Course A' used to be)

tristan
avatar
Mac
spinning smiley sticking its tongue out Re: YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?
October 20, 2008 06:45AM
Relook at the example I gave - I made us of $whatever - no use giving you the complete code.....

Oh, I made an error there - should read:

echo "<OPTION value='$whatever'>$whatever</OPTION>";


You cannot have this...

<option value="<?php echo $cname; ?>" >
<?php
}
?>
</option>

The closing </option> is outside your loop, meaning this is what you get:

In any event this is more HTML than PHP related - you should know that a drop-down is
<select name.....>
<option value=...>whatever</option>
<option value=...>whatever</option>
<option value=...>whatever</option>
</select>

The only trick here is that the <option></option> goes into the while loop, being repeated with every loop and getting filling with the values (course names) it gets from the db.



echo "<SELECT NAME=Email><OPTION SELECTED value='Select'>Select</OPTION>";
$mail=mysql_query("SELECT email FROM dept.staff ORDER BY sname"winking smiley or die(mysql_error()); //I did not specify a db, so I prefer to use "FROM db.table as in "dept.staff".

$nummail = mysql_numrows($mail);
$i = 0;
while ($i<$nummail){
$nextmail = mysql_result($mail,$i,'email'winking smiley;
echo "<OPTION value='$nextmail>$nextmail</OPTION>";
$i++;
}
echo "</SELECT>";


I can make it prettier, adding an if before the loop:

....
$nummail = mysql_numrows($mail);

if($nummail > 0){ //there are entries, so generate a drop-down

echo "<SELECT NAME=Email><OPTION SELECTED value='Select'>Select</OPTION>"; //moved it here inside the if

$i = 0;
while ($i<$nummail){
$nextmail = mysql_result($mail,$i,'email'winking smiley;
echo "<OPTION>$nextmail</OPTION>";
$i++;
} //end while

echo "</SELECT>";

} //end if true

else {
echo "No choices, since no email addresses found! // don't show a drop-down - show and error or tip on what to do now
}
avatar
Mac
spinning smiley sticking its tongue out Re: Re: YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?
October 20, 2008 12:19PM
the first record will have an id of 1. You delete it, it cannot be used again. So if you had 10 entries, a new entry will have an id of 11, then 12 and so on. So your select statement detemines the order

e.g. select....... ORDER BY something ASC

You have to choose by what you want to order it - by id, or by coursename ASC (ascending) or DESC (or if you don't ASC or DESC, it is assumed ASC).
If I want the latest entry at the top of a list (as in guestbook entries) then by id is probably best to use.....you must decide.
spinning smiley sticking its tongue out Re: Re: Re: YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?
October 21, 2008 01:25PM
i appreciate the fact that you don't want to spell out the script for me, but i am now even more confused with the additional introduction into the equasion of nummail, nextmail etc. without having access to the same db that you're using for those examples, i don't know whether these are tables, fields, db's or what, so it's only confusing the issue more.
also, having 2 or more variations is not helping me understand the mechanism in any way. i can't figure out which part of the while is calling which part of the db/table.
i understand the principle by which the <option></option> needs to go into the while loop (so that the result is contained in the dropdown) but am somehow defeated by the retrieval of the relevant bits from the db.

as to the cid ordering & number assignment, thanx this is now clear to me.

tristan
spinning smiley sticking its tongue out Re: YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?
October 21, 2008 10:30PM
I have no problem in getting the cname data from table "course", BUT when the student register select the displayed course name I suppose it must be sent to the "course_student" table, it would have been no problem if there was a column called cname in the course_student table, but there is only a CID (curse id) column. So I think one must get a particular row from the course table and display the cname and when that is selected submit the CID to course_student. Am I right or in a wild goose chase. Please can someone help or advise me. I am running in circles smile I have spent hours Googling and searching in textbook and on forum, but I am confused. Please help.

JB
avatar
Mac
spinning smiley sticking its tongue out Re: YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?
October 22, 2008 06:40AM
mail, nextmail is names that I gave. I could have called it my_wife, my_cat - whatever.These are idendependent of any database. So you could use the code just like I gave it if you wanted to. So I merely used $mail since I'm doing a query find emails and $mail reminds me what the variable holds, $num(ber)mail to help me know that this variable is for the number of emails I found etc.

Strictly speaking you just need to replace
SELECT email FROM dept.staff ORDER BY sname
with your query and
.....($mail,$i,'email'winking smiley;
the email with the db field, and the code should work.
avatar
Mac
spinning smiley sticking its tongue out Re: Re: YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?
October 22, 2008 06:49AM
// I state my query i.e. what data do I want from what database.table, and how I want it sorted. I name the query $mail. Could name it $jupiter if I wanted to - but then I need to use $jupiter right through the code below (in the place of $mail).

$mail=mysql_query("SELECT email FROM dept.staff ORDER BY sname"winking smiley or die mysql_error());

// I now am going to check the number of rows. I can use this information in many other ways, but here I use it only to help me loop through the rows found.For example, if I echo "$nummail" I may get 10, which tells me there is 10 rows (10 emails).

$nummail = mysql_numrows($mail);

// here my loop starts. This is fixed. If I used $my_dog = mysql_numrows($mail) then further down here I would say while ($i<$my_dog){ .....

$i = 0;
while ($i<$nummail){
$nextmail = mysql_result($mail,$i,'email'winking smiley;
echo "<OPTION value='$nextmail>$nextmail</OPTION>";
$i++;
}
echo "</SELECT>";

// now I said I can use $nummail for other things - as I did in the example higher up.

if($nummail > 0){
//that means there are rows and thus entries in the db, so I can show the drop-down.... so I add the while loop in here
}
else {
// there are no rows (and thus no entries), therefore it is no use to show the drop-down. Perhaps a warning to the administrator that he should make sure there are courses in the db...?
}


And finally - understand that functions are written function(), so mysql_result(...) is a function (php has many built-in functions). You can guess what this function does. Of course, you can write your own functions. So reading code become seasier if you knwo at least what a function is.
avatar
Mac
spinning smiley sticking its tongue out Re: Re: Re: YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?
October 22, 2008 07:00AM
> Am I right or in a wild goose chase

Quite correct.So you would use
.....
$cname= mysql_result($mail,$i,'cname'winking smiley;
$cid= mysql_result($mail,$i,cid);
.....
echo "<OPTION value='$cid>$cname</OPTION>";

Here you're sending the cid to the next step (to insert it into course_student).

I've mentioned eslewhere that the db structure we provide is not necessary the best design (as you correctly think, there is a good case for inserting the course name as opposed to the course id into the student_course, but then that would be too easy to write the code not so smiling smiley We're far more interested in the coding aspect.

When finished with this course, do a Google search on "tutorial relational database design". Here is a good intro http://www.geekgirls.com/databases_from_scratch_3.htm You may well argue that we should teach you correct from the start, but this is not a relational database design course.... which requires another 6 months. And as said, adding the cid as opposed to the course name means you have to think a little bit more...!
thanx Mac, i followed your breakdown of the various parts & was able to understand all the steps more clearly; my dropdown finally works!

next problem now is to get the cid & sno to the student_course table; i can see the logic of converting the cname entry from the reg form to a cid to enter into the table but not quite sure at which point this should happen. i am using a 'via browser' form to get the data from my reg form to the student table but not sure if it should be happening at this level or if an additional level is called for.

one question about the 'edit courses' section of the form: what data is editable in the courses table (other than the course name)? as there are only 2 fields (cid - which is not editable, & cname) if a course name changes, you would simply do this by deleting the old entry & adding a new one, no?

tristan
avatar
Mac
spinning smiley sticking its tongue out Re: YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?
October 27, 2008 06:38AM
If you have collected the information via the form then the student submits his form then the next step is to enter it into the student_course table.....

and yes, that's all you can edit.
spinning smiley sticking its tongue out Re: YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?
October 27, 2008 10:34AM
Mac please help - is the final due date the 9th of Nov ?.

Thanx..
avatar
Mac
spinning smiley sticking its tongue out Re: YEAH SOLVED - final prac: possible to fill drop down lists automatically from tables?
October 27, 2008 12:49PM
yes
Sorry, only registered users may post in this forum.

Click here to login