[Resolved] multiple drop down menus with multiple entries

dartcol

I've been trying to get this sorted without success for a while so
if anyone can give me a hand I'd be more than grateful. I'm
setting up two pages on my localhost for all my favourite links
using two tables: links and topics.

Links:
links_id tinyint(5) unsigned NOT NULL auto_increment,
links_url varchar(250) NOT NULL default '',
links_description varchar(40) NOT NULL default '',
links_access set('local','remote') NOT NULL default '',
links_area smallint(3) unsigned NOT NULL default '1',
PRIMARY KEY (links_id)

Topics:
topics_id tinyint(5) unsigned NOT NULL auto_increment,
topics_area varchar(30) NOT NULL default '',
PRIMARY KEY (topics_id)

There are two pages: virtual.html (for all links on my computer)
and internet.html (for all online links). I want to pull the data from
the DB onto these two pages passing an argument $access to a
generic function. $access is either 'local' (for virtual.html) or 'remote' (for internet.html).

The problem is that when I pull the data I try to loop through the
array to get the individual links per topic area. All the topic areas
come up fine, however, only one topic per area shows up. Does
anyone have any idea what I'm getting wrong?

function links_menus($access)
	{
	$query = "	SELECT links_url, links_description, topics_id, topics_area, links_area
			FROM topics a
			LEFT JOIN links b
			ON a.topics_id = b.links_area
			WHERE b.links_access = '$access'
			GROUP BY a.topics_area
			ORDER BY a.topics_area";

$result = mysql_query($query);

if (mysql_num_rows($result) > '0')
	{
	while($row = mysql_fetch_array($result))
		{

		echo "<form name=linksmenu>
				<select name=newlocation>
				<option>" . $row["topics_area"] . "</option>
				<option>-----------------------------------------------------------------</option>
				<option value=" . $row["links_url"] . ">" . $row["links_description"] . "</option>";

		if (isset($previousareaname) && $previousareaname == $row["topics_id"])
			{
			echo "<option value=" . $row["links_url"] . ">" . $row["links_description"] . "</option>";

			}

		$previousareaname = $row["topics_id"];

		echo "</select>
				<input value=go type=button onclick=jumppage(this.form.newlocation)>
				</form>";
		}

	mysql_free_result ($result);
	}
}

John_Canyon

Im not sure exactly what you are trying to do, but if i were you i would definitely analyze what you have done below.

You will have as many forms created as there are results because
your form tag is within the while loop. Surely this cannot be what
you are trying to accomplish.

If I were you i would try something like this. Again this is vague because i am not sure exactly what you are trying to accomplish.

            echo "<form name=linksmenu>
                    <select name=newlocation>
                    <option>" . $row["topics_area"] . "</option>
                    <option>-----------------------------------------------------------------</option>";
        while($row = mysql_fetch_array($result))
            {

        if (isset($previousareaname) && $previousareaname == $row["topics_id"])
            {
            echo "<option value=" . $row["links_url"] . ">" . $row["links_description"] . "</option>";

            }

        $previousareaname = $row["topics_id"];
        }

        echo "</select>
                <input value=go type=button onclick=jumppage(this.form.newlocation)>
                </form>";

Also i notice that you have this line repeated twice.

echo "<option value=" . $row["links_url"] . ">" . $row["links_description"] . "</option>";

Maybe you can explain a little further what it is your trying to do.
all this will do is repeat the same <option> when $previoususername == $row[topic_id].

also.

you might want to change

ORDER BY a.topic_area TO ORDER BY a.topic_area,a.topic_id

Cheers.

dartcol

