Database logic needed.
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>
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>
Comments
[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
You're almost a genius