Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

explode array in mysql

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
wakk0
Asked:
wakk0
  • 4
1 Solution
 
Roger BaklundCommented:
Try this:
$strsql = "SELECT ... AND s.sid in (w.assignto)";

Open in new window

0
 
NerdsOfTechCommented:
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
 
NerdsOfTechCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
wakk0Author Commented:
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
 
NerdsOfTechCommented:
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
 
NerdsOfTechCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now