MySQL/PHP/Javascript dropdown menu

Not sure how to proceed from here.

[Ernos]

I've searched the forums here and searched on Google, and I think I'm almost at a solution here, hopefully y'all can help me out again (I've gotten some great answers here before).

I've been running my site with just PHP, and no MySQL for a while, but now that I have MySQL I'd like to make use of it. I've got a table called "articles" where each row has fields "id," "category," "title," and "article."

Here's my index.php page for right now (it's divided up into divs for a two-column layout but I'll snip those tags for space):

<?php
require("top.php");
?>
<?php
require("menu.php");
?>
<?php
if ($a ¦¦ $b!= "") {
 include("content.php");
 }
 else {
 include("main.php");
 }
?>
<?php
require("bottom.php")
?>

This is an abbreviated version of the menu.php page:

<SCRIPT type="text/javascript">
 function doSel(obj)
 {
 for (i = 1; i < obj.length; i++)
 if (obj[i].selected == true)
 eval(obj[i].value);
}
</SCRIPT>
<P><form name="form1">
<select name="menu1" onchange="doSel(this)">
<option selected value="location.href='index.php'"> Choose a Page
<option value="location.href='index.php?a=1'">Blah
<option value="location.href='index.php?a=2'">BlahBlah
</select>
</form>
<P><form name="form2">
<select name="menu2" onchange="doSel(this)">
<option selected value="location.href='index.php'"> Choose a Page
<option value="location.href='index.php?b=1'">Blee
<option value="location.href='index.php?b=2'">BleeBlee
</select>
</form>
<P><A HREF="index.php">Main page</A>

So it's set up to include the content page in the "content" area next to the left nav area. I'd like to be able to include stuff from a database in the content page, based on what's selected in the dropdown menu. Like, if the user chooses "BleeBlee," the menu calls "index.php?b=2" and loads an article from the db into "content.php" that has a "category" of "b" and an "id" of "2."

I thought this (or something like it) would work in "content.php":

<?php
if (!$a ¦¦!$b)
{
echo 'Article not found.';
exit;
}
$date = addslashes($date);
$category = addslashes($category);
$title = addslashes($title);
$article = addslashes($article);
$dbh=mysql_connect ("localhost", "username", "password") 
or die ("Cannot connect to the database");
mysql_select_db ("db_name", $dbh);
$query = "select * from articles where id='$a'";
$result = mysql_query($query);
if ($result)
echo $date.'<BR>'.$category.'<BR>'.$title.'<BR>'.$article;
?>

But obviously there's something I'm missing.

I'm a sad little newbie trying to figure everything out for myself, but once again I'm lost. Any help? Am I going about this the comepletely wrong way? Is this even possible?

[dreamcatcher]

Nothing sad about trying to work it out yourself.

Firstly, if you are passing a variable in a query string, you should always test the state of the variable first. I think empty() is best in this case:

if (empty($a) ¦¦ empty($b))

You then need to use the superglobal $_GET. Just before your database query add:

$b = $_GET['b'];

Now I`m a little confused about the following:

$query = "select * from articles where id='$a'";

Should that be:

$query = "select * from articles where id='$b'";

Just a few observations! :)

[coopster]

Assuming all else is working for you and you just aren't getting your data:

The result set is there, but you have to learn how to get the data from it. jatar_k has posted a great tutorial in this thread Basics of extracting data from MySQL [webmasterworld.com] (and even more great links in this thread [webmasterworld.com]!)

Your code will end up looking something like this:

$query = "select * from articles where id='$a'"; 
$result = mysql_query($query); 
// if ($result) <--change this to this: 
while ($row = mysql_fetch_array($result)) { 
 echo $row['date'].'<BR>'.$row['category'].'<BR>'.$row['title'].'<BR>'.$row['article']; 
} // <-- don't forget to close the while loop! 

[dreamcatcher]

One other thing, if you are getting info from a database, you won`t need addslashes(). If you have slashes when the info displays use stripslashes() instead. You use addslashes when adding info to a database if Magic Quotes are set to off.

[Ernos]

Thank you both for your responses!

Okay, I'm still having some trouble. I've tried making changes as suggested above (and checking out the linked thread about retrieving the data from the db) and I'm still a little mystified. I'm wondering if it has to do with the fact that my forms don't have a METHOD and ACTION attribute? Since they specify neither GET nor POST, maybe that's what's confusing the script in "content.php"? I'm thinking (maybe wrong) that if the form doesn't say GET, then using the $_GET superglobal won't work right? If that's the case how would I make it work with the Javascript? I tried playing with it, like this:

<form method="GET" action="location.href" name="form1">

But that doesn't seem to work.

Specifically, the error I keep getting is the "Article not found" message that I set up here:

if (empty($a) ¦¦ empty($b))
{
echo 'Article not found.';
exit;
}

