beckjoh

Sorry I was not very clears. Let say I have a table like this: Column 1 | Column 2 | Column 3 89 0 99 88 0 98 76 0 98 Then I would like the csv file to look like this: Column 1 | Column 3 89 99 88 98 76 98 Thanks

I am trying to export column names and data from a mysql table into a csv file. I can do that just fine, however sometimes (if no data is present) one or more of my columns will have zeros placed into the rows. This is how I programmed it to work. However, now I want to export only the columns that do not have the zeros. In other words, I want to only export the columns and data that are valid. Can anyone point me into the right direction. Thanks JB

I have a mysql table with a set of x,y points (latitude, and longitude) and I was trying to find some examples on how to implement a inverse distance weighting interpolation formula on those points. Does anyone know if this has already been done? I have done a quite bit of searching but have not found any good examples. Thanks in advance. JB

Perfect Thanks

Sorry I accidently hit return before I finished typing this post. I am trying to figure out how to query my table to see if two columns are storing identical values. For example: I have a mysql table like this: id column1 column2 1 3.65 3.65 2 4.10 4.10 3 5.23 5.23 I want to query the table to compare column1 and column2 to see is every row is equal or if any is not equal. They must either be all equal or if one row is not equal then the query would return false. Any help would be greatly appreciated. JB

I am trying to figure out how to query my table to see if two columns are storaging identical values. For example: I have a mysql table like this: id column1 column2 1 3.65 3.65 2 4.1

I am trying to set $var1, $var2, and $var3 with this code. However, I do not always know the names of the columns or the number of columns, so I wrote some code to pull out the field names and number of fields and I was trying to use them to accomplish this task. Actually I would like to set $column1_name = $myrow['column1'], etc. $result = mysql_query("SELECT * FROM table"); while($myrow=mysql_fetch_array($result)) { $var1 = $myrow['column1']; $var2 = $myrow['column2']; $var3 = $myrow['column3']; }

I am trying to do this: $result = mysql_query("SELECT * FROM table"); while($myrow=mysql_fetch_array($result)) { $var1 = $myrow['column1']; $var2 = $myrow['column2']; $var3 = $myrow['column3']; } With this: $pre_query = "SELECT * FROM $table"; //to get field names $result = mysql_query($pre_query) or die(mysql_error()); $dollar = "$"; $myrow = "myrow"; for($i = 0; $i < mysql_num_fields($result); $i++) { if ($i == (mysql_num_fields($result)-1)) $math .= "$".mysql_field_name($result,$i)." = ".$dollar.$myrow."['".mysql_field_name($result,$i)."'];<br>"; else $math .= "$".mysql_field_name($result,$i)." = ".$dollar.$myrow."['".mysql_field_name($result,$i)."'];<br>"; } $result = mysql_query("SELECT * FROM table"); while($myrow=mysql_fetch_array($result)) { $math; } CAN IT BE DONE? THANKS

