EternalSorrow

I'm currently working on a simple search form for a single table and have run into an error message I can't seem to fix. First, for reference here is the form code and result code, both placed on the search.php file: <?php mysql_connect("localhost","animara_ham","Alucard666"); mysql_select_db("animara_historia"); ?> <div class="font">Advanced Search</div> <form action="#results" method="post"> <table align="center" style="text-align: right;"> <tr><td>Keyword(s) <input category="text" name="info"> <tr><td>Category <select name="category"> <option value="all">Search All</option> <?php $qCategory = "SELECT DISTINCT category FROM history ORDER BY category "; $rsCategory = mysql_query($qCategory) or die ('Cannot execute query'); while ($row = mysql_fetch_array($rsCategory)) { extract($row); echo '<option value="'.$category.'">'.$category.'</option>'; } ?> </select> <tr><td> <input type="submit" name="search" value="Search"> </table> </form> <hr> <p>Here are the <a name="results">results</A> of your search: <p><?php $info = strtolower(strip_tags(mysql_escape_string($_POST['info']))); $category = strip_tags(mysql_escape_string($_POST['category'])); $sql_category = ($category == all) ? "" : "WHERE category='$category'"; $qSearch = "SELECT * FROM history WHERE info LIKE '%$info%' $sql_category ORDER BY title desc "; $rsSearch = mysql_query($qSearch) or die(mysql_error()); if (mysql_num_rows($rsSearch) == 0) { print '<p>Sorry, there were no results returned for your search. Please try again.</p>'; } else { print '<center><b>'.mysql_num_rows($rsSearch).'</b> article(s) found.</p></center>'; $i = 1; while ($row = mysql_fetch_array($rsSearch)) { extract($row); $dot = ''; $morelink = ''; $text=substr($info, 0, 300); if (strlen($info) >= 301) { $dot = ".."; } if (strlen($info) >= 301) { $morelink = "<div class=\"continue\"><a href=\"counter.php?entry_id=$entry_id\">continue reading</A></div>"; } echo '<p><blockquote><div class="title">'.$title.'</div> '.$text.''.$dot.' '.$morelink.' <div class="cat">Category: <a href="categories.php?category='.$category.'">'.$category.'</A></div> </blockquote>'; } } ?> The following error message appears when a visitor enters the page: The line mentioned is part of the $sql_category string, which is then placed in the $qSearch query. I can't figure out why it's already querying when a search hasn't been done, nor can I find a fix for the problem. The form, however, does work perfectly when a search is entered. Any hints on why the form is prematurely searching?

Bump....any tutorial ideas?

Bump.

1. I'm afraid it doesn't, regardless of where I put the DISTINCT command. 2. This is what I'm asking how to perform. I have single to two categories per article, so an IF statement which counted the number of categories assigned to that entry_id would be ideal. Any idea where to learn such a statement?

I have this horrible feeling this post won't be solved...and another bump.

Bump. Does anyone even know a place I can find a tutorial for this?

This is both a bump and an upgrade with the code. I've modified the code with assistance from this discussion, but I've found I still have some problems. I now have three tables to work with: The history table has fields: entry_id, title, category, sub_category, and info The category table merely has fields: category_id, category_name And the catjoin table: entry_id, category_id The problems that arise are two: the articles are duplicated according to how many categories they have, and I obviously want only a single article shown. The LIMIT command doesn't work and I'm unsure how to apply the code from the discussion above for my own PHP. Second, I wish to show all categories assigned to a single article at the bottom of that article. To do this I would need to create an IF statement which would review the results of the query, determine whether an article had greater than one category assigned (such as category > 1 or category >= 2), and output the second category using the IF statement. I have no idea what command, other than a count command, could do this and I'm not sure how to set up such a code (multiple attempts have failed). Here's the modified code (ignore the one on the first posting): <?php mysql_connect(localhost,user,pw); @mysql_select_db(db) or die( "Unable to select database"); $query = "SELECT a.*, b.* FROM (SELECT * FROM history ORDER BY entry_id DESC) a INNER JOIN catjoin c ON a.entry_id = c.entry_id INNER JOIN category b ON b.category_id = c.category_id"; $result = mysql_query($query) or die(mysql_error()); while($row=mysql_fetch_array($result)) { extract($row); echo '<p><blockquote><div class="title">'.$title.'</div> '.$info.' <div class="cat">Categories: <a href="categories.php?category_id='.$category_id.'">'.$category_name.'</A>'.$sub.'</div> </blockquote>'; } ?>

