variant_and 2

[drtom]

Being absolutely new to php I have stumbled when trying to perform what is a very basic query

$query = 'SELECT question '
. ' FROM `master` '
. ' WHERE category = \'personal\', LIMIT 0, 150';
. ' WHERE area = \'tax\' LIMIT 0, 150';

// execute query

I want the agument to look at the category and then look at the area within that category and return an answer.
With the above I get the error "Parse error: parse error, unexpected '.' in /home/pencils/public_html/B1/qa_home/personal_q_tax.php on line 89"

From what I can tell I want to use the variant_and function but haven't been able to find how to write it properly.
Thanks

 

[BabyJeffy]

I don't quite know what you mean by a variant_and function (but I'm hardly a PHP guru). This alternative query may be more what you are looking for:

$query = " SELECT question ";
$query .= " FROM master ";
$query .= " WHERE (category = 'personal') ";
$query .= " AND (area = 'tax')";

I don't know about those LIMIT statements... probably something that is important for your specific query... but the query I have above ought to at least work.

Cheers,
Jeff

[tt]Jeff's Page @ Code Couch

http://www.codecouch.com/jeff/

http://www.codecouch.com/jeff/blog/

[/tt]

What is Javascript? FAQ216-6094

 

[drtom]

Thanks Jeff, that did it.
I can't tell you about the limit statements either. I must admit I copied the original from a tutorial that I can't seem to find

 

[drtom]

Jeff:
While I have your attention. I inserted a javascript expanding list in a page and it worked in all browsers on my machine except Firefox. I have it enabled but cannot see why it won't work. Any hints?

see

http://www.businesssitedesign.com/industries/accountant/acct_newsletter.htm

Thanks

 

[BabyJeffy]

Here is a solution that I've integrated with your code...

The following javascript function replaces the doit() function that you had:

<script type="text/javascript">
function twiddle(_data) {
  var _target = document.getElementById(_data+'Data');
  if (_target) {
    _target.style.display = (_target.style.display == 'block') ? 'none' : 'block';
  } else {
    alert('You need to set the ID of the DL to ' + _data + 'Data');
  }
}
</script>

