or Connect
AppleInsider › Forums › General › Genius Bar › Database logic needed.
New Posts  All Forums:Forum Nav:

Database logic needed.

post #1 of 6
Thread Starter 
I thought I'd be a complete nerd and procrastinator at the same time, so I decided that I would organize my journal articles in a database. I've got a ton of them and they're too hard to keep organized on my own.

My plan was to have a database designed such that I input the 3 first authors, the article name, and some keywords. I want to do a search based on any of these things, so if there are more than search terms entered, the results get more and more narrow.

I'm using an isset statement to determine whether, say the second and third authors are specified in the search. The problem is, if they are not and they are included in my database, the search fails because my author_id2 and author_id3 values in the search are set to NULL and in the database they have a value. Does that make any sense? Basically, in my search, all of the fields are optional, but the more I enter, the better the search is. The problem is that my NULL search terms aren't matching up with the actual values in the database. Here's an example of my really really sloppy code (I'm pretty new at this)

[code]
\t$query = "SELECT author_id, author_name FROM
authors WHERE author_name = '$author1'";
\t$resultname = mysql_query($query);
\t$auth_id1 = "";
\twhile($query_data = mysql_fetch_row($resultname)) {
\t\t$auth_id1 = mysql_result($resultname, $i, "authors.author_id");\t
\t$i++;
\t}
\tif(isset($author2)) {\t
\t$query = "SELECT author_id, author_name FROM
authors WHERE author_name = '$author2'";\t
\t$resultname = mysql_query($query);\t
\twhile($query_data = mysql_fetch_row($resultname)) {
\t$auth_id2 = mysql_result($resultname, $id, "authors.author_id");\t\t
\t$auth_name2 = mysql_result($resultname, $id, "authors.author_name");\t\t
\t$id++;\t\t
\t}\t
}\t
\telse $auth_id2 = "";\t
\t$query = "SELECT article_name FROM
article_info WHERE author1_id = '$auth_id1' AND author2_id = '$auth_id2'";
\t$resultname = mysql_query($query);
\t$number_rows = mysql_num_rows($resultname);
\techo "$query";
\techo "There were $number_rows articles returned";
\twhile($query_data = mysql_fetch_row($resultname)) {\t
\t$article_name = mysql_result($resultname, $j, "article_info.article_name");\t
\t$j++;
}
</pre><hr></blockquote>

I tried to draw a database diagram to help you understand the set up, but I'm still working on it. I'll post it later if need be. TIA.

[edit: tried to fix formatting]

[ 11-20-2001: Message edited by: torifile ]</p>
post #2 of 6
The simple solution is to just put another isset test in your last query. For example:
[code]
...
$query = "SELECT article_name FROM article_info WHERE author1_id = '$authid1' ";
if( isset( $author2 ) ){
$query .= "AND author2_id = '$authid2'";
}
...
</pre><hr></blockquote>
HTH,
Sarah
post #3 of 6
Thread Starter 
Thanks. I think that does it... I'll need to check some more before I can call you a genius
post #4 of 6
Thread Starter 
orque,
You're almost a genius I had to make the isset statement to check for the value of $auth_id2. But considering how poor a job I did of explaining my 'vision', not bad. Thanks.
post #5 of 6
Ah yes, of course. Glad I could be of assistance.
post #6 of 6
Ah yes, of course. Glad I could be of assistance.
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Genius Bar
AppleInsider › Forums › General › Genius Bar › Database logic needed.