using several variables in a mysql statement in a php script

[yoda69]

so...

two php files:

1. asking the user to select a teacher and adding to the hyperlink a variable ($id) to be used in the next page

2. showing a list of classes that teacher gave and ordering them by date or title.

 

problems:

1. when i try and use variables for the "order by" function (one for $orderby and another for $direction), from some reason the query fails. I think that problem is about the other variable ($id) which is used in the same statement.... when i take that variable off and just enter a static number there is no problem. However trying to use these two or even three variables in the same mysql statement fails. any ideas?

 

Here's the code of the second page:

---

    <?PHP

 

 

$db = mysql_connect("test","test","test") or die("Problem connecting");

mysql_select_db("education") or die("Problem selecting database");

 

//set a direction for the search. If none is set, we'll choose ASC

if(!isset($_GET['direction'])){

$direction = 'ASC';

}else{

$direction = $_GET['direction'];

}

//set the newdirection as the opposite to the old

if($direction=="ASC"){

$newdirection= "DESC";

}else{

$newdirection = "ASC";

}

if(isset($_GET['orderby'])){

$query = "SELECT * FROM classes, teachers, types WHERE (classes.teacher_id=teachers.id AND classes.discipline_id=classes.id AND teachers.id=$id) ORDER BY '$_GET[orderby]' $direction";

}else{

$query = "SELECT * FROM classes, teachers, types WHERE (classes.teacher_id=teachers.id AND classes.discipline_id=classes.id AND teachers.id=$id)";

}

 

$result = mysql_query($query) or die ("Main Query failed");

//let's get the number of rows in our result so we can use it in a for loop

$numofrows = mysql_num_rows($result);

 

echo "<TABLE class=\"maintable\" BORDER=1 frame=box width=100% CELLPADDING=0 CELLSPACING=1 >\n";

echo "<TR bgcolor=\"lightblue\">

<TD><a href='".$_SERVER["PHP_SELF"]."?orderby=start_date&direction=".($newdirection)."'>Start Date</a></TD>

<TD><a href='".$_SERVER["PHP_SELF"]."?orderby=title&direction=".($newdirection)."'>Title</a></TD>

<TD>Type</TD>

<TD>Teacher</TD>

</TR>\n";

for($i = 0; $i < $numofrows; $i++) {

$row = mysql_fetch_array($result); //get a row from our result set

if($i % 2) { //this means if there is a remainder

echo "<TR bgcolor=\"#CCFFCC\">\n";

} else { //if there isn't a remainder we will do the else

echo "<TR bgcolor=\"#BFD8BC\">\n";

}

echo"

<TD>".$row['start_date']."</TD>

<TD><a href='/showall.php?primary_index={$row[primary_index]}'>".stripslashes($row[title])."</a></TD>

<TD>".$row['type_name']."</TD>

<TD>".$row['teacher_name']."</TD>

\n";

echo "</TR>\n";

}

//now let's close the table and be done with it

echo "</TABLE>\n";

mysql_free_result($result);

?>

 

 

 

[tr33b1rd]

Since it's working when you input a static number, $id may hold a different value than you think. To troubleshoot it, print the query on the page before you execute it.

 

ex:

print $query;

$result = mysql_query($query) or die ("Main Query failed");

 

That way you can see exactly what you are executing. If you do not see "Main Query failed" output on the page, your query is probably executing successfully, but is selecting 0 rows.

[Illusion]

try this in your statement.

teachers.id='$id'

[yoda69]

tnx for the help,

After using the 'print' function i can see now that $id has no value..... from some reason the variables doesn't pass between the pages or get reseted when i use the variables for the Order by... any idea how i can work around this?