Solved

explode array in mysql

Posted on 2009-04-12
6
523 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

809 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