...

View Full Version : Resolved How to display NEW info from database table?



<?php???>
01-22-2012, 05:34 AM
I am writing a query to display the last 5 results that were entered into one of my tables. The problem is, I only want to display them only if they are new (don't exist). I am stumped and cannot think it through. Here is the code for the query


$query = "SELECT DISTINCT `name` FROM `title` ORDER BY `titleID` DESC LIMIT 5";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
echo '<a href="home.php">'.$row['name']. '</a><br>';
}

Im not sure what to do next. The only thing this reminds me of is searching when a user inputs a piece of data into the table, but since this is just grabbing data that exists, I am confused

felgall
01-22-2012, 06:28 AM
That code will display the last five entries from the table (or all if there are less than 5) all as links to home.php

If the entries don't exist then of course the query will be unable to return them.

Perhaps what you need to determine if they are new is to store the date and time with each entry and add a WHERE clause that tests for less than however old they are allowed to be and still be considered new.

If you have some other criteria for what "new" means then you'll need to be a bit more specific (what is it that doesn't exist for new entries?)

<?php???>
01-22-2012, 06:54 AM
That code will display the last five entries from the table (or all if there are less than 5) all as links to home.php

If the entries don't exist then of course the query will be unable to return them.

Perhaps what you need to determine if they are new is to store the date and time with each entry and add a WHERE clause that tests for less than however old they are allowed to be and still be considered new.

If you have some other criteria for what "new" means then you'll need to be a bit more specific (what is it that doesn't exist for new entries?)

Yes I explained it wrong, the more I think about it the more I think am asking the wrong question.

As of now, any user can add a title, stored in the title table. Since each user can have a title with the same name, the title table will have duplicates. DISTINCT makes sure it doesn't show the same name, BUT say someone just submitted a title, I want to know if that is new or already exists. If it is new, I want it to be displayed. If it already exists, I don't want it to. Basically what I want to accomplish is show the most recent NEW titles.

I know I am explaining it terribly so i will give an example: Say this is the current list:

Phones
Computers
Baseball
Snakes
Bears

Now if someone submits a list titled "Computers", it would go back to the top of the list. That is what I want to avoid. If its already in the database, I want it to be left out. But if Someone submits a list 'Hats' AND is not in the database, I want it on the list.

I hope that helps.

Old Pedant
01-22-2012, 07:03 AM
Well, once the data s in the table, the only way to distiguish new from old would be a timestamp of some kind.

You *could* exclude any titles that occur more than once, though. Is that what you want?



SELECT name, COUNT(*) AS howMany
FROM title
GROUP BY name
HAVING howMany = 1
ORDER BY titleID DESC
LIMIT 5

<?php???>
01-23-2012, 07:33 PM
Well, once the data s in the table, the only way to distiguish new from old would be a timestamp of some kind.

You *could* exclude any titles that occur more than once, though. Is that what you want?



SELECT name, COUNT(*) AS howMany
FROM title
GROUP BY name
HAVING howMany = 1
ORDER BY titleID DESC
LIMIT 5


This query is getting no rows back, maybe I am doing something wrong. But excluding titles that occur more than once won't help me I don't think because it it will still pull up the first item, THEN exclude the rest where I want to ONLY show it IF there is no another item that matches it.

Old Pedant
01-23-2012, 11:16 PM
That query should have done just what you said.

But there's an easy way to find out what the situation really is.

Do this:


SELECT name, COUNT(*) AS howMany
FROM title
GROUP BY name
ORDER BY howMany ASC

Do that in a query tool, not in PHP code.

See what that shows you.

If you have any names that only occur once, they will show up first in the list.

But if the first record you see from that query shows a howMany value that is greater than 1, then you simply don't have any names that qualify: Every name has two or more matches.

<?php???>
01-24-2012, 02:07 AM
That query should have done just what you said.

But there's an easy way to find out what the situation really is.

Do this:


SELECT name, COUNT(*) AS howMany
FROM title
GROUP BY name
ORDER BY howMany ASC

Do that in a query tool, not in PHP code.

See what that shows you.

If you have any names that only occur once, they will show up first in the list.

But if the first record you see from that query shows a howMany value that is greater than 1, then you simply don't have any names that qualify: Every name has two or more matches.

Two problems i see with this ( if I am understanding it correctly): it is not ordering by 'howMany'. When I do that query, it shows how many but the second row has a howMany value of 3. Also, I feel like even if it did return correctly, it would give the value of the new one a 1, which would put it behind the other "1"'s. So I guess that would be a good time to add the time stamp?


EDIT: Wait I'm sorry, I think in theory that should work and I wouldn't need a time stamp, because it will get a 1 when it comes in and there are no others...so if I order it by DESC then it will be the only 1 there. Now I just have to figure out why it is not ordering by howMany.

Old Pedant
01-24-2012, 02:17 AM
Once again, read what *YOU* wrote:


I want to ONLY show it IF there is no another item that matches it.

The *ONLY* way for that to happen is if a given name occurs ONE TIME AND ONE TIME ONLY in your table.

As soon as a name has a count of 2, it does *NOT* fulfill the qualifications that you yourself set forth.

So if you don't see a name that has a howMany field value of 1, then you have NO NAMES AT ALL in that table that meet your "IF there is no another item that matches it" criterion.

Old Pedant
01-24-2012, 02:20 AM
Now I just have to figure out why it is not ordering by howMany.

And I contend that it *IS* doing so. I contend that you simply do not have even one single name in the table that only occurs once in the table.

