What is this php code doing?

[Rocky86]

Hello can somebody explain the code to me thx in advance!!
[PHP]
<?php
$temp="";

$sqlconnect=mys ql_connect("loc alhost","hyperi an_track","__mu nged__");
if(!$sqlconnect )
die(mysql_error ());
mysql_select_db ("hyperian_trac k", $sqlconnect);


if($_GET['postal']))

$postalcode=sub str($_GET['postal'],0,2); //will return from 0 position 2 characters

$resultpostal=m ysql_query("SEL ECT startlat,endlat ,startlng,endln g FROM districts
WHERE districtno = $postalcode"); //get startlat,endlat ,startlng,endln g of location

while($row = mysql_fetch_arr ay($resultposta l))
{
$temp[]=$row; // store each record in an array
}



$resultname=mys ql_query("SELEC T uname FROM location,distri cts
WHERE location.lat < districts.start lat AND location.lat > districts.endla t AND location.lng < districts.start lng
AND location.lng > districts.endln g");

while($row = mysql_fetch_arr ay($resultname) )
{
$temp[]=$row;
}



foreach($temp as $key=>$extract)
$reportname=$re portname.$key." =".$extract."&" ;

echo '&reportname& ';
}//end of if loops



mysql_close($co n);



[/PHP]

[pbmods]

Heya, Rocky.

This is some pretty basic code, and I know that parts of it have been explained to you before.

Is there a *specific* part of the code that you don't understand that we can help you with?

[Rocky86]

Heya, Rocky.

This is some pretty basic code, and I know that parts of it have been explained to you before.

Is there a *specific* part of the code that you don't understand that we can help you with?

I need help especially on the section of code here
[PHP]
$resultpostal=m ysql_query("SEL ECT startlat,endlat ,startlng,endln g FROM districts
WHERE districtno = $postalcode"); //get startlat,endlat ,startlng,endln g of location

while($row = mysql_fetch_arr ay($resultposta l))
{
$temp[]=$row; // store each record in an array
}



$resultname=mys ql_query("SELEC T uname FROM location,distri cts
WHERE location.lat < districts.start lat AND location.lat > districts.endla t AND location.lng < districts.start lng
AND location.lng > districts.endln g");

while($row = mysql_fetch_arr ay($resultname) )
{
$temp[]=$row;
}



foreach($temp as $key=>$extract)
$report=$report .$key."=".$extr act."&";


echo '&$report&';
[/PHP]
this section of the code I want to know if the $resultpostal value is passing down to the $resultname because I wanted to compare the location.lat against the districts.start lat of the $resultpostal

[Rocky86]

Anybody pls help me thx alot

[pbmods]

Heya, Rocky.

Actually, that code probably doesn't do what you want it to do.
(pulled out and organized for simplicity)
[code=mysql]
SELECT
`uname`
FROM
`location`,
`districts`
WHERE
`location`.`lat ` < `districts`.`st artlat`
AND
`location`.`lat ` > `districts`.`en dlat`
AND
`location`.`lng ` < `districts`.`st artlng`
AND
`location`.`lng ` > `districts`.`en dlng`[/code]

This will return a (seemingly) random set of locations, since there is no absolute limiter; you are fetching `uname`s for every location that falls inside ANY district, and there are presumably a lot of those!

Instead, you want something like this:
[code=mysql]
SELECT
`locations`.`un ame`
FROM
(
`locations`,
`districts`
)
WHERE
(
`districts`.`di strictno` = '{$postalcode}'
AND
(
`locations`.`la t`
BETWEEN
`districts`.`st artlat`
AND
`districts`.`en dlat`
)
AND
(
`locations`.`ln g`
BETWEEN
`districts`.`st artlng`
AND
`districts`.`en dlng`
)
)
[/code]

This will pick out the uname of any location that is in the set of coordinates determined by picking out the district whose `districtno` is $postalcode.

[Rocky86]

Heya, Rocky.

Actually, that code probably doesn't do what you want it to do.
(pulled out and organized for simplicity)
[code=mysql]
SELECT
`uname`
FROM
`location`,
`districts`
WHERE
`location`.`lat ` < `districts`.`st artlat`
AND
`location`.`lat ` > `districts`.`en dlat`
AND
`location`.`lng ` < `districts`.`st artlng`
AND
`location`.`lng ` > `districts`.`en dlng`[/code]

This will return a (seemingly) random set of locations, since there is no absolute limiter; you are fetching `uname`s for every location that falls inside ANY district, and there are presumably a lot of those!

Instead, you want something like this:
[code=mysql]
SELECT
`locations`.`un ame`
FROM
(
`locations`,
`districts`
)
WHERE
(
`districts`.`di strictno` = '{$postalcode}'
AND
(
`locations`.`la t`
BETWEEN
`districts`.`st artlat`
AND
`districts`.`en dlat`
)
AND
(
`locations`.`ln g`
BETWEEN
`districts`.`st artlng`
AND
`districts`.`en dlng`
)
)
[/code]

This will pick out the uname of any location that is in the set of coordinates determined by picking out the district whose `districtno` is $postalcode.

Hiya how do I implentment all this sql into my php coding? will be glad if you can modify my exisiting code for me thx alot!!

[pbmods]

I will post back in a little bit.

[pbmods]

Heya, Rocky.

Hiya how do I implentment all this sql into my php coding? will be glad if you can modify my exisiting code for me thx alot!!

Ok. So we have this SQL code:
[code=mysql]
SELECT
`locations`.`un ame`
FROM
(
`locations`,
`districts`
)
WHERE
(
`districts`.`di strictno` = '{$postalcode}'
AND
(
`locations`.`la t`
BETWEEN
`districts`.`st artlat`
AND
`districts`.`en dlat`
)
AND
(
`locations`.`ln g`
BETWEEN
`districts`.`st artlng`
AND
`districts`.`en dlng`
)
)
[/code]

To convert it to PHP, we simply replace line 24 of the code in your OP:
[code=php]
$resultname = mysql_query("
SELECT
`locations`.`un ame`
FROM
(
`locations`,
`districts`
)
WHERE
(
`districts`.`di strictno` = '{$postalcode}'
AND
(
`locations`.`la t`
BETWEEN
`districts`.`st artlat`
AND
`districts`.`en dlat`
)
AND
(
`locations`.`ln g`
BETWEEN
`districts`.`st artlng`
AND
`districts`.`en dlng`
)
)");
[/code]

Note that you can get rid of lines 14-23 of the code in your original post, as they do absolutely nothing.

[Rocky86]

Heya, Rocky.



Ok. So we have this SQL code:
[code=mysql]
SELECT
`locations`.`un ame`
FROM
(
`locations`,
`districts`
)
WHERE
(
`districts`.`di strictno` = '{$postalcode}'
AND
(
`locations`.`la t`
BETWEEN
`districts`.`st artlat`
AND
`districts`.`en dlat`
)
AND
(
`locations`.`ln g`
BETWEEN
`districts`.`st artlng`
AND
`districts`.`en dlng`
)
)
[/code]

To convert it to PHP, we simply replace line 24 of the code in your OP:
[code=php]
$resultname = mysql_query("
SELECT
`locations`.`un ame`
FROM
(
`locations`,
`districts`
)
WHERE
(
`districts`.`di strictno` = '{$postalcode}'
AND
(
`locations`.`la t`
BETWEEN
`districts`.`st artlat`
AND
`districts`.`en dlat`
)
AND
(
`locations`.`ln g`
BETWEEN
`districts`.`st artlng`
AND
`districts`.`en dlng`
)
)");
[/code]

Note that you can get rid of lines 14-23 of the code in your original post, as they do absolutely nothing.

Hiya I basically try the run query on SQL to see if the BETWEEN statement would work before implentment the code in php but I don't since to get any return result from the SQL statement so does it matter? or SQL cannot perform the BETWEEN function ?

[kovik]

Well the use of the values you are getting results between would be unclear to SQL because you create no relationship between the two tables, thus rendering the join invalid.

I understand that the SQL wasn't written by you, but take the responsibility to establish the relationship in the query yourself, as pbmods may have been unaware of what the relationship between the two actually was, and was leaving it up to you to specify.

[Rocky86]

Well the use of the values you are getting results between would be unclear to SQL because you create no relationship between the two tables, thus rendering the join invalid.

I understand that the SQL wasn't written by you, but take the responsibility to establish the relationship in the query yourself, as pbmods may have been unaware of what the relationship between the two actually was, and was leaving it up to you to specify.

So how do I create relationship between the two table? through forign key? if have relation does it mean the BETWEEN would now be valid?

[Rocky86]

ok I need to noe about something import about PHP currently this is my code right now
[PHP]
<?php
$temp="";




$sqlconnect=mys ql_connect("loc alhost","hyperi an_track","gsmt rack");
if(!$sqlconnect )
die(mysql_error ());
mysql_select_db ("hyperian_trac k", $sqlconnect);


if($_GET['postal']))

$postalcode=sub str($_GET['postal'],0,2); //will return from 0 position 2 characters

$resultpostal=m ysql_query("SEL ECT startlat,endlat ,startlng,endln g FROM districts
WHERE districtno = $postalcode"); //get startlat,endlat ,startlng,endln g of location

if($row = mysql_fetch_arr ay($resultposta l))
{
$postalno=$row; // store each record in an array
}





$resultname=mys ql_query("SELEC T uname FROM location,$posta lno
WHERE location.lat < $postalno.start lat AND location.lat > $postalno.endla t AND location.lng < $postalno.start lng
AND location.lng > $postalno.endln g");

if($row = mysql_fetch_arr ay($resultname) )
{
$temp[]=$row;
}



foreach($temp as $key=>$extract)
$reportname=$re portname.$key." =".$extract."&" ;


echo '&reportname& ';
}//end of if loops



mysql_close($co n);


[/PHP]

right now I need to compare the value of the top SQL statement against the second SQL statement which mean that the location.lat and location.lng is to be compare against the value inside the variable $postalno but the question is can php allow me to just compare it by using this sql statement? $resultname=mys ql_query("SELEC T uname FROM location,$posta lno
WHERE location.lat < $postalno.start lat AND location.lat > $postalno.endla t AND location.lng < $postalno..star tlng
AND location.lng > $postalno.endln g");
I just create a variable to store in all the value inside $postalno and I do a comparing by in the next statement so is this possible?

[kovik]

So how do I create relationship between the two table? through forign key? if have relation does it mean the BETWEEN would now be valid?

A relationship is a key that determines which values correspond to the rows of each table. The relationship must be specified in the WHERE clause.

[Rocky86]

A relationship is a key that determines which values correspond to the rows of each table. The relationship must be specified in the WHERE clause.

mind to show me an example thx

[kovik]

mind to show me an example thx

First, describe the relationship between your tables.