It takes the ID of the H2 and appends a known string ('Data') to it - so it can access the child DL (with the same ID as the H2 only 'Data' has been added). It then sets the style for the DL to be block or none (depending on it's current state).

And the corresponding HTML (based on your code - with the images removed to save space):

<h2 id="march2005" title="Click here to expand/contract!" onclick="twiddle(this.id)">March</h2>
<dl id="march2005Data" style="display:none;">
  <dt><a href="../../Newsletters/march.mht" target="_blank">HTML</a></dt>
  <dt><a href="../../Newsletters/march.pdf" target="_blank">PDF</a></dt>
</dl>

<h2 id="june2005" title="Click here to expand/contract!" onclick="twiddle(this.id)">June</h2>
<dl id="june2005Data" style="display:none;">
  <dt><a href="../../Newsletters/june.mht" target="_blank">HTML</a></dt>
  <dt><a href="../../Newsletters/june.pdf" target="_blank">PDF</a></dt>
</dl>

You will have to manage the IDs for each section carefully... but that's not much of a chore - the twiddle() function will send an alert if the ID of DL is not correct.

There are so many ways of doing this... but I've tried to leave the code in a state that is more readable (so you can easily modify it in the future).

Cheers,
Jeff

[tt]Jeff's Page @ Code Couch

http://www.codecouch.com/jeff/

http://www.codecouch.com/jeff/blog/

[/tt]

What is Javascript? FAQ216-6094

 

[drtom]

Thanks. One more question and then I will stop bugging you...

Returning to the first question
$query = " SELECT question ";
$query .= " FROM master ";
$query .= " WHERE (category = 'personal') ";
$query .= " AND (area = 'employ')";
// execute query

The above (thanks to you) returns 11 questions related to employment from a personal list of +/- 50 questions.
Once the list is returned I want the user to be able to click on any one of the questions that is returned and have the answer for that question returned.
Because of the potential length of the list of questions and the length of some of the answers to the questions it is not practical to have all questions and answers open at the same time.
Is what I'm describing possible? (I figure that if I have thought of it, there is every liklihood that it must have been done before)
Thanks

 

[BabyJeffy]

Yes, it's very much possible!

As you said, the actual amount of data might prevent you from returning the answers for every question.

One way to address this is to offer a URL that requests the answer to a specific question... and returns the page with the answer (as well as the other questions without answers). This can be done without javascript but requires requesting the page every time the user wants to see the answer.

An alternative to the above, is to pop the answer up in a popup page (you request the answer by passing in the primary key for the question as part of the URL). This will not require the main page to be reloaded. This can require javascript but you can do the whole thing using just HTML (use the target="_blank" on the URL to do it).

Another solution is to employ AJAX techniques. This would require Javascript but not require the page to be reloaded all the time. Ideally you would offer a non-javascript solution as well... so probably the previous suggestion would be more simple to adopt.

I'd suggest the second suggestion at this stage. The javascript is simple and it can easily deprecate. Check this as a concept:

<a href="answers.php?question_id=6" target="_blank" onclick="window.open('answers.php?question_id=6','answerPopUpWindow','width=400,height=250');return false;">Show the answer</a>

Cheers,
Jeff

[tt]Jeff's Page @ Code Couch

http://www.codecouch.com/jeff/

http://www.codecouch.com/jeff/blog/

[/tt]

What is Javascript? FAQ216-6094

 

[drtom]

Jeffy Baby !!
Seems I am a bit more of a newbie than I thought.
I created the primary key but am struggling on where to put your suggested code. I keep getting an error on line "x" when I place your code between the <?php ?> tags.
When the code is placed ourside of the <? ?> tags the results are returned correctly but when I click on the link to "Show the answer" I get a page not found.

Here is the code on the page that returns the appropriate questions:
Thanks

============================================================

<?php
// set database server access variables:
$host = "localhost";
$user = "pencils";
$pass = "eraser";
$db = "pencils_qa";

// open connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

// select database
mysql_select_db($db) or die ("Unable to select database!");

$query = " SELECT question ";
$query .= " FROM master ";
$query .= " WHERE (category = 'personal') ";
$query .= " AND (area = 'tax')";

// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

// see if any rows were returned
if (mysql_num_rows($result) > 0) {
// yes
// print them one after another
echo "<table cellpadding=1 border=1>";
while($row = mysql_fetch_row($result)) {
echo "<tr>";
echo "<td>".$row[0]."</td>";
echo "</tr>";
}
echo "</table>";
}
else {
// no
// print status message
echo "No rows found!";
}

// free result set memory
mysql_free_result($result);

// close connection
mysql_close($connection);

?>

 

[BabyJeffy]

Ah, the code I posted was client-side HTML (with Javascript sprinkled in the middle to upset the mix).

The reason you got the "page not found" would simply be because I made up the file name answers.php -- you would modify this to be the name of a php file that can return the answer from the database when passed a parameter (as a part of the $_GET array).

Let's see if we can integrate it into your script above. Here are my changes to what you have:

...
while($row = mysql_fetch_row($result)) { 
  echo "<tr>"; 
  echo "<td>".$row[0];

$questionKey = $row['primary_key'];
echo '<br><a href="answers.php?question_id='.$questionKey;
echo '" target="_blank" onclick="window.open(';
echo "'answers.php?question_id=".$questionKey;
echo "','answerPopUpWindow','width=400,height=250');return false;";
echo '">Show the answer</a>';

  echo "</td>"; 
  echo "</tr>"; 
}
...

You will have to change the value of $questionKey to be the name of the primary key field of the question table (or some equivalent reference).

As indicated above, you will also have to change the name of the file (currently answers.php - note there are 2 instances) and maybe the parameter being passed into that file.

Hope that keeps you going!

Next step will be making the page that returns the answer.

Cheers,
Jeff

[tt]Jeff's Page @ Code Couch

http://www.codecouch.com/jeff/

http://www.codecouch.com/jeff/blog/

[/tt]

What is Javascript? FAQ216-6094

 

[drtom]

OK
Here is what I have done with your code: (brw, I don't know how to grab the code like you have been so taht is why I am using the ++ seperator)
The primary field (column) in the db is titled "id"
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
while($row = mysql_fetch_row($result)) {
echo "<tr>";
echo "<td>".$row[0];

$questionKey = $row['primary_key'];
echo '<br><a href="answer.php?question_id='.$id;
echo '" target="_blank" onclick="window.open(';
echo "'answer.php?question_id=".$id;
echo "','answerPopUpWindow','width=400,height=250');return false;";
echo '">Show the answer</a>';

echo "</td>";
echo "</tr>";
}
echo "</table>";

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Here is what I have attempted in the pop-up window. I saved it as answer.php

Problem is, I get a blank page.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

<!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>Untitled Document</title>
</head>

<body>
<?php
$questionKey = $_POST['primary_key'];
?>

<?php echo $primary_key ?>

</body>
</html>

 

[BabyJeffy]

Lemme help out here...

Wrap your snippet in [code] your code here [/code] tags and you will see it rendered like this:

 your code here

Now... in the first snippet of code you might have mis-understood what I meant... try this minor alteration to what you have:

while($row = mysql_fetch_row($result)) {
  echo "<tr>";
  echo "<td>".$row[0];

$questionKey = $row['id']; // this gets the primary key for this record
echo '<br><a href="answer.php?question_id='.$questionKey;
echo '" target="_blank" onclick="window.open(';
echo "'answer.php?question_id=".$questionKey;
echo "','answerPopUpWindow','width=400,height=250');return false;";
echo '">Show the answer</a>';

  echo "</td>";
  echo "</tr>";
}

Finally... we are effectively populating the $_GET array by sending the parameters as part of the URL. It looks like you might be missing the concept of using variables here... I've changed the code you supplied in the second snippet to be a little more friendly:

<?php
$questionKey = 'Error'; // set the default text to be echo'd to the page
if (isset($_GET['question_id'])) { // check the parameter was supplied
  $questionKey = $_GET['question_id']; // put the value into $questionKey 
}
echo $questionKey; // echo the contents of the $questionKey variable
?>

Hopefully that will print a number to the page - and not the text string Error

Cheers,
Jeff

[tt]Jeff's Page @ Code Couch

http://www.codecouch.com/jeff/

http://www.codecouch.com/jeff/blog/

[/tt]

What is Javascript? FAQ216-6094

 

[drtom]

I'm sure we're getting closer and you are right re: "missing the concept of using variables". Being new to something can be frustrating. I am reading through the php manual but you know how big it is.

I copied the code in your first snippet as is.

In the answers.php page I copied the second snippet to between the body tags.

I still get a blank page when I click for the answer.
I must be really thick here cause you don't normally have to really beat me over the head with the mallet... not too often anyway.

Thanks btw, I am ever so grateful.

Here's the url for the page:

http://www.businesssitedesign.com/B1/qa_home/personal_q.htm

Click on the Taxation link

 

[BabyJeffy]

OK... the main problem is with this part of the code:

$questionKey = $row['id']; // this gets the primary key for this record

This is giving an empty string... so the URLs in the "Show the answer" HREF don't have the unique key for the question.

Thinking it through it's becuase the initial query only returns one field - "question". Let's make a modification to the initial query:

$query = " SELECT question[b] ,id[/b] ";
$query .= " FROM master ";
$query .= " WHERE (category = 'personal') ";
$query .= " AND (area = 'tax')";

Now we should be cooking

One final change I'd suggest is to reference the fieldname for the question differently (instead of referencing the zero-th element of the $rows array, we can access the element by it's name - easier to read etc). Here is the code to do that:

echo "<td>".$row[[b]'question'[/b]];

Hope that's it!

Cheers,
Jeff

[tt]Jeff's Page @ Code Couch

http://www.codecouch.com/jeff/

http://www.codecouch.com/jeff/blog/

[/tt]

What is Javascript? FAQ216-6094

 

[drtom]

I made the changes to the code as you have suggested in the last post

$query = " SELECT question ,id ";

and

echo "<td>".$row['question'];

and nothing is returned other than "Show the answer" (which is always returned) and there is no return in the pop up answer window.

I started experimenting with the following combinations:

#1

$query = " SELECT question ,id ";

echo "<td>".$row['1'];

the id # of the corresponding question in the database is returned but no question

#2 - I reverse the SELECT order

$query = " SELECT id ,question ";

and

echo "<td>".$row['0'];

the id # is returned but no question

#3 - insert 1 in $row

$query = " SELECT id ,question ";
and
echo "<td>".$row['1'];

the question is returned but no id #

#4 - reverse the SELECT order and insert anything else in $row

$query = " SELECT id ,question ";
and
echo "<td>".$row['anything'];

nothing is returned

#5 - add answer to the SELECT order and insert 1 in $row

$query = " SELECT id ,question ,answer ";
and
echo "<td>".$row['1'];

the question is returned but no id #

In all cases the answer is not returned when I click on "Show the answer".

I am going to keep adjusting it but I think I am missing something in your explanation. Is there something missing in the code on the pop up page?

<?php
$questionKey = 'Error'; // set the default text to be echo'd to the page
if (isset($_GET['id'])) { // check the parameter was supplied
  $questionKey = $_GET['id']; // put the value into $questionKey
}
echo $questionKey; // echo the contents of the $questionKey variable
?>

I also tried substituting answer in

  $questionKey = $_GET['answer']; // put the value into $questionKey
}

