distinct field in foreach statement in while statement?

I have some code that works.

while ($row = mysql_fetch_assoc($result))
{
foreach
echo “<a target=”_blank" href=“download.php?download_file=”.$row[‘obname’].""> “.$row[‘title’].”   “.$row[‘filesize’].” mb  “.$row[‘obtype’].”  “.$row[‘reference’].”
";
}

But the end results looks like:
Title, File Size, Object Type, Reference

01 Summer 30mb Audio Supernatural Series
02 Cool Nights 28mb Audio Supernatural Series
03 Hot Days 18mb Audio Supernatural Series
Watching Waiting 18mb Audio
01 Funny 31mb Audio My Thoughts
02 Hard Times 25mb Audio My Thoughts
Waiting for Somethin 40mb Audio

What I want to do is foreach “Reference” do one thing and then list everything else together such as.

Supernatural Series
Audio
01 Summer 30mb
02 Cool Nights 28mb
03 Hot Days 18mb

My Thoughts
Audio
01 Funny 31mb
02 Hard Times 25mb

Watching Waiting 18mb Audio
Waiting for Somethin 40mb Audio

Not sure how to do this…fairly new at this.

in your query i assume you’re doing something like this:

SELECT * FROM table WHERE x = y 

Just add a group by clause:

SELECT * FROM table WHERE x = y GROUP BY z

Hope that helps,
Red :wink:

$query = “select * from nl_audio where author = ‘ed’”;
$result = mysql_query($query);

	while ($row = mysql_fetch_assoc($result))
	{
		foreach 
		echo "<a target=\"_blank\" href=\"download.php?download_file=".$row['obname']."\">&nbsp;".$row['title']."</a> &nbsp;&nbsp;".$row['filesize']."&nbsp;mb&nbsp;&nbsp;".$row['obtype']."&nbsp;&nbsp;".$row['reference']."<br />";
	}

I’m already doing a where author = ed
If i do a where reference = supernatural series I will miss my other series and the stuff not in a series.
Not to mention I might get some of Debbie’s stuff.
I’m still confussed on how to break things down and captions series and then do the other stuff differently.

Substitue xxxxx for ‘Supernatural Series’ or ‘My Thoughts’…
You get the idea…
[php]$query = “select * from nl_audio where author = ‘ed’ GROUP BY xxxxx”; [/php]

Honestly, i won’t write any code for you while you’re using mysql - That’s not being unkind, on the contrary… You are using outdated ancient code!!

Switch to Mysqli or PDO, then we can proceed…
Red :wink:

Thanks Redscouse but I’m not asking you two write the code for me…i’m trying to learn here.
However you missed my last line of my first post…
Not sure how to do this…fairly new at this.

I don’t know what you are talkiing about…
mysqli, PDO, mysql
I just found some code that looked like it would work for me and I modified it to use my fields.

$query = "select * from nl_audio where author = ‘ed’ group by ‘reference’:
would group all the objects correctly but how would I prestent them?

would I do an (not code just logic of code statement)
if distinct ‘reference’ {
echo "reference
obtype
foreach
echo "obname fileSize
}

elseif reference blank
foreach
echo
name type size

is that the correct logic? the right direction?

Firstly convert your query to either PDO or MySQLi, if you run into errors, post back. I’ve noticed a few people are taking the stance of being unwilling to help with outdated deprecated junk code. If you update your queries, I’m sure we would be able to help further.

Ed, I sense I may have offended you slightly, being mean wasn’t my intentions at all, once you have changed over i’ll be only too happy to help.
It would be really really irresponsible of me to write any code using mysql drivers as it is seriously flawed and leaves your code open to serious problems to someone who likes to break things…

You wrote this:

That pretty much sums it up.

Hope you get it sorted as it really peeves me not to be able to help…

Red :wink:

“there are no such things as bad students - only bad teachers!”

I would update but I don’t know what you all are talking about.
I believe this code was written by Dreamweaver CS4…do I need to update to CS5?