I'm currently creating a simple blog/database for an article database. I wish to assign multiple categories, or tags, to each article but I can't seem to figure out how to accomplish this goal. Currently I have two tables: history, and category The history table has fields: id, title, category, sub_category, and info. The category table merely has fields: category_id, category_name I am able to display a single category for the articles without a problem, but I have no idea how I should go about implementing a second category. Do I add another table which relates the article id field to the category_id field? Is there an ELSE or IF statement I can use to adopt my sub_category field to read the same value that is in the category_id field using another Join statement in the query? I'm stumped and would be grateful for any guidance. Here's the code for the article page (the $sub field is a lame attempt at using the $sub_category field; I doubt the IF will work at all in what I'm trying to do): <?php mysql_connect(localhost,user,pw); @mysql_select_db(db) or die( "Unable to select database"); $query = "SELECT l.*, r.* from history l Inner Join category r ON l.category = r.category_id ORDER BY l.id desc LIMIT 10"; $result = mysql_query($query) or die(mysql_error()); while($row=mysql_fetch_array($result)) { extract($row); $sub = ''; if (strlen($sub_category) >= 1) { $sub = ", <a href=\"categories.php?category_id=$sub_category\">$sub_category_name</A>"; } echo '<p><blockquote><div class="title">'.$title.'</div> '.$info.' <div class="cat">Categories: <a href="categories.php?category_id='.$category_id.'">'.$category_name.'</A>'.$sub.'</div> </blockquote>'; } ?>

I've also realized that when the same name which has been assigned to the $sub_category is placed in the $category field, the name will not call the article which has been assigned the $sub_category. For example, the category 'Film' is in both the $category and $sub_category fields, and when the link is pressed when it is in either field, the other field will not be searched.

I have tried the AND instead of OR, but then the $sub_category options, when clicked, show an empty page and the $category options show the $sub_category options.

So I should replace all double ampersands (&&) with the simple 'OR' command? (I'm still learning PHP, so even simple instructions can confuse me)

Currently I'm setting up an article database which has the option of two categories for each article. The two database fields for the different categories are 'category' and 'sub_category.' The page for the articles shows the articles with their categories below. A visitor is able to click on the category title, which then leads them to a page showing all articles in that category. However, I'm unable to figure out how to point the different category field names to the same page using 'if' and 'if else' statements to retrieve the correct articles. For an example, when the article has two categories and one of the categories is clicked, it will retrieve articles which correspond to both categories, when it is only supposed to retrieve articles for that single clicked category. I've tried using several variations of the 'if', 'else if', and 'else' commands but nothing has worked. I'd be grateful for any point in the right direction. Here's the code for the article page: <?php mysql_connect(localhost,username,password); @mysql_select_db(database) or die( "Unable to select database"); $query = "SELECT * from history ORDER BY id desc LIMIT 10"; $result = mysql_query($query) or die(mysql_error()); while($row=mysql_fetch_array($result)) { extract($row); $sub = ''; if (strlen($sub_category) >= 1) { $sub = ", <a href=\"categories.php?sub_category=$sub_category\">$sub_category</A>"; } echo '<p><blockquote><div class="title">'.$title.'</div> '.$info.' <div class="cat">Categories: <a href="categories.php?category='.$category.'">'.$category.'</A>'.$sub.'</div> </blockquote>'; } ?> And here's the code for the categories.php page the article page points to: <?php if (!is_numeric($_GET["category"]) && !empty($_GET["category"]) && $_GET["category"]!="") { $category = $_GET["category"]; } else if (!is_numeric($_GET["sub_category"]) && !empty($_GET["sub_category"]) && $_GET["sub_category"]!="") { $sub_category = $_GET["sub_category"]; } mysql_connect(localhost,username,password); @mysql_select_db(database) or die( "Unable to select database"); $query="SELECT * FROM `history` WHERE `category` = '$category' OR `sub_category` = '$sub_category' ORDER BY title "; $result = mysql_query( $query ) or die(mysql_error()); while($row=mysql_fetch_array($result)) { extract ($row); echo '<li><a href="article.php?id='.$id.'">'.$title.'</A>'; } ?>

Another bump for this thread. I also have an update on the code (since the modify button has vanished on my first and second posts): <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <?php include("banner.php"); ?> <ul class="fanfiction"><?php if (!is_numeric($_GET["series"]) && !empty($_GET["series"]) && $_GET["series"]!="") { $series = $_GET["series"]; } mysql_connect(localhost,user,pw); @mysql_select_db(db) or die( "Unable to select database"); $query="SELECT a.*, b.rating from stories a, rating b WHERE `series` = '$series' AND a.rating_id=b.rating_id ORDER BY story asc "; $result = mysql_query( $query ) or die(mysql_error()); // Insert Code A Here $screen = $_GET['screen']; $PHP_SELF = $_SERVER['PHP_SELF']; $rows_per_page=8; $total_records=mysql_num_rows($result); $pages = ceil($total_records / $rows_per_page); $last = $pages -1; if (!isset($screen)) $screen=0; $start = $screen * $rows_per_page; $query .= "LIMIT $start, $rows_per_page"; $result= mysql_query($query) or die ("Could not execute query : $query." . mysql_error()); $i = 1; echo '<div class="head">'.$series.'</div>'; while ($row = mysql_fetch_array($result)) { extract($row); $select_story = mysql_query("SELECT DISTINCT chapter_id, date FROM fanfiction WHERE 'a.series'='$series' ORDER by chapter_id desc ") or die (mysql_error()); $contents_here = '<li><div class="font"><a href="fanfiction.php?story='.$story.'" >'.$story.'</A></div> '.$summary.' <div class="fan">Universe: '.$universe.' | Rating: '.$rating.' | Status: '.$status.' | Chapters: '.$chapter_id.' | Pairing: '.$pairing.' | Genre: '.$genre.' | Updated: '.$date.'</div>'; if ($i==1) { echo '<div class="even">'.$contents_here.'</div>'; } else if ($i==0) { echo '<div class="odd">'.$contents_here.'</div>'; } $i++; $i=$i%2; } echo '</ul></div>

A bump, I'm afraid

It could be because your field type for the view count is something like varchar when it should be integer (int).

I had previously asked a question about DISTINCT fields in this thread. Now, however, I have an even bigger challenge in the form of multiple tables being accessed with the same, and different, fields. The task I'm trying to perform is showing all information for stories, whose information is archived in three separate tables: rating, stories, and fanfiction. The trick is I need to make distinct the field chapter_id from the fanfiction table because it is returning all results found, when I only want a single return. Below is the code so far (and here is a view of the current page). <?php if (!is_numeric($_GET["series"]) && !empty($_GET["series"]) && $_GET["series"]!="") { $series = $_GET["series"]; } mysql_connect(localhost,user,pw); @mysql_select_db(db) or die( "Unable to select database"); $query="SELECT a.*, b.rating, c.chapter_id, c.date from stories a, rating b, fanfiction c WHERE a.series = '$series' AND a.rating_id=b.rating_id AND a.story = c.story ORDER by a.story asc, c.chapter_id desc "; $result = mysql_query( $query ) or die(mysql_error()); // Insert Code A Here // dynamic navigation variables $screen = $_GET['screen']; $PHP_SELF = $_SERVER['PHP_SELF']; $rows_per_page=8; $total_records=mysql_num_rows($result); $pages = ceil($total_records / $rows_per_page); $last = $pages -1; if (!isset($screen)) $screen=0; $start = $screen * $rows_per_page; $query .= "LIMIT $start, $rows_per_page"; $result= mysql_query($query) or die ("Could not execute query : $query." . mysql_error()); $i = 1; echo '<div class="head">'.$series.'</div>'; while ($row = mysql_fetch_array($result)) { extract($row); $contents_here = '<li><div class="font"><a href="fanfiction.php?story='.$story.'" >'.$story.'</A></div> '.$summary.' <div class="fan">Universe: '.$universe.' | Rating: '.$rating.' | Status: '.$status.' | Chapters: '.$chapter_id.' | Pairing: '.$pairing.' | Genre: '.$genre.' | Updated: '.$date.'</div>'; if ($i==1) { echo '<div class="even">'.$contents_here.'</div>'; } else if ($i==0) { echo '<div class="odd">'.$contents_here.'</div>'; } $i++; $i=$i%2; } echo '</ul></div>

The code works perfectly now! Thank you so much for the help! And don't tear yourself down with that "junk code" nonsense! I've learned a lot from the questions you were asking (had to look up a number of items to answer them) and I'm truly grateful for all the help you've given me. Thanks for putting up with my annoying questions

Sure, here are the relevant fields and column functions in their exact order: id int(5) title varchar(100) url varchar(100) image varchar(100) series varchar(100) series_letter varchar(5) dl varchar(100) count_display int(100) preview_display varchar(5) date varchar(50) type varchar(25) resource varchar(25)

Deleting the period did nothing and the same message as before appears when I place the var_dump($result) in the for() loop.

The var_dump($queryResult); gives the following message:

The code returns a blank page without any error message (rather unusual).

@bobbinsbro: your assumption about my database structure is correct. And the better idea would be simpler for the code, but if I added another design to the same series I would have to change the display_image field for the older design, so I'm willing to try the first, and more complicated PHP. I inputted the information as it was written, but I appear to have left out some detail because my page receives an error message: and the error is found on this line: for ($j = 0; $j < $loopCount; ++$j){ Here is the current, changed code in its entirety: <?php if (!is_numeric($_GET["series_letter"]) && !empty($_GET["series_letter"]) && $_GET["series_letter"]!="") { $series_letter = $_GET["series_letter"]; } $sql = "SELECT * FROM design where series_letter='.$series_letter' ORDER BY series ASC"; $results = //multidimentional array: first level - numeric, holding the row number. second level - assoc, holding the column names $lastSeries = //holds the name of the last series treated $loopCount = //amount of rows in $result for ($j = 0; $j < $loopCount; ++$j){ if ($lastSeries == $result[$i]['series']){ continue; //don't treat the same series twice } else{ $lastSeries = $result[$i]['series']; //so we know we treated this series already $image = $result[$i]['image']; //get first image for this series that happens to be in the table $date = $result[$i][date]; //get date of the image //your layout code untouched $contents_here = '<table align="center" width="100%" style="text-align: center;"> <tr><td><a href="info.php?series='.$series.'"><img src="previews/'.$image.'" title="design" alt="design"> <br><b>'.$series.'</b></A> <br>Designs: '.$count_amount.' ~ Updated: '.$date.' </table>'; if ($i==1) { echo '<tr><td><div class="even">'.$contents_here.'</div>'; } else if ($i==0) { echo '<td><div class="odd">'.$contents_here.'</div>'; } $i++; $i=$i%2; } echo '</table>'; ?>

That would be the difficulty I'm having. If I set the values of the $image and $date in this part of the code: $query="SELECT DISTINCT series FROM `design` WHERE `series_letter` = '$series_letter' ORDER BY series asc "; then all designs for the series would load because they each have a unique image. If I remove the DISTINCT command then all designs for the series will again show. So my difficulty is if I access the db with the DISTINCT command with the $image and $date fields, all designs for the series will show. If I do not put the DISTINCT command with the $image and $date fields, all designs for the series will show. Is there any way to place the series as a DISTINCT field without the $image and $date fields being distinct?

Currently I am trying to organize my designs by series where when the series letter is clicked, the visitor will view all the series under that series letter category with the newest design image and date updated also shown. The problem I'm having is that I wish to have indistinct fields, such as the image and date, along with the DISTINCT series field. Here's the live preview of my difficulty (the database is choosing a random image with it's respective date, and I am unable to figure out how it is choosing that specific image). Here's the code for the PHP. I'd be grateful for any point in the right direction. <?php if (!is_numeric($_GET["series_letter"]) && !empty($_GET["series_letter"]) && $_GET["series_letter"]!="") { $series_letter = $_GET["series_letter"]; } $query="SELECT DISTINCT series FROM `design` WHERE `series_letter` = '$series_letter' ORDER BY series asc "; $result = mysql_query( $query ) or die(mysql_error()); $i = 1; echo '<table align="center" width="80%">'; while ($row = mysql_fetch_array($result)) { extract($row); $select_series_count = mysql_query("SELECT * FROM `design` where `series`='$series' ORDER BY `series` ASC") or die (mysql_error()); $count_amount = mysql_num_rows($select_series_count); $contents_here = '<table align="center" width="100%" style="text-align: center;"> <tr><td><a href="info.php?series='.$series.'"><img src="previews/'.$image.'" title="design" alt="design"> <br><b>'.$series.'</b></A> <br>Designs: '.$count_amount.' ~ Updated: '.$date.' </table>'; if ($i==1) { echo '<tr><td><div class="even">'.$contents_here.'</div>'; } else if ($i==0) { echo '<td><div class="odd">'.$contents_here.'</div>'; } $i++; $i=$i%2; } echo '</table>'; ?>