What is this php code doing?

[Rocky86]

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

$sqlconnect=mysql_connect("localhost","hyperian_tr ack","__munged__");
if(!$sqlconnect)
die(mysql_error());
mysql_select_db("hyperian_track", $sqlconnect);


if($_GET['postal']))

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

$resultpostal=mysql_query("SELECT startlat,endlat,startlng,endlng FROM districts
WHERE districtno = $postalcode"); //get startlat,endlat,startlng,endlng of location

while($row = mysql_fetch_array($resultpostal))
{
$temp[]=$row; // store each record in an array
}



$resultname=mysql_query("SELECT uname FROM location,districts
WHERE location.lat < districts.startlat AND location.lat > districts.endlat AND location.lng < districts.startlng
AND location.lng > districts.endlng");

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



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

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



mysql_close($con);



[/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=mysql_query("SELECT startlat,endlat,startlng,endlng FROM districts
WHERE districtno = $postalcode"); //get startlat,endlat,startlng,endlng of location

while($row = mysql_fetch_array($resultpostal))
{
$temp[]=$row; // store each record in an array
}



$resultname=mysql_query("SELECT uname FROM location,districts
WHERE location.lat < districts.startlat AND location.lat > districts.endlat AND location.lng < districts.startlng
AND location.lng > districts.endlng");

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



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


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.startlat 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)

SELECT
        `uname`
    FROM
        `location`,
        `districts`
    WHERE
            `location`.`lat` < `districts`.`startlat`
        AND
            `location`.`lat` > `districts`.`endlat`
        AND
            `location`.`lng` < `districts`.`startlng`
        AND
            `location`.`lng` > `districts`.`endlng`

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:

SELECT
        `locations`.`uname`
    FROM
    (
        `locations`,
        `districts`
    )
    WHERE
    (
            `districts`.`districtno` = '{$postalcode}'
        AND
        (
            `locations`.`lat`
                BETWEEN
                        `districts`.`startlat`
                    AND
                        `districts`.`endlat`
        )
        AND
        (
            `locations`.`lng`
                BETWEEN
                        `districts`.`startlng`
                    AND
                        `districts`.`endlng`
        )
    )
 

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)

SELECT
        `uname`
    FROM
        `location`,
        `districts`
    WHERE
            `location`.`lat` < `districts`.`startlat`
        AND
            `location`.`lat` > `districts`.`endlat`
        AND
            `location`.`lng` < `districts`.`startlng`
        AND
            `location`.`lng` > `districts`.`endlng`

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:

SELECT
        `locations`.`uname`
    FROM
    (
        `locations`,
        `districts`
    )
    WHERE
    (
            `districts`.`districtno` = '{$postalcode}'
        AND
        (
            `locations`.`lat`
                BETWEEN
                        `districts`.`startlat`
                    AND
                        `districts`.`endlat`
        )
        AND
        (
            `locations`.`lng`
                BETWEEN
                        `districts`.`startlng`
                    AND
                        `districts`.`endlng`
        )
    )
 

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:

SELECT
        `locations`.`uname`
    FROM
    (
        `locations`,
        `districts`
    )
    WHERE
    (
            `districts`.`districtno` = '{$postalcode}'
        AND
        (
            `locations`.`lat`
                BETWEEN
                        `districts`.`startlat`
                    AND
                        `districts`.`endlat`
        )
        AND
        (
            `locations`.`lng`
                BETWEEN
                        `districts`.`startlng`
                    AND
                        `districts`.`endlng`
        )
    )
 

To convert it to PHP, we simply replace line 24 of the code in your OP:

$resultname = mysql_query("
SELECT
        `locations`.`uname`
    FROM
    (
        `locations`,
        `districts`
    )
    WHERE
    (
            `districts`.`districtno` = '{$postalcode}'
        AND
        (
            `locations`.`lat`
                BETWEEN
                        `districts`.`startlat`
                    AND
                        `districts`.`endlat`
        )
        AND
        (
            `locations`.`lng`
                BETWEEN
                        `districts`.`startlng`
                    AND
                        `districts`.`endlng`
        )
    )");
 

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:

SELECT
        `locations`.`uname`
    FROM
    (
        `locations`,
        `districts`
    )
    WHERE
    (
            `districts`.`districtno` = '{$postalcode}'
        AND
        (
            `locations`.`lat`
                BETWEEN
                        `districts`.`startlat`
                    AND
                        `districts`.`endlat`
        )
        AND
        (
            `locations`.`lng`
                BETWEEN
                        `districts`.`startlng`
                    AND
                        `districts`.`endlng`
        )
    )
 

To convert it to PHP, we simply replace line 24 of the code in your OP:

$resultname = mysql_query("
SELECT
        `locations`.`uname`
    FROM
    (
        `locations`,
        `districts`
    )
    WHERE
    (
            `districts`.`districtno` = '{$postalcode}'
        AND
        (
            `locations`.`lat`
                BETWEEN
                        `districts`.`startlat`
                    AND
                        `districts`.`endlat`
        )
        AND
        (
            `locations`.`lng`
                BETWEEN
                        `districts`.`startlng`
                    AND
                        `districts`.`endlng`
        )
    )");
 

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=mysql_connect("localhost","hyperian_tr ack","gsmtrack");
if(!$sqlconnect)
die(mysql_error());
mysql_select_db("hyperian_track", $sqlconnect);


if($_GET['postal']))

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

$resultpostal=mysql_query("SELECT startlat,endlat,startlng,endlng FROM districts
WHERE districtno = $postalcode"); //get startlat,endlat,startlng,endlng of location

if($row = mysql_fetch_array($resultpostal))
{
$postalno=$row; // store each record in an array
}





$resultname=mysql_query("SELECT uname FROM location,$postalno
WHERE location.lat < $postalno.startlat AND location.lat > $postalno.endlat AND location.lng < $postalno.startlng
AND location.lng > $postalno.endlng");

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



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


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



mysql_close($con);


[/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=mysql_query("SELECT uname FROM location,$postalno
WHERE location.lat < $postalno.startlat AND location.lat > $postalno.endlat AND location.lng < $postalno..startlng
AND location.lng > $postalno.endlng");
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.

[Rocky86]

First, describe the relationship between your tables.

right now my table does not use any forigen key they have no common colume name but I want to compare the value from the 2 different table

[kovik]

right now my table does not use any forigen key they have no common colume name but I want to compare the value from the 2 different table

There is no need for a foreign key. You need to think about WHAT you are trying to do and EXACTLY what values you want to look between, and tell me. If you don't know what you want to do, I can't help you.

[Rocky86]

There is no need for a foreign key. You need to think about WHAT you are trying to do and EXACTLY what values you want to look between, and tell me. If you don't know what you want to do, I can't help you.

Ok currently I have 2 table
District Table

Location Table

As you can see from here I want to do a PHP coding that would allow me to get the startlat,endlat,startlng,endlng FROM districts table
WHERE districtno is = to the postalcode enter by the user store inside a variable

After that I want to get the username of the person from location table where the location latitude and location longtitude is BETWEEN the value of the startlat and endlat and startlng and endlng if it is between then get the username

Basically the top part must be able to pass the value to the second part to do the comparing that what I wants.

[kovik]

So, the relationship between the two tables is that location.lat must be between district.startlat and disctrict.endlat and location.lng must be between district.startlng and district.endlng.

Looking back over this thread neither you nor pbmods really understand how this would be done (no offense, pbmods). Your original code was on the right track and if you did it correctly, it'd be the easy way. Too bad for you, I'm not a fan of the "easy way." ;)

What you need to do is JOIN the tables where they are related. You JOIN tables by specifying the way that they will be JOINed, and then specify how they will be joined. When tables are joined, it means that all of their rows are linked together according to a condition.

The way you join tables is like this:

SELECT [data] FROM [table1] LEFT JOIN ([table2]) ON ([a relationship between table1 and table2]);

So, in order to this, we need to get the data (the username) from the first table (location) and join the second table (district) on the relationship condition we have specified.

SELECT `location`.`uname` FROM `location` LEFT JOIN (`district`) ON (`location`.`lat` BETWEEN `district`.`startlat` AND `district`.`endlat` AND `location`.`lng` BETWEEN `district`.`startlng` AND `district`.`endlng`) WHERE `district`.`districtno` = foo;

Take a look at it and make sure you UNDERSTAND it before you use it. Table joining essentially creates an entirely new table. This join would take each row from the location table and join a row from the district table onto it whereever the ON() condition is met. Then, it takes every row from that new table where districtno is equal to a value.

And FYI, you don't need to specify any table names in this query because none of your tables have the same column names. If they did though, you'd have to specify. I've done it so that it's easy for you to comprehend.

[pbmods]

Heya, Volectricity.

My query does the same thing as yours, but mine uses a cross join instead of a left join.

Tomato, Tomahto.

[kovik]

Heya, Volectricity.

My query does the same thing as yours, but mine uses a cross join instead of a left join.

Tomato, Tomahto.

Yeah, at second look, I see the join. My apologies. I had only skimmed the thread.
Also at second look, I see Rocky was just... Halfway clueless as to how to implement the code. No idea how he came up with that query. :-p

Maybe he thought that '.' and '->' were one in the same.

[Rocky86]

Yeah, at second look, I see the join. My apologies. I had only skimmed the thread.
Also at second look, I see Rocky was just... Halfway clueless as to how to implement the code. No idea how he came up with that query. :-p

Maybe he thought that '.' and '->' were one in the same.

Hi so my final coding is it correct plss help me check, point out to me if any mistake thx
[PHP]
<?php
$temp="";




$sqlconnect=mysql_connect("localhost","hyperian_tr ack","gsmtrack");
if(!$sqlconnect)
die(mysql_error());
mysql_select_db("hyperian_track", $sqlconnect);


if($_GET['postal']))

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

$resultpostal=mysql_query("SELECT `location`.`uname` FROM `location` LEFT JOIN (`districts`) ON (`location`.`lat` BETWEEN `districts`.`startlat` AND `districts`.`endlat` AND `location`.`lng` BETWEEN `districts`.`startlng` AND `districts`.`endlng`)
WHERE `districts`.`districtno` = '$postalcode'");

if($row = mysql_fetch_array($resultpostal))
{
$temp[]=$row; // store each record in an array
}



foreach($temp as $key=>$extract)
$reportname=$reportname.$key."=".$extract."&";
echo '&reportname&';
}//end of if loops



mysql_close($con);

[/PHP]

[Rocky86]

Basciallay I try to test if the Between statement can be use on the SQL but I don't think it works so does it mean I cannot see Between anymore to compare the value this is what I get:


Table

This statement should return me all the value between the department and contact which is the data in designation but instead is show me an error

Error

So does it mean I cannot make use of the Between or there is something wrong with my SQL statement?

[Rocky86]

anyone? plsss help thx alot!

[gregerly]

Im only skimming here also, but it seems your using BETWEEN improperly. Here is a link you should check out which may clear things up (maybe). You could probably find the answers your looking for with a quick google search.

Here is a little tutorial on the BETWEEN operator:

MySQL Between

Hope that helps.

Greg

[Rocky86]

ok I get it that mean the BETWEEN cannot be use when selecting 2 table right?

[kovik]

ok I get it that mean the BETWEEN cannot be use when selecting 2 table right?

No. The syntax for BETWEEN is "someValue BETWEEN minValue AND maxValue."

What the hell do you think your code is doing? You cannot simply echo an object or an array. Ever stop to think that maybe YOU are the one doing everything wrong?

What does mysql_fetch_array() return? Oh! That's right! An array.
What is $temp? An array of arrays? Okay.
And in your foreach loop, what does that make each $extract? Each $extract is an array? Really?

Stop being so helpless and start learning the language and using some logic, or I will abandon this thread quickly.

[Rocky86]

So sorry I noe I being stupid and dumbass I will think about it seriously and come out with the code hope you can still help me with it thx!

[kovik]

I don't really enjoy being helpless if I undertstand I would not ask so much in the forum alreadx is just that I am such a hurry cause my deadline for this project is in 2 week time all I want to know is which part I did wrongly that all if you think I am being stupid and helpless is fine thx for yr help

If you still have the problem, then you are still being helpless. I just gave you the answer to your problem in my last post.

[Rocky86]

If you still have the problem, then you are still being helpless. I just gave you the answer to your problem in my last post.

Hey volectricity I been thinking of what you mention but I come out the this set of coding I just hope you can give me some guide. I know they sure is gona be mistake again but would you mind correct it for me so I can have betetr understanding of it thx alot!!

[PHP]
<?php
$temp="";




$sqlconnect=mysql_connect("localhost","hyperian_tr ack","gsmtrack");
if(!$sqlconnect)
die(mysql_error());
mysql_select_db("hyperian_track", $sqlconnect);


if($_GET['postal']))

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

$resultpostal=mysql_query("SELECT location.uname FROM location,districts WHERE districts.districtno = '".$postalcode."' AND
location.lat BETWEEN districts.startlat AND districts.endlat AND location.lng BETWEEN districts.startlng AND districts.endlng");

if($row = mysql_fetch_array($resultpostal))
{
$temp[]=$row; // store each record in an array
}



foreach($temp as $result)
$result=$reportname; //store the array into a new variable
echo '&$reportname&'; //pass the value to actionscripts
}//end of if loops



mysql_close($con);


[/PHP]

[pbmods]

Heya, Rocky.

Your MySQL query is looking a lot better. Now let's work on the next step.

 foreach($temp as $result)
        $result=$reportname;    //store the array into a new variable
        echo '&$reportname&'; //pass the value to actionscripts
 

Not only does this section not do what you want it to do, but it in fact doesn't even do anything! The PHP '=' operator assigns the value on the *right* to the variable on the 'left'. So in the code above, you are setting *$result* to the value of *$reportname*, which is opposite of what it looks like you intended.

And at any rate, you don't even need the additional variable, since all you're doing is echoing the value.

And fortunately, there's already a PHP function that does exactly what you need.

I'll give you a second. Ok. Now, where do we go from here?

You'll need to change your code a little bit (use 'while' instead of 'if'):

$temp = array();
$i = 0;
while($row = mysql_fetch_array($resultpostal))
        {
            echo http_build_query($row, ++$i) . '&';
        }
 

What does this do? Well, the first thing is we set up a temporary array and a counter.

Next, while there is a row left to fetch in the MySQL result set, we fetch it, and then we output a query string, using the incremented value of $i as a suffix (e.g., 'uname_1', 'uname_2', etc.).

[Rocky86]

Heya, Rocky.

Your MySQL query is looking a lot better. Now let's work on the next step.



Not only does this section not do what you want it to do, but it in fact doesn't even do anything! The PHP '=' operator assigns the value on the *right* to the variable on the 'left'. So in the code above, you are setting *$result* to the value of *$reportname*, which is opposite of what it looks like you intended.

And at any rate, you don't even need the additional variable, since all you're doing is echoing the value.

And fortunately, there's already a PHP function that does exactly what you need.

I'll give you a second. Ok. Now, where do we go from here?

You'll need to change your code a little bit (use 'while' instead of 'if'):

$temp = array();
$i = 0;
while($row = mysql_fetch_array($resultpostal))
        {
            echo http_build_query($row, ++$i) . '&';
        }
 

What does this do? Well, the first thing is we set up a temporary array and a counter.

Next, while there is a row left to fetch in the MySQL result set, we fetch it, and then we output a query string, using the incremented value of $i as a suffix (e.g., 'uname_1', 'uname_2', etc.).

hi thx for replying I understand what u mention except this part here I still don't get it "we output a query string, using the incremented value of $i as a suffix (e.g., 'uname_1', 'uname_2', etc.)" and echo http_build_query($row, ++$i) . '&';basically the end result should be pass to actionscript to do the output

[Rocky86]

heya I manage to solve the SQL issue thx alot for helping me up!

[pbmods]

Heya, Rocky.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)