Dreamweaver is just the editor used to write the code, it has nothing to do with how the end product functions.
(well it can, but that’s a whole other story…)

Have a read here, don’t just skim, read.
Don’t forget to click the three links halfway down the page and read them too.

Red :wink:

Quick tip, bin Dreamweaver!

So I changed

$db = mysql_connect(‘my server’,‘user name’,‘password’) or die(“Database error”);
mysql_select_db(‘nlchurch’, $db);

$query = “select * from nl_audio where author = ‘ed’”;
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result))
{
echo “<a target=”_blank" href=“download.php?download_file=”.$row[‘obname’].""> “.$row[‘title’].”   “.$row[‘filesize’].” mb  “.$row[‘obtype’].”  “.$row[‘reference’].”
";
}

to

$mysqli = new mysqli(“my server”,“user name”,“password”, “nlchurch”);
$result = $mysqli-
>query(“SELECT * AS _message FROM nl_audio WHERE author = ‘ed’”);
$row = $result->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row[’_message’]);

Been playing with it for two days…can kinda follow the logic of the first one but have no clue with the second, and it still doesn’t work.

Ed, First and foremost I applaud you for making the effort to change over - you don’t know it yet but you really have done such a simple, yet essential switch - the guys who don’t, well they’ll learn the hard way pretty soon.
Well done!! :smiley: ;D 8) :wink:

Now, back to the task at hand, mysqli is my cup of tea so i’m going to show you how to do your query and parametrised querys. It can quite easily be changed over to PDO with very little effort as i’m sure one of the PDO guys will chip in.

[php]

<?php $mysqli = new mysqli("my server","user name","password", "nlchurch"); // for your query it's quite ok to do it like you have here. // obviously you can still do htmlentities or some kind of security. $result = $mysqli->query("SELECT * AS _message FROM nl_audio WHERE author = 'ed'"); if($row = $result->fetch_assoc()) { foreach($row as $k => $v) { $row[$k] = stripslashes($v); // this *should* be made more secure!! } } echo $row['_message']; [/php] However, take a look at this: [php] // for a query that takes user input you need to parameterise the query, // so let me break it down using your own query.. // I'm going to pretend 'ed' is a string, taken from user input. // pretend input $ed = 'ed'; // lets create and prepare the query (notice i have called specific table names instead of using * ) $query = "SELECT table1, table2 AS _message FROM nl_audio WHERE author = ?"; if($stmt = $mysqli->prepare($query)) { // always check the query before proceeding.. if($stmt->bind_param("s", $ed)) { // we are binding a string 's' that is the value of $ed $stmt->execute(); // if it binded ok, execute the query. $stmt->store_result(); // store the result so we can loop through it. if($stmt->num_rows > 0) { // if there were more than zero rows.. $stmt->bind_result($table1, $table2); // bind the results to variables. $stmt->fetch(); // in a fetch (array) } $stmt->close(); // now we're done, close the $stmt } else { echo $mysqli->error; // if the bind params failed.. } } else { echo $mysqli->error; // if the prepare failed.. } echo $table1; echo $table2; // etc.. ?>

[/php]

It really is simple when you get the hang of it and will leave your scripts much more secure than before.

Hope that helps,
Red :wink:

Your second code looks really cool but I can’t begin to follow it.
I can mostly follow the first code…but it gets an error on this line

if($row = $result->fetch_assoc()) {

the error says… Fatal error: Call to a member function fetch_assoc() on a non-object

try this: (obviously you would handle the error more gracefully in a live environment :wink: )
[php]$result = $mysqli->query(“SELECT * AS _message FROM nl_audio WHERE author = ‘ed’”)
or die($mysqli->error);[/php]

see what it reports back…

Now I’m getting this error

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 ‘AS _message FROM nl_audio WHERE author = ‘ed’’ at line 1

I assume this is because we are using an old server version…a version that Mysqli and PDO does not work on?
this I will not be able to change.

remove the ‘As _message’ bit from the query and only select one table row (instead of *) see if that runs ok…

Sponsor our Newsletter | Privacy Policy | Terms of Service