but that didn't work either.

Thanks

 

[jpadie]

try replacing mysql_fetch_row with mysql_fetch_assoc. this tells php to return each row of the recordset as an associative array. then you will be able to address the columns within each row in the manner that Jeff suggested.

 

[drtom]

Thanks jpadie, it's getting close as I now get the question's id # in the answer pop-up.
I am trying to see the flow through here but am missing something (lack of experience... or worse)
If I can try to put into lay terms; we are asking that the id and question be returned from the db and populate a page. Then we ask the resulting question to retrieve the related answer from the db and open it in a new window. Should we not be asking for the answer in the original SELECT and designate it as the contents of the questionkey?

I changed the pop up a little as I want to show the question as well as the answer. You can see what's happening at

http://www.businesssitedesign.com/B1/qa_home/personal_q.htm

click on "Taxation" and then "Show the answer"

 

[jpadie]

i'm coming to this post quite late. I'm sure Jeff's got a great hand on your code and if he's online perhaps he can answer this. Me, i'm not certain i can derive everything i'd need from the code fragments above so ...

happy to help but could you post the code that you use in answer.php? also could you post your table structure?

 

[drtom]

Thanks:
Code from answer.php

<table width="375" border="1" align="left">
<tr>
<td>
Question placed in Table Row #1 -- 
<?php
$questionKey = 'Error'; // set the default text to be echo'd to the page
if (isset($_GET['id'])) { // check the parameter was supplied
  $questionKey = $_GET['id']; // put the value into $questionKey
}
echo $questionKey; // echo the contents of the $questionKey variable
?>