This is my last attempt at trying to use Barand's idea to find points within polygons.  I have a shapefile with 18 polygons.  I import this shapefile and us GD to draw the polygons.  I have a mysql table with the x, y (long, lat) coordinates.  This table has approximately 5,000 points.  I put the following code together from a variety of sources from this site http://www.easywms.com/easywms/?q=zh-hant/node/78.  When I use only one point, the code works great.  But when I loop the 5,000 points it takes over 10 minutes to run. Way too long.  Here is my code.  If anyone knows how to speed this up or approach in a different manner please let me know.  THanks for your help in advance. $image_sx = 600; $image_sy = 600; if(empty($scale))$scale    = 350000000; $select = mysql_query("SELECT * FROM dbtable"); $rows = mysql_num_rows($select); while ($query = mysql_fetch_array($select)) { $my_long[] = $query['Long']; $my_lat[] = $query['Lat']; } for($m = 0;$m < $rows; $m++) { $my_long = $my_long[$m]; $my_lat = $my_long[$m]; $sx   = 2 * $scale; $sy      = $scale; $center  = getlocationcoords($my_lat, $my_long, $sx,$sy) ; $min_x    = $center["x"] - ($image_sx / 2); $min_y    = $center["y"] - ($image_sy / 2); $im        = imagecreate($image_sx,$image_sy); $land      = imagecolorallocate ($im, 0xF7,0xEF,0xDE); $land2      = imagecolorallocate ($im, 0x00, 0x00, 0x00); $red      = imagecolorallocate ($im, 0xff,0x00,0x00); $pt["x"] = $center["x"] - $min_x; $x = $pt["x"]; $pt["y"] = $center["y"] - $min_y; $y = $pt["y"]; $pts = array($x,$y); $arrGeometry = loadShapeFile("polygons.shp") ; foreach($arrGeometry as $poly) {     $converted_points = array();     $numparts = $poly["geom"]["numparts"]; if ($numparts >= 1) {         for($j = 0;$j < $numparts;$j++)         // make "x y " to "x y"         $points = trim($poly["geom"]["parts"][$j]["pointString"]);         $points = explode(" ", $points);         $number_points = count($points);         $i = 0;         while ($i < $number_points) {             $lon = $points[$i];             $lat = $points[$i + 1];                       $pt = getlocationcoords($lat, $lon, $sx, $sy);             $converted_points[] = $pt["x"] - $min_x;             $converted_points[] = $pt["y"] - $min_y;             $i += 2;         }         switch ($poly["shapetype"]) {             case '0':// null                 break;             case '1':// point                 imagefilledellipse($im, $converted_points[0], $converted_points[1], 4, 4, $red);                 break;             case '3':// polyline                 imageline($im, $converted_points[0], $converted_points[1], $converted_points[2], $converted_points[3], $red);                 break;             case '5':// polygon //print_r($converted_points);                 imagefilledpolygon($im, $converted_points, $number_points / 2, $land2);                 break;             case '8':// multipoint                 imagefilledpolygon($im, $converted_points, $number_points / 2, $red);                 break;         }     } } $color = imagecolorat($im, $pts[0], $pts[1]) ; if ($color == $land2) { echo 'YES'; echo '<br>'; } else { echo 'NO'; echo '<br>'; } } imagedestroy($im); function getlocationcoords($lat, $lon, $width, $height) {     $x = (($lon + 180) * ($width / 360));     $y = ((($lat * -1) + 90) * ($height / 180));     return array("x" => round($x), "y" => round($y)); } function loadShapeFile($file_name) {     $handle = fopen($file_name, "rb");     // fetchShpBasicConfiguration();     // fetchRecords();     loadHeaders($handle);     return $arrGeometry = loadRecords($handle); } function loadHeaders($handle) {     fseek($handle, 24, SEEK_SET);     $fileLength = loadData("N", fread($handle, 4));     // echo $fileLength;     fseek($handle, 32, SEEK_SET);     $shapeType = loadData("V", fread($handle, 4));     //echo $shapeType;     $boundingBox = array();     $boundingBox["xmin"] = loadData("d", fread($handle, 8));     $boundingBox["ymin"] = loadData("d", fread($handle, 8));     $boundingBox["xmax"] = loadData("d", fread($handle, 8));     $boundingBox["ymax"] = loadData("d", fread($handle, 8));     //print_r($boundingBox); } function loadRecords($handle) {     fseek($handle, 100);     while (!feof($handle)) {         $bByte = ftell($handle);         $record = new ShapeRecord(-1);         $record->loadFromFile($handle);         $records["shapetype"] = $record->shapeType;         $records["geom"] = $record->SHPData;         if (isset($records)) {             $arrGeometry[] = $records;             unset($records);         }         $eByte = ftell($handle);         if ($eByte <= $bByte) {             return $arrGeometry;         }     } } function loadData($type, $data) {     if (!$data) return $data;     $tmp = unpack($type, $data);     return current($tmp); } class ShapeRecord {     var $SHPFile = null;     var $recordNumber = null;     var $shapeType = null;     var $SHPData = array();     function ShapeRecord($shapeType)     {         $this->shapeType = $shapeType;     }     function loadFromFile($handle)     {         $this->SHPFile = $handle;         $this->loadStoreHeaders();         switch ($this->shapeType) {             case 0:                 $this->loadNullRecord();                 break;             case 1:                 $this->loadPointRecord();                 break;             case 3:                 $this->loadPolyLineRecord();                 break;             case 5:                 $this->loadPolygonRecord();                 break;             case 8:                 $this->loadMultiPointRecord();                 break;             default:                 // $setError(sprintf("The Shape Type '%s' is not supported.", $shapeType));                 break;         }     }     function loadStoreHeaders()     {         $this->recordNumber = loadData("N", fread($this->SHPFile, 4));         $tmp = loadData("N", fread($this->SHPFile, 4)); //We read the length of the record         $this->shapeType = loadData("V", fread($this->SHPFile, 4));     }     function loadPoint()     {         $data = array();         $x1 = loadData("d", fread($this->SHPFile, 8));         $y1 = loadData("d", fread($this->SHPFile, 8));         $data["pointString"] = "$x1 $y1 ";         return $data;     }     function loadNullRecord()     {         $this->SHPData = array();     }     function loadPointRecord()     {         $data = $this->loadPoint();         $tmp = explode(" ", $data["pointString"]);         $this->SHPData["xmin"] = $this->SHPData["xmax"] = $tmp[0];         $this->SHPData["ymin"] = $this->SHPData["ymax"] = $tmp[1];         $this->SHPData["numparts"] = 1;         $this->SHPData["numpoints"] = 1;         $this->SHPData["parts"][0]["pointString"] = $data["pointString"];         // $this->SHPData["pointString"] = $data["pointString"];     }     function loadMultiPointRecord()     {         $this->SHPData = array();         $this->SHPData["xmin"] = loadData("d", fread($this->SHPFile, 8));         $this->SHPData["ymin"] = loadData("d", fread($this->SHPFile, 8));         $this->SHPData["xmax"] = loadData("d", fread($this->SHPFile, 8));         $this->SHPData["ymax"] = loadData("d", fread($this->SHPFile, 8));         $this->SHPData["numpoints"] = loadData("V", fread($this->SHPFile, 4));         for ($i = 0; $i <= $this->SHPData["numpoints"]; $i++) {             $data = $this->loadPoint();             $this->SHPData["pointString"] .= $data["pointString"];         }     }     function loadPolyLineRecord()     {         $this->SHPData = array();         $this->SHPData["xmin"] = loadData("d", fread($this->SHPFile, 8));         $this->SHPData["ymin"] = loadData("d", fread($this->SHPFile, 8));         $this->SHPData["xmax"] = loadData("d", fread($this->SHPFile, 8));         $this->SHPData["ymax"] = loadData("d", fread($this->SHPFile, 8));         $this->SHPData["numparts"] = loadData("V", fread($this->SHPFile, 4));         $this->SHPData["numpoints"] = loadData("V", fread($this->SHPFile, 4));         for ($i = 0; $i < $this->SHPData["numparts"]; $i++) {             $this->SHPData["parts"][$i] = loadData("V", fread($this->SHPFile, 4));         }         $firstIndex = ftell($this->SHPFile);         $readPoints = 0;         while (list($partIndex, $partData) = each($this->SHPData["parts"])) {             if (!isset($this->SHPData["parts"][$partIndex]["pointString"]) || !is_array($this->SHPData["parts"][$partIndex]["pointString"])) {                 $this->SHPData["parts"][$partIndex] = array();                 // $this->SHPData["parts"][$partIndex]["pointString"] = array();             } while (!in_array($readPoints, $this->SHPData["parts"]) && ($readPoints < ($this->SHPData["numpoints"])) && !feof($this->SHPFile)) {                 $data = $this->loadPoint();                 $this->SHPData["parts"][$partIndex]["pointString"] .= $data["pointString"];                 $readPoints++;             }         }         fseek($this->SHPFile, $firstIndex + ($readPoints * 16));     }     function loadPolygonRecord()     {         $this->loadPolyLineRecord();     } }