I'll try to clarify the problem a little. In the topics table there are
twenty topics (friend's sites, PHP, database, code library, etc.) at
the moment. This will increase. Some are online only, some are
on my computer only and some are both.

I've separated them onto two pages. Each topic gets its own
populated drop down. This means there will be n number of forms
for each page providing me with a category by category system
to organize my links.

In order to do this I need to get the topic area as the first
<option> to give a title to the DD, then the various urls with the
matching description which can be viewed in the drop down. If I
can get this automated it saves me having to update the html
pages.

If I take the first <form> and <select> tags out of the while
clause then I can't generate the forms with whatever contents
there are. I repeated the <option> line twice because this was
the only way I could come up with to repopulate the forms with
all the entries in the database.

Is this clearer? 🙂

dartcol

Here is an example picture.

John_Canyon

$list = array();
$ctgry = array();
while($row = mysql_fetch_array($result))
{
	$ctgry[] 									= $row[top_area];
	$list[$row[topics_area]][links_url][] 		= $row[links_url];       

	$list[$row[topics_area]][links_desciption][]= $row[links_description];
	$list[$row[topics_area]][topic_id][] 		= $row[topic_id];
}

for($i=0;$i<count($ctgry);$i++)
{
	echo 	"<form name=linksmenu>
			<select name=newlocation>
			<option>" . $ctgry[$i] . "</option>
			<option>-----------------------------------------------------------------</option>";

foreach($list[$ctgry[$i]][topic_id] as $key = $value)
{
	echo "<option value=" . $list[$ctgry[$i]][links_url][$key] . ">" . $list[$ctgry[$i]][links_description][$key] . "</option>";
}

    echo "</select>
          <input value=go type=button onclick=jumppage(this.form.newlocation)>
          </form>";
}

Try this and let me know the results. Sorry i didnt have a chance to test it.

Cheers

dartcol

Thanks for your help but I've still got the same problem of it only pulling the first entry from the DB for each form.
I had to do a couple of things to the code to get it to work. I assume none of this inadvertently caused it not to work.

I rewrote $key = $value as $key =>$value. I was getting a parse error saying that there was an unexpected '='.
I put single quotes around the variable in $row[topics_etc] to $row['topics_etc'] as I was getting an undefined constant
and index error messages for each variable.

John_Canyon

It looks like the issue is how you have your database contstructed.

You need some way of relating links and topics.

I would add a field called topic_id to links that is
identical to that in your topic table.

As you add links make sure that you add a relational
link back to the topic_id.

This should solve your problem, sorry i didnt see it
earlier.

Cheers.

dartcol

I've already done this. You see, the links_area column in table links receives the number that exists as topics_id in table topics. The topics_id
then pulls the name from table topics and joins this information to the query. When I run the query in phpmyadmin all the information is displayed correctly.
Isn't the problem that I can't group the data correctly on the page? If I take out the GROUP BY clause in the query then I get an individual
form per entry. Each form has all the entries for that topics. So, for example, say I have three entries for area Javascript. On the page
are three forms, each with Javascript as the title. Each form then has the three entries from the database.

Hmmm ... not quite sure what to do.

dartcol

Oh, dear. I know I'm not supposed to do this :o ('So don't!' I hear you all cry) and I wouldn't unless I was desperate but I'm afraid the code
suggested hasn't solved my problem and I've been searching for three days now - not continuously, obviously!
As outlined above, I'm doing a bit of a complex <select> form loop that isn't pulling all the results in groups and displaying them in separate
forms on the table. I'm starting to think it can't be done, but I know can't isn't a word for some of you. If no-one helps this time I promise
to crawl away quietly.

John_Canyon

I dont think you are quite understanding what I am saying.
The reason why you are only getting 1 link showing up is
because you are related your tables incorrectly.

Try adding the following line.

links_id tinyint(5) unsigned NOT NULL auto_increment,
topics_id tinyint(5), <-----add here
links_url varchar(250) NOT NULL default '',
links_description varchar(40) NOT NULL default '',
links_access set('local','remote') NOT NULL default '',
links_area smallint(3) unsigned NOT NULL default '1',
PRIMARY KEY (links_id)

Topics:
topics_id tinyint(5) unsigned NOT NULL auto_increment,
topics_area varchar(30) NOT NULL default '',
PRIMARY KEY (topics_id)

So, now when you are inserting your entries everything
will key off of topics_id, this way for every topic_area you
can have an n amount of links.

a select statement might look like this
$sql = mysql_query("SELECT * FROM topics as t, links as l WHERE t.topics_id=l.topics_id ORDER BY topics_area,links_description");

Cheers

dartcol

