Newbie question on mysql_fetch.

[le_gber]

Hi all,

I have 2 tables: one for questions and one for answers. There are 5 'possible' answers for each question.

I created a SQL Statement as follows:

$result = @mysql_query('SELECT col1,col2,col3 FROM table_q,table_a WHERE col1 = col3);

col1 is in table_q and col3 in table_a;

It has the desired effect of fetching the responses associated to the answer but I have a problem with the output.

I would like to display the question one and have the 5 possible answers in a drop down.

How do I go about doing this? I cannot get my head around it? Should I create two queries one for the questions and one for the answers and then join both?

thanks for your help

[Habtom]

Your queries should look like:

(if col2 is in table_q:)
$result = @mysql_query('SELECT Q.col1, Q.col2, A.col3 FROM table_q Q,table_a A WHERE Q.col1 = A.col3);

(if col2 is in table_a:)
$result = @mysql_query('SELECT Q.col1, A.col2, A.col3 FROM table_q Q,table_a A WHERE Q.col1 = A.col3);

Habtom

[le_gber]

Habtom

thanks for your reply - my current query returns the right results the problem I had was figuring out how to display the question only once while 'looping' through the all the possible answers.

I solved it by using mysql_result($result,0) to display the question and a while loop on mysql_fetch_array($result) to display the answers

cheers

le_gber

[le_gber]

oh and I used mysql_data_seek(result,0) to move the cursor back at the start of $result as well - just for those that it might useful for.

[coopster]

I would join the two tables and each time the question changes I would start a new paragraph format in the HTML.

$question = false; // initialize 
$html = ''; // initialize 
while (processing query result set) { 
 if ($question!== $row['question']) { 
  if ($html) { 
   // end any previous question block 
  } 
  // begin a new block 
 } 
 // build the answers list 
} 
// tidy things up if necessary: 
if ($html) { 
 // end any previous question block 
}

Something like that anyway.

[deejay]

le_gber, this sounds exactly like a situation I want to tackle at the moment, but I'm only just starting with php/mysql.

I've got a product that gets assigned to multiple descriptive categories, and I want to be able to display the product name and then a list of the assigned categories under each product.

Would you mind posting your intact code that you worked out?

[le_gber]

deejay,

do you want to display one product per page or many products per page.

If the latter you might be better off with two SELECT statements one inside the other.

$resultCat = 'SELECT * FROM categories'

while ($theCat = mysql_fetch_array($resultCat){
$categoryID = $theCat['catID'];
echo "$categoryID";

$resultProd = 'SELECT * FROM products WHERE catID = '.$categoryID
// for this catID I am assuming that you have tow tables and that in the products table you have a column catID to match the ID from the categories table

while ($theProd = mysql_fetch_array($resultProd){
$productID = $theProd['prodID'];
$productName = $theProd['prodName']

echo "$productID $productName";

}//end while loop for the products

}//end while loop for the categories

disclaimer:I'm also just starting off hand-coding PHP from scratch so I am not sure if this works;)

[deejay]

Awesome, thank you! :) I'll pick it apart tonight hopefully and see how it goes.

do you want to display one product per page or many products per page.

Actually I'll need both - multiple products and single products for two different pages, but if I can nail one then the other should come.. I hope.

[deejay]

woohoo! It took a couple of hours of fluffing around, but it is working. Mostly just quotes, semi-colons and fine detail to fix - the basic structure is correct in le-gber's post

I've kept it simple to get it working, so the tables this is looking to are:

Table: Tree
Category table - in this case just uses the category ID (catno)

Table: cpbsproductcats
Because I have a many:many relationship between categories and products, I have this table as a 'lookup'. Only has two colums: catno and productno

I also reversed my original requirement, which was to list each product with all the cats it is assigned to, to match le_gber's situation more closely.. and instead listed each category and all the products assigned to that category.

code:

<?php
$resultCat = mysql_query('SELECT * FROM `tree`');
while ($theCat = mysql_fetch_array($resultCat)) {
$categoryID = $theCat['catno'];
echo '<p><b>'."$categoryID".'</b></p>';

$resultProd = mysql_query('SELECT * FROM `cpbsproductcats` WHERE cpbsproductcats.catno = "'.$categoryID.'"') or die ('Error: '.mysql_error ());

while ($theProd = mysql_fetch_array($resultProd)) {
$productID = $theProd['productno'];

echo "$productID".'<br>';

}//end while loop for the products

}//end while loop for the categories

?>

result:

catno 1

productno 1
productno 2

catno

productno 1
productno 3
productno 4
productno 5

etc