select statement won't do its job

[crazylegswilson]

this is probably an easy fix, but i'm still not exactly a whiz at this- thank you for reading this thread.

I've got this variable from one query I'm trying to use in another. here is the first query:

$sql = "SELECT * FROM homework_records WHERE recid = $id";
// execute SQL query and get result
$sql_result = pg_query($dbconn1,$sql) or die(pg_result_error());
while ($row = pg_fetch_array($sql_result)){
                $home_chapt_num_rec = $row["home_chapt_num_rec"];
                $title_chapt_rec = $row["title_chapt_rec"];
                $score = $row["score"]; 

so what i'm trying to do is limit the output of the next one with $home_chapt_num_rec like this:

$sql = "SELECT * FROM homework_questions WHERE clase_de_espana='$clase_de_esp' AND begin<$now AND $now<to_time AND home_chapt_num!=$home_chapt_num_rec";
// execute SQL query and get result
$sql_result = pg_query($dbconn,$sql) or die(pg_result_error());
while ($row = pg_fetch_array($sql_result)) {
                $home_chapt_num = $row["home_chapt_num"];
                $title_chapt = $row["title_chapt"];
$result = pg_query($sql) or die (pg_error());
 echo " <form method=\"POST\" action=\"work.php\"> 
 <table width=\"200\" border=\"1\">
  <tr>
    <td>Chapter</td>
    <td>Title</td>
  </tr>
  <tr>
    <td><input name=\"home_chapt_num\" type=\"radio\" value=\"$home_chapt_num\">
$home_chapt_num
<input type=\"submit\" name=\"Submit\" value=\"Do\"></td>
    <td>$title_chapt</td>
  </tr>
</table></form>"; }
      pg_close($dbconn);?> 

see what i'm doing wrong? is there enough code here to make sense of it all? too much? It seems to me like there needs to be something that checks each result in the "home_chapt_num!=$home_chapt_num_rec" part. is there a way to break up 'home_chapt_num" into a comma delimited list or something?

What i'm shooting for is for results only to display in the table only where home_chapt_num!=$home_chapt_num_rec. TIA for your help!

[siteguru]

Surely it is <> for Not Equal? != is PHP code not SQL. Alternatively ...

AND NOT home_chapt_num = $home_chapt_num_rec

... but is that really what you want to do - EXCLUDE that record ID from the results?

[spikeZ]

Hi folks, sorry to disagree with you site guru but the != is valid in mysql....
Quote from MySQL manual....

Operator precedences are shown in the following list, from lowest precedence to the highest. Operators that are shown together on a line have the same precedence

:=
||, OR, XOR
&&, AND
NOT
BETWEEN, CASE, WHEN, THEN, ELSE
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
|
&
<<, >>
-, +
*, /, DIV, %, MOD
^
- (unary minus), ~ (unary bit inversion)
!
BINARY, COLLATE

Just for info!

SpikeZ

[siteguru]

No probs.

[crazylegswilson]

Well, I'm using PostgreSQL for starters, but that isn't throwing an error like that. I'll change the sql just for gp- thank you! Yes, I do not want records to show for $id where home_chapt_num_rec is in home_chapt_num. Perhaps a bit of background would help out here. this code is on a page that displays two tables to users, one which holds scores from tests they have taken, the other that shows tests they have yet to take. the logic I'm trying to get in there is that if they have taken all the available tests, none will show up in the 'tests to do' table. Also, the 'to do' column only displays tests which correspond to that users course enrollment and only then if the current time is inside a set parameter. everything seems to work pretty well, but for some reason, if you do one of the sample tests, it still shows up in the 'to do' table- however when you do it again, it disappears. This is not the case with a second sample test- that one only lets you do it once. I don't get it. Maybe my select statement(s) doesn't discriminate enough? Am I getting results for all records that correspond to clase_de_esp but not necc. to $id? Would it do what I need if I add "WHERE recid = $id" to my second query as well? I guess it needs to be filtered by both. I'm starting to think i'm too confused. Is there any code i could add to clarify this situation? Thank you both again for your quick and useful help!

[crazylegswilson]

i was using this for a while, and it worked for a few hours, but now doesnt do what i need it to-

$sql = "SELECT DISTINCT home_chapt_num, title_chapt FROM homework_questions, homework_records WHERE clase_de_espana='$clase_de_esp' AND '$recid'='$id' AND begin<'$now' AND '$now'<to_time AND NOT home_chapt_num='$home_chapt_num_rec'"; 

Help?

[crazylegswilson]

This doesnt seem to work either:

$sql = "SELECT home_chapt_num, title_chapt FROM homework_questions WHERE clase_de_espana='$clase_de_esp' AND '$recid'='$id' AND begin<'$now' AND '$now'<to_time AND NOT home_chapt_num='$home_chapt_num_rec'";
// execute SQL query and get result
$sql_result = pg_query($dbconn,$sql) or die(pg_result_error());
while ($row = pg_fetch_array($sql_result)) {
                $home_chapt_num = $row["home_chapt_num"];
                $title_chapt = $row["title_chapt"];
$result = pg_query($sql) or die (pg_error());
$num = pg_num_rows($result);
// Present results based on validity.
if ($num > "0") { echo " <form method=\"POST\" action=\"do_homework.php\"> 
 <table width=\"200\" border=\"1\">
  <tr>
    <td>Chapter</td>
    <td>Title</td>
  </tr>
  <tr>
    <td><input name=\"home_chapt_num\" type=\"radio\" value=\"$home_chapt_num\">
$home_chapt_num
<input type=\"submit\" name=\"Submit\" value=\"Do some homework!\"></td>
    <td>$title_chapt</td>
  </tr>
</table></form>"; 
} else { echo "no tests to do right now";}}
      pg_close($dbconn);?> 

that was the whole query and output minus my connection stuff. I dont want to show the form if $home_chapt_num exists in another table, and i only want it to show if $now is inside a certain time, and also only if $clase_de_esp and $id match variables set earlier in the page. I keep thinking i have it working only to find out that it doesnt.

Can you help me?

[siteguru]

'$recid'='$id'

What is $recid? and why is it inside quotes?

[crazylegswilson]

Uh-oh. Does putting stuff in quotes make a difference?

$recid is the relational counterpart to $id; it's in the same table as $home_chapt_num_rec (which is homework_records). what i'm after is for $home_chapt_num_rec to be set with data only where $recid = $id, that way when i query homework_questions and ask for home_chapt_num <> $home_chapt_num_rec, the output will not include any home_chapt_num which are entered in my homework_records (btw, this holds teh results of the tests that the next table sends students to) table that correspond to $id. that way, when a student has taken a test, they only get one shot at it. now i'm confused just writing that. does what I want to have happen make sense to you? I set $home_chapt_num_rec in a query to homework_records right before i query homework_questions. it seems straightforward to me, but obviously it isnt at all, and maybe I'm missing some crucial bit of SQL here- i dont know. do you need to hear symptoms?

as of this edit, my code looks like this:

$sql = "SELECT home_chapt_num, title_chapt FROM homework_questions WHERE clase_de_espana=$clase_de_esp AND begin<$now AND $now<to_time AND NOT home_chapt_num=$home_chapt_num_rec"; 

and the error says this, which means it's getting hung up at the end: Warning: pg_query(): Query failed: ERROR: syntax error at or near "232" at character 90 in student_menu.php on line 93

[siteguru]

It seems to me you need 3 tables, example ...

Students = StID | StudentName
Tests = TID | TestName
Taken = TID | StID

... so to get the tests still to be taken by a student you could use ...

SELECT TestName FROM Tests t
INNER JOIN Taken tk ON t.TID <> tk.TID
WHERE tk.StID = $id

That is just an example (and is untested) but should give you some food for thought.

[siteguru]

I should also say that when a student takes a test you populate the Taken table with the StID of the student and the TID of the test.

[crazylegswilson]

when a student takes a test you populate the Taken table with the StID of the student and the TID of the test

yup... when the student complets the test, homework_records recieves the id (StID) as 'recid' and the chapter number (TID) as home_chapt_num_rec.

It seems to me you need 3 tables

yup- i got that and more, but most importantly, i have the 3 you reccommend. one holds users (or students or slaves or however you look at that), one holds
the test chapter numbers as well as the questions to the tests and the course each entry is specific to and finally, there is a table that holds the record of the test- with id of the taker and chapter number and a bunch of other unrelated info.

[crazylegswilson]

Of course, it still isn't working. Is there anything I'm forgetting to do, or anything blatently wrong? I feel like it should be working, and I've tried several variations of the SQL, and none has done what I'm after. I just dont understand.

[swdev]

Not sure if this will help, but here goes ...

in post #7 you had

$sql = "SELECT home_chapt_num, title_chapt FROM homework_questions WHERE clase_de_espana='$clase_de_esp' AND '$recid'='$id' AND begin<'$now' AND '$now'<to_time AND NOT home_chapt_num='$home_chapt_num_rec'";

I think that '$recid' should not be a variable, but the name of the column that you are searching, probably just recid, assuming that recid is the name of column in the homework_questions table. Try it and see if it works.

[crazylegswilson]

Thank you for replying! Well, I'm not getting an error message, but unfortunately, I'm not seeing any tests that have not been done either. I'm wondering if the select statement is being too exclusive or something... I cant figure this out. This is starting to make me think i need to look for another job...

[swdev]

A couple of things to do

1) echo the query (echo $sql just before you run the query. Does the query look correct? Try running the query directly on the database

2)

Your code

 $sql = "SELECT home_chapt_num, title_chapt FROM homework_questions WHERE clase_de_espana='$clase_de_esp' AND '$recid'='$id' AND begin<'$now' AND '$now'<to_time AND NOT home_chapt_num='$home_chapt_num_rec'"; 
   // execute SQL query and get result 
   $sql_result = pg_query($dbconn,$sql) or die(pg_result_error()); 
   while ($row = pg_fetch_array($sql_result)) { 
                 $home_chapt_num = $row["home_chapt_num"]; 
                 $title_chapt = $row["title_chapt"]; 
   $result = pg_query($sql) or die (pg_error());  // <== WHY 
   $num = pg_num_rows($result); 

See my commnet WHY. Why are you running the query agin inside the loop?

It would be better to do this

   
 $sql = "SELECT home_chapt_num, title_chapt FROM homework_questions WHERE clase_de_espana='$clase_de_esp' AND '$recid'='$id' AND begin<'$now' AND '$now'<to_time AND NOT home_chapt_num='$home_chapt_num_rec'"; 
  // execute SQL query and get result 
  $sql_result = pg_query($dbconn,$sql) or die(pg_result_error()); 
  $num = pg_num_rodw($result);
   if (0 < $num_rows)
   {
   while ($row = pg_fetch_array($sql_result))
    { 
      $home_chapt_num = $row["home_chapt_num"]; 
      $title_chapt = $row["title_chapt"]; 
      // display results here
    }
   }
   else
   {
     echo "no tests to do right now";
   } 
  pg_close($dbconn); 

Hope this helps

[crazylegswilson]

Well, I'm not sure whether it helped or not, but thank you! I get the same result- no error, but no new tests show up. I looked in the db, and they are in fact there, and the paramters should meet the requirements of the query and output some tests to do. I'm totally stumped now. Is it possible I'm asking for the wrong output?

How do i run the query directly on the db?

[swdev]

Do you have direct access to the database e.g. from a command line or administration tool?

Post the results of the echo $sql command. Let's have a look at the actual query that is being run.

[crazylegswilson]

Here is the result of putting the code where i think you meant to put it:

SELECT home_chapt_num, title_chapt FROM homework_questions WHERE clase_de_espana='Spanish 232' AND '28'='28' AND begin<'1102357692' AND '1102357692'<to_time AND NOT home_chapt_num='1.1'no tests to do right now> 

that was taken from the page source...

i put the echo like this:

// formulate and execute the query
 $sql = "SELECT home_chapt_num, title_chapt FROM homework_questions WHERE clase_de_espana='$clase_de_esp' AND '$recid'='$id' AND begin<'$now' AND '$now'<to_time AND NOT home_chapt_num='$home_chapt_num_rec'";
  // execute SQL query and get result
  $sql_result = pg_query($dbconn,$sql) or die(pg_result_error());
 echo "$sql";
  $num = pg_num_rows($result);
   if (0 < $num_rows)
   {
   while ($row = pg_fetch_array($sql_result))
    {
      $home_chapt_num = $row["home_chapt_num"];
      $title_chapt = $row["title_chapt"];
      // display results here 

i would run the sql in my pg admin, but the variables wouldnt be there, right?
Also, it seems as though only one variable is going into the query- not all of the home_chapt_num_recs are in that query... so i guess the question needs to be: "how do i get stuff like $home_chapt_num_rec to be ALL of the results from the earlier query- I have 2 entries in the table for that variable, so i want this select statement to check the other table for both possibilities?"

[siteguru]

Why not use a derivative of the example I gave you earlier using a JOIN? You could start by making the SQL statement very simple then build up its complexity.

It would also help if you posted here the actual structure of each of the tables together with some example data from each, then someone will likely be able to give you the exact SQL statement your need. In fact, if you'd done this at the start you would have had your answer a few days ago.

[crazylegswilson]

well, not to be sassy, but i have tried to ask what additional info someone who might help me could want. Thank you for helping me though! I have included sample data in this thread, and i am a NEWBIE- this is my first PHP app ever. I'm a smart guy, and I'm willing to do what it takes to learn how to do it the right way and get it done right. I dont get how to do wierd joins yet, i have done a few, but the way you described it to me earlier in this thread only made my head hurt.

here is what I'll do, and you can ask for more if you want, I'll copy all of my page (minus some secret stuff )

here it is!

<?php
if ((!$HTTP_COOKIE_VARS["id"])) {
    header("Location: login_postgre1.html");
    exit;
}
?>
<?php 
$now = strtotime ("now");
?>
<?php
$id = $HTTP_COOKIE_VARS["id"];
// create connection; substitute your own information
$dbconn = pg_connect("") or die("Couldn't make a connection");
// formulate and execute the query
$sql = "SELECT * FROM record_del_estudiante WHERE id=$id";
// execute SQL query and get result
$sql_result = pg_query($dbconn,$sql) or die(pg_result_error());
while ($row = pg_fetch_array($sql_result)) {
                $user_name = $row["user_name"];
                $clase_de_esp = $row["clase_de_esp"];
$result = pg_query($sql) or die (pg_error());
$num = pg_num_rows($result);
// Present results based on validity.
if ($num = "0") {
    echo "You are not authorized! Maybe you could try to <a href=\"login_postgre1.html\">login again</a>";}
    }
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Student Menu for <?php echo "$user_name"; ?></title>
<style type="text/css">
<!--
body {
    background-color: #F4ED05;
}
body,td,th {
    color: #990000;
}
-->
</style></head>
<body>
Well, <?php echo "$user_name , your record shows that you are enrolled in $clase_de_esp. What would you like to do now?"; 
    pg_close($dbconn);
    ?>
<table width="393" height="114" border="1">
  <tr>
    <th width="179" height="23" > See your scores? </th>
    <th width="241" >Take a test?</th>
  </tr>
  <tr>
    <td height="39"><table width="179" border="0" align="center">
      <tr>
        <td width="173"><div align="left">
          <table width="173" border="1">
                <tr>
                  <td width="53">Chapter </td>
                  <td width="62"><div align="center">Title</div></td>
                  <td width="36">Score</td>
                </tr>
                <?php
    // create connection; substitute your own information
$dbconn1 = pg_connect("") or die("Couldn't make a connection");
// formulate and execute the query
$sql = "SELECT * FROM homework_records WHERE recid = $id";
// execute SQL query and get result
$sql_result = pg_query($dbconn1,$sql) or die(pg_result_error());
while ($row = pg_fetch_array($sql_result)){
                $home_chapt_num_rec = $row["home_chapt_num_rec"];
                $title_chapt_rec = $row["title_chapt_rec"];
                $score = $row["score"];
                $recid = $row["recid"];
                $result = pg_query($sql) or die (pg_error());
     echo "  
      <tr>
        <td>$home_chapt_num_rec</td>
        <td>$title_chapt_rec</td>
        <td>$score</td>
      </tr>";} 
       pg_close($dbconn1);
?>
              </table>
        </div></td>
      </tr>
    </table></td>
    <td><?php
    // create connection; substitute your own information
$dbconn = pg_connect("") or die("Couldn't make a connection");
// formulate and execute the query
 $sql = "SELECT home_chapt_num, title_chapt FROM homework_questions WHERE clase_de_espana='$clase_de_esp' AND '$recid'='$id' AND begin<'$now' AND to_time>'$now' AND NOT home_chapt_num='$home_chapt_num_rec'";
  // execute SQL query and get result
  $sql_result = pg_query($dbconn,$sql) or die(pg_result_error());
 echo "$sql";
  $num = pg_num_rows($result);
   if (0 < $num_rows)
   {
   while ($row = pg_fetch_array($sql_result))
    {
      $home_chapt_num = $row["home_chapt_num"];
      $title_chapt = $row["title_chapt"];
      // display results here
    echo " <form method=\"POST\" action=\"do_homework.php\"> 
 <table width=\"200\" border=\"1\">
  <tr>
    <td>Chapter</td>
    <td>Title</td>
  </tr>
  <tr>
    <td><input name=\"home_chapt_num\" type=\"radio\" value=\"$home_chapt_num\">
$home_chapt_num
<input type=\"submit\" name=\"Submit\" value=\"Do some homework!\"></td>
    <td>$title_chapt</td>
  </tr>
</table></form>";}
   }
   else
   {
     echo "no tests to take right now";
   }
  pg_close($dbconn); 
?></td>
  </tr>
  <tr>
    <td height="42"><div align="center">Click &quot; Log Out!&quot; when you're done</div></td><form method="POST" action="delete_cookie.php"> 
    <td><div align="center">
      <input name="logout" type="hidden" id="logout" value="logout">
          <input type="submit" name="Submit" value="Log Out!">
    </div>
    </form>
  </tr>
</table>
</body>
</html>

I hope that helps you help me- thank you again for spending the time!

[crazylegswilson]

Can anyone explain how to do this join siteguru speaks of? I'm running out of time faster than you can say "funding runs out at the end of the year"!

this one :

It seems to me you need 3 tables, example ...

Students = StID | StudentName
Tests = TID | TestName
Taken = TID | StID

... so to get the tests still to be taken by a student you could use ...

Code:

SELECT TestName FROM Tests t INNER JOIN Taken tk ON t.TID <> tk.TID WHERE tk.StID = $id

[crazylegswilson]

I may have found why this thing is broken... there seems to be an issue with the time function...


could someone tell me if this is right?

<?php 
$now = strtotime ("now");
?>

I looked at my db and noticed that when i set the "begin" time to '1', the tables with homework yet to do pops right up... so maybe I'm doing something wrong with the table that inputs the time into the db?

[crazylegswilson]

Yup- i definately have a problem with the $begin variable and WITHOUT A DOUBT an issue with not all of the results of $home_chapt_num_rec being checked against all home_chapt_num.

I'm so confused!

[siteguru]

I replied to your PM. Follow the instructions and post the details here.