SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 42
-
Dec 3, 2004, 00:10 #1
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
select statement won't do its job
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:
PHP Code:$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"];
PHP Code:$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);?>
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!
-
Dec 3, 2004, 02:32 #2
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Dec 3, 2004, 03:44 #3
- Join Date
- Aug 2004
- Location
- Manchester UK
- Posts
- 13,807
- Mentioned
- 158 Post(s)
- Tagged
- 3 Thread(s)
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
SpikeZMike Swiffin - Community Team Advisor
Only a woman can read between the lines of a one word answer.....
-
Dec 3, 2004, 09:26 #4
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
No probs.
-
Dec 3, 2004, 11:44 #5
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Dec 3, 2004, 19:26 #6
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
i was using this for a while, and it worked for a few hours, but now doesnt do what i need it to-
PHP Code:$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'";
-
Dec 4, 2004, 11:12 #7
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
This doesnt seem to work either:
PHP 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());
$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?
-
Dec 4, 2004, 12:41 #8
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
'$recid'='$id'
What is $recid? and why is it inside quotes?
-
Dec 4, 2004, 15:40 #9
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:PHP Code:$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";
-
Dec 4, 2004, 16:02 #10
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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
-
Dec 4, 2004, 16:03 #11
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Dec 4, 2004, 16:11 #12
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
when a student takes a test you populate the Taken table with the StID of the student and the TID of the test
It seems to me you need 3 tables), 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.
-
Dec 5, 2004, 17:06 #13
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Dec 6, 2004, 07:29 #14
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.
-
Dec 6, 2004, 10:43 #15
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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...
-
Dec 6, 2004, 10:57 #16
A couple of things to do
1) echo the query (echo $sqljust before you run the query. Does the query look correct? Try running the query directly on the database
2)
Your code
PHP 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);
It would be better to do this
PHP 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());
$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);
-
Dec 6, 2004, 11:46 #17
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Dec 6, 2004, 11:55 #18
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.
-
Dec 6, 2004, 12:49 #19
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Here is the result of putting the code where i think you meant to put it:
PHP Code: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>
i put the echo like this:PHP Code:// 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
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?"
-
Dec 6, 2004, 14:16 #20
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Dec 6, 2004, 20:19 #21
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 Code:<?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 " Log Out!" 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>
-
Dec 7, 2004, 11:01 #22
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Dec 7, 2004, 11:42 #23
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 Code:<?php
$now = strtotime ("now");
?>
-
Dec 7, 2004, 11:57 #24
- Join Date
- Nov 2004
- Location
- oregon
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Dec 7, 2004, 17:07 #25
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
I replied to your PM. Follow the instructions and post the details here.
Bookmarks