Solved

Problem with query and resulting data

Posted on 2011-09-30
10
297 Views
Last Modified: 2012-05-12
Hello,

I have the following issue:

I have a database with the following tables:
- products
- authors
- lu_prod_au (luprod_id and luauth_id)

lu_prod_au is the lookup table. Because any product can have up to 4 authors. Every product_id and entered author_id gets stored in this table.

Now when I use the following query this results in a products list where products with multiple authors are displayed multiple times while each product should only be shown once in the list of products.
Note:. The various authors need to get displayed next to the listed product in stead of the repetition of a product with multiple authors stating a different author next to it each time...

Can anyone tell me how to change the query to make it only show each of the products with mulitple authors only once?

Here is the query:
SELECT * FROM products, lu_prod_au, authors WHERE prod_Id = luprod_id AND luauth_id = auth_Id ORDER BY xxx DESC

I hope someone can help me out with this.

WKR
RV
0
Comment
Question by:Witheet
  • 6
  • 4
10 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36890848
do you want something like this
give your GROUP_CONCAT(auth_name ) frildname for auth_name



SELECT prod_Id , GROUP_CONCAT(auth_name )FROM products, lu_prod_au, authors WHERE prod_Id = luprod_id AND luauth_id = auth_Id
group by prod_Id
0
 

Author Comment

by:Witheet
ID: 36890875
TYVM for your quick reply..
I will try this and let you know asap if it did the trick...

wkr rv
0
 

Author Comment

by:Witheet
ID: 36890932
Hello Pratima,

This makes the list not have double items... so tnx for that...
But I still need to be able to display each available author for a product next to the product in the list... Now only one author remains.
The list looks has to look like:
- Image of product
- Title of the product
- Author(s) (here the names of other possible authors need to be displayed aswell. If I am correct the recordset now only holds 1 of the 4 possible authors when there is more then 1 author.)

Any suggestions?

TYVM and wkr
RV
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 36890956
SELECT Image ,prod_Id , GROUP_CONCAT(auth_name ) as Authors FROM products, lu_prod_au, authors WHERE prod_Id = luprod_id AND luauth_id = auth_Id
group by Image ,prod_Id

This will work
GROUP_CONCAT(auth_name )  will give you comma seperated list of all possible authors for that product in one column
0
 

Author Comment

by:Witheet
ID: 36891028
That does the trick... Now I have one last small detail... Authname in my authors table is divided over 3 columns: authfirstname / authmiddlename / authlastname

I tried the following:
GROUP_CONCAT(authfirstname , authmiddlename , authlastname)
This however results in the the name to be displayed without spaces between first middle and lastname. Also there is no space between the names and the comma. How can I realise this.
EG. LukeSkywalker,HanSolo needs to be: Luke Skywalker, Han Solo

The comma I could probably add a space using str_replace in php (Atleast that's what I would try first) But the spaces between the name parts I can't think of a solution for...

Again tyvm this is exactly what I needed... Hope you can help me fix this last thing..

wkr rv
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Witheet
ID: 36891060
Pratima Don't bother... I have the solution! TYVM for your help.. this helps me a great deal in this issue and understanding more complex queries in general..very grateful for this...

GROUP_CONCAT(authfirstname, ' ',  authmiddlename, ' ', authlastname)

Tanx again

WKR RV
0
 

Author Closing Comment

by:Witheet
ID: 36891062
Excellent help!
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36891067
try

GROUP_CONCAT(Concat_ws(' ',authfirstname , authmiddlename , authlastname) )
0
 

Author Comment

by:Witheet
ID: 36891082
I'll be sure to use your last solution instead of mine... looks different and probably better then mine... TY again ... very happy to get this working correct...
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36891095
:)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now