close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

How to write query to retrieve csv values

Thread began 12/01/2011 2:01 pm by Jedi | Last modified 12/06/2011 11:37 am by Jedi | 1465 views | 10 replies |

Jedi

How to write query to retrieve csv values

Hi, I have a website where I want to display all records by city. The city column in the table has cities stored in csv values. How do I qery the database to display the information by one city then by all cities?
Thanks in advance

Sign in to reply to this post

Ian S

So your database column for cities might read...

Manchester, Birmingham, London, Swansea, Leeds ?

Assuming the recordset is called rscity and the field name for city is called 'city' you could try to put them into an array such as

<?php
$citynames = explode(",",$row_rscity['city]);
?>

Then the cites would be stored in the array so

$citynames[0] = Manchester
$citynames[1] = Birmingham
$citynames[2] = London

etc, etc

Cheers
Ian

Sign in to reply to this post

Jedi

Thanks Ian, That certainly does work. This is my dilema however: I will have a product that is offered for certain cities which could be up to 40 cities, and then other products that are offered to other cities. When the product is put into the database the input will use a mulitple select menu to add all the cities that the product will be offered to. This creates a column that has csv data.
Now when a customer is back on the website I want to show that product to only the cities that it is offered in. I am looking for a way to not be so labor oriented.
Thanks again for your help.

Sign in to reply to this post

Ian S

OK, so you never actually know how many cities might be related to a product?

If its not too late in the project I would look at creating a separate table for this data so you can have many cities to one product, rather than storing it as CSV data.

e.g. your Cities table would have a structure such as

cityid unique ID for the record
productidfk id that links to the product ID
cityname name of the city

then you can write a query to pull back the product and related city detail and filter it out as required.

Cheers
Ian

Sign in to reply to this post

Jedi

Hi Ian,
I did think about that route. The issue is however that I will have hundreds and possibly thousands of products that will change on a weekly basis. Some will be in all major cities and some only in some cities. So, I was looking for a solution whereby when a product was added that multiple cities could be added as well. And not have to then go to the city table and make hundreds of entries for the product. I hope I explained this correctly.
So, I am looking for a way to not only add the cities to the product but also be able search by state for the cities that contain the product as well.

Sign in to reply to this post

Jason ByrnesWebAssist

you need to use a linking table as suggested by Ian.

and use the Data Assist Manage relational table behavior on the products insert and update pages to assign cities to the product.

Andrew reded wrote a good tutorial for using the manage relational table behaviors here:
showpost.php?p=84393&postcount=17

to be able search by city and state you will need to use a join query to return data from the products and cities table using the linking table to create the relationships.

see the following for details on joined queries:
sql_join.asp

Sign in to reply to this post

Jedi

Thanks Jason,
You are right and it worked perfectly. One last question pertaining to the multiple relational behavior: Is it possible to use it for more than one option? I put cities, states, zip codes, and ip addresses in the options table, and I'm wondering if I can apply the same process to each option? it looks like I will be able to but I wanted to ask before I blow it up.
Thanks,

Sign in to reply to this post

Jason ByrnesWebAssist

no, at this time it can only be used for one option

Sign in to reply to this post

Jedi

Ok, thanks. Can you apply more than 1 manage relational table behaviors to an update form? By that I mean I have one now for cities. Can I do another for sates?

Sign in to reply to this post

Jason ByrnesWebAssist

well, the state a city applies to should not be done on the products page, it should be donme when inserting or updating the city to the database.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...