[PHP-DB] Query from Hell (for me anyway)
Hi All, I've been trying to solve this for hours now with no end in sight, I'm hoping that someone here might be able to help me. I've got four tables set out as shown below: tbl_student_details id student_idnamestudy_mode --- 1 12 Joe Smith 1 2 123 Jane Smith 1 tbl_study_mode id txt_study_mode 1 Full Time 2 Part Time 3 Online tbl_student_history_answers idquestion_id p_answer 119 Network Management 219 Web Site Production 319 Web Site Management 420 Network Management 520 Web Site Production 620 Web Site Management tbl_student_history_info id student_idquestion_idanswer_id --- 11219 1 21220 6 3123 19 5 4123 20 6 Basically I need to pull out student_id, name and course selected for all students who elected full time study - and their response to question 19 and 20 I already have two separate queries and would like to join them, but I just can't seem to figure it out. The queries I have so far are: SELECT tbl_student_history_info.student_id, tbl_student_history_answers.panswers as Preference_1 FROM tbl_student_histpry_info, tbl_student_histpry_answers WHERE tbl_student_history_info,question_id = 19 AND tbl_student_history_info.answer = tbl_student_history_answers.id; SELECT tbl_student_details.student_id, tbl_student_details.name, tbl_study_mode.txt_study_mode FROM tbl_student_details, tbl_study_mode WHERE tbl_student_details.study_mode = tbl_study_mode.id; Basically I'm after a query that returns student_id, name, the student study mode and their answer to question 19. Any help/clue stick whacks are most welcome ! :-) -- Rgds, Chris MacKenzie Windows: "Where do you want to go today ?" Mac OS: "Where do you want to be tomorrow ?" Linux: "Are you coming or what ?" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Maximum field length with PHP 4.1.2/MSSQL v7 ?
"Frank M. Kromann" wrote: Many thanks Frank, changing from varchar to text did indeed fix the problem. On reflection, I do now remember reading about this on another mailing list, it's a pitty my memory failed me when I needed it the most :-) > The tool used to build the MSSQL extension for PHP (DB Library from Microsoft) was >created for SQL Server 6.x and not updated with the release of 7.x or 2000. > > In version 6.x the max length of char and varchar columns was 255 bytes, and that is >what you are seeing when you try to select data from char, nchar, varchar or nvarchar >> columns defined to be longar than 255 bytes. If you use the TEXT type you will not >have this limitation. > > - Frank > > > Hi All, > > > > I've come across an interesting problem. I'm trying to retrieve some > > text data (in this case a question up to 400 chars), but each time I > > perform a select statement I only receive the first 255 chars. > > > > What's the go here, is this just another microsoft thing ? -- Rgds, Chris MacKenzie Windows: "Where do you want to go today ?" Mac OS: "Where do you want to be tomorrow ?" Linux: "Are you coming or what ?" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Maximum field length with PHP 4.1.2/MSSQL v7 ?
pan wrote:
> >Ah, sorry I didn't make it terribly clear. I meant that I only ever get
> >a maximum of 255 chars returned.
>
> O.K. - then that makes it seem a truncation problem and not a
> data definition problem.
>
> minor thing: in edit_exam_question.php there is
> " print 'Select Correct Answer:'; "
>
> What's the You might want to add the 'readonly' attritbute to
> that textarea - lessens chance of unwanted post variables.
> You seem to be using that textarea only for displaying the
> retrieved question_text
These pages are for the "admin" user to be able to edit/delete any
question or possible answers, As such I left the text area rw so that
they could change it.
> Either one of the unknown queries in edit_exam_question.inc
> is truncating the data or the url parameter is truncated, or
> dbconn.inc is the culprit.
dbconn.inc only holds servername, username and password. I've attached
it and the other missing file to this message - with differing passwords
of course :-)
> Three chances at answering the problem, not enough data.
> Except for the url parameter passing - but only id and
> question_id get passed so no chance on the parameter formatting
Yes, I thought I'd keep url passing down to a minimum and only ever pass
record id's.
> ooops ... files attached only reiterate the problem and do not serve to
> further illuminate the path to happiness.
I've attached the two missing files, so this should help to diagnose
what I've stuffed up.
--
Rgds,
Chris MacKenzie
Windows: "Where do you want to go today ?"
Mac OS: "Where do you want to be tomorrow ?"
Linux: "Are you coming or what ?"
";
mssql_close($conn1);
exit;
}
$result1=mssql_query($sql1,$conn1);
if(!$result) {
print "No Records Found.";
mssql_close($conn1);
exit;
}
# Can't seem to find a good way to copy the returned array
# so I'll use a kludge and just re-submit the query.
# There must be a better way, but I'm pressed for time :-(
$result2=mssql_query($sql1,$conn1);
if(!$result) {
print "No Records Found.";
mssql_close($conn1);
exit;
}
$numrows = mssql_num_rows($result);
$numrows1 = mssql_num_rows($result1);
# $numrows2 = mssql_num_rows($result2);
#
# It doesn't matter if no rows are found as an empty table will be
# presented to the web browser, which looks better than some
# ugly text based error message anyway.
#
#if(!$numrows) {
# print "Cannot load data into array.";
# mssql_close($conn1);
# exit;
#}
## TODO
# I might add some code to alternate the background colour of the table cells
# from a light grey and white to make it easier to read the table when a large
# amount of data is returned or the page is printed to a hardcopy.
$row = mssql_fetch_array($result);
?>
";
mssql_close($conn1);
exit;
}
?>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Maximum field length with PHP 4.1.2/MSSQL v7 ?
pan wrote: > i.e., do you know the db.table.field in question is a column type meant > to hold the data you expect? You seem to be indicating a consistent > return of 255 when you ask for 400 - seems to be a clue about the > field specification. Ah, sorry I didn't make it terribly clear. I meant that I only ever get a maximum of 255 chars returned. -- Rgds, Chris MacKenzie Windows: "Where do you want to go today ?" Mac OS: "Where do you want to be tomorrow ?" Linux: "Are you coming or what ?" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Maximum field length with PHP 4.1.2/MSSQL v7 ?
pan wrote: > > From: "Chris MacKenzie" <[EMAIL PROTECTED]> > > I've come across an interesting problem. I'm trying to retrieve some > > text data (in this case a question up to 400 chars), but each time I > > perform a select statement I only receive the first 255 chars. > > > > What's the go here, is this just another microsoft thing ? > > > > Based on the paucity of data in your problem description I would > be inclined to trust the machine and ask what makes you think there > should be more than 'char(255)' worth of characters to retrieve? > > i.e., do you know the db.table.field in question is a column type meant > to hold the data you expect? You seem to be indicating a consistent > return of 255 when you ask for 400 - seems to be a clue about the > field specification. Well, yes. The table in question is specified as such: CREATE TABLE [dbo].[tbl_exam_questions] ( [question_id] [int] IDENTITY (1, 1) NOT NULL , [question_text] [char] (400) NOT NULL , [correct_answer] [int] NOT NULL ) ON [PRIMARY] I've also used a strlen to measure the string length on submission to the db via a form post, and I've done the same on the returned string (also via a form post). On submission the test text I entered was 271 chars, after retreiving the text I only get 255 chars each time. I'm only using a very simple query (select * from tbl_exam_questions where question_id=x;) and using the microsoft client tools such as query analyser and ms sql enterprise manager, both return the full string. I've attached the two related php files that deal with the process, I can't see anything too horrible (other than my coding style) :-) -- Rgds, Chris MacKenzie Windows: "Where do you want to go today ?" Mac OS: "Where do you want to be tomorrow ?" Linux: "Are you coming or what ?" edit_exam_question.php Description: application/unknown-content-type-php_auto_file update_exam_question.php Description: application/unknown-content-type-php_auto_file -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Maximum field length with PHP 4.1.2/MSSQL v7 ?
Hi All, I've come across an interesting problem. I'm trying to retrieve some text data (in this case a question up to 400 chars), but each time I perform a select statement I only receive the first 255 chars. What's the go here, is this just another microsoft thing ? -- Rgds, Chris MacKenzie Windows: "Where do you want to go today ?" Mac OS: "Where do you want to be tomorrow ?" Linux: "Are you coming or what ?" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Submitting Dynamic Form
Ah, I just knew there must be an easy way of doing it. Thanks Rick !
Is there a good reference on stuff like this with some examples ?
Am I pushing the friendship ? :-)
Rick Emery wrote:
>
> $HTTP_POST_VARS or $_POST is an associative array that holds the keys and
> values for all inputs/selects in form from the submitted page. Use
> array_keys() to determine the names of all your list boxes to examine each
> key and its value. Or you can use the list($key,$val) =
> each($HTTP_POST_VARS) construct to extract each key and value.
>
> So then you just walk through the array and process each entry.
>
> -Original Message-
> From: Chris MacKenzie [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 03, 2002 8:47 AM
> To: Rick Emery
> Cc: [EMAIL PROTECTED]
> Subject: Re: [PHP-DB] Submitting Dynamic Form
>
> Hi Rick,
>
> Here's a code snippet (less error checking). Basically there could be as
> many as twnety questions listed with their associated listboxes of
> possible answers. Also be aware that ms-sql identity type is similar to
> mysqls autoincrement type except that once a record is deleted, that
> identity value is never used again.
>
> My problem is that since the question_id field is never guaranteed to be
> sequential and seeing that I have named the answer listboxes with the
> question_id field, what is a good way to extract the values from the
> form and perform an update query ?
>
> How can I do this if I don't know what the question_id values will be ?
>
> -- CUT
> $sql= "SELECT * FROM tbl_exam_questions ORDER BY question_id;";
>
> $result=mssql_query($sql,$conn1);
> $numrows = mssql_num_rows($result);
>
> print '';
>
> ## List All Questions and load the possible answers into list box.
>
> for($cnt = 0; $cnt < $numrows; $cnt++){
> $row = mssql_fetch_array($result);
> print ''.$row["question_text"].'';
> print 'My Answer is: name="'.$row["question_id"].'">- SELECT ONE
> -';
>
> ## Now fetch the possible answers and load it into the list box.
>
> $pa_sql = "SELECT * FROM tbl_exam_answers WHERE question_id =
> ".$row["question_id"].";";
> $pa_result = mssql_query($pa_sql,$conn1);
>
> $pa_numrows = mssql_num_rows($pa_result);
>
> for($pa_cnt = 0; $pa_cnt < $pa_numrows; $pa_cnt++) {
> $pa_row = mssql_fetch_array($pa_result);
> print ' value="'.$pa_row["id"].'">'.$pa_row["p_answers"].'';
> }
> print '';
> ## Finished Loading Possible Answers into list box.
>
> }
>
> print ' type="reset" value="Reset Form" name="Reset">';
>
> -- CUT
>
> Oh, and before I forget tbl_exam_questions.correct_answer holds the
> relevent id entry from tbl_exam_answers
>
> > what happened when you extracted the form field names from the DB?
> >
> >> Hi All,
> >>
> >> I'm pretty new to the whole php thing and I'm currently making an
> >> multiple choice exam type of thing with php/mssql.
> >>
> >> The two tables concerned are called tbl_exam_questions and
> >> tbl_exam_questions which are defined like so.
> >>
> >> [tbl_exam_questions] (
> >> [question_id] [int] IDENTITY (1, 1) NOT NULL ,
> >> [question_text] [varchar] (355) NOT NULL ,
> >> [correct_answer] [int] NOT NULL
> >> ) ON [PRIMARY]
> >>
> >> [tbl_exam_answers] (
> >> [id] [int] IDENTITY (1, 1) NOT NULL ,
> >> [question_id] [int] NOT NULL ,
> >> [p_answers] [varchar] (255) NOT NULL
> >> ) ON [PRIMARY]
> >>
> >> What I'm trying to do is to pull all the questions out and display them
> >> with the possible answer in a html form and dropbox.
> >> I'm displaying the entire exam on one page, with one submit button on
> >> the bottom so that the student can review the answers before finally
> >> submitting them.
> >>
> >> The problem I have is that the form field names are dynamic in that they
> >> are set up the value held in tbl_exam_answers.question_id and with
> >> ms-sql the identity type is not guarenteed to be incremental, so how can
> >> I reference them to send a query back to the DB to mark the student ?
> >>
> >> Have I painted myself into a corner ? :-/
--
Rgds,
Chris MacKenzie
Windows: "Where do you want to go today ?"
Mac OS: "Where do you want to be tomorrow ?"
Linux: "Are you coming or what ?"
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Submitting Dynamic Form
Hi Rick,
Here's a code snippet (less error checking). Basically there could be as
many as twnety questions listed with their associated listboxes of
possible answers. Also be aware that ms-sql identity type is similar to
mysqls autoincrement type except that once a record is deleted, that
identity value is never used again.
My problem is that since the question_id field is never guaranteed to be
sequential and seeing that I have named the answer listboxes with the
question_id field, what is a good way to extract the values from the
form and perform an update query ?
How can I do this if I don't know what the question_id values will be ?
-- CUT
$sql= "SELECT * FROM tbl_exam_questions ORDER BY question_id;";
$result=mssql_query($sql,$conn1);
$numrows = mssql_num_rows($result);
print '';
## List All Questions and load the possible answers into list box.
for($cnt = 0; $cnt < $numrows; $cnt++){
$row = mssql_fetch_array($result);
print ''.$row["question_text"].'';
print 'My Answer is: - SELECT ONE
-';
## Now fetch the possible answers and load it into the list box.
$pa_sql = "SELECT * FROM tbl_exam_answers WHERE question_id =
".$row["question_id"].";";
$pa_result = mssql_query($pa_sql,$conn1);
$pa_numrows = mssql_num_rows($pa_result);
for($pa_cnt = 0; $pa_cnt < $pa_numrows; $pa_cnt++) {
$pa_row = mssql_fetch_array($pa_result);
print ''.$pa_row["p_answers"].'';
}
print '';
## Finished Loading Possible Answers into list box.
}
print '';
-- CUT
Oh, and before I forget tbl_exam_questions.correct_answer holds the
relevent id entry from tbl_exam_answers
> what happened when you extracted the form field names from the DB?
>
>> Hi All,
>>
>> I'm pretty new to the whole php thing and I'm currently making an
>> multiple choice exam type of thing with php/mssql.
>>
>> The two tables concerned are called tbl_exam_questions and
>> tbl_exam_questions which are defined like so.
>>
>> [tbl_exam_questions] (
>> [question_id] [int] IDENTITY (1, 1) NOT NULL ,
>> [question_text] [varchar] (355) NOT NULL ,
>> [correct_answer] [int] NOT NULL
>> ) ON [PRIMARY]
>>
>> [tbl_exam_answers] (
>> [id] [int] IDENTITY (1, 1) NOT NULL ,
>> [question_id] [int] NOT NULL ,
>> [p_answers] [varchar] (255) NOT NULL
>> ) ON [PRIMARY]
>>
>> What I'm trying to do is to pull all the questions out and display them
>> with the possible answer in a html form and dropbox.
>> I'm displaying the entire exam on one page, with one submit button on
>> the bottom so that the student can review the answers before finally
>> submitting them.
>>
>> The problem I have is that the form field names are dynamic in that they
>> are set up the value held in tbl_exam_answers.question_id and with
>> ms-sql the identity type is not guarenteed to be incremental, so how can
>> I reference them to send a query back to the DB to mark the student ?
>>
>> Have I painted myself into a corner ? :-/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Submitting Dynamic Form
Hi All, I'm pretty new to the whole php thing and I'm currently making an multiple choice exam type of thing with php/mssql. The two tables concerned are called tbl_exam_questions and tbl_exam_questions which are defined like so. [tbl_exam_questions] ( [question_id] [int] IDENTITY (1, 1) NOT NULL , [question_text] [varchar] (355) NOT NULL , [correct_answer] [int] NOT NULL ) ON [PRIMARY] [tbl_exam_answers] ( [id] [int] IDENTITY (1, 1) NOT NULL , [question_id] [int] NOT NULL , [p_answers] [varchar] (255) NOT NULL ) ON [PRIMARY] What I'm trying to do is to pull all the questions out and display them with the possible answer in a html form and dropbox. I'm displaying the entire exam on one page, with one submit button on the bottom so that the student can review the answers before finally submitting them. The problem I have is that the form field names are dynamic in that they are set up the value held in tbl_exam_answers.question_id and with ms-sql the identity type is not guarenteed to be incremental, so how can I reference them to send a query back to the DB to mark the student ? Have I painted myself into a corner ? :-/ -- Rgds, Chris MacKenzie Windows: "Where do you want to go today ?" Mac OS: "Where do you want to be tomorrow ?" Linux: "Are you coming or what ?" -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
