<Help>How 2 retrieve infor frm DB(MYSQL) when u select an option form a dropdown lis

[tpifc]

Hi I am new to php and is trying to retrieve specific information from db(mysql)when selecting an option from a dropdown list.

I am suppose to select a school from the dropdown list than the information bout the school will be displayed on the following page. like school location and all.

my db is
classmate

table:
create table schools (schName varchar(80), schLocation varchar(80), schDesc varchar(500), schContact varchar(30), schEmail varchar(30));

been trying a while but can't seem to retrieve the specific information.
😕

[etully]

We do not do your homework assignments for you. You should have a textbook that gives you some instruction. Read that.

Instead of asking, "How do I do it?", here's what you do:

1. write some code and give it a try
2. if it doesn't work, look for error messages. Use Google or PHPBuilder.com's search page to figure out why there was an error
3. search PHPBuilder.com for people who were doing something similar to what you are doing and see how they got their problem solved.

If you still can't figure out why your code isn't running, post it and we'll take a look at it. If we feel like you just want us to do your work for you, we're probably not going to help you. If you've proven that you've really tried, usually someone will help you out.

[tpifc]

Erm u might have understood, i have already done up the codes before i start this thread. will post it up tomorrow. the main problem is that i am able to read a word from the options but unable to display the rest of the related information. btw i don't have textbook at all. I am currently reading library books and online tutorials to learn php from scratch. Sorry if i sounded like dependent or anything, but i am not lazy, i myself will like to try things on my own first before seeking others for help. From that then i will learn. =)

[etully]

When you create the table, you should always have a unique ID. When I say "always", I mean 99% of the time. Even if you can technically get by without a unique ID, someone is going to change something about the project and you will wish you had used a Unique ID back when you started the project.

So instead of this:

create table schools (schName varchar(80), schLocation varchar(80), schDesc varchar(500), schContact varchar(30), schEmail varchar(30));

I would use this:

create table schools (id INT NOT NULL AUTO_INCREMENT, schName varchar(80), schLocation varchar(80), schDesc varchar(500), schContact varchar(30), schEmail varchar(30), primary key (id));

Then when you make your drop down list, the option value should include the ID # like this:

<select name="id">
<option value="12">New York School for the Gifted

Then, when the user clicks "submit" to post the request to your "display school data" script, you will do a select like this:

$id = $_REQUEST['id'];
$query = "select schName , schLocation , schDesc , schContact , schEmail  from schools where id=$id";

[big_nerd]

tpifc,

Here is a tip to auto display the id's.

<?php
// connect to the db

$query = "SELECT id, schName FROM schools ORDER BY schName ASC"; // get only the school id & name...
$result = mysql_query($query);
?>
<select name="school_id">
<?php
while ($row=mysql_fetch_object($result)) {
   echo "<option value=\"$row->id\">$row->schName</option>";
}
?>
</select>

And on the problem with your code only showing one word, show us what you have & we will be more than happy to help you.

etully,

