breakdown array for mysql query

Hi all,
I have mysql table named personnel.  In this table I have a field named personnel_dealer which contains ~ delimited data.  What I am trying to do is get all records for personnel then get the personnel_dealer data for each record and either explode the ~ delimited data or create an array from it so I can do another mysql query against the explode or array.  
For example, I have 2 personnel and each belongs to multiple dealers:
personnel_fname | personnel_dealer (~ delimited)
John | 2~4~1~
Mary | 5~2~
I want to do a dealer query to see who is assigned to to a dealer (ie. dealer 2 should result in both John and Mary)
Here is what I have been attempting to get working:
$query="SELECT * FROM personnel";
$result=mysql_query($query);
$personnelArray = array();
while($row = mysql_fetch_array($result))
{
	$a5 = array($row["personnel_dealer"] => $row["personnel_id"] );
	$personnelArray = array_merge($a5, $personnelArray);
}
$personnelArray2 = array_flip($personnelArray);
 
/*
need to explode personnel_dealer into another usable array to do the next query with
*/
 
$query="SELECT * FROM personnel WHERE personnelArray2[$link]";
$result=mysql_query($query);
$num=mysql_numrows($result);

Open in new window

newbe101Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
try:
$query="SELECT * FROM personnel";
$result=mysql_query($query);
$personnelArray = array();
while($row = mysql_fetch_array($result))
{
	$personnelArray[] = array('id' => $row["personnel_id"], 'dealer' => $row["personnel_dealer"] );
}
 
 
foreach( $personnelArray as $subArr)
{
	$temp = explode('~',substr($subArr['dealer'],0,-1) );
	foreach($temp as $d)
	{
		$query2="SELECT * FROM personnel WHERE id=$d";
		$result2=mysql_query($query2);
		$num=mysql_numrows($result2);
		echo $num;
	}
}

Open in new window

0
newbe101Author Commented:
looks like a good start.  A couple of things:
1. I am using $link=$_GET["link"]; to get the dealer id from the url.  I don't see where you are using this.
2. how can I display personnel_fname and personnel_lname in the while loop?
0
hieloCommented:
well, if you already have the id (in $link) and you are querying the same table, why are you doing the first query. Can't you just query the table directly using $link?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

newbe101Author Commented:
yes, but I need to query personnel_dealer against $link and I don't know how to handle the the explode part (or array part) for the delimited personnel_dealer
0
hieloCommented:
is this what you are after?
$query="SELECT * FROM personnel WHERE id LIKE '~" . $link. "~'";
$result=mysql_query($query);
 
while($row = mysql_fetch_array($result))
{
	echo $row[1];	
}

Open in new window

0
hieloCommented:
I meant:
$query="SELECT * FROM personnel WHERE CONCAT('~',personnel_dealer ) LIKE '~" . $link. "~'";
0
newbe101Author Commented:
what does that do with the ~'s?  And, does that take into consideration that $link = one dealer and that personnel.personnel_dealer may contain many?
0
newbe101Author Commented:
that only works for personnel that have the $link as their first dealer in personnel_dealer (ie. 1~2~ works but 2~1~ doesnt if link=1)
0
hieloCommented:
you field has:
2~4~1~
for purposes of querying, concat is  changing that value to:
~2~4~1~

so, if $link=2, it is looking for ~2~ in ~2~4~1~. You can also try:

$query="SELECT * FROM personnel WHERE 0 < INSTR( CONCAT('~',personnel_dealer),  '~" . $link . "~')";
0
hieloCommented:
>.that only works for personnel
try:
$query="SELECT * FROM personnel WHERE 0 < INSTR( CONCAT('~',personnel_dealer),  '~" . $link . "~')";
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
newbe101Author Commented:
there we go... works great!  Thank you.  If you want, will you give me a breakdown of the query so I understand what is happening?
0
hieloCommented:
>>will you give me a breakdown of the query so I understand what is happening?
refer to: ID: 22998300

INSTR returns the position where the second argument appears within the first. IF it is not found, zero is returned. So you just need to make sure the value is greater than zero - meaning if greater than zero, $link is within the personnel_dealer field.
0
newbe101Author Commented:
sheds some light... I still need to play around with it to completely understand it... but thank you.
0
hieloCommented:
you are welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.