So that's telling me that somehow it's not the db but the form that I'm screwing up somehow.

(Also, on the addslashes/stripslashes thing, I have HTML tags in one of the db fields (the "article" field, which is LONGTEXT), so I thought you had to do addslashes in that situation for the HTML to actually be rendered. Not so?)

[lorax]

Have you echoed the vars $a and $b to see what they do contain?

[Ernos]

Yes. If the URL is "index.php?a=1" and I do echo $a, it prints 1. Or is that not what you mean?

[lorax]

No, that's what I mean. So both vars are getting passed ($a AND $b)?.

If yes, that means we're off into troubleshooting land. So just to be sure I understand how things are laid out: The form is index.php. The 'action=' is what exactly - index.php or content.php?

[dreamcatcher]

so I thought you had to do addslashes in that situation for the HTML to actually be rendered. Not so?)

addslashes is mainly for problems with quotes.

If you are pulling HTML from your database, it will display as HTML. If you have problems, ie, a mysterious > symbol appearing from nowhere, then you should use htmspecialchars.

$name = htmlspecialchars($name);

Its probably best to use htmlspecialchars when you actually store the information, rather than when you extract it.

[Ernos]

lorax: Well, no, it doesn't seem to be getting both $a and $b, it looks like it's only getting what's in the URL. I tested this by stripping everything out of "content.php" and putting in this:

<TABLE BORDER="1" width="60">
<?php
echo '<TR><TD>'.$a.'</TD><TD>'.$b.'</Td></TR>';
?>
</TABLE>

So when I use the dropdown menu and go to "index.php?a=1" then I get a table that has "1" in the first cell and nothing in the next cell. When the URL is "index.php?b=1" then the table has nothing in the first cell and "1" in the second cell.

