trying to display data from search results

[misticles]

im trying to display the results of a search when users use the drop down menus. ive been at this for 2 wks and cannot get it to work at all. please help :(

<?php

$programme = $progyear = $subjectname = $examyear = null; //declare vars

$conn= mysql_connect("localhost", "root", "7mysql12user");
$db = mysql_select_db('exampapers',$conn);

if(isset($_GET["programme"]) && is_numeric($_GET["programme"]))
{
$programme = $_GET["programme"];
}

if(isset($_GET["progyear"]) && is_numeric($_GET["progyear"]))
{
$progyear = $_GET["progyear"];
}

if(isset($_GET["subjectname"]) && is_numeric($_GET["subjectname"]))
{
$subjectname = $_GET["subjectname"];
}

if(isset($_GET["examyear"]) && is_numeric($_GET["examyear"]))
{
$examyear = $_GET["examyear"];
}

?>

<script language="JavaScript">

function autoSubmit()
{
var formObject = document.forms['theForm'];
formObject.submit();
}

</script>

<form name="theForm" method="get">



<select name="programme" onChange="autoSubmit();">
<option value="null"></option>
<option value="1" <?php if($programme == 1) echo " selected"; ?>>DT205</option>
<option value="2" <?php if($programme == 2) echo " selected"; ?>>DT008</option>
</select>

<br><br>



<?php

if($programme != null && is_numeric($programme))
{

?>

<select name="progyear" onChange="autoSubmit();">
<option value="null"></option>

<?php

//POPULATE DROP DOWN MENU WITH course_year FROM A GIVEN course_code

$sql = "SELECT DISTINCT yearid, course_year FROM courseyeartable WHERE courseid = $programme";
$progyears = mysql_query($sql, $conn);

while($row = mysql_fetch_array($progyears))
{
echo ("<option value=\"$row[yearid]\" " . ($progyear == $row["yearid"] ? " selected" : "") . ">$row[course_year]</option>");
}

?>

</select>

<?php

}

?>

<br><br>

<?php

if($progyear != null && is_numeric($progyear) && $programme != null)
{

?>

<select name="subjectname" onChange="autoSubmit();">
<option value="null"></option>

<?php

//POPULATE DROP DOWN MENU WITH subjects FROM A GIVEN course_code, course_year

$sql = "SELECT DISTINCT subject from elements WHERE course_year = $progyear ";
$subjects = mysql_query($sql,$conn);

while($row = mysql_fetch_array($subjects))
{
echo ("<option value=\"$row[subject]\" " . ($subjects == $row["subject"] ? " selected" : "") . ">$row[subject]</option>");
}

?>

</select>

<?php

}

?>


</form>

[martin5211]

Could you show me the error displayed? I can't find a syntax error in your code... the error seems to be located on the course year selection, subjects drop down appears to be filtered ok.

[misticles]

theres no error on screen, the search results just wont appear :( and its driving me insane!

[martin5211]

I can't replicate your issue without the structure of your tables.

I'm testing your code here, seems to be working right with these tables

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `elements`;

CREATE TABLE `elements` (
  `exampapers` text NOT NULL,
  `subject` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into `elements` values('1','tom'),
 ('2','tom'),
 ('1','svenson'),
 ('1','taylor');

SET FOREIGN_KEY_CHECKS = 1;

and

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `courseyeartable`;

CREATE TABLE `courseyeartable` (
  `yearid` int(11) NOT NULL AUTO_INCREMENT,
  `courseid` int(11) NOT NULL,
  `course_year` int(11) NOT NULL,
  PRIMARY KEY (`yearid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

insert into `courseyeartable` values('1','1','2008'),
 ('2','2','2009');

SET FOREIGN_KEY_CHECKS = 1;

Testing server:

http://novo.ath.cx/d.php

[BzzBee]

try to customize you code. its too messy. dont mind please. or try to use martin5211 code. it looks fine.

[misticles]

ye that part works, its displaying the results from the search thats the problem, these are the table that im using:

course_code course_year subject sitting year l ink
DT205 2 Calculus/Analysis Semester 2 2006/2007 Click here
DT205 3 Classical Mechanics Semester 1 2006/2007 Click here
DT205 3 Classical Mechanics Semester 1 2007/2008 Click here
DT205 4 Integral Equations Semester 2 2006/2007 Click here
DT205 4 Differential Equations Semester 1 2006/2007 Click here
DT205 4 Differential Equations Semester 1 2007/2008 Click here
DT205 1 Computer Architecture Semester 1 2008/2009 Click here

coursecodetable

courseid course_code
1 dt205
2 dt008

courseyeartable

yearid courseid course_year
1 1 1
2 1 2
3 1 3
4 1 4
5 2 1
6 2 2
7 2 3

[martin5211]

Misticles, please try to make a more precision or give more details what do you want to perform. I mean, the code is working right, I can't see what is your real issue.

You're performing only two queries in the code, one for the second drop-down and the other for the last one. Maybe, you want to perform a query with the data selected from both tables joined, is that what you want to do?

[misticles]

after you make a selection from the 3rd drop down, I want it to query the database for each selected choice and automatically produce the results from the search on screen.

[martin5211]

Use a SELECT...JOIN query to get results from related tables e.g.

SELECT * FROM courseyeartable JOIN elements ON (yearid = exampapers) WHERE courseid = $programme

I have seen an error in your code, $subjectname is a string, so checking for numeric value will always get FALSE. (look at the top of your source code where are assigned the $_GET array to simple variables).

This is the final code, try to customize it for your needs:

echo "<br /><br />";
if($subjectname != null && is_string($_GET["subjectname"]) && $programme != null)
{
	$sql = "SELECT * FROM courseyeartable  JOIN elements ON (courseyeartable.yearid = elements.exampapers) WHERE elements.subject = '".$subjectname."'";

	$result = mysql_query($sql,$conn);

	while($row = mysql_fetch_array($result))
	{
		echo $row['course_year'].' '.$row['subject']."<br />";
	}
}

This example shows course_year column from courseyeartable and subject from elements, you can use more fields.

[misticles]

Martin5211 i really appreciate all your help. I have changed $subjectname from a numeric value to a string. I have used the final code you have provided me with, i can't see any errors in the syntax, yet when i run this i get an error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/example.php on line 131

which is

while($row = mysql_fetch_array($result))

I can't see anything wrong with this?

Thanks again.

[martin5211]

Paste the code at end, outside from the last conditional that checks $progyear variable.

Test the MySQL query directly from phpMyAdmin 'SQL' sheet if returns any result from your tables... I'm using yearid and exampapers fields with coincidental values, that's crucial to make a query on multiple tables. Maybe your field is courseyeartable.courseid. Modify it according to your requirements.

[misticles]

Ok i've gotten it to work. seriously thank you so much for your time and help, its very well appreciated. i will be able to sleep tonight lol