Selecting rows matching certain category.

[dkin]

I am writing a script to display the latest news related to the category the user is viewing, but I do not want to display the headline that he is seeing,

So if the headline is Purple Pumpkins

I want

Red Pumpkins
Yellow Pumpkins
orange Pumpkins
green Pumpkins
blue Pumpkins

not

Purple Pumpkins
Red Pumpkins
Yellow Pumpkins
orange Pumpkins
green Pumpkins

Anyone know how this can be done?

[grandpa]

Assuming that you already know the current category, you could write a query that excluded that category.

$category = 'Purple Pumpkins';
$sql = "SELECT category FROM table WHERE category <> '$category' ";

If you have to select all the categories before you know which one to exclude then you can manually omit that category.

$sql = "SELECT category FROM table";
$result=mysql_query($sql);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
if ($category <> 'Purple Pumpkins')
echo $category;
}

Either way, it sounds like you'll need to be able to know the name of the category to exclude, which might mean 2 queries. The first would retrieve all the categories, the second would retrieve all but the one you want to omit.

[dkin]

I do not want to exclude categories, just the headline that the user is viewing.

This is my query.

$cresult = mysql_query("SELECT * FROM news where category = '$row[category]' order by id desc limit 5"

so I do not know what to add to that to exlude $headline.

[grandpa]

OK, so then the headline is in this result set, yes? And you know what headline is being displayed?

I'm at a loss to explain, partly because I don't know what your script/page is/has done. And partly because I can envision at least 2 options to achieve your goal... neither of which may work depending on everything else. In other words, it's hard to describe ;)

I'm going on the assumption that your user is looking at a page right now, looking at headline #1. And somewhere else on the page you want to show the other 4 headlines.

Based on those assumptions then you will know what headline is being displayed. You could load your headlines into an array, and at the point you want to show the remaining headlines, exlude the current headline.

if (in_array($headline,$array)) {
// do nothing
}
else {
// display the remaining headlines
}

Or, you can run a second query, now that you know the current headline.

$cresult = mysql_query("SELECT * FROM news where category = '$row[category]' && '$row[headline]'!= '$headline' order by id desc limit 5"

[dkin]

I really thought that would have worked but it doesnt seem to have helped. this is my new query.

$cresult = mysql_query("SELECT * FROM news where category = '$row[category]' and title!= '$crow[title]' order by id desc limit 5"

your assumption was correct I gues its just a matter of toning my script, any thoughts?

[grandpa]

Let's back up to your original query.

$cresult = mysql_query("SELECT * FROM news where category = '$row[category]' order by id desc limit 5"
while ($this_row = mysql_fetch_array($cresult, MYSQL_ASSOC)) {
$this_array[] = $this_row[headline];
}

Now you've got a 5 element array containing your headlines. When you are ready to print the related headlines run a short loop.

foreach ($this_array as $key=>$val)
if (!in_array($val,$this_array)) echo $val;

This will probably need some fine tuning, but it should get you close. It's off the top of my head, admittedly not always a safe place. Maybe someone else will drop in a more elegant solution.

[grandpa]

I edited that 3 times, and still knew something was wrong. I believe this is what I was trying to do....

foreach ($this_array as $key=>$val)
if (!in_array($headline,$this_array)) echo $val;

[dkin]

I am not very good with arrays and such, I dont have a clue what to do with that.

Sorry, maybe a lil more help.

[dkin]

I dont mean to sound like a shmo, it was 5 am lol. Would anyone be able to explain wqhat I am supposed to do with the previously posted code?

[grandpa]

Hi dkin,

I was working off the top of my head, not testing. However, I did test this and it works. And, working with arrays is not too difficult. This is a pretty simple example.

To begin I'll populate the array, $this_aray.

$this_array = array(headline1, headline2, headline3, headline4, headline5);

This is essentially what you are doing in this code:

$cresult = mysql_query("SELECT * FROM news where category = '$row[category]' order by id desc limit 5"
while ($this_row = mysql_fetch_array($cresult, MYSQL_ASSOC)) {
$this_array[] = $this_row[headline];
}

Now we have an array of headlines. It's a small array, with just five titles. To see the data in the array you can use this code.
echo "<PRE>";
print_r ($this_array);
echo "</PRE>";

Which gives an output like this:
Array
(
[0] => headline1
[1] => headline2
[2] => headline3
[3] => headline4
[4] => headline5
)

That's helpful to see the values in an array, and to help understand arrays. But it's not very useful for your users. Not yet.

Now you want to print the headlines that are related to the current page. My first example did not work.. sorry. The following example has been tested.

$headline = 'headline3';
foreach ($this_array as $key=>$val) {
if ($val!= $headline) echo "<br>$val";
}

This results will be printed:
headline1
headline2
headline4
headline5

Where to place the code? You'll need to populate the variable $headline with the current headline. Then put the foreach loop where ever you want to display the related headlines.

Why am I going on about arrays? Because your other option is to perform a second query. The array will reduce your overhead. Admittedly, for such a small dataset it doesn't represent much savings. But someday you may want to display 10,000 related headlines. Also, if you've ever encountered a max_questions limit in MySQL you'll know that there is a limit to the number of queries that you can send to MySQL within a given time frame. The array helps reduce the number of queries. And, it's faster.