I know I need to scale the coordinates to an image, but I really don't care about the scale.  I am just trying to find out if an xy coordinate in within a polygon made up of several vertices.  Any suggestions on a scale?

The polygon would not change it would stay the same.

Barand, I have revisited your idea to find the point within a polygon.  I am using Lat Long values.  Would I first have to convert the lat long values to screen coordinates?  If so, can you show me how with your example? Thanks. JB

Sorry, You are right but I fixed the variable names and it still only loops one set of points.

I first query a table called Points for all rows. This table holds x and y points in longitude and latitude. I then while loop the results to get each row. I then want to run a new query on a second table called segments in which I will use the stored x,y data as a condition or variable in the query. I can get this to run for one point but not for all the points. Can you use data from one table to use for a query in another table? I would try to use JOIN but the tables do not relate, I just need to evaluate each point, the problem is there are hundreds of points. Here is my code. $pointresults = mysql_query("SELECT * FROM points"); $num_points = mysql_num_rows($pointresults); if($num_points == 0) { echo "No Points in Table, There is an Error!"; } else { // get each row while($mypointrow = mysql_fetch_array($pointresults)) { $thelon = $mypointrow['Longitude']; $thelat = $mypointrow['Latitude']; $result = "SELECT poly_id, Segment_id FROM segments WHERE ( longa > $thelng OR longb > $thelng ) AND ( (lata > $thelat AND latb < $thelat ) OR (latb > $thelat AND lata < $thelat ) )"; $result2=mysql_query($result); $resultnumrows = mysql_num_rows($result2); Any help would be appreciated.

Thanks, I think I have it working. I was going to try your function on http://www.phpfreaks.com/forums/index.php/topic,108445.msg436434.html#msg436434 to see if it works. Do I need the imagecreate function or is it within php? Thanks again.

Barand, Any more thoughts on my problem?

That solution looks great, but I still have the same problem because the author of that script physically added the point and polygon values. I need the script to pull all of the values from a mysql table. The only way I know how to fill the array is through two seperate loops, but to run the function, I would need to be able for each array to loop through all the points. Therefore my first question remains, can you first fill two arrays and then loop them together. Or do you use the explode function to fix the problem?

The two tables are not related. One table is a set of points, the other hold the xy vertices of a polygon. I am trying to select all points that fall within a polygon. I may be attempting something that is not logical.

Here is the Table structure for table `point` -- CREATE TABLE IF NOT EXISTS `point` ( `PointID` int(11) NOT NULL, `Lon` decimal(10,6) NOT NULL, `Lat` decimal(10,6) NOT NULL, `Elevation` decimal(5,1) NOT NULL, `FixType` decimal(4,0) NOT NULL, `UTCTime` decimal(7,1) NOT NULL, `Speed` decimal(3,2) NOT NULL, `Course` decimal(4,1) NOT NULL, `SF1` int(11) NOT NULL, `SF2` decimal(7,0) NOT NULL, `SF3` decimal(7,0) NOT NULL, `SF4` decimal(4,3) NOT NULL, `SF5` decimal(10,4) NOT NULL, `SF6` decimal(10,4) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; and -- Table structure for table `polygon` -- CREATE TABLE IF NOT EXISTS `polygon` ( `PolygonID` int(11) NOT NULL, `PointNum` int(11) NOT NULL, `Lon` decimal(10,6) NOT NULL, `Lat` decimal(10,6) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I need to select multiple rows (x,y data0 from two seperate tables and then store the values in two seperate array's. One will be pts the other polygon, I think I have done that. However, I then need to use all values in each array in a seperate function that is outside of the while loop for each query. The function only returns one value, instead of all of the values that should be stored in the array. Here is some of the code: <?php $link = mysql_connect("localhost", "root", ""); mysql_select_db("database", $link); $bndresult = mysql_query("SELECT * FROM polygon ", $link); $num_rows = mysql_num_rows($bndresult); if($num_rows == 0) { echo "No rows!"; } else { // get each row while($myrow = mysql_fetch_array($bndresult, MYSQL_BOTH)) { $x = $myrow[Lon]; $y = $myrow[Lat]; $myPolygon = array("x," => $x, "y" => $y); } $result = mysql_query("SELECT * FROM point", $link); $num_rows2 = mysql_num_rows($result); if($num_rows2 == 0) { echo "No rows!"; } else { // get each row while($myrow2 = mysql_fetch_assoc($result)) { $xpoint = $myrow2[Lon]; $ypoint = $myrow2[Lat]; $point = array("x" => $xpoint, "y" => $ypoint); } foreach($point as $key => $point) { echo "$key ($point) is " . $pointLocation->pointInPolygon($point, $myPolygon) . "<br>"; } ?> This loop does not work correctly because it only loops for one of the arrays how do I loop for both?beckjoh New php-forum User Posts: 1 Joined: Sat Feb 23, 2008 9:17 am Private message