Solved

explode array in mysql

Posted on 2009-04-12
6
517 Views
Last Modified: 2013-12-12
i have a select query where i'm trying to get all records assigned to a person. is it possible to use explode within the query so that i only have to run 1 query?

the w.assignto is the field that may can contain multiple values.

1,2,3,4
$strsql = "SELECT m.id, DATE_FORMAT(m.mdate,'%m/%d/%Y'), m.item, m.description, m.status, w.assignto, s.sid, s.serviceby FROM maint m left join workassign w on m.id=w.mid left join service s on w.assignto=s.sid WHERE s.username='$loginuser' AND

w.assignto=s.sid";

 $result = mysql_query($strsql) or die("Cannot perform query: ".mysql_error());

Open in new window

0
Comment
Question by:wakk0
  • 4
6 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24125164
Try this:
$strsql = "SELECT ... AND s.sid in (w.assignto)";

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24126984
Translating cxr's response to this:
$strsql = "SELECT m.id, DATE_FORMAT(m.mdate,'%m/%d/%Y'), m.item, m.description, m.status, w.assignto, s.sid, s.serviceby FROM maint m left join workassign w on m.id=w.mid left join service s on w.assignto=s.sid WHERE s.username='$loginuser' AND

s.sid in (w.assignto";
 

 $result = mysql_query($strsql) or die("Cannot perform query: ".mysql_error());

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24126986
Corrected myself.
$strsql = "SELECT m.id, DATE_FORMAT(m.mdate,'%m/%d/%Y'), m.item, m.description, m.status, w.assignto, s.sid, s.serviceby FROM maint m left join workassign w on m.id=w.mid left join service s on w.assignto=s.sid WHERE s.username='$loginuser' AND

s.sid in (w.assignto)";

 

$result = mysql_query($strsql) or die("Cannot perform query: ".mysql_error());

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:wakk0
ID: 24128131
that's not working....i know there are records assign to that person because i assign before i test and it still returns 0 records.
0
 
LVL 19

Accepted Solution

by:
NerdsOfTech earned 125 total points
ID: 24128283
Unfortunately because the w.assignto is a comma demlimited list instead of a 1:M table relationship the only option is to run a primary query to explode each record (server time heavy) and then run your desired select query.

The best practice solution is to alter the table relationship so that you have a 1:M row relationship to assignto


table workassign
=============
workid: 45
assignto: "1, 2, 3, 4" {remove this column completely after backing up data}




So you have now

table workassign
=============
workid: 45

AND

table workassign_service
=========================
workid: 45
serviceid: 1
***
workid: 45
serviceid: 2
***
workid: 45
serviceid: 3
***
workid: 45
serviceid: 4


By doing so you remove the search inability and you can then utilize the "IN function" and run JUST THE "PRIMARY QUERY".

Otherwise, in lieu of this change, you will need to do a primary "SEARCH LOOP TO EXTRACT IDs" then do a "PRIMARY QUERY"
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24128315
That or you could utilize a rarely used string function that may help you:

FIND_IN_SET

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set
$strsql = "SELECT m.id, DATE_FORMAT(m.mdate,'%m/%d/%Y'), m.item, m.description, m.status, w.assignto, s.sid, s.serviceby FROM maint m left join workassign w on m.id=w.mid left join service s on w.assignto=s.sid WHERE s.username='$loginuser' AND ((FIND_IN_SET(s.sid, (w.assignto)) > 0)";

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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 …

948 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

24 Experts available now in Live!

Get 1:1 Help Now