Trying to figure out how to build output from a query

[Seona]

Hi guys,

I'm trying to teach myself PHP as I build a site for a friend. It's turning out to be rather more complex than I first planned, however. Well, I always was ambitious. :rolleyes:

To get a feel for the data I'm working with, have a look here: http://www.dare2.com.au/productsservices.php

The headings are stored in a table called Category. The listing under each heading is stored in a table called Section, and each one references the CategoryID of the category it belongs to.

What I want is for a user to be able to select multiple sections and, when they hit the Show me button, have those sections displayed under their relevant category heading. What I currently have does nothing like that. 🙁

Just to illustrate, say you select Fusion and Weft Hair Extensions from the first category, as well as Synthetic Hair from the second category. The output should show:

Hair Applications
- Fusion
- Weft Hair Extensions
Hair Range
- Synthetic Hair

The third category will not show up at all, since nothing was selected from it.

I've been trying for a few days now to figure out how to a) construct the query and b) write the code to create the desired output, and I've tied myself in a mental knot over it. Can someone please help me? I'd really like to get this figured out so that I can move on to the rest of the site.

Cheers,

Seona.

[Roger_Ramjet]

Assuming you've got the selection process right and post the selections to the display page in an array, then something like this should do

if (isset($_POST['submit'])) {

   $cat_list = implode("," , $_POST['selections'];
	$sql = "SELECT c.cat_name AS n, s.listing AS l
	       FROM category c INNER JOIN section s USING (categoryid)
	       WHERE categoryid IN ($cat_list)
	       ORDER BY n, l";
	$result = mysql_query($sql);
	$curr = ""
	while ($row=mysql_fetch_array($result)) {
		if ($row['c'] != $curr) {
			echo $row['c'] . '<br />;
			$curr = $row['c'];
		}
		else {
			echo '&nbsp &nbsp' . $row['l'] . '<br />';
		}
	}
}

PS read my sig below

[Seona]

Thanks. I've put the code in and tweaked it a bit (you were right, there were a few typos as well as needing to change a few things to they apply properly), and now have this code:

		if (isset($_GET['submit'])) {
			$cat_list = implode("," , $_GET['Sections']);
			$sql = "SELECT c.CategoryName AS n, s.SectionName AS l
				FROM category c INNER JOIN section s USING (categoryid)
				WHERE categoryid IN ($cat_list)
				ORDER BY n, l";
			$result3 = mysql_query($sql);
			$curr = "";
			while ($row3 = mysql_fetch_array($result3)) {
				if ($row3['c'] != $curr) {
					echo $row3['c'] . '<br />';
					$curr = $row3['c'];
				} else {
					echo '&nbsp &nbsp' . $row3['l'] . '<br />';
				}
			}
		}

When I run it by selecting a couple of options and submitting the form, I get the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/html/productsservices.php on line 77

Line 77 is the start of the while loop. I'm really not sure what's wrong with it, or if it is a problem somewhere else that is simply being attributed to that line. The downside to trying to teach yourself I guess - I only have a hazy idea of what most of my code is doing and why, since I've only been doing this on and off for about 2 weeks.

Can you please have a look at the code and suggest why it might be failing like that?

[planetsim]

Run your query in PHPMyAdmin you may find that to be the reason.

[Seona]

OK, I tried that, and got the following error:

Unknown column 'c.categoryid' in 'on clause'

I'm guessing that the "on clause" referred to is the stuff for the JOIN field, but I'm not 100% sure.

If so, is this because there needs to be the same named column in both tables? At the moment, the Category table as the field ID as the key and the Section table has the field CategoryID. Do I need to go in and re-write the database fields? Or is there a way around this?

[Seona]

OK, never mind that one, I've sorted the query out - I did end up going and changing the table. I found I also needed to specify in the WHERE clause what table's CategoryID I was working with.

The output, however, is every section. 🙁 It doesn't show the category names, either.

Can someone walk me through this code step by step and tell me what it's doing, please? It's really hard to bug-fix something if you don't understand what it's doing.

			$sql = "SELECT c.CategoryName AS n, s.SectionName AS l
				FROM category c INNER JOIN section s USING (CategoryID)
				WHERE s.CategoryID IN ($list1)
				ORDER BY n, l";
			$result3 = mysql_query($sql);
			$curr = "";
			while ($row3 = mysql_fetch_array($result3)) {
				if ($row3['c'] != $curr) {
					echo $row3['c'] . '<br />';
					$curr = $row3['c'];
				} else {
					echo '&nbsp &nbsp' . $row3['l'] . '<br />';
				}
			}

Thanks.

[mtmosier]

I imagine I took the explanation far beyond what you need, but I wanted to be thorough.

/*  Break the sql statment down to describe it line by line  */
$sql  = 'SELECT ';
/*  Get the category name and section name, and refer to them as "n" and "l" in the data returned  */
$sql .= 'c.CategoryName AS n, ';
$sql .= 's.SectionName AS l ';
/*  Pull from the category table, and refer to the table as "c" in this query  */
$sql .= 'FROM category c ';
/*  Also pull from the section table, calling it "s".  Make sure records pulled from the two have matching CategoryID's, and only pull a row for that CategoryID if both tables have a record.  */
$sql .= 'INNER JOIN section s USING (CategoryID) ';
/*  Only grab rows in which the CategoryID is one of those listed in $list1  */
$sql .= "WHERE s.CategoryID IN ($list1) ";
/*  Get the listing alphabetically by category name (n), then section name (l)  */
$sql .= 'ORDER BY n, l';
/*  Execute the query  */
$result3 = mysql_query($sql);
/*  $curr should be the current category name we're working on.  Since we have none yet default it to blank.  */
$curr = "";
/*  Retrive a row from the query and store it as an array in $row3, looping through all rows available. */
while ($row3 = mysql_fetch_array($result3)) {
    if ($row3['c'] != $curr) {
        /*  If the "c" column returned from the query is not equal to $curr, then echo the "c" column and set $curr to this value.  We keep track of the current "c" we're working on so as not to display it more then once.  */
        echo $row3['c'] . '<br />';
        $curr = $row3['c'];
    } else {
        /*  If we're not echoing the "c" column, then we echo the "l" column instead (section name).  */
        echo '&nbsp &nbsp' . $row3['l'] . '<br />';
    }
}

BTW, I didn't really try to mark out any bugs, well, mainly because you didn't ask for that kind of help. If that should have been implied let me know. (Some people enjoy debugging... like me.)

[Seona]

That was an awesome explanation. Thank you, I now have a much better idea of what I am actually working with. 🙂

A couple of questions, though. In this code:

    if ($row3['c'] != $curr) {
        /*  If the "c" column returned from the query is not equal to $curr, then echo the "c" column and set $curr to this value.  We keep track of the current "c" we're working on so as not to display it more then once.  */
        echo $row3['c'] . '<br />';
        $curr = $row3['c'];
    } else {
        /*  If we're not echoing the "c" column, then we echo the "l" column instead (section name).  */
        echo '&nbsp &nbsp' . $row3['l'] . '<br />';
    } 

• Why are we using "c", which is the name we gave the category table, but "l", which is the name we gave a piece of returned data? I tried changing the c's to n's, but that made it do wierd things.

• This is a simplified version of what I ultimately want to do, which is get both the CategoryName and TitleImage fields from the Category table so that I can use the graphical heading for each category. How will introducing a second returned field from the Category table effect this code?

• At the moment, it doesn't output the titles of the Categories. I've tweaked the code so that it only outputs the selected sections (WHERE s.CategoryID IN ($list2) became WHERE s.SectionID IN ($list2) ). However it doesn't output the category names. When I changed c to n as I mentioned before, it output the category name in the place of the first section in that category, which is not right either. What needs to change?

I've certainly nothing against attempting the bug fixing myself - it's a good way to learn. It just highlights how much I don't know about the language, however. Still, I'm learning a little more with each question. 🙂

[mtmosier]

* Why are we using "c", which is the name we gave the category table, but "l", which is the name we gave a piece of returned data? I tried changing the c's to n's, but that made it do wierd things.

As you noticed, c's need to change to n's.

* This is a simplified version of what I ultimately want to do, which is get both the CategoryName and TitleImage fields from the Category table so that I can use the graphical heading for each category. How will introducing a second returned field from the Category table effect this code?

It doesn't have to change the logic. Since the new field comes from the same table just add the column into the list you're selecting.

* At the moment, it doesn't output the titles of the Categories. I've tweaked the code so that it only outputs the selected sections (WHERE s.CategoryID IN ($list2) became WHERE s.SectionID IN ($list2) ). However it doesn't output the category names. When I changed c to n as I mentioned before, it output the category name in the place of the first section in that category, which is not right either. What needs to change?

Changes you made sound good. The reason category names are outputted in place of the first section is because of the else in that block of code. The section name is only outputted when the category name isn't, but the first time we hit a new category name there is a perfectly valid section name in that row as well. Outputting the section name shouldn't be dependent on whether or not the category name was outputted.

[Seona]

OK, I think I have it. I canged the c's to n's and added the line that's in the else block to the if block so that it now reads:

				if ($row3['n'] != $curr) {
					echo $row3['n'] . '<br />';
					echo '&nbsp &nbsp' . $row3['l'] . '<br />';
					$curr = $row3['n'];
				} else {
					echo '&nbsp &nbsp' . $row3['l'] . '<br />';
				}

So when it first encounters a category name it outputs that and then outputs the section name for that record as well. I feel proud (although I know that I sure as hellfire couldn't have done it without your consice explanations and answers hugs). 🙂

Now I get to start playing with images.... 😉

[Roger_Ramjet]

Yes. mtmosier and Seona, their were some basic bugs in my original code.

I make no appologies for posting half-baked code: I'm not here to write your code for you. I only post code as an example, to point people in the right direction. That way they learn something.

Glad to see you all sorted this out. Well done mtmosier, that was an excellent explanation of the principles I applied. Fun isn't it Seona?

[Seona]

Perhaps I was remiss in not also thanking you for pointing me in the right direction initially. I apologise if you feel slighted. 🙁 Your original code block did indeed start me thinking along the right track, I just needed to have a lot of things explained and clarified. I have indeed learned quite a bit though.

So thanks to both of you. 🙂

[Roger_Ramjet]

Hey! Don't worry about me - read my signature again. I'm only sorry I was not online to join in the fun.

I know humans only learn from their mistakes so being wrong please me: it;s another learning opportunity. I'm only upset with myself if I don't learn the lesson. That is the sort of thing that damages my self-esteem, but then I'm emotionally self-contained: some would say self-absorbed.

[Seona]

grin Well, just wait until Monday when I get to start introducing the next level of complexity into that code. There's a good chance I'll be back here again. 😉

Decided to give myself a weekend off first, though....

[Seona]

I'm back again. :rolleyes:

I'm up to extending the code we worked out last week so that I now gives me all the correct details I want. The first part was easy. I added some fields to the query, added a few lines to the output, and I now have a correct combination of headings and descriptions. Yay me. 🙂

I have one last problem, however. Each category has two images associated with it that just help jazz up the page a little if there's lots of content. I don't want to overload the page with unnecessary pictures, though, so I want to write some code to do the following:

• If a category has at least two sections selected, it also displays the first image.
• If a category has at least four sections selected, it also displays the second image.

So, for example:

Category 1
- Section 1
- Section 2
- Section 3
- Section 4
Category 2
- Section 5
- Section 6
- Section 7

Category 1 will show two pictures and Category 2 will show one.

At the moment I have the following code:

$sql = "SELECT c.CategoryName AS n, c.TitleImage AS t, c.Image1 AS p1, c.Image2 AS p2, s.SectionName AS l, s.Description AS d
	FROM category c INNER JOIN section s USING (CategoryID)
	WHERE s.ID IN ($list2)
	ORDER BY n, l";
$result3 = mysql_query($sql);
$number = mysql_num_rows($result3);
$curr = "";
while ($row3 = mysql_fetch_array($result3)) {
	if ($row3['n'] != $curr) {
		echo "<img src=\"" . $row3["t"] . "\" alt=\"" . $row3["n"] . "\" class=\"titleImage\" />";
		echo "<h2>" . $row3['l'] . "</h2>";
		if ($number >= 2) {
			echo "<img src=\"" . $row3["p1"] . "\" />";
		}
		echo $row3['d'];
		$curr = $row3['n'];
	} else {
		echo "<h2>" . $row3['l'] . "</h2>";
		echo $row3['d'];
	}
}

Unsurprisingly, this doesn't quite do what I want because I gets the number of results in the whole query instead of just for the current category.

Can anyone suggest a way I can easily get the number of results for a single category and use that as a comparison?

Cheers,

Seona.

[Roger_Ramjet]

You add a counter to the loop that outputs the text.
Every time it hits a new Category it gets reset to 0.
It then gets 1 added to it every time it outputs a section.
When it hits the next category, and before it gets reset to 0, you test it and output the correct number of images.
When you get to the end of the last category and exit the loop, you again test it and output the correct number of images.

The problem you now have is that the images are after the text and not where you want them to be on the page.

You will need to use CSS to move the images to the correct position on the page.

Here are 2 links that you need in order to learn about CSS

CSS tutirial
good basic tutorial that will take you step by step through them

Full CSS referance
All the exact specifications about CSS for every html tag

CSS will be the easiest way to do this, despite your first reaction which will probably 😕 followed by :bemused: :queasy: 🙁

Just see if you can work out the code to get the right number if pix first. We'll worry about making the page look nice once you've got that sorted.

[mtmosier]

Don't you already have the category list in $list1? You should be able to get your counts from there instead of the db.

[Seona]

Roger Ramjet:

Hmm... I think I haven't quite done it correctly, but I'm not sure why. What I have now is:

while ($row3 = mysql_fetch_array($result3)) {
	if ($row3['n'] != $curr) {
		echo "<img src=\"" . $row3["t"] . "\" alt=\"" . $row3["n"] . "\" class=\"titleImage\" />";
		echo "<h2>" . $row3['l'] . "</h2>";
		echo $row3['d'];
		$curr = $row3['n'];
		$count = "1";
	} else {
		$count = $count + 1;
		if ($count = 2) {
			echo "<img src=\"" . $row3["p1"] . "\" alt=\"Image1\" />";
		}
		if ($count = 4) {
			echo "<img src=\"" . $row3["p2"] . "\" alt=\"Image2\" />";
		}
		echo "<h2>" . $row3['l'] . "</h2>";
		echo $row3['d'];
	}
}

It's putting out both images before every section except the first one. 🙁 Why?

Oh, and don't worry about the CSS stuff - that's something I already do. I got over the 😕 followed by :bemused: :queasy: 🙁 some time ago. Thanks for the resources though. 🙂

mtmosier
No, I don't think that will work. I've ended up with a list of category IDs and a list of section IDs, but what I need is a way to see how many sections are in a given category. I'm not sure how to do that with the lists I've got so far.

[Roger_Ramjet]

Does she? What's list 1?

Seriously though, mtmosier, it's the number of sections in the category that determines the number of images. Simply counting them as you loop through them does it. Bunging them in a div and floating off to the side should be no problem.

[Roger_Ramjet]

Where do you want them?

Can you post the code you had that worked before you started trying to add the pix, please. I really can't remeber where we were.