Solved

explode array in mysql

Posted on 2009-04-12
6
526 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to confirm and grey the submit button using JavaScript ? 14 69
Code not executing correctly. 3 44
SSL unsecure page mystery 17 47
php time 12 30
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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 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…

759 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