Help needed - display basic and enhanced listings from database

Report · Feb 27, 2009

Hi - a bit of a newbie question so bear with me. The project is a fairly simple online product directory. I have a MySQL database and have set up two pages - search.php and results.php. So far so good. The db table holds a number of listings, some of which are basic (free) and some of which are enhanced (paid-for). The idea is to be able to search so that when you enter (say) chocolate, it will return all the matches for that category, but the enhanced listings will appear in a coloured box at the top of the page, with the basic listings displayed beneath in a plainer style. The enhanced listings have a product description in the db table, the basic listings don't. In a sense what I'm looking for is the sort of thing you get when you Google - sponsored links at the top, the rest below. I have no idea how to achieve this but assume that something along the lines of two recordsets need to be attached to the results page, but don't know how it will work in practice. If anyone can point me in the right direction I'd really appreciate it, especially in a non-techie way! thanks in advance for any input,
regards,
Jeff

Report · Feb 27, 2009

There are any number of ways you can do this. Two recordsets is one way,
each unloading within a repeat region placed as needed for proper screen
display. Another way would use a single recordset, but reset to the first
record after the first repeat region so that the second one will scan
through the recordset again from the start. To reset the recordset, you
would use the mysql_data_seek ( resource $result , int $row_number )
function, e.g.,

mysql_data_seek ( $rsWhatever, 0 )

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"jeffmg" <webforumsuser@macromedia.com> wrote in message
news:go8k7f$avt$1@forums.macromedia.com...
> Hi - a bit of a newbie question so bear with me. The project is a fairly
> simple
> online product directory. I have a MySQL database and have set up two
> pages -
> search.php and results.php. So far so good. The db table holds a number of
> listings, some of which are basic (free) and some of which are enhanced
> (paid-for). The idea is to be able to search so that when you enter (say)
> chocolate, it will return all the matches for that category, but the
> enhanced
> listings will appear in a coloured box at the top of the page, with the
> basic
> listings displayed beneath in a plainer style. The enhanced listings have
> a
> product description in the db table, the basic listings don't. In a sense
> what
> I'm looking for is the sort of thing you get when you Google - sponsored
> links
> at the top, the rest below. I have no idea how to achieve this but assume
> that
> something along the lines of two recordsets need to be attached to the
> results
> page, but don't know how it will work in practice. If anyone can point me
> in
> the right direction I'd really appreciate it, especially in a non-techie
> way!
> thanks in advance for any input,
> regards,
> Jeff
>

Report · Feb 27, 2009

On 27 Feb 2009 in macromedia.dreamweaver.appdev, jeffmg wrote:

> Hi - a bit of a newbie question so bear with me. The project is a
> fairly simple online product directory. I have a MySQL database and
> have set up two pages - search.php and results.php. So far so good.
> The db table holds a number of listings, some of which are basic
> (free) and some of which are enhanced (paid-for). The idea is to be
> able to search so that when you enter (say) chocolate, it will
> return all the matches for that category, but the enhanced listings
> will appear in a coloured box at the top of the page, with the basic
> listings displayed beneath in a plainer style. The enhanced listings
> have a product description in the db table, the basic listings
> don't. In a sense what I'm looking for is the sort of thing you get
> when you Google - sponsored links at the top, the rest below. I have
> no idea how to achieve this but assume that something along the
> lines of two recordsets need to be attached to the results page, but
> don't know how it will work in practice. If anyone can point me in
> the right direction I'd really appreciate it, especially in a
> non-techie way! thanks in advance for any input,

How do you differentiate the records in the database?

A recordset something like this should work:

SELECT field1, field2, description, (LENGTH(description) > 0) AS
enhanced
FROM myData
ORDER BY enhanced DESC

"(LENGTH(description) > 0) AS enhanced" returnes another column,
enhanced, which will be either 1 or 0. Then in your repeat region, you
can either set a class on every row with enhanced = 1, or you can check
for where enhanced changes from 1 to 0 and throw another header and
change styles.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Feb 27, 2009

Thanks for responses - to answer Joe's question, the records are differentiated in the database only by the fact that some have an entry in the description field, some don't. I'm not sure what I would need to do to amend the table so that the entries with a description display first.
At the moment there are 16 fields - id, contact, company, address1, address2, town, county, postcode, country, telephone, fax, email, web, service, products, description - and the only differentiation is that some entries have something in the description field (enhanced) some don't (basic) - should I have another column of some sort which designates which is enhanced and which isn't by using a 1 or 0, or have I misread the post below?
I've done searches previously where I've used a SQL statement like "SELECT DISTINCT product from category ORDER BY companyname ASC" so that products will display by company name in ascending alphabetical order, but when I've tried to add an ORDER BY to the recordset I've got for this search, it just returns blank records.
I'm looking for the simplest solution so if Gary's solution can be made to work somehow that would be good - the least technical route the better - thanks for suggestions and help.
Jeff

Report · Feb 28, 2009

