Recordset, and
having a field record that increments for each entry, how do
I have it list,
say, every 5th record?
Here's what I have (obviously this is listing any one where
there's a '5' in
the record number):
SELECT job_title, city
FROM jobs
WHERE record LIKE '%5'
ORDER BY job_title ASC
Thanks!
Mad Dog
Recordset - list every 5th record
Why do you want to display every 5th record? Maybe there is a
better way
to accomplish what you are trying to do. You just want random
records,
or there is something magic about every 5th one?
--
Alec Fehl, MCSE, A+, ACE, ACI
Adobe Community Expert
AUTHOR:
Microsoft Office 2007 PowerPoint: Comprehensive Course
(Labyrinth
Publications)
Welcome to Web Design and HTML (Labyrinth Publications)
CO-AUTHOR:
Microsoft Office 2007: Essentials (Labyrinth Publications)
Computer Concepts and Vista (Labyrinth Publications)
Mike Meyers' A+ Guide to Managing and Troubleshooting PCs
(McGraw-Hill)
Internet Systems and Applications (EMC Paradigm)
Recordset - list every 5th record
Alec Fehl wrote:
%26gt; Why do you want to display every 5th record? Maybe there
is a better
%26gt; way to accomplish what you are trying to do. You just
want random
%26gt; records, or there is something magic about every 5th
one?
It's a list of available jobs and I want to list a sampling
of them in
alphabetical order. Listing every fifth would probably
generally accomplish
this though I open to other SIMPLE ideas.
Thanks!
Ah. So you had WHERE Record LIKE '%5'. What data does the
Record field
hold? Is that a numerical primary key?
--
Alec Fehl, MCSE, A+, ACE, ACI
Adobe Community Expert
AUTHOR:
Microsoft Office 2007 PowerPoint: Comprehensive Course
(Labyrinth
Publications)
Welcome to Web Design and HTML (Labyrinth Publications)
CO-AUTHOR:
Microsoft Office 2007: Essentials (Labyrinth Publications)
Computer Concepts and Vista (Labyrinth Publications)
Mike Meyers' A+ Guide to Managing and Troubleshooting PCs
(McGraw-Hill)
Internet Systems and Applications (EMC Paradigm)
Alec Fehl wrote:
%26gt; Ah. So you had WHERE Record LIKE '%5'. What data does
the Record field
%26gt; hold? Is that a numerical primary key?
Integer. auto_increment. Unique index
Then you are correct - this should work just fine:
SELECT job_title, city
FROM jobs
WHERE record LIKE '%5'
ORDER BY job_title ASC
Assuming all fields/tables are spelled correctly.
--
Alec Fehl, MCSE, A+, ACE, ACI
Adobe Community Expert
AUTHOR:
Microsoft Office 2007 PowerPoint: Comprehensive Course
(Labyrinth
Publications)
Welcome to Web Design and HTML (Labyrinth Publications)
CO-AUTHOR:
Microsoft Office 2007: Essentials (Labyrinth Publications)
Computer Concepts and Vista (Labyrinth Publications)
Mike Meyers' A+ Guide to Managing and Troubleshooting PCs
(McGraw-Hill)
Internet Systems and Applications (EMC Paradigm)
Alec Fehl wrote:
%26gt; Then you are correct - this should work just fine:
%26gt;
%26gt; SELECT job_title, city
%26gt; FROM jobs
%26gt; WHERE record LIKE '%5'
%26gt; ORDER BY job_title ASC
%26gt;
%26gt; Assuming all fields/tables are spelled correctly.
But won't that show records 5, 15, 25, 35, 45, 50, 55, etc?
That's not every fifth record, it's records with a five in
the number. What
I'd prefer is for it to show records 5, 10, 15, 20, 25, etc
MD
How about record #10?
--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com
- Template Triage!
http://www.projectseven.com/go
- DW FAQs, Tutorials %26amp; Resources
http://www.dwfaq.com - DW FAQs,
Tutorials %26amp; Resources
http://www.macromedia.com/support/search/
- Macromedia (MM) Technotes
==================
''Alec Fehl'' %26lt;jacalart@hotmail.com%26gt; wrote in message
news:f205v8$htp$1@forums.macromedia.com...
%26gt; Then you are correct - this should work just fine:
%26gt;
%26gt; SELECT job_title, city
%26gt; FROM jobs
%26gt; WHERE record LIKE '%5'
%26gt; ORDER BY job_title ASC
%26gt;
%26gt; Assuming all fields/tables are spelled correctly.
%26gt;
%26gt;
%26gt; --
%26gt; Alec Fehl, MCSE, A+, ACE, ACI
%26gt; Adobe Community Expert
%26gt;
%26gt; AUTHOR:
%26gt; Microsoft Office 2007 PowerPoint: Comprehensive Course
(Labyrinth
%26gt; Publications)
%26gt; Welcome to Web Design and HTML (Labyrinth Publications)
%26gt;
%26gt; CO-AUTHOR:
%26gt; Microsoft Office 2007: Essentials (Labyrinth
Publications)
%26gt; Computer Concepts and Vista (Labyrinth Publications)
%26gt; Mike Meyers' A+ Guide to Managing and Troubleshooting
PCs (McGraw-Hill)
%26gt; Internet Systems and Applications (EMC Paradigm)
Have any suggestions, Murray? Obviously I'm not exactly Mr.
PHP.
Murray *ACE* wrote:
%26gt; How about record #10?
%26gt;
%26gt;
%26gt; ''Alec Fehl'' %26lt;jacalart@hotmail.com%26gt; wrote in
message
%26gt; news:f205v8$htp$1@forums.macromedia.com...
%26gt;%26gt; Then you are correct - this should work just fine:
%26gt;%26gt;
%26gt;%26gt; SELECT job_title, city
%26gt;%26gt; FROM jobs
%26gt;%26gt; WHERE record LIKE '%5'
%26gt;%26gt; ORDER BY job_title ASC
%26gt;%26gt;
%26gt;%26gt; Assuming all fields/tables are spelled correctly.
%26gt;%26gt;
%26gt;%26gt;
%26gt;%26gt; --
%26gt;%26gt; Alec Fehl, MCSE, A+, ACE, ACI
%26gt;%26gt; Adobe Community Expert
%26gt;%26gt;
%26gt;%26gt; AUTHOR:
%26gt;%26gt; Microsoft Office 2007 PowerPoint: Comprehensive
Course (Labyrinth
%26gt;%26gt; Publications)
%26gt;%26gt; Welcome to Web Design and HTML (Labyrinth
Publications)
%26gt;%26gt;
%26gt;%26gt; CO-AUTHOR:
%26gt;%26gt; Microsoft Office 2007: Essentials (Labyrinth
Publications)
%26gt;%26gt; Computer Concepts and Vista (Labyrinth Publications)
%26gt;%26gt; Mike Meyers' A+ Guide to Managing and
Troubleshooting PCs
%26gt;%26gt; (McGraw-Hill) Internet Systems and Applications (EMC
Paradigm)
guess you can combine both variants this way:
WHERE record LIKE '%5' OR record LIKE '%0'
That'd do it.
--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com
- Template Triage!
http://www.projectseven.com/go
- DW FAQs, Tutorials %26amp; Resources
http://www.dwfaq.com - DW FAQs,
Tutorials %26amp; Resources
http://www.macromedia.com/support/search/
- Macromedia (MM) Technotes
==================
''geschenk'' %26lt;webforumsuser@macromedia.com%26gt; wrote in
message
news:f20779$jee$1@forums.macromedia.com...
%26gt; guess you can combine both variants this way:
%26gt;
%26gt; WHERE record LIKE '%5' OR record LIKE '%0'
Duh! Stupid math. Thinks it's so big! You're right - that's
just numbers
that end with 5. How about this:
SELECT job_title, city FROM jobs WHERE mod(record,5)=0 ORDER
BY
job_title ASC
Modular division. mod(record,5) means divide the value of the
field
'record' by 5 and return the remainder. So, if the remainder
is 0, the
number is evenly divisible by 5.
--
Alec Fehl, MCSE, A+, ACE, ACI
Adobe Community Expert
AUTHOR:
Microsoft Office 2007 PowerPoint: Comprehensive Course
(Labyrinth
Publications)
Welcome to Web Design and HTML (Labyrinth Publications)
CO-AUTHOR:
Microsoft Office 2007: Essentials (Labyrinth Publications)
Computer Concepts and Vista (Labyrinth Publications)
Mike Meyers' A+ Guide to Managing and Troubleshooting PCs
(McGraw-Hill)
Internet Systems and Applications (EMC Paradigm)
geschenk wrote:
%26gt; guess you can combine both variants this way:
%26gt;
%26gt; WHERE record LIKE '%5' OR record LIKE '%0'
That does what I wanted, but because of the database it's not
coming out as
hoped. Hmmmm.....how to get a ''randomized'' sample......
Alec Fehl wrote:
%26gt; Duh! Stupid math. Thinks it's so big! You're right -
that's just
%26gt; numbers that end with 5. How about this:
%26gt;
%26gt; SELECT job_title, city FROM jobs WHERE mod(record,5)=0
ORDER BY
%26gt; job_title ASC
%26gt;
%26gt; Modular division. mod(record,5) means divide the value
of the field
%26gt; 'record' by 5 and return the remainder. So, if the
remainder is 0, the
%26gt; number is evenly divisible by 5.
Thanks. As I mentioned in my other response, it's doing what
it should be
the results aren't turning out as I'd like. I almost wish I
could have it
grab, say, five different listings with no two for the same
job_title.
Any thoughts without my getting into a complicated mess?
MD
On 10 May 2007 in macromedia.dreamweaver, Mad Dog wrote:
%26gt; Have any suggestions, Murray? Obviously I'm not exactly
Mr. PHP.
I'm toying around with this a bit. Using a subquery to get
database
size should work, but MySQL 4.1.something is choking on it:
SELECT `fieldname` FROM `myDatabase` ORDER BY RAND()
LIMIT (SELECT ROUND(COUNT(`fieldname`)/5) AS totalCount FROM
`myDatabase`);
You could do it in two queries:
- first one to get the total size of the database
- second one to select 20% of the records, randomly ordered
Implemented on my test server using standard DW behaviors:
%26lt;?php
// Get the count of the recordset
mysql_select_db($database_test, $test);
$query_getCount = ''SELECT COUNT(images.imagename) as myCount
FROM images'';
$getCount = mysql_query($query_getCount, $test) or
die(mysql_error());
$row_getCount = mysql_fetch_assoc($getCount);
$totalRows_getCount = mysql_num_rows($getCount);
// Set the sample size to 1/5 of the total records
// This is the only nonstandard part
// Change '5' to whatever you want to cut down on records
$recordsetSize = round($row_getCount['myCount']/5);
// Default recoredset size = 20
$upperLimit_randomSample = ''20'';
if (isset($recordsetSize)) {
$upperLimit_randomSample = (get_magic_quotes_gpc()) ?
$recordsetSize : addslashes($recordsetSize);
}
mysql_select_db($database_test, $test);
$query_randomSample = sprintf(''SELECT images.imagename FROM
images ORDER BY RAND() LIMIT %s'', $upperLimit_randomSample);
$randomSample = mysql_query($query_randomSample, $test) or
die(mysql_error());
$row_randomSample = mysql_fetch_assoc($randomSample);
$totalRows_randomSample = mysql_num_rows($randomSample);
?%26gt;
--
Joe Makowiec
http://makowiec.net/
Email:
http://makowiec.net/contact.php
No comments:
Post a Comment