gwydionwaters

got it, using joins i had to be more specific with the columns MATCH (items.Title,items.Desc) AGAINST and in mysql 5.0 (not 5.1 like i have been reading tutorials for) there is no need for the AGAINST ('term' IN NATURAL LANGUAGE MODE) as opposed to AGAINST ('term') thanks for helping me figure it out

ok, i got this which would be refering to this here i have tried it with caps and also i have tried searching the whole title. no luck there. i even tried matching only against title and using the full exact title of an entry, also no luck

do i just add mysql_error(); i get this error on the page after a search attempt is there a better way to search a small (under 1000 entries) table? i only want to search two fields in the table. and i want to send a term or string (depending on user that is) and have it return all entires that contain the term or string in either field1 or field2 or both

try this <?php "SELECT name FROM tblsections INNER JOIN tblsubsections ON tblsubsections.sectionid=tblsections.sectionid WHERE tblsubsections.subsectionid = '12'" where the '12' at the end is the value you are basing your search on, can be a variable too

to get the genre name instead of the id <?php $result = $connector->query('SELECT Artists.Artist,Artists.Bio,Genres.Name FROM Artists INNER JOIN Genres ON Artists.Genres=Genres.Id WHERE ID = '.$HTTP_GET_VARS['id']); you can also do it like <?php $result = $connector->query('SELECT Artist,Bio,Name FROM Artists INNER JOIN Genres ON Artists.Genres=Genres.Id WHERE ID = '.$HTTP_GET_VARS['id']); i just like to use the table.field format for ease of reading. oh and because of the name used in the genres table you will have to echo Name instead of Genre

thanks that did the trick

thank you so much! works great now. at first it didn't and i couldn't figure out why, turns out that my if statements insert into author which was INSERT INTO author.Author, author.EMail VALUES ('$Author','$EMail'); for some reason wasn't working so i changed it to INSERT INTO author VALUES ('$Author','$EMail',''); which now works. again thanks for your help.

ok, i'm with you that far now. i'm trying to work it out but i get confused without the actual database to try. i'll let you know if i figure it out, i might just recreate your tables on my server in the end