Thanks for your time here, John. I've done
just as you said but what I get now is the
same number of rows per entries. All the
links_url and links_description are under
the correct topics_area but there is one
form per entry in each drop down. It's kind
of difficult to explain, especially when it's
1.15 am here so here's a screenshot. I
realize I'm probably beinga bit dim again
but do you know of a way to fix it?
Thanks again for your help.

John_Canyon

Please repost all the code you are using.

dartcol

Here you go.

function links_menus($access)
{
$query = "	SELECT *
			FROM topics as t, links as l
			WHERE t.topics_id = l.links_area
			AND l.links_access = '$access'
			ORDER BY topics_area,links_description";

$result = mysql_query($query);

if (mysql_num_rows($result) > '0')
{
$list = array();
$ctgry = array();
while($row = mysql_fetch_array($result))
{
$ctgry[] 										= $row['topics_area'];
$list[$row['topics_area']]['links_url'][] 			= $row['links_url'];
$list[$row['topics_area']]['links_description'][] = $row['links_description'];
$list[$row['topics_area']]['topics_id'][]			= $row['topics_id'];
}

for($i=0;$i<count($ctgry);$i++)
{
echo "	<form name=linksmenu>
		<select name=newlocation>
		<option>" . $ctgry[$i] . "</option>
		<option>-----------------------------------------------------------------</option>";

foreach($list[$ctgry[$i]]['topics_id'] as $key => $value)
{

echo "	<option value=" . $list[$ctgry[$i]]['links_url'][$key] . ">" . $list[$ctgry[$i]]['links_description'][$key] . "</option>";
}

echo "	</select>
		<input value=go type=button onclick=jumppage(this.form.newlocation)>
		</form>";
}

mysql_free_result ($result);
}
}

John_Canyon


$ctgry = array_unique($ctgry); //ADD

for($i=0;$i<count($ctgry);$i++)
{
echo "    <form name=linksmenu>
        <select name=newlocation>
        <option>" . $ctgry[$i] . "</option>
        <option>-----------------------------------------------------------------</option>";

foreach($list[$ctgry[$i]]['topics_id'] as $key => $value)
{

echo "    <option value=" . $list[$ctgry[$i]]['links_url'][$key] . ">" . $list[$ctgry[$i]]['links_description'][$key] . "</option>";
}

echo "    </select>
        <input value=go type=button onclick=jumppage(this.form.newlocation)>
        </form>";
}

mysql_free_result ($result);
}
}

Add the line mentioned above, it should work. Sorry i missed that minor detail.

dartcol

I knew there must be something like that. I need to brush up on my PHP functions a bit I think.
So, it's brought the number of forms on the page to
the correct number. However, what I get now is
Notice: Undefined offset: 1 in functions.php on line 181
Line 181 is

<select name=newlocation>
<option>" . $ctgry[$i] . "</option>

The offset is for 1, 4, 6, 7 and 8. These correspond to 5 of the 10 forms being displayed.

(EDIT) In each of these undefined there is no information. Also, just checked the source code. Got this:
Notice: Undefined offset: 2 in functions.php on line 185

Warning: Invalid argument supplied for foreach() in functions.php on line 185 (EDIT)

Any ideas?

John_Canyon

foreach($ctgry as $i => $ctgry_nm)
{
echo "    <form name=linksmenu>
        <select name=newlocation>
        <option>" .  $ctgry_nm . "</option>
        <option>-----------------------------------------------------------------</option>";

foreach($list[$ctgry_nm]['topics_id'] as $key => $value)
{

echo "    <option value=" . $list[$ctgry_nm]['links_url'][$key] . ">" . $list[$ctgry_nm]['links_description'][$key] . "</option>";
}

echo "    </select>
        <input value=go type=button onclick=jumppage(this.form.newlocation)>
        </form>";
}

mysql_free_result ($result);
}
}

Try this, noticed i changed the first loop structure to foreach, I did this because the keys in the array have changed, I could have run the array through a function to reset the keys, but in this case
i decided it was easier to just go though a foreach loop.

Hopefully, this time it will work eh? 😃

Cheers

dartcol

John, you've made me a very happy man as well as teaching me a hell of a lot about arrays that I ever knew before. Nice one.
Hopefully I can return the favour one day.
Cheers. 😃