mysql query on explode string to get names

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;
} 

Select allOpen in new window

My problem is that I need to do a query against my exploded string.  Please give me an example.

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>

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.

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)

"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.

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.

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>';
}

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>';
}

Select allOpen in new window

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)

no do not loop inside a loop

can you give me an example of your array idea?

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.

$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);
}

while($row = mysql_fetch_array($result))

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.

https://www.experts-exchange.com/questions/23831636/AJAX-3-Level-Selections.html?cid=236&anchorAnswerId=22827275#a22827275

Maybe Ajax is what you need

onClick='doChange(src, val);'
Get new results from selection

?

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.

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      ]
}

Select allOpen in new window

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>';
}

Select allOpen in new window

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>';
}

Select allOpen in new window

Create a follow up question so that we can solve this last issue.