</td>
</tr>
<tr>
<td>
Answer placed in table row #2 -- 
<?php
$questionKey = 'Error'; // set the default text to be echo'd to the page
if (isset($_GET['id'])) { // check the parameter was supplied
  $questionKey = $_GET['id']; // put the value into $questionKey
}
echo $questionKey; // echo the contents of the $questionKey variable
?>
</td>
</tr>
<tr>
<td align="center"><input name="close" type=button id="close" onClick="javascript:self.close();" value="Close Window"> 
</td>
</tr>
</table>

Table Structure (not sure if this is what you are asking for)

 	 Field   	Type  	Collation  	Attributes  	Null  	Default  	Extra  	Action
	 id  	int(4) 	  	  	No  	  	auto_increment  	Change 	Drop 	Primary 	Index 	Unique 	Fulltext
	 category  	varchar(10) 	latin1_swedish_ci 	  	No  	  	  	Change 	Drop 	Primary 	Index 	Unique 	Fulltext
	 area  	varchar(10) 	latin1_swedish_ci 	  	No  	  	  	Change 	Drop 	Primary 	Index 	Unique 	Fulltext
	 question  	longtext 	latin1_swedish_ci 	  	No  	  	  	Change 	Drop 	Primary 	Index 	Unique 	Fulltext
	 answer  	longtext 	latin1_swedish_ci 	  	No

Row Statistics:
Statements 	Value
Format 	dynamic
Rows 	110
Row length ø 	949
Row size  ø 	968 Bytes
Next Autoindex 	111

 Indexes:
Keyname 	Type 	Cardinality 	Field
PRIMARY 	PRIMARY 	110  	id

 

[drtom]

Jeffy has been a tremendouse help and has earn mucho stars. If he has a normal life he's probably enjoying the weekend with hsi family.

 

[jpadie]

i've rejigged your code a bit. this should work if i have got a handle on your needs

<table width="375" border="1" align="left">
<tr>
<td>
<?php
$questionKey = isset($_GET['id']) ? $_GET['id'] : "Error"; // this is a simplified  way of doing what you previouly had.
//do the query etc.  THIS ASSUMES YOU ARE ALREADY CONNECTED TO THE DB

$query = "Select * from master where id='$questionKey'";
$results = mysql_query($query) or die ("Something has gone wrong with the query "  . mysql_error());
$row = mysql_fetch_assoc($results); //don't need a while loop as this is a query on the primary key
?>
Question placed in Table Row #1 -- <?=$questionKey?>
<br/>
<div class="question">
<?=$row['question']?>
</div>
</td>
</tr>
<tr>
<td>
Answer placed in table row #2 -- <?=$questionkey?>
<br/>
<div class="answer">
<?=$row['answer']?>
</div>
</td>
</tr>
<tr>
<td align="center"><input name="close" type=button id="close" onClick="javascript:self.close();" value="Close Window"> 
</td>
</tr>
</table>