now i find this strange, if in the case of the author already existing this works flawlessly but if the author is new there is an error as: also it has the same error if i use row 0 the code is this <? include("dbase.incl.php"); $Title=$_POST['Title']; $Author=$_POST['Author']; $EMail=$_POST['EMail']; $Type_id=$_POST['Type']; $Func_id=$_POST['Func']; $Cost_id=$_POST['Cost']; $Rating_id=$_POST['Rate']; $Description=$_POST['Description']; mysql_connect($hostname,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query1 = "SELECT author.Author, author.Author_id FROM author WHERE Author='$Author'"; $result1=mysql_query($query1); $num=mysql_num_rows($result1); // ** the error line is below ** $Author_id=mysql_result($result1,1,'Author_id'); if ($num =(0)) { $query2 = "INSERT INTO author.Author, author.EMail VALUES ('$Author', '$EMail')"; mysql_query($query2); $query3 = "SELECT author.Author_id FROM author WHERE Author = '$Author'"; $result2=mysql_query($query3); $Author_id=mysql_result($result2,1,'Author_id'); } $query4 = "INSERT INTO items VALUES ('','$Title','$Cost_id','$Type_id','$Func_id','$Rating_id','$Author_id','$Description')"; mysql_query($query4); mysql_close(); ?> i also had it so that it was like this <? include("dbase.incl.php"); $Title=$_POST['Title']; $Author=$_POST['Author']; $EMail=$_POST['EMail']; $Type_id=$_POST['Type']; $Func_id=$_POST['Func']; $Cost_id=$_POST['Cost']; $Rating_id=$_POST['Rate']; $Description=$_POST['Description']; mysql_connect($hostname,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query1 = "SELECT author.Author, author.Author_id FROM author WHERE Author='$Author'"; $result1=mysql_query($query1); $num=mysql_num_rows($result1); if ($num =(0)) { $query2 = "INSERT INTO author.Author, author.EMail VALUES ('$Author', '$EMail')"; mysql_query($query2); $query3 = "SELECT author.Author_id FROM author WHERE Author = '$Author'"; $result2=mysql_query($query3); $Author_id=mysql_result($result2,1,'Author_id'); } else { $Author_id=mysql_result($result1,1,'Author_id'); // ** This was where it was ** } $query4 = "INSERT INTO items VALUES ('','$Title','$Cost_id','$Type_id','$Func_id','$Rating_id','$Author_id','$Description')"; mysql_query($query4); mysql_close(); ?> sorry it may be messy, but like i said it works as long as the author already exists in author(table)

one select being a select input type in an html form. i don't know if this should go in a java forum or not.. anyway the title says it all, what i want to do is instead of having <select name="option1"><option value="$var1"></option></select> i want to be able to send $var1 and $var2 as option1 in a way that i can use both as their own variable in the script they are sent to. maybe if i were to send it as "$var1,$var2" as $bundle and then somehow separate the two into their own variables on the other side? i am not sure how to do this, or what to google to learn for that matter

so i have started another approach as a second option i have indexed (table)items on title and desc and now am trying this to get the key term in the select as text $Key=$_POST['Key']; $Key=trim($Key); //remove whitespace off ends include("dbase.incl.php"); mysql_connect($hostname,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query = "SELECT items.Title, items.Desc, author.Author, type.Type, func.Func, cost.Cost, rate.Rate FROM items INNER JOIN author ON items.Author_id = author.Author_id INNER JOIN type ON items.Type_id = type.Type_id INNER JOIN func ON items.Func_id = func.Func_id INNER JOIN cost ON items.Cost_id = cost.Cost_id INNER JOIN rate ON items.Rate_id = rate.Rate_id WHERE MATCH (Title,Desc) AGAINST ('"; $Key2 = $Key; //i want to use $Key as is later on $key2 .= "' IN BOOLEAN MODE)"; $query .= $Key2; $result=mysql_query($query); $num=mysql_num_rows($result); mysql_close(); but it say's my query is not a valid result resource my query would be SELECT items.Title, items.Desc, author.Author, type.Type, func.Func, cost.Cost, rate.Rate FROM items INNER JOIN author ON items.Author_id = author.Author_id INNER JOIN type ON items.Type_id = type.Type_id INNER JOIN func ON items.Func_id = func.Func_id INNER JOIN cost ON items.Cost_id = cost.Cost_id INNER JOIN rate ON items.Rate_id = rate.Rate_id WHERE MATCH (Title,Desc) AGAINST ('term' IN BOOLEAN MODE) is this an incorrect syntax?

hmmm, i was re-reading your original post and i was becoming confused. i see in the table user_prefs_for_alloys(which i assume is a table containing information for each user?) there is the same alloy_id_no as in the new_alloys_added_by_users. if my assumption is correct i was wondering why a user is being associated with one alloy instead of just associating a user with every alloy of theirs in the new_alloys_added_by_users table, which may be creating a problem when trying to associate them

that's wierd, my table is items, i skipped the s due to lazzines. my full query is as such SELECT items.Title, items.Desc, author.Author, type.Type, func.Func, cost.Cost, rate.Rate FROM items INNER JOIN author ON items.Author_id = author.Author_id INNER JOIN type ON items.Type_id = type.Type_id INNER JOIN func ON items.Func_id = func.Func_id INNER JOIN cost ON items.Cost_id = cost.Cost_id INNER JOIN rate ON items.Rate_id = rate.Rate_id WHERE items.Title LIKE '%$Key%' OR items.Desc LIKE '%$Key%' which like i said works fine with only the 'WHERE items.Title = '$Key'" but not with any sort of wildcard, nor will it return any results if i try "WHERE items.Title = '$Key' OR items.Desc = '$Key'"

so i am trying to get a keyword search where the user can type in a word and have the select search two columns and return results from either/both columns. i have tried SELECT * FROM blah WHERE items.Title LIKE '%$Key%' OR items.Desc LIKE '%$Key%' also i have tried just where item.title = '$key' which works, but i want to be able to use a boolean type search. so you can search for car and get: car parts my new car etc ..

what happens if you try this SELECT * FROM new_alloys_added_by_users JOIN user_prefs_for_alloys ON user_prefs_for_alloys.alloy_ID_No = new_alloys_added_by_users.alloy_ID_No INNER JOIN ratings_for_alloy_scores ON ratings_for_alloy_scores.alloy_ID_No = new_alloys_added_by_users.alloy_ID_No WHERE user_prefs_for_alloys.id = 1 AND ratings_for_alloy_scores.id_of_user_who_provided_this_rating = 1 it is what i would use

lol yeah, but now i also know how to do a case also. it's funny, i must be dyslexic: when i was reading the response about the typo, with a quote of my code, i had to read it five times before i saw the typo ... and i knew where to look

maybe where you have $sql = "DELETE FROM studentrec WHERE id='$del_id'"; should it be $sql = "DELETE * FROM studentrec WHERE id='$del_id'";

$i=$i+10;

Kind of harsh wouldn't you say? You sure like flaming people Sorry to be like this but when people double post it irritates me. It wastes everyone elses time and is ignorant. So, as far as your question, "Kind of harsh wouldn't you say?", my answer is NO. Flaming? Far from it... Guess I can see your point, I did read the other thread and you answered the exact same question there, quite politely at that. As coders we should ALL go through each line of code and check for spelling and typos before we post a thread here ... gwydionwaters as well as utilize all the tools you have at your hands, such as google ... quite harsh, yes. i posted in two places not knowing which forum was appropriate. if i make you angry i'd rather you didn't post at all than be a jerk. also i posted both within moments of one another so i did not know you had already answered me, people usually don't answer right away. thank you all for helping, i do appreciate you taking the time to read over my probably mindane issues and finding solutions for me. i really do. what am i trying to accomplish? i made a menu driven database browser, for lack of a better term. users can select options from drop downs that will return database entries matching the option used. in regards to my problem i found a solution using a different call, that being switch/case and it is working great the code i created now is such switch ($browse) { case author: $query .= "author = '$author1'"; $term = $author1; break; case type: $query .= "type = '$type1'"; $term = $type1; break; case func: $browse = "use"; $query .= "func = '$func1'"; $term = $func1; break; case cost: $browse = "price < ="; $query .= "cost <= '$cost1'"; $term = $cost1; break; case rating: $query .= "rate = '$rate1'"; $term = $rate1; break; default: echo 'Sorry an error occurred, please try again'; } sorry to have offended some, and again thank you muchly to those who have been patient with me

i am trying to write an esleif sequence and for some reason i get a parse error on a line that should be no different than the last if ($browse=('author')) { $var1 = "author = '$author'"; $query .= $var1; } elseif ($browse=('type')) { $var1 = "type = '$type'"; $query .= $var1; } esleif ($browse=('use')) { //** Line 24 is here ** $var1 = "func = '$func'"; $query .= $var1; } elseif ($browse=('cost <=')) { $var1 = "cost = '$cost'"; $query .= $var1; } elseif ($browse=('rating')){ $var1 = "rate = '$rate'"; $query .= $var1; } else echo 'Sorry an error occurred, please try again'; endif; i tried to use : instead of curly brackets but apparently i need them becuase i have multiple actions in my elseif? either way i try it won't work, the problem is before i had it as just if statements in sequence and for some reason it doesn't work, if i comment out everything but the first if and run it then it works. but obviously only for author

it seems you only have so long to edit your post anyway, i have changed my script a bit, to try an else if type sequence like so if ($browse=('author')) { $var1 = "author = '$author'"; $query .= $var1; } elseif ($browse=('type')) { $var1 = "type = '$type'"; $query .= $var1; } esleif ($browse=('use')) { //**Line 24 ** $var1 = "func = '$func'"; $query .= $var1; } elseif ($browse=('cost <=')) { $var1 = "cost = '$cost'"; $query .= $var1; } elseif ($browse=('rating')){ $var1 = "rate = '$rate'"; $query .= $var1; } else echo 'Sorry an error occurred, please try again'; endif; but get a parse error on line 24, unexpected { which makes no sense as the syntax on that elseif is the same as the previous which is not a parse error ... confusing stuff lol

strange, i'm not sure what i was thinking. i needed all the tables because i wanted to only display options which had a relationship to a complete entry. but i gave up and now have full lists but an error if there are no complete entries. i have a new problem though i am trying to now work out a feature, where a user can browse through things like type, cost etc. i have a page wth what appears to be one form, but really there is a form for each type of value (cost, type ..) i have one script to handle the data from whatever form is sent, i had it working with just the value 'author' but now that i have tried to add the others it doesn't want to work anymore. the handler script is this <? $browse=$_POST['browse']; $author=$_POST['author']; $type=$_POST['type']; $func=$_POST['func']; $cost=$_POST['cost']; $rate=$_POST['rate']; $query = "SELECT items.Title, items.Desc, author.Author, type.Type, func.Func, cost.Cost, rate.Rate FROM items INNER JOIN author ON items.Author_id = author.Author_id INNER JOIN type ON items.Type_id = type.Type_id INNER JOIN func ON items.Func_id = func.Func_id INNER JOIN cost ON items.Cost_id = cost.Cost_id INNER JOIN rate ON items.Rate_id = rate.Rate_id WHERE "; if ($browse=('author')) { $var1 = "author = $author"; $query .= $var1; } if ($browse=('type')) { $var1 = "type = $type"; $query .= $var1; } if ($browse=('use')) { $var1 = "func = $func"; $query .= $var1; } if ($browse=('cost <=')) { $var1 = "cost = $cost"; $query .= $var1; } if ($browse=('rating')) { $var1 = "rate = $rate"; $query .= $var1; } else { echo 'Sorry an error occurred, please try again'; } include("dbase.incl.php"); mysql_connect($hostname,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $result=mysql_query($query); $num=mysql_num_rows($result); mysql_close(); ?> thanks for helping

i suppose you could say that, but they both pertain to different aspects of my project. so to avoid confusing portions of a discussion i broke it up into one for each question. and i gave up trying and just retrieved the values from their index table instead of being based on their use in the items table

why not set page order to a primary key and then to auto_increment and skip entering in the number, mysql will add one to the last for you

hello, so i have a database containing tables with foreign keys corresponding to values like; rate(being a rating /10) author, cost, etc. the database also has a table of items like this: id|title|desc|cost_id|type_id etc ... my form for browsing this database is built dynamically depending on what values have been used in the items table. each selectable list has it's own form. problem is that if i have three entries that are classed as a rating of 8/10, my drop list in the form contains three choices for 8/10. my question.. how do i eliminate the extras of identical nature? my form code is <? include('scripts/dbase.incl.php'); mysql_connect($hostname,$username,$password); @mysql_select_db($database) or die( 'unable to select database, please refresh'); $query = 'SELECT * FROM items INNER JOIN type ON items.Type_id = type.Type_id INNER JOIN author ON items.Author_id = author.Author_id INNER JOIN func ON items.Func_id = func.Func_id INNER JOIN cost ON items.Cost_id = cost.Cost_id INNER JOIN rate ON items.Rate_id = rate.Rate_id'; $result = mysql_query($query); $num = mysql_num_rows($result); mysql_close(); ?> // ...skip some irrelivance <form name="abrowse" action="scripts/gearbrowse.php" method="post">Author<BR><select onChange="this.form.submit()" name="Author"><option></option> <? $i = 0; while ($i < $num) { $Author=mysql_result($result,$i,"Author"); ?> <option value="<? echo $Author; ?>"><? echo $Author; ?></option><? $i++ ; } ?></select></form>