[RESOLVED] query confusion!

[eheia]

i have two tables that are involved with this. One table(Contractors) has contractor information, including a text field that is filled with all the zip codes where the contractor will do work(Coverage). i then have another table that holds lead information(Leads) including the zip code(Zip_Code) where the lead is located. I need to make a query that will show me all the leads that are within the contractors service area. i thought it would be easy, and maybe it is and i'm over-thinking this, but thus far this has stumped me. if anyone can atleast get me started in the right direction it would be SA-WEET!

thanks.

also, i'm terrible at explaining things, so i'm sure you may need to clarify wtf i'm talking about.

[bogu]

Select the zip for contractors Explode() them into an array.

With that array u can select all the leads for that contractor.

[eheia]

can you possibly post just an example of what to do? i have searched everywhere and i cannot find anyway to implement what you are saying. I understand the concept of explode and what your idea is, but i just dont have any clue of how to implement this.

ALSO, i forgot a large portion of the equation here, it needs to not only get the leads that match the zip codes, it also has to match the contractors service type(s).

thanks much in advance.

[knashash]

Need to see an example of how you are storing your zipcodes. I think what youre saying is that you have them all in one field for each contractor. If that is the case then you need to do like bogu said. Can help a lot more if we see how youre storing your zipcodes

[eheia]

right, sorry about that. for each contractor all of the zip codes are stored in a text field with a space separating them. also, the services are the same setup, text field with entries separated by spaces. for each lead, the zip code is varchar field as well as the type of lead(windows, siding, etc.)

[knashash]

Well you need to do like bogu said and a bit more.

1. Select The Zipcode and Services Fields from The Contractor Table.

2. Once you have those fields, which each should be one long string seperated with spaces..then you will need to explode it out by spaces like this ($zipcodeString is the variable name assigned to the database value):

$zipcodeStringArray = explode(" ",$zipcodeString) ;

Do the same thing for the services field so you have 2 arrays with all the zipcode and services info.

3. I would create a string from the Arrays to query the Leads table like this:

$zipQueryString="";
$z=0;
foreach ($zipcodeStringArray as $key => $value)
{
if ($z) $zipQueryString .= "or zipcode='$value' ";
else $zipQueryString = "zipcode='$value' ";
$z++;

}

The above for loop will build a string that looks like this:

"zipcode=60613 or zipcode=50594 or zipcode=22234" etc....

do the same thing for the services string.

4. Finally you will need to query the database using the 2 strings you built.

So your finalQuery string should be:

$finalQueryString = "select * from Leads where ($zipQueryString) and ($servicesQueryString)"

that should at least point you in the right direction

[eheia]

a ha! ok that makes perfect sense! man somedays i just make things waaaaaaay harder than they need to be! thank you VERY much!

[eheia]

ok, while trying to get this to work, i am testing on a different query but this applies perfectly, but i'm running into an issue.

$query1 = "SELECT states FROM sales WHERE salesid = '$salesid'";
$result1 = mysql_query($query1);
$row1= mysql_fetch_array($result1);
$states = $row1['states'];
print_r (explode(" ",$states));

that code displays this on the page:

Array ( [0] => CO TX LA MS AL GA FL AR WY )

what am i doing wrong? shouldn't it be dividing up as Array ([0], Array([1] etc..? i think this is what is causing the rest of the code not to work.

thanks.

[knashash]

its a little confusing for me to see a printout directly from the explode function.

Assign the explode function to an array variable first like this:

$stateArray = explode(" ",$states);

then try printing out the $stateArray variable:

if that doesnt work....then print out your $state string right after you get it from the database to make sure that it is indeed coming back to you the way you think it should.

[bogu]

Is OK like knashash told u to do, I just want to change something from his code:

This code:

foreach ($zipcodeStringArray as $key => $value)
{
    if ($z) $zipQueryString .= "or zipcode='$value' ";
    else $zipQueryString = "zipcode='$value' ";
    $z++;

}

Make if like this:

$zips = '';
foreach ($zipcodeStringArray as $value) $zips = "'{value}',"; //prepare a string like this ('CO','TX','LA','MS',...,'WY',)
$zips = subsrt($zips,0,-1);  //cut the last coma

after u have the $zips string (like this 'CO','TX','LA','MS',...,'WY') u can make the select like this:
SELECT * FROM Coverage
WHERE Zip_Code IN ($zips)

I hope u understand ....

P.S. In knashash example, if u got hundreds of zip codes that's going to be a long query ...

[eheia]

i will try that when i get to that point. right now i'm stuck on the explode function. for some reason its not working correctly(i dont think).

when i echo the states from the query i get this:

echo $states;//code

CO TX LA MS AL GA FL AR WY //output

that is correct. the query is pulling the correct states.
now i use explode and display the results:

$statesStringArray = explode(" ",$states);
echo $statesStringArray[0];
echo $statesStringArray[1];

CO TX LA MS AL GA FL AR WY //output

ok so now, based on the echo from the query, echo $statesStringArray[0]; should return just the first state from the query correct? it should echo CO. but it doesnt, it echoes all the states from the query. and echo $statesStringArray[1]; echoes nothing. what am I doing wrong? or is my understanding of explode incorrect?

thanks

[laserlight]

Fascinating. What do you get as the output of this script?

<?php
$states = "CO TX LA MS AL GA FL AR WY";
$states_array = explode(" ", $states);
echo $states_array[0];
?>

[eheia]

that works. it simply outputs CO.

[knashash]

Is it possible that there hidden characters being returned from the mysql query instead of spaces....that is the only thing I can think of as to why that explode function isnt working for you. The field in your database that holds the state values...what type is it? Varhchar?

[eheia]

text

[eheia]

ok, thanks for the idea, i went and checked in the table, its because after each entry there is a return, not a space. so i changed my code to this:

$statesArray = explode("\n",$states);

i just changed the space separator to a \n and now it works perfectly. now i have to try the rest of the code.