Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mysql query on explode string to get names

Posted on 2008-10-29
25
Medium Priority
?
1,887 Views
Last Modified: 2013-12-13
I have a php mysql query and on the results, I end up with an array which I explode into a drop-down list. The list is of ID numbers.  The ID numbers relate to another table.  What I need to do is replace the ID numbers with the name... ie. ID 1 becomes apple and ID 2 becomes orange.
My tables (the parts that matter):
dealers.dealers_type
department.department_id
department.department_name
My dealers.dealers_type stores ~ separated values of  department.department_id.  I need to change the department.department_id to department.department_name.
Here is what I have which is working, just need to replace the id with the name from the department table:
$str = $dealers_type;
$str2 = substr($str, 0, -1);
$data = explode("~",$str2);
foreach($data as $k => $v)
{
echo '<option>'.$v.'</option>';
}

Open in new window

0
Comment
Question by:newbe101
[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
  • 10
  • 8
  • 4
  • +1
25 Comments
 

Author Comment

by:newbe101
ID: 22836081
So I need a query inside of a query.  Here is my main mysql query with has some unrelated stuff:
$query="SELECT * FROM dealers, dealers_location where dealers.dealers_location=dealers_location.dealers_location_id$orderby";
$result=mysql_query($query);
$num=mysql_numrows($result);
...
$i=0;
while ($i < $num) {$dealers_id=mysql_result($result,$i,"dealers_id");
$dealers_id=mysql_result($result,$i,"dealers_id");
...
++$i;
} 

Open in new window

0
 
LVL 8

Assisted Solution

by:CoyotesIT
CoyotesIT earned 400 total points
ID: 22836106
this is a basic join that you are trying to accomplish.

something such as

select a.id, b.name from id_table [a], name_table [b] where a.id = b.id

this would produce something like

id       name
1        apple
2        orange
1        apple
etc...

Then you can use the name column to get your values you need, however you may want to leave the value of the drop down as the id, since this is the value you store in your data table.

<select name="options">
<option value="1">Apple</option>
<option value="2">Orange</option>
</select>

Not sure if that example is relevant to what you are trying to do, but a simple join is all you need.

Good luck


0
 
LVL 10

Accepted Solution

by:
Tyler Laczko earned 1600 total points
ID: 22836107
your question is worded very difficult...

if you are looking to have the id as the value for the drop down and the name display

see attached
foreach($data as $k => $v)
{
echo '<option value="' . $v . '">'.$k.'</option>';
}

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:newbe101
ID: 22836180
My problem is that I need to do a query against my exploded string.  Please give me an example.
0
 
LVL 8

Expert Comment

by:CoyotesIT
ID: 22836196
I agree it is a little confusing. However I dont think he is wanting to store the counter in the value, but rather the actual id field from the select, the value should be the corresponding value which is stored in the separate table.

Your query:

$query="SELECT * FROM dealers, dealers_location where dealers.dealers_location=dealers_location.dealers_location_id$orderby";

is pulling back more data than you need by using (*) since you are wanting what looks to be the location you may try something like

SELECT dealers.location_id, dealers_location.id FROM dealers, dealers_location WHERE dealers.location_id = dealers_location.id

Then you can run your foreach assigning the values to the proper variables and create your <select>


0
 
LVL 8

Expert Comment

by:CoyotesIT
ID: 22836203
SELECT dealers.location_id, dealers_location.location FROM dealers, dealers_location WHERE dealers.location_id = dealers_location.id

changed dealers_location.id to dealers_location.location

sorry for the confusion.


0
 
LVL 10

Expert Comment

by:Tyler Laczko
ID: 22836212
coyetesit is not doing the join correctly.

can you please show us your tables that you are trying to get data from. also which tables you want to display and finally how the tables are connected. (which ids)
0
 
LVL 8

Expert Comment

by:CoyotesIT
ID: 22836213
"My problem is that I need to do a query against my exploded string.  Please give me an example."

I am not understanding why you need to query your results from your query. You should be able to get the results you want from a single query and manipulate the output of the result to whatever you need. You are building a standard drop down list.


0
 

Author Comment

by:newbe101
ID: 22836287
This is for display purpose only.  I am not going to do anything with the drop-down except show it... so I don't care about the values for it.  My original query shows all of the dealers.  The original query already is joining 2 tables to show the dealer location.  I think my query is fine and is working.  Part of my dealers query displays dealers.dealers_type which is ~ separated values of IDs for a completely separate table (departments).  I explode the dealers.dealers_type to create a dropdown for display purposes only.  What I need is to take the results of my string and query it to replace the string of IDs with names.
Maybe create an array with the string values then query the array to replace the IDs with the corresponding names.
To be clear, I have 3 tables that I am dealing with: dealers, dealers_location and departments.  I need to turn my dealers.dealers_type string (which is a string of departments.departments_id) into departments.departments_name
I hope this is more clear.
0
 
LVL 10

Expert Comment

by:Tyler Laczko
ID: 22836496
does this display the correct ids?

$str = $dealers_type;
$str2 = substr($str, 0, -1);
$data = explode("~",$str2);
foreach($data as $k => $v)
{
echo '<option>'.$v.'</option>';
}
0
 

Author Comment

by:newbe101
ID: 22836521
Yes it does.  I was thinking something like this... But I don't know if loops inside of loops is a good idea or not.
$str = $dealers_type;
$str2 = substr($str, 0, -1);
$data = explode("~",$str2);
foreach($data as $k => $v)
{
 
$query="SELECT * FROM department WHERE department_id=$v ORDER BY department_name";
$result=mysql_query($query);
$num=mysql_numrows($result);
 
$i=0;
while ($i < $num) {$department_id=mysql_result($result,$i,"department_id");
$department_id=mysql_result($result,$i,"department_id");
$department_name=mysql_result($result,$i,"department_name");
echo echo '<option>'.$department_name.'</option>';
++$i;
}
 
//echo '<option>'.$v.'</option>';
}

Open in new window

0
 
LVL 10

Expert Comment

by:Tyler Laczko
ID: 22836526
I would query the departments table for the id and name

using the id and name create an associative array

arrayDept = { 1=>"IT", 2=>"HR" }

Then you can address the associative array to get your values.


(Main reason for doin it this way is to only query the dept table once)
0
 
LVL 10

Expert Comment

by:Tyler Laczko
ID: 22836528
no do not loop inside a loop

0
 

Author Comment

by:newbe101
ID: 22836535
can you give me an example of your array idea?
0
 

Author Comment

by:newbe101
ID: 22836562
I can do a query to build the department array, but I wouldn't know how to compair the string to the array to get the names.
0
 
LVL 10

Expert Comment

by:Tyler Laczko
ID: 22836605
$query="SELECT dept_id, dept_name FROM department;"
$result=mysql_query($query);

$deptArray = array();
while($row = mysql_fetch_array())
{
$a1 = array($row[0] => $row[1]);
$deptArray = array_merge($a1, $deptArray);
}
0
 
LVL 10

Expert Comment

by:Tyler Laczko
ID: 22836615

while($row = mysql_fetch_array($result))
0
 

Author Comment

by:newbe101
ID: 22836649
I still don't see how I can compare the array against the string to get the names...  it looks like you just made the array for department.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 22839602
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 22839605
onClick='doChange(src, val);'
Get new results from selection

?
0
 
LVL 10

Expert Comment

by:Tyler Laczko
ID: 22842453
now that you have the dept array you can index it using your ids

$deptArray[     yourID      ]

you can access all of the values in the associative array.
0
 

Author Comment

by:newbe101
ID: 22845585
I don't follow...  Something like this...
$query="SELECT * FROM department;"
$result=mysql_query($query);
 
$deptArray = array();
while($row = mysql_fetch_array($result))
{
$a1 = array($row[0] => $row[1]); // or $a1 = array($row[department_id] => $row[department_name]);
$deptArray = array_merge($a1, $deptArray);
}
 
//then
 
$str = $dealers_type;
$str2 = substr($str, 0, -1);
$data = explode("~",$str2);
foreach($data as $k => $v)
{
$deptArray[     $v      ]
}

Open in new window

0
 

Author Comment

by:newbe101
ID: 22846747
OK, since we now have the array for table departments, we now need to change the value of my string to the value of department_name if the value of the string == department_id of the array... here is what I have so far (which doesn't work)
$str = $dealers_type;
$str2 = substr($str, 0, -1);
$data = explode("~",$str2);
foreach($data as $k => $v)
{
if ( $v == $deptArray[$v] ) {
	echo '<option>'.$deptArray($row[department_name].'</option>';
}
//echo '<option>'.$v.'</option>';
}

Open in new window

0
 

Author Comment

by:newbe101
ID: 22846883
getting close... now it shows the first letter of the department_name... why is the rest of the word not showing?
$str = $dealers_type;
$str2 = substr($str, 0, -1);
$data = explode("~",$str2);
foreach($data as $k => $v)
{
echo '<option>'.$deptArray[$v][department_name].' '.$v.'</option>';
//echo '<option>'.$v.'</option>';
}

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 22853554
Create a follow up question so that we can solve this last issue.
0

Featured Post

Industry Leaders: 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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

671 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