But there's another easy way to find out: If you think there is a name that occurs only once, then just do this:


SELECT * FROM title WHERE name = 'xxxx'

Just replace the xxxx with the name you expect to find only once.

If you get back only one record from that SELECT, then you are right and I am wrong.

But I'll bet a latte that I am right.

<?php???>
01-24-2012, 03:01 AM
And I contend that it *IS* doing so. I contend that you simply do not have even one single name in the table that only occurs once in the table.

But there's another easy way to find out: If you think there is a name that occurs only once, then just do this:


SELECT * FROM title WHERE name = 'xxxx'

Just replace the xxxx with the name you expect to find only once.

If you get back only one record from that SELECT, then you are right and I am wrong.

But I'll bet a latte that I am right.

:/ I do though. "poptarts" only comes up once. You are a funny guy, and a coding genius, but I am bad at explaining myself. I modified the code a little to get it to work the way I *said* I wanted it to work (edit-I thought I modified it but its the same code you used in the fist place, I think I messed up with the ('')'s)


SELECT `name`, COUNT(*) AS 'howMany'
FROM `title`
GROUP BY `name`
HAVING 'howMany' = 1
ORDER BY `titleID` DESC
LIMIT 5

So this is where I have to feel like an idiot: As soon as someone enters the same title, it will get a 2 and disappear from the list (which it does what its supposed to do)...BUT what I want is for the newer(and unique) titles to be on top until its replaces by a newer one.

So say new and unique 5 look like this:

1.poptarts
2.soda
3.xxxx
4.yyyy
5.xxxxx

because soda is on the list, I dont want it to disappear when someone submits a list called soda, i want it to remain until a new unique title replaces it. My apologies for all of this...

Old Pedant
01-24-2012, 03:21 AM
See, that's what I thought you probably wanted all along, but you kept using words that convinced me I was wrong.

One last question: Say that "soda" is in the list. When somebody adds "soda" again, does that bump "soda" to the top of the list (that is, do we used the latest time added as the "TOP 5" qualifier) or do we ignore the latest time added and only use the FIRST time added?

Let's keep it simple. Say we only wan the top 2.

Say we have these names with the "whenAdded" dates shown:


apple 1/1/2012
banana 1/2/2012
canteloupe 1/3/2012

TO show the top 2 we would show:


canteloupe 1/3/2012
banana 1/2/2012

Okay so now somebody comes along and adds another "apple", so the full list is this:


apple 1/1/2012
banana 1/2/2012
canteloupe 1/3/2012
apple 1/4/2012


*NOW* what should we show for the top 2???

Should it still be


canteloupe 1/3/2012
banana 1/2/2012

or should it change to


apple 1/4/2012
canteloupe 1/3/2012


???

Answer that, and I think we can finally start over and write the correct query.

NOTE: If you have an AUTO_INCREMENT field on this table, you don't need a TIMESTAMP field.

If you have a TIMESTAMP (whenAdded) field, you don't need the AUTO_INCREMENT field (but it can't hurt to have both...but you must have one of the other).

<?php???>
01-24-2012, 03:30 AM
See, that's what I thought you probably wanted all along, but you kept using words that convinced me I was wrong.

One last question: Say that "soda" is in the list. When somebody adds "soda" again, does that bump "soda" to the top of the list (that is, do we used the latest time added as the "TOP 5" qualifier) or do we ignore the latest time added and only use the FIRST time added?

Let's keep it simple. Say we only wan the top 2.

Say we have these names with the "whenAdded" dates shown:


apple 1/1/2012
banana 1/2/2012
canteloupe 1/3/2012

TO show the top 2 we would show:


canteloupe 1/3/2012
banana 1/2/2012

Okay so now somebody comes along and adds another "apple", so the full list is this:


apple 1/1/2012
banana 1/2/2012
canteloupe 1/3/2012
apple 1/4/2012


*NOW* what should we show for the top 2???

Should it still be


canteloupe 1/3/2012
banana 1/2/2012

or should it change to


apple 1/4/2012
canteloupe 1/3/2012


???

Answer that, and I think we can finally start over and write the correct query.

NOTE: If you have an AUTO_INCREMENT field on this table, you don't need a TIMESTAMP field.

If you have a TIMESTAMP (whenAdded) field, you don't need the AUTO_INCREMENT field (but it can't hurt to have both...but you must have one of the other).

Okay, I want the first case to be true, where the apple added on 1/4/2012 does not show, only the first instance of it(which would show at #3 if we didnt limit it to 2). And titleID is the primary key so I dont think we should need the TIMESTAMP...but you are right it would be better to have one anyway.

Old Pedant
01-24-2012, 03:46 AM
No need for the TIMESTAMP, really.

Okay this is easy:



SELECT name, MIN(titleID) AS firstOccurrence
FROM title
GROUP BY name
ORDER BY firstOccurrence DESC'
LIMIT 5

You see it? By picking the first auto_increment value for each name, using MIN(), we are effectively ignoring all later additions with that same name.

Presto.

<?php???>
01-24-2012, 04:03 AM
No need for the TIMESTAMP, really.

Okay this is easy:



SELECT name, MIN(titleID) AS firstOccurrence
FROM title
GROUP BY name
ORDER BY firstOccurrence DESC'
LIMIT 5

You see it? By picking the first auto_increment value for each name, using MIN(), we are effectively ignoring all later additions with that same name.

Presto.

Its depressing thinking about how much I need to learn. But thank you for your help once again! I am going to be re-examining my database structure soon if I have any questions I'm sure it will be a little more challenging for you :p



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum