Solved

explode array in mysql

Posted on 2009-04-12
6
514 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to resize preview image 4 33
.htaccess file settings 4 36
session dropped in IE 10 22
Wordpress Body Class 5 13
Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

747 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

12 Experts available now in Live!

Get 1:1 Help Now