As for how it's laid out, "index.php" is the actual page with a whole bunch of includes. The <FORM> is in "menu.php", but it doesn't have an ACTION at all -- it's just <FORM name="form1"> with no METHOD or ACTION. I've been using a Javascript onChange in the SELECT to send the information into the "content.php" page. So index.php has a header, then two columns ("menu.php and "content.php"), then a footer. (I tried using the pre-formatted tag to show what I'm talking about, but it doesn't seem to render well.)

So "content.php" is the file in which I've been doing the query. (I'm afraid I'm confusing myself even worse now, heh.)

dreamcatcher, thanks for that tip. Can you explain how I would go about using htmlspecialchars when I store the information in the db? When I enter an article into the "article" LONGTEXT field, I just enter it as if I was typing out any normal HTML page: <P>Blah blah blah blah etc. Where or how would I put the htmlspecialchars information?

[jatar_k]

try it this way and see what happens

<TABLE BORDER="1" width="60">
<?php
echo '<TR><TD>'.$_GET['a'].'</TD><TD>'.$_GET['b'].'</Td></TR>';
?>
</TABLE>

[dreamcatcher]

Where or how would I put the htmlspecialchars information?

Well, if you were inserting data into your database from a form field, and lets say your form field text area was:

<textarea name="message" rows="8" cols="40"></textarea>

So, your database query would be something like:

$message = $_POST['message'];

$query = "INSERT INTO table (message) VALUES ('$message');
$result = mysql_query($query);

(Or you might be updating the info)

Before you add the info, use htmlspecialchars:

$message = $_POST['message'];
$message = htmlspecialchars($message);

$query = "INSERT INTO table (message) VALUES ('$message');
$result = mysql_query($query);

This will strip the tags from your code when its inserted into the database and replace them with character entities. When you pull it from the database, it displays safely on your page.

If you don`t use it, you can get strange displays on your page. For instance if you are getting the info from the database and storing it in hidden variables, you can sometimes get a rogue "> or <" appearing, because in affect the tags have already been closed from the HTML stored in your database.

Hope some of that makes sense! LOL!

[Ernos]

Hmm, it comes up exactly the same. But isn't that probably because there's no GET parameter in the form?

I feel like I should point out here that I have been able to output stuff from the db in other ways than what I want to do here; for example, I was playing around with a search feature, and I made a form where the user could choose a category from a dropdown menu and enter some text to search for in the "article" field, and when they hit the button another page would come up with their search results. Worked great, but in that case I was using POST in the form, and I was using a submit button rather than an onChange event in the dropdown menu.

I know linking is forbidden here, would it help anyone if I sticky them my URL so they can get an idea of the layout?

Edit: Thanks for that dreamcatcher, I'll try and play around with that. :) I believe I understand, but I may have to do it differently if I'm using something like PHPmyadmin to update the db rather than a form field (I've been using both lately).

[lorax]

You can sticky me the URL though I may not be able to look at it until tomorrow.

SO you've got two seperate forms - with different names and you're using a javascript onChange event triggered by a selection on one of the forms.

I'm feeling like I'm about to stick my foot in my mouth because I've missed something you've said but from what I'm seeing - at the moment - you'll never have both values passed to content.php with this setup because the select inputs are in different forms and the javascript only submits one of the forms. So the code if (empty($a) ¦¦ empty($b)) will always be true?!

[jatar_k]

PHP Predefined Variables [ca.php.net]

take a look at $_GET and $_POST superglobal arrays

my mistake i thought I saw that your form was set to method="get" but if there is no action then I believe it is post by default

echo '<TR><TD>'.$_POST['a'].'</TD><TD>'.$_POST['b'].'</Td></TR>';

[lorax]

I think you're right jatar_k but am I missing something obvious here re: two seperate forms?

[Ernos]

I apologize if I've been unclear, past few days this thing has been making my head swim. :) Lorax, I'll sticky you the URL of the place on my site where I'm testing this stuff, but basically, yes, there are two forms (ideally I want four, but I snipped it down here to keep the code as short as possible). Yes, the Javascript is set up just as you say. Select an item from one of the dropdown menus (both separate forms) and onChange tells it to load "content.php" into the content area of "index.php" -- it's the "?a=1" and "?b=2" things that I'm trying to use to query the database. (I sure hope I'm using the right terms here.)

Thinking of it that way, you're right -- $a and $b wouldn't both be passed to "content.php" so...I guess that empty() would always be true, which explains some of the errors I'm getting while I keep playing with it.

Let me share some of the progress I've now made thanks to the talking-out I've been doing with you guys. I've tried putting this into "content.php":

<?php 
[b]if (empty($a)) { 
echo 'Article not found.';
exit; 
}[/b]
$dbh=mysql_connect ("localhost", "username", "password") 
or die ("Cannot connect to the database"); 
mysql_select_db ("db_name", $dbh); 
$query = "select * from articles where id='$a' or id='$b'"; 
$result = mysql_query($query); 
while ($row = mysql_fetch_array($result)) {
echo $row['date'].'<BR>'.$row['category'].'<BR>'.$row['title'].'<BR>'.$row['article']; 
}
?>

This actually works great -- sort of. The article is successfully called from the database, but only if the URL is "index.php?a=1" or whatever number. If I try to choose something from the b menu, I get the "article not found" message, since, well, there's that "if (empty($a))" thing.

If I tempt fate and take out that "if" statement altogether, then I'm actually sort of getting somewhere, because then if I choose an item from either form, the query picks up the number and displays the record. But it might not be safe to leave that out, right?

Finally, assuming all else goes well, how can I define the a menu as a particular category and the b menu as another category so that the query in "content.php" can differentiate them? As the script stands now, it's only matching up the id number, not the id number and the category as I would like, ideally. Should I define them as
$a = $remarks
$b = $reviews
or something to that effect?

[Ernos]

Hey all, I just thought I'd pop back in with my solution, such as it is. Thanks to all of your suggestions and pointers, I think I've come up with something that should work out pretty well. Basically, I've revamped the db to have multiple tables, one for each category, rather than just one table to hold all articles. So I've got a different "content.php" page to call each different category -- "contenta.php" is called for all articles of category a, "contentb.php" for category b, and so on. So the index.php has:

<?php
if ($a!= "") {
 include("contenta.php");
 }
elseif ($b!= "") {
 include("contentb.php");
 } 
elseif ($c!= "") {
 include("contentc.php");
 } 
elseif ($d!= "") {
 include("contentd.php");
 } 
 else {
 include("main.php");
 }
?>

And the different content pages, like "contenta.php" will have this:

<?php 
if (empty([b]$a[/b])) { 
echo 'Article not found.'; 
exit; 
} 
$dbh=mysql_connect ("localhost", "username", "password") 
or die ("Cannot connect to the database"); 
mysql_select_db ("db_name", $dbh); 
$query = "select * from [b]categorya[/b] where id='$a'"; 
$result = mysql_query($query); 
while ($row = mysql_fetch_array($result)) { 
echo $row['date'].'<BR>'.$row['category'].'<BR>'.$row['title'].'<BR>'.$row['article']; 
} 
?> 

So if the user selects something from the first form, calling "index.php?a=2" then "contenta.php" will be loaded and the query will look for the article in Category A's table with an ID of 2.

I can't find a problem with this so I think it's okay, though I hope someone'll tell me if I overlooked something that's important to the code. I would have like to have just one content page, but having four pages is still better than having a separate HTML page for each and every article on the site.

One thing that's bugging me at the back of my mind is what to do if someone tries to type something into the address bar that doesn't exist yet -- like if someone types in "index.php?a=66" and I don't have an article with that ID, can I echo a string that says so? I tried putting a thing like

if (empty($result)) {
echo 'The article you requested is not in the database.'
}

But that just got me a syntax error.

[ergophobe]

if (empty($result)) {
echo 'The article you requested is not in the database.'
}

But that just got me a syntax error.

Missing semicolon.

BTW, you could also achieve the same thing with a custom 404 page.

[Ernos]

Thanks for the response ergophobe, I could smack myself for not catching that (it always seems to be the little things that sneak by).

Unfortunately, though putting in the semicolon got rid of the syntax error, I still wasn't getting a result. Then I figured that instead of an empty $result I should be looking for an empty $row, and when I put this in after the WHILE statement, it worked out:

if (empty($row)) { 
echo 'The article you requested is not in the database.'; 
}

Once again, let me just say how much I appreciate all the support and feedback y'all provide here. It's really a great place you've got. :)

[Ernos]

Gaaah, I spoke too soon. I noticed, right after I posted that, that my message "The article you requested is not in the database" is being echoed on every page at the end of the articles, and not just on pages where the user may have asked for an article that doesn't exist. Now my brain is really fried, and I've tried multiple solutions. Like:

if (empty($row)) { 
echo 'The article you requested is not in the database.'; 
}

***

if (empty($row['article'])) { 
echo 'The article you requested is not in the database.'; 
}

***

if (!$row) { 
echo 'The article you requested is not in the database.'; 
}

***

if (!$row['article']) { 
echo 'The article you requested is not in the database.'; 
}

***

if (empty($row['id'])) { 
echo 'The article you requested is not in the database.'; 
}

***

if (empty($result)) { 
echo 'The article you requested is not in the database.'; 
}

(^^^ This one actually doesn't echo on every page, but it doesn't echo on the pages I want it to, either -- you just get a completely blank page.)

if (!$result) { 
echo 'The article you requested is not in the database.'; 
}

(^^^ Same here.)

So, to sort of recap, here's my code, working just great with my dropdown menu so far:
[code]
<?php
if (empty($a)) {
echo 'Article not found.';
exit;
}
$dbh=mysql_connect ("localhost", "username", "password")
or die ("Cannot connect to the database");
mysql_select_db ("db_name", $dbh);

$query = "SELECT *, date_format(date, '%M %e, %Y') as date FROM tablename where id='$a'";
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
echo '<H3>'.$row['date'].'</H3><h3>'.$row['title'].'</H3>'.$row['article'];
}
?>

I just want to echo that "article not in database" message only if the number in the URL doesn't exist in the db -- like, if I only have articles in Category A going up to number 5, but the user types in "index.php?a=66" (a lot of people I know do this sort of thing "just to see what happens," I've done it myself). Where and how should I put that "if" statement to get it to do that? Even if I went with a custom 404 page, I'd still need to figure a way to get the script to include the 404 page only if the record didn't exist in the db.

[ergophobe]

I need you to back me up a bit. If I understand, the drop down works now, but you are not getting the page you want. Or you are getting the page, but you are also getting the "Page not found message"?

I can't tell from your post and your code, but to get things started, I'm going to assume that it crops up whether the page is displayed or not, and that the echo comes outside and after the while loop. If that's not true, this won't answer your question, but at least it eliminates one possibility.

while ($row = mysql_fetch_array($result)) {
echo '<H3>'.$row['date'].'</H3><h3>'.$row['title'].'</H3>'.$row['article'];

Let's start here. Why are you using a loop? If I understand right, this is the page content and there should only be one row. I assume $row['article'] is the page content.

If that's the case, if your "Page not found" echo is outside the while loop, then $row is always going to be empty. Let's say you get your one row where a=5. You go through the while loop and get that info and put it on the page. You come back up to the top of the while loop and it fails. Now you go to you if(empty()) statement. $row is now empty, because you have looped beyond your result set.

If you only have one row (which you should since a=5 should be unique) then you can do something like

if ($row = mysql_fetch_array($result)) {
include($main_template.php); //template plugs in the $row values
} else {
include($404_template.php); // template plugs in HTTP_REFERER or something so user can go back or perhaps the URI requested so user can see if there was a typo.
}

I hope that helps

Tom

[Ernos]

Why am I using a loop? Well, I'm a moronic newbie. :) Actually, I was using it because I'd been using it on another part of my site (a basic little search script, which does return multiple rows, so the loop is actually needed there).

I totally see now why it doesn't make sense to do that if I'm only over going to have one row in the content page, so I changed it to this:

if ($row = mysql_fetch_array($result)) { 
echo '<H3>'.$row['date'].'</H3><h3>'.$row['title'].'</H3>'.$row['article']; 
} 
else {
echo 'The article you requested is not in the database.';
}

As simple as changing the "while" to an "if" and adding an "else." It works just the way I want it now.

Thanks for your patience, the way you explained it was very clear and helpful. :)

[ergophobe]

No one gets that far by being moronic! Anyway, my pleasure to help out.