[SOLVED] Looping once through one column, then through ALL of another column?...

[ShootingBlanks]

I have a database table called "documents" with a "doc_id" column (primary key) "cat_id" column (which is a number) and a "doc_title" column (which is text).  I also have a "categories" table with "cat_id" (primary key) and "cat_name" (text) columns...

 

In the "documents" table, a number of docs may share the same cat_id.  So, docs A, B, & C could all have a cat_id of 1, and docs D, E, & F could have a cat_id of 2, etc...

 

What I want to do is this...

 

On a php page, have it loop through the documents table.  If it sees a "cat_id", list it's corresponding "cat_name" once as a header, then list all the "doc_title"s that correspond to that cat_id underneath it.  I think I know how to do it so that it keeps listing the cat_name over and over with each doc_title that it goes with, but I don't know how to just list each cat_name ONCE, and then have it continue to loop through each doc_title to just list THAT over and over again, and then continue on to the NEXT cat_name to repeat the process...

 

So, it should end up looking like:

FIRST CATEGORY

• document 1
  
• document 2
  

THIRD CATEGORY (maybe in my SQL query i run, there's no filtered results that have a 2nd category, so it skips to the third)

• document 1
  
• document 2
  
• document 3
  

 

...is that too confusing, or am I making sense?  I ramble a lot. 

 

[Daniel0]

SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id;

 

Should do that.

[ShootingBlanks]

SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id;

 

Should do that.

 

That seemed to just get me everything on both tables when I tested that...

 

Also, my problem (i think primarily) lies in the PHP/looping code to make it display properly (i've ammended my original post to show an example of what i'd like it to end up looking like)...

 

Thanks!

 

[Daniel0]

Ah ok, but another time, please just include it in a new post.

 

Try this:

<?php
// connect to db here...

$result = mysql_query("SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id");
$categories = array();
if(mysql_num_row($result) > 0)
{
while($document = mysql_fetch_assoc($result))
{
	if(!key_exists($document['cat_id'], $categories)) $categories[$document['cat_id']] = array(); // to prevent E_NOTICE
	$categories[$document['cat_id']][] = $document;
}

foreach($categories as $cat_id => $documents)
{
	echo "<strong>{$documents[0]['cat_name']}</strong><ul>";
	foreach($documents as $document)
	{
		echo "<li>{$document['doc_title']}</li>";
	}
	echo "</ul>";
}
}
else {
echo "No documents...";
}
?>

[ShootingBlanks]

I'm getting the following error:

 

Call to undefined function mysql_num_row()

 

And it refers to this line:

if(mysql_num_row($result) > 0)

 

 

[Daniel0]

Ah, well. It's just a typo. Change it to mysql_num_rows()

[ShootingBlanks]

Ah, well. It's just a typo. Change it to mysql_num_rows()

 

A few things...

 

First off - THANKS!!!  You're a life-saver!

 

Secondly - can you explain what this query means or is pulling? (again - i'm new).  It works, but I just want to learn so I don't have to bother people for stuff like this in the future. :

SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id

 

Thirdly - I didn't totally explain my FULL problem in my initial post, cuz I figured taking "baby steps" would be easier (but I swear this is the end of my issue!)....I now also need to ONLY pull doc_titles that begin with a $_GET['alphaID'] variable (the variable is there - I know how to do that - I just need to pull it into the query)...

 

...I'm sure it's just tacking on "WHERE something = $_GET['alphaID']", but I'm not sure what that something is.  The $_GET['alphaID'] variable is just one capital letter (A-Z), so I don't know if SQL queries search through case-sensitive stuff, or if I'd have to somehow convert the first letter of all the docs to uppercase automatically somehow too?

 

Then the final "glitch" in my quandry is that aside from the A, B, C, D,...Z "alphaID"s, there is also one that is "0-9" (not one for each number - just literally the string "0-9"), and that one should filter any docs that begin with a number...

 

Head spinning yet?  Mine is!  ???

 

[Daniel0]

First off - THANKS!!!  You're a life-saver!

You're welcome.

 

Secondly - can you explain what this query means or is pulling? (again - i'm new).  It works, but I just want to learn so I don't have to bother people for stuff like this in the future. :

SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id

It takes all rows from documents and joins it with categories where they match in cat_id. The thing I like about SQL is that it is so easy to read/understand. For example SELECT * FROM table (means "select everything from table" (or something like that)).

 

Take a look at this: http://dev.mysql.com/doc/refman/5.1/en/join.html

 

Thirdly - I didn't totally explain my FULL problem in my initial post, cuz I figured taking "baby steps" would be easier (but I swear this is the end of my issue!)....I now also need to ONLY pull doc_titles that begin with a $_GET['alphaID'] variable (the variable is there - I know how to do that - I just need to pull it into the query)...

 

...I'm sure it's just tacking on "WHERE something = $_GET['alphaID']", but I'm not sure what that something is.  The $_GET['alphaID'] variable is just one capital letter (A-Z), so I don't know if SQL queries search through case-sensitive stuff, or if I'd have to somehow convert the first letter of all the docs to uppercase automatically somehow too?

 

Then the final "glitch" in my quandry is that aside from the A, B, C, D,...Z "alphaID"s, there is also one that is "0-9" (not one for each number - just literally the string "0-9"), and that one should filter any docs that begin with a number...

Perhaps

SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id WHERE d.doc_title LIKE '{$_GET['alphaID']}%'

?

[ShootingBlanks]

SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id

It takes all rows from documents and joins it with categories where they match in cat_id. The thing I like about SQL is that it is so easy to read/understand. For example SELECT * FROM table (means "select everything from table" (or something like that)).

Okay - that makes sense.  Except for why did you use the dots/periods in "d.*,c.*" at the beginning?  Couldn't you have just used "d *, c*"?..

 

Perhaps

SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id WHERE d.doc_title LIKE '{$_GET['alphaID']}%'

?

That worked fine for the letters, but not for the "0-9".  I have a suggestion in "english" if you could translate it to PHP?  How would you say this?...

Do the original query above, unless $_GET['alphaID'] = '0-9'....then do some other different query that pulls only results where the doc_title begins with a numeric character

 

Would that work, and can it be done?

 

Thanks so much again!!!

 

[Daniel0]

SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id

It takes all rows from documents and joins it with categories where they match in cat_id. The thing I like about SQL is that it is so easy to read/understand. For example SELECT * FROM table (means "select everything from table" (or something like that)).

Okay - that makes sense.  Except for why did you use the dots/periods in "d.*,c.*" at the beginning?  Couldn't you have just used "d *, c*"?..

 

As you see I said documents AS d. It's a way of creating some shortcut inside the query. I use d. to tell that it is in the table d (which I later in the query tell is actually database). I don't know if that's understandable.

 

Perhaps

SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id WHERE d.doc_title LIKE '{$_GET['alphaID']}%'

?

That worked fine for the letters, but not for the "0-9".  I have a suggestion in "english" if you could translate it to PHP?  How would you say this?...

Do the original query above, unless $_GET['alphaID'] = '0-9'....then do some other different query that pulls only results where the doc_title begins with a numeric character

 

Would that work, and can it be done?

 

Hmm... try this

<?php
if($_GET['alphaID'] == '0-9')
{
$query = "SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id WHERE LEFT(d.doc_title,1) IN(0,1,2,3,4,5,6,7,8,9)";
}
else {
$query = "SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id WHERE d.doc_title LIKE '{$_GET['alphaID']}%'";
}
?>

[GingerRobot]

Question 1:

 

The . is used as a separator between the table name and the field name. 'd' is being used an an alias for the documents table (documents AS d), whilst c is being used as an alias for the categories table. The query reads something along the lines of "Select all the fields from the table d, and all the fields from the table c...."

 

As for your second question, i think you'll be wanting regular expressions. Id do something like:

 

<?php
if($_GET['alphaID'] == '0-9')){
$parameter = "REGEXP '^[0-9]'";
}else{
$parameter = "LIKE '{$_GET['alphaID']}%'";
}
$result = mysql_query("SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id WHERE d.doc_title ".$parameter);
?>

 

That way, we only use have the overhead of the regular expression when we need it, otherwise we use the orginal LIKE comparison. It's rather like you english description of the problem.

 

 

Edit: Beaten to it, but thought id post anyway. Didn't want to waste the typing. Also shows an alternative to the IN clause.

[Daniel0]

You could perhaps try

SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id WHERE d.doc_title REGEXP '^[0-9]'

instead (for the first query - the one where _GET['alphaID'] == '0-9').

[ShootingBlanks]

<?php
if($_GET['alphaID'] == '0-9')){
$parameter = "REGEXP '^[0-9]'";
}else{
$parameter = "LIKE '{$_GET['alphaID']}%'";
}
$result = mysql_query("SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id WHERE d.doc_title ".$parameter);
?>

 

First I got an error telling me to take the last ")" out of:

...WHERE d.doc_title ".$parameter);

 

I did that, then I got an error telling me to take the last ")" out of:

if($_GET['alphaID'] == '0-9')){

 

I did that, and now I'm getting the following error (only on the '0-9' page, not on the 'A-Z' pages):

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REGEXP '^[0-9]'' at line 1

 

Any ideas?

 

(so close!!! )

[GingerRobot]

First I got an error telling me to take the last ")" out of:

...WHERE d.doc_title ".$parameter);

 

Well im not entirely sure what thats about. As far as i can see, there is only one closing bracket, which is the one which closes the mysql_query() function.

 

The second one was indeed a typo on my part.

 

As for the mysql error, i cant see why you should be getting one. Unless you're not using the code you posted, and you're using the pre-edited version of my code(when i first posted, i acidently wrote REGEXP twice).

 

Try changing the query to:

 

$sql = "SELECT * FROM documents WHERE doc_title ".$parameter;
$result = mysql_query($sql) or die(mysql_error().'<br />Query:'.$sql);

 

So we can see what the contents of the query is if there is an error.

[ShootingBlanks]

Unless you're not using the code you posted, and you're using the pre-edited version of my code(when i first posted, i acidently wrote REGEXP twice).

That's exactly what I was doing wrong, actually!  ...

 

Okay - there's still one thing that's not working, and I don't get it at all...

 

...I started noticing that the first record will not be displayed.  So, if there should be 15 results, only results 2-15 will show up...

 

...I did some testing, and by adding this code to the top of the body of the page, I found out the the results were THERE (they were being pulled), just not displayed for some reason:

$row_result = mysql_fetch_assoc($result);
echo $row_result['cat_id']."<br />";
echo $row_result['doc_id'];

 

What made me even notice this in the first place is that some pages ("letters", if you will) don't have any docs associated with them.  Like, if someone clicked on "Z", there's no docs that begin with "Z", so they'd get that "there are no documents" message that was caused by the "else" statement in the original set of code...

 

...BUT, if there was only one document (like, if someone clicked "F", and there was only one document that began with "F") there would be no message stating that there were no documents.  It would just be a blank page.  So, basically it's not executing the "else" statement to tell the user there's no docs because there IS a result.  It just isn't displaying for some reason.  With that "test code" I did above, it WILL display the cat_id and doc_id (like I set it up to do), so I'm not sure why it won't export the actual info itself into a properly formatted list.

 

I've noticed this elsewhere too.  Like, I was trying to populate a drop-down menu using all the fields in a column of a database table, and it would only populate the drop-down list with all of the fields EXCEPT for the first one.  This seems like a similar problem, but I just don't get it! ???

 

 

[GingerRobot]

Ok, its probably time we got an updated version of the code you are using. Othewise we might make suggestions on code that is no longer being used. Might be an idea to comment out any of your testing too - so we dont confuse the issue.

[ShootingBlanks]

Ok, its probably time we got an updated version of the code you are using. Othewise we might make suggestions on code that is no longer being used.

 

Good call. 

 

Here:

<?php require_once('../Connections/SalesRepository.php'); ?>
<?php
if($_GET['alphaID'] == '0-9'){
$parameter = "REGEXP '^[0-9]'";
}else{
$parameter = "LIKE '{$_GET['alphaID']}%'";
}

mysql_select_db($database_SalesRepository, $SalesRepository);
$query_result = "SELECT d.*,c.* FROM documents AS d LEFT JOIN categories AS c ON d.cat_id=c.cat_id WHERE d.title_full ".$parameter;
$result = mysql_query($query_result, $SalesRepository) or die(mysql_error());
$row_result = mysql_fetch_assoc($result);
$totalRows_result = mysql_num_rows($result);
$categories = array();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title><?php echo $_GET['alphaID']; ?></title>

<link href="../includes/home.css" rel="stylesheet" type="text/css" />
<link href="../includes/section.css" rel="stylesheet" type="text/css" />
<link href="../includes/alphabetPages.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div id="container">
<div id="header">
	<p id="title">Anixter&#8217;s Sales Process Repository</p>
	<p id="section">Resources beginning with "<?php echo $_GET['alphaID']; ?>"</p>
</div>
<div id="nav"><?php include('../includes/alphabetNav.inc.php'); ?></div>
  <div id="docs">
  <?php

  /* TESTING CODE******************************
  echo $row_result['cat_id']."<br />";
  echo $row_result['doc_id'];
  ******************************************** */

  if($totalRows_result > 0) {
while($document = mysql_fetch_assoc($result)) {
	if(!key_exists($document['cat_id'], $categories)) {
	  $categories[$document['cat_id']] = array(); // to prevent E_NOTICE
	}
	$categories[$document['cat_id']][] = $document;
}
foreach($categories as $cat_id => $documents) {
	echo "<h1>{$documents[0]['cat_name']}</h1>";
	echo "<table width='100%' class='mainTable'>";
	echo "<tr><th>DOCUMENT (<em>viewable online</em>)</th></tr>";
	$counter = 0;
	foreach($documents as $document) {
		echo "<tr";
		if ($counter++ % 2) {
			echo ' class="hilite"';
		}
		echo "><td>{$document['title_full']}</td></tr>";
	}
	echo "</table><br />";
}
}
else { ?>
<p><font color="#006699" face="Arial, Helvetica, sans-serif"><strong>There 
            are no available resources<br>
            that begin with "<?php echo $_GET['alphaID']; ?>" at this time.</strong></font></p>
<p>
  <?php
}
?>
    </p>
<p>| <a href="../index.php" class="backNav">HOME</a> | </p>
  </div>
<div class="spacer"></div>
</div>
</body>
</html>
<?php
mysql_free_result($result);
?>

[GingerRobot]

I thought as much. Line 12 is your problem. Delete this one:

$row_result = mysql_fetch_assoc($result);

 

If you take a look at the manual (it probably explains it better than i can), you will see that by calling the mysql_fetch_assoc function, you move the internal pointer forward. This essentially means that each time you use the function, you work with the next row of data. In your code, you call this prior to your loop. Therefore, when the loop is executed, it starts work on the second row.

 

[ShootingBlanks]

by calling the mysql_fetch_assoc function, you move the internal pointer forward. This essentially means that each time you use the function, you work with the next row of data.

 

AHHHHHH - I see!  Thanks!

 

Is there a way to "reset" the mysql_fetch_assoc function in any way?  Or, once you've called it initially, you've sort of got the ball rolling and there's no way of going back?

 

 

[sasa]

yes use

mysql_data_seek($result, 0);

[ShootingBlanks]

Great - thanks again, everyone!!!