> I'm not sure what I would need to do to amend the table so
> that the entries with a description display first.

Nothing. Just sort the recordset by that field (in the SELECT statement) so
that those records rise to the top of the recordset.

> but when I've tried to
> add an ORDER BY to the recordset I've got for this search, it just returns
> blank records.

We would need to see this SELECT statement.

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"jeffmg" <webforumsuser@macromedia.com> wrote in message
news:goaoh2$1b1$1@forums.macromedia.com...
> Thanks for responses - to answer Joe's question, the records are
> differentiated
> in the database only by the fact that some have an entry in the
> description
> field, some don't. I'm not sure what I would need to do to amend the table
> so
> that the entries with a description display first.
> At the moment there are 16 fields - id, contact, company, address1,
> address2,
> town, county, postcode, country, telephone, fax, email, web, service,
> products,
> description - and the only differentiation is that some entries have
> something
> in the description field (enhanced) some don't (basic) - should I have
> another
> column of some sort which designates which is enhanced and which isn't by
> using
> a 1 or 0, or have I misread the post below?
> I've done searches previously where I've used a SQL statement like
> "SELECT
> DISTINCT product from category ORDER BY companyname ASC" so that products
> will
> display by company name in ascending alphabetical order, > I'm looking for
> the simplest solution so if Gary's solution can be made to
> work somehow that would be good - the least technical route the better -
> thanks
> for suggestions and help.
> Jeff
>

Report · Feb 28, 2009

On 28 Feb 2009 in macromedia.dreamweaver.appdev, jeffmg wrote:

> Thanks for responses - to answer Joe's question, the records are
> differentiated in the database only by the fact that some have an
> entry in the description field, some don't. I'm not sure what I
> would need to do to amend the table so that the entries with a
> description display first.
> At the moment there are 16 fields - id, contact, company, address1,
> address2,
> town, county, postcode, country, telephone, fax, email, web,
> service, products, description - and the only differentiation is
> that some entries have something in the description field (enhanced)
> some don't (basic) - should I have another column of some sort which
> designates which is enhanced and which isn't by using a 1 or 0, or
> have I misread the post below?

No. The SQL statement I provided will give you what you want:

SELECT id, contact, company, address1, address2, town, county,
postcode, country, telephone, fax, email, web, service, products,
description, (LENGTH(description) > 0) AS enhanced

FROM myTable

ORDER BY enhanced DESC, products

A bit of explanation:

"(LENGTH(description) > 0) AS enhanced" will return 1 if there's
anything in the `description` field, and 0 otherwise. ">" is a boolean
operator which returns either TRUE (1) or FALSE (0). You can then use
that to sort, and since you want 'enhanced' products to sort to the
top, the sort should be descending.

A bit more explanation:

You want to do this at selection time. There's a general principle of
database design that you don't want to store anything in a database
which you can derive from data in the database. Thus, if you have
dates of birth, you don't want to also store ages, because as soon as
one day has passed, the age information is out of date. On the other
hand, DOB will never be out of date, and you can use it to determine
age at retrieval time.

Likewise with your system - if the status of the product changes,
someone will enter or delete the description, but will inevitiably
forget to change the 'enhanced' field.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Feb 27, 2009

On Fri, 27 Feb 2009 07:08:49 -0500, "Murray *ACE*"
<forums@HAHAgreat-web-sights.com> wrote:

>There are any number of ways you can do this. Two recordsets is one way,
>each unloading within a repeat region placed as needed for proper screen
>display. Another way would use a single recordset, but reset to the first
>record after the first repeat region so that the second one will scan
>through the recordset again from the start. To reset the recordset, you
>would use the mysql_data_seek ( resource $result , int $row_number )
>function, e.g.,

Would it not be simpler to use a single recordset with an ORDER BY that
places the enhanced records first?

Gary

Report · Feb 27, 2009

Yes.

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"Gary White" <reply@newsgroup.please> wrote in message
news:flsfq4degq4tprbh69eo55tmmvk2jm9vj3@4ax.com...
> On Fri, 27 Feb 2009 07:08:49 -0500, "Murray *ACE*"
> <forums@HAHAgreat-web-sights.com> wrote:
>
>>There are any number of ways you can do this. Two recordsets is one way,
>>each unloading within a repeat region placed as needed for proper screen
>>display. Another way would use a single recordset, but reset to the first
>>record after the first repeat region so that the second one will scan
>>through the recordset again from the start. To reset the recordset, you
>>would use the mysql_data_seek ( resource $result , int $row_number )
>>function, e.g.,
>
> Would it not be simpler to use a single recordset with an ORDER BY that
> places the enhanced records first?
>
> Gary

Report · Feb 28, 2009

Joe - many, many thanks for not only giving me the solution but for also explaining how/why it works, which has given me a huge push forwards. I've just changed the SQL statement so that it follows your guideline above, tested the page and it works like a dream. You've made my life so much easier with this and I really can't thank you enough for your input.

Help needed - display basic and enhanced listings from database

1 Correct answer