colinsp Posted February 20, 2009 Share Posted February 20, 2009 I am new at PHP so I apologise if I am asking something stupid. I have spent a couple of days trying to sort this without success. So I have now come asking for help. I have a mysql database with 2 tables. I have populated a select box with the records from the first table and I can successfully select the correct record and pass the variable. After clicking the submit button I want to take the variable as the answer to a where in a query. I just can't get this to work. This is my code <?php include('dbconnect.php'); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" > <title>Test Page</title> </head> <body> <form action="<?=$_SERVER['PHP_SELF'] ?>" method="POST"> <select name="vil"> <?php $sql = "SELECT * FROM locations ORDER BY village"; $rs = mysql_query($sql); while($row = mysql_fetch_array($rs)) { echo "<option value=\"".$row['unique']."\">".$row['village']." - ".$row['church']."\n "; } ?> </select> <input type="submit" name="Submit" id="Submit" value="Submit" > </form> //<?php //@$village = addslashes($_POST['vil']); //echo $village ; //?> <?php $sql1 = "SELECT * FROM records where church=$village"; // $rs1 = mysql_query($sql1); while($row = mysql_fetch_array($rs1)); { echo $row['church'].$row['type'].$row['year'].$row['url']."\n "; } ?> </body> </html> If anyone has any ideas or of any tutorials that will get me sorted I would be grateful. TIA -- Colin Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/ Share on other sites More sharing options...
farkewie Posted February 20, 2009 Share Posted February 20, 2009 Hi, Looking at your dropdown box the value you are passing to the second query is from the row called "unique", so **maybe** this <?php echo "<option value=\"".$row['unique']."\">".$row['village']." - ".$row['church']."\n "; ?> should be this <?php echo "<option value=\"".$row['church']."\">".$row['village']." - ".$row['church']."\n "; ?> Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/#findComment-767041 Share on other sites More sharing options...
colinsp Posted February 20, 2009 Author Share Posted February 20, 2009 Thanks for the thought but unique is a number and it is the number that is being passed from the select box to the second query, which is correct. The variable church in the second table only stores the 'unique' number from the first table. So I think that I am using unique correctly. Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/#findComment-767048 Share on other sites More sharing options...
Mad Mick Posted February 20, 2009 Share Posted February 20, 2009 Try: $sql1 = "SELECT * FROM records where church='$village'"; So you are passing back a number - unique - to the form. But you are using this in the sql query: $sql1 = "SELECT * FROM records where church=$village"; so church is a number? Not sure whats happening but the variable names are a bit confusing... Try using a standard notation like church_id or church_name and try to organise variables a bit i.e. you might easily assume a field called 'church' contains a name of a church not a number and not something to do with a village. Try to keep field names common between tables i.e. the church ref number should be called church_id in both tables. Only suggesting all this as you say you are new - its best to start off structured and organised, it will make life easier later! Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/#findComment-767065 Share on other sites More sharing options...
colinsp Posted February 20, 2009 Author Share Posted February 20, 2009 Thanks Mad Mick but that change didn't work either. I have renamed the field names as you have suggested. I even tried splitting the output to a second page to see if it was 'self' that was causing the problems, still without success. I cannot get any result echoed to the screen from query $sql1. Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/#findComment-767090 Share on other sites More sharing options...
Yesideez Posted February 20, 2009 Share Posted February 20, 2009 You've commented out this line: //@$village = addslashes($_POST['vil']); Replace it with this: @$village = $_POST['vil']; Also, replace another line: echo '<option value="'.$row['unique'].'">'.stripslashes($row['village']).' - '.stripslashes($row['church']).'</option>'; Notice you forgot to close your option tag. Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/#findComment-767096 Share on other sites More sharing options...
Mad Mick Posted February 20, 2009 Share Posted February 20, 2009 You commented out the $village definition: //@$village = addslashes($_POST['vil']); Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/#findComment-767098 Share on other sites More sharing options...
colinsp Posted February 20, 2009 Author Share Posted February 20, 2009 OOPs you're are right I did. However I don't understand fully what I've done but I now have it working. This is my code now that works!!! <?php @$village = addslashes($_POST[vil]); $query = "SELECT * FROM records WHERE church_id='$village'"; $result = mysql_query($query); while($row = mysql_fetch_array($result, MYSQL_NUM)) { echo $row[2]." ".$row[3]." ".$row[4]."\n "; } ?> I searched around in some books and found one example with the 'MYSQL_NUM' added that and it seems to work. Now to understand why ??? Thanks everyone for your help. Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/#findComment-767137 Share on other sites More sharing options...
premiso Posted February 20, 2009 Share Posted February 20, 2009 @ = error suppressor, I suggest not using it. Instead when you go into a production (live website) environment turn off display_errors in the php.ini addslashes is being depreciated. I suggest using mysql_real_escape_string instead. <?php $village = mysql_real_escape_string($_POST['vil']); $query = "SELECT * FROM records WHERE church_id='$village'"; $result = mysql_query($query); while($row = mysql_fetch_row($result)) { echo $row[2]." ".$row[3]." ".$row[4]."\n "; } ?> A few things, one always encapsulate array variables (not numbers) in single or double quotes. This prevents an "Undefined Constant" Notice error from occurring. Two, mysql has many functions, the array is nice but you have to specify what you want back or else you get both a numbered index and an associative index back. Using either mysql_fetch_row if you want a numbered index or mysql_fetch_assoc if you want an associative index is preferred. If you have any other questions about that code post it here. Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/#findComment-767145 Share on other sites More sharing options...
colinsp Posted February 20, 2009 Author Share Posted February 20, 2009 Premiso, thanks for your comments I will take them on board. Two, mysql() has many functions, the array is nice but you have to specify what you want back or else you get both a numbered index and an associative index back. Using either mysql_fetch_row() if you want a numbered index or mysql_fetch_assoc() if you want an associative index is preferred. As I said I am a newbie at PHP. Can you give some suggestions as to where I could look to understand your comment above? Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/#findComment-767195 Share on other sites More sharing options...
premiso Posted February 20, 2009 Share Posted February 20, 2009 The php.net manual. I posted the links there, they should give examples on which each returns. mysql_fetch_array that will tell you the different types and should show you the different ways a data can be returned. If you do not know what an array is, the manual sort of explains those too, but an array is basically a collection of data to put it in simple/lamans terms. Sort of like a filing cabinet, you have folders with labels and inside folders contain files, you access the files by finding the right name then pulling it out, same type of deal. So basically you wanted to access the columns of the mysql query you ran with Numbered indexes (0, 1, 2,3) ($row[0] etc...). Arrays can also be associative ("id", "col2", "col3") which would accessed by $row['id'] ....etc The fetch_array function for mysql, if no 2nd parameter is used, returns a collection with both numbered and associative indexes. So it essentially doubles the array size/data returned. Specifying either row or assoc will give you 1 set of data with how you want to access it. (In this case you wanted to use mysql_fetch_row). Hope that helps. Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/#findComment-767198 Share on other sites More sharing options...
colinsp Posted February 21, 2009 Author Share Posted February 21, 2009 Premiso, thank you for taking the time to help and for the links. Having programmed years ago in Basic, Pascal and a little C I understand arrays, getting my head around a new language as I get older gets tougher. Thanks once again, I have certainly increased my knowledge today. Quote Link to comment https://forums.phpfreaks.com/topic/146108-solved-newbie-trying-to-read-from-a-second-table/#findComment-767654 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.