[RESOLVED] Can't get head around this query!

[Tezread]

Im looking to create a similar procedure to this forum whereby when you click a thread you run a query which effectively lists all the responses. Ive been trying all day now to develop a solution with no luck.

My table (trainingtopics) is:

coursecode (primary_key)
coursename
elearning
facetoface
blended

etc

I have a list of coursenames and now I would like to click each one and see all the corresponding details.

This is my first page called elearningcourses which lists all courses using a query and the href function:

  <?php 

$user = "root";
$host = "localhost";
$password = "";
$connection = mysql_connect($host, $root, $password) or die ("Couldn't connect to server.");
$database = "courses";
$db = mysql_select_db($database) or die ("Couldn't select database.");

$sql = "SELECT coursename FROM trainingtopics";
//echo $sql."<br>";

$result = mysql_query($sql) or die(mysql_error());
echo $result."=result<br>";

while($row=mysql_fetch_array($result))
{ // NOTE this one ABOVE the echo
//echo "result found!";
//echo $row[0];
echo "<a href=\"details.php?course=".$row[0]."\">".$row[0]."</a><br>";

}
?>

And here is what I have for the details.php page which is supposed to expand on the chosen coursename in elearningcourse.php and show all the records such as elearning/facetoface/blended etc.

<?php

if (isset($_GET['course'])) {
$user = "root";
$host = "localhost";
$password = "";
$connection = mysql_connect($host, $root, $password) or die ("Couldn't connect to server.");
$database = "courses";
$db = mysql_select_db($database) or die ("Couldn't select database.");

$course = mysql_real_escape_string($_GET['course']);

echo $course;

$res = mysql_query("SELECT * FROM trainingtopics WHERE coursename = '$course'");

echo $res;
if ($res && mysql_num_rows($res) != 0) {
// get results from query
// output to browser with echo
} else {
//course name not found
//note: the query may have failed to so maybe check for that.
}
} else {
//no course specified
}

?>

So far sometimes I get a blank page and other times just the chosen coursename is displayed.

HELP!!!!

[saraadmin]

I notice you have have a primary key field. It would make sense to use this as the query string parameter to specify the course in the second script rather than the course name as it is guarenteed unique and may also be more compatible with query string encoding.

Also, where you have those comments in your second script, does the working version have actual code? Particularly, if you get it to echo whats going on then you can tell what has caused a blank page. This may help you to debug.

Your problem probably occurs around here:

$res = mysql_query("SELECT * FROM trainingtopics WHERE coursename = '$course'"); 

echo $res; 
if ($res && mysql_num_rows($res) != 0) { 

and using the course id may help to solve it. Also, try just testing mysql_num_rows in your condition and also try echoing the query before you execute it to make sure its correct.

[Tezread]

cool. how would I go about testing mysql_num_rows.
By the way- this is a working version. The code is adapted from something given to me.

[Tezread]

Mmmm, interesting result- I have an error message-

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #3' at line 1

what does this mean?

[saraadmin]

how would I go about testing mysql_num_rows

I just meant that in your if statement where you have

if ($res && mysql_num_rows($res) != 0)

you could just have:

if (mysql_num_rows($res) != 0)

You have an error in your SQL syntax

Good, this is healthy. Where abouts does this occur? Is it in the second script?

Try replacing

$res = mysql_query("SELECT * FROM trainingtopics WHERE coursename = '$course'");

with:

$query = "SELECT * FROM trainingtopics WHERE coursename = '$course'";
echo("<p>QUERY=\"$query\"</p>");
$res = mysql_query($query);

so that we can see exactly what query has been passed to MySQL.