If he was in school, he probably would have been taught about id numbers from the beginning... I know a friend of mine is and attempted to coax me into doing his homework, I helped him (but no, didn't write anyting, just helped him). His first db lession was to create a table in phpmyadmin (to understand datatypes, etc) and the first one was id.

Funny enough his teacher frequented this and many other boards...

They very well know their own assignments... that and they also realize what was taught in the class, and if a student is bringing stuff in that is too advanced, it becomes obvious.

[tpifc]

Erm hi thx for all your help. i tried to work towards etu codes, i displayed the id number on the next page but not the other values. After checking i realised the id (1) echoed out was from the <option value="1">AJ</option> panel and not from the db column.
Another problem was whenever i put

mysql_fetch_assoc
mysql_fetch_row

schools.php links to jc.php to display the results.
lol4.php is just the connection which i included in.

i will get the error suppiled argument is not a valid mysql command.
therefore had to comment it out.

Schools.php

<?PHP
session_start();
include("lol4.php");
$id= $_POST['dd1'];
$_SESSION['id']=$id;
echo $_SESSION['id'];

//echo "session abv";

?>
</a>
<!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=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
body {
	background-color: #fff0c4;
}
.style1 {
	font-size: 24px;
	color: #FF0000;
}
.style2 {
	font-size: 18px;
	font-weight: bold;
}
-->
</style></head>

<body>

<form id="form1" name="form1" method="post" action="Jc.php">
  <p class="style1">Schools In Singapore</p>
  <p class="style2">Select the school to view its respective particulars and information. </p>

  <p>&nbsp;</p>
  <p><a href=""></a><img src="moe2.PNG" width="303" height="74" longdesc="Education.bmp" /></p>
  <p>&nbsp;</p>
  <p>
    <label>Junior Colleges:
    <select name="id" id="dd1">
<option value="1">AJ</option>
<option value="2">BJ</option>
<option value="3">CJ</option>
<option value="4">DJ</option>
<option value="5">EJ</option>
<option value="6">FJ</option>
<option value="7">GJ</option>
<option value="8">HJ</option>
<option value="9">IJ</option>
<option value="10">JJ</option>
<option value="11">KJ</option>
<option value="12">LJ</option>
<option value="13">MJ</option>
<option value="14">NJ</option>
<option value="15">OJ</option>
<option value="16">PJ</option>
<option value="17">QJ</option>
<option value="18">RJ</option>
<option value="19">SJ</option>
<option value="20">TJ</option>
<option value="21">UJ</option>
<input type=submit name=submit value="Get School">
    </select>  

	<?PHP
	$_SESSIOM['schName'] = $_POST['schName'];

?>  
  </p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
</form>
</body>
</html>

Jc.php

<?PHP
include ("lol4.php");

session_start();
?>
<?php
$id = $_REQUEST['id'];
//$schName = $_REQUEST['schName'];
//$schLocation = $_REQUEST['schLocation'];
$query = "select schName , schLocation , schDesc , schContact , schEmail  from schools where id=$id";
$result=mysql_query($query);
echo $_SESSION=$id;
//echo $_SESSION=$schName;
//echo $_SESSION=$schLocation;
//echo $id;

//$row=mysql_fetch_row($result)
//echo"<table border=1><tr><th>School Name</th>";
//echo"<th>School Location</th><th>School Description</th><th>School Contact</th><th//>School Email</th></tr>";

//while($row=mysql_fetch_assoc($result))
//while ($row = mysql_fetch_row($query))
//{{
//echo"<tr>";
//echo '/$row['id']';
//}foreach ($row as $field)
//{
//echo"<td>$field</td>";
//}
//echo"</tr>";
//}
//echo"</table>";
//mysql_close($linkid);
?>
<!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=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
body {
	background-color: #fff0c4;
}
.style1 {
	color: #FF0000;
	font-size: 18px;
}
-->
</style></head>

<body>
<form id="form1" name="form1" method="post" action="">
  <p class="style1">Junior Colleges</p>
  <p>

  </p>
  <p>
    <label></label>
    <br />
  </p>
  <p>
    <label></label>
    <br />
  </p>
  <p>
    <label></label>
    <br />
  </p>
  <p>
    <label></label>
    <br />
  </p>
  <p>
    <label></label>
  </p>
  <p>&nbsp;</p>
</form>
</body>
</html>

lol4.php

<?php
session_start();
$id=$_POST['id'];
echo $schName;
//echo "lala";
// Connecting, selecting database
$linkid = mysql_connect('localhost','root','')
    or die('Could not connect: ' . mysql_error());
echo 'Connected successfully<br>';
mysql_select_db('classmate') or die('Could not select database');

?>

[big_nerd]

Try this:

Indead of this (from jc.php)

$query = "select schName , schLocation , schDesc , schContact , schEmail  from schools where id=$id"; 
$result=mysql_query($query); 
echo $_SESSION=$id; 

Try this:

$query = "select schName , schLocation , schDesc , schContact , schEmail  from schools where id=$id"; 
$result=mysql_query($query); 

while ($row = mysql_fetch_object($result)) {
 echo "School Name: $row->schName<br>\n";
 echo "Location: $row->schLocation<br>\n";
// etc etc etc for all of your variables
}

Good Luck

[tpifc]

Hm thx alot for your help big.nerd. Haven solve the problem thou, still trying at it.
Still getting this error:

mysql_fetch_object(); supplied argument is not a valid MYSQL result resource
in (directory).

Once again thx

[big_nerd]

That means that there was a problem with your query.

Try instead of "where id=$id" put "where id = '$id' "

[bradgrafelman]

big.nerd wrote:

Try instead of "where id=$id" put "where id = '$id' "

On the contrary, if id is a numeric column (sure sounds like it) there shouldn't be any quotes around its values.

There must be some other problem with the query. Instead of tring to guess what the problem is, you should debug the query and see what error messages the server is trying to give you. A common way to debug an erroneous SQL query is like so:

$query = 'SELECT foo FROM bar';
$exec = mysql_query($query) or die('MySQL error: ' . mysql_error() . '<hr>Query: ' . $query);

Adjust your code accordingly, and let us know what the SQL error is.

[tpifc]

Thx everyone for thier help, the problem was almost solved, but was handed over to my member to finish.
Thanks alot once again