[PHP-DB] Semi-newbie - postgres polygon/points and PHP?
Ok, a kinda newbie question. I set up a Postgres DB with a table that had a
Point and a Polygon in it (to hold XY and shape settings for munging a jpeg
image in php).
I could not easily figure out how to extract the data into a reasonably easy
to use format in PHP. The best I could do seemed like it was extracted
into a string, and then I had to parse that.
While this works, it seems like a BIG hack to me, and the docs have been
less then forthcoming.
I've also tried the usual google search of php postgres polygon to see if
I could get an idea.
For example, I stored the Point as:(432, 120)
And I got (432, 120) as a string, and had to parse it:
...
$Q = SELECT * FROM room WHERE id=$id;
if(!($Result = pg_exec($Connection, $Q)))
{
print(Query failed $Q);
print(pg_errormessage($Connection));
print(BR\n);
exit;
}
$myroom = pg_fetch_array($Result, $i);
$xy = $myroom[xy];
$xyar = split([\(,\)], $xy);
$x = $xyar[1];
$y = $xyar[2];
...
Any ideas?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Semi-newbie - postgres polygon/points and PHP?
Ok Roger, Ok, a kinda newbie question. I set up a Postgres DB with a table that had a Point and a Polygon in it (to hold XY and shape settings for munging a jpeg image in php). I could not easily figure out how to extract the data into a reasonably easy to use format in PHP. The best I could do seemed like it was extracted into a string, and then I had to parse that. While this works, it seems like a BIG hack to me, and the docs have been less then forthcoming. I've also tried the usual google search of php postgres polygon to see if I could get an idea. For example, I stored the Point as:(432, 120) And I got (432, 120) as a string, and had to parse it: If I understand you correctly, the cartesian coordinates are stored in the tbl as a string, eg CREATE TABLE room... id... xy tinytext... but each coordinate whilst a single entity is defined by two numbers. Why not store them as integers/reals on a single row, eg CREATE TABLE room... id... x int, y int... Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] counting multiple columns based on different values
John, I'm gonna go out on a limb and guess that I'm missing something obvious (and easy) because this sure seems like it should be able to be done. Here's the issue: I need to pull the client name and ID out of one table and then, count the records in a different table (called ratings) that match two different criteria. If I was doing 2 different sql statments, they would look like this: select clientname, clients.ID, count(*) FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '2' select clientname, clients.ID, count(*) FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '3' In a perfect world, I'd be able to receive the following data from a single query: | ClientName | ClientID | Status-2 | Status-3| | Bob| 28 | 103 | 87 | | Steve | 29 | 11 | 106 | | Jerry | 30 | 50 | 82 | The first idea (multiple COUNT()s and GROUP BYs) didn't work, probably because of the way I was confusing the use of table aliases with VIEWs. No matter, let's go to 'plan B'. Following my usual 'formula' approach, breaking it down into smaller problems: 1 getting the data out of the clients tbl and grab all of the status rows 2 refine the first ratings criteria to a 'count' 3 do the same for the second ratings criteria The first is a trivial join: SELECT clientname, clients.ID, ratings.status-2, ratings.status-3 FROM clients, ratings WHERE clients.ID = ratings.clientID AND (ratings.status = '2' OR ratings.status = '3') Just to be sure of my assumptions (remember I don't have enough info to be able to debug/test!), please check that this is ALL of the data you want included, and that none other exists/is missing. This won't satisfy you - you don't want line after line of status data, you want that info aggregated. Taking status=2 first, we should go from: SELECT clientname, clients.ID, ratings.status-2 FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '2' to: SELECT clientname, clients.ID, count(ratings.status-2) FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '2' GROUP BY clients.ID - or should that be GROUP BY ratings.clientID? (they've 'fiddled' with some of the finer points of GROUP BY recently) Trouble is this falls apart when we add status-3 - as we discovered earlier. Let's review what COUNT() does. SQL groups the rows according to the WHERE clause and then notes the number of rows in the group. Ok, so we can do that too - let's imagine adding an extra column to the table, and put a TRUE into the column if it is relevant to the count, and FALSE if it is not. Yes, there is an IF() in SQL (some very 'meaty' RTFM starts at 6.3 Functions for Use in SELECT and WHERE Clauses). IF( ratings.status = '2', TRUE, FALSE ) So now we could simply count the TRUEs to arrive at the number of status=2 rows. Substitute 1 for TRUE and 0 for FALSE, and we can have SQL do that last calc for us, ie SUM( imaginary-column ). SUM( IF( ratings.status = '2', 1, 0 ) ) Because there is no GROUP structure, we can replicate the same for status='3'. SELECT clientname, clients.ID, SUM( IF( ratings.status = '2', 1, 0 ) ) AS Status-2, SUM( IF( ratings.status = '3', 1, 0 ) ) AS Status-3 FROM clients, ratings WHERE clients.ID = ratings.clientID BTW I am taking from your query the idea that status is a string not a numeric. Another BTW is that appropriate indexing will speed things up considerably if you are operating on larger tables! Beaten it this time? Please advise, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] PHP InterBase Multiple BLOBs display
Hi, I have read several posting on How to display a graphical BLOB but all
of them show how to display only one image. I need to display all the images
found in the query including some descriptions for that images.
I tried the following but of course only one image appeared.
?PHP
header(Content-type: image/jpeg);
$table1 = 'myBLOB';
$db_user = 'SYSDBA';
$db_pass = 'masterkey';
$host = 'd:/Program Files/Borland/InterBase/bin/Test.gdb';
$query1 = SELECT * FROM $table1 WHERE ID100 ORDER BY ID;
$query2 = INSERT INTO $table1 (ID,Pic) VALUES (4,?);
$connHndl = ibase_connect($host,$db_user,$db_pass) or die (Cannot
comunicate with the DataBase);
$set = ibase_query(SELECT * from $table1 where ID100);
while($row = ibase_fetch_row($set)){
ibase_blob_echo($row[1]);
}
ibase_close($connHndl);
?
A little source would be appreciated.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: can't insert into MS-access
Hi there, sounds like you do not have the proper read/write permissions on
that file.
HTH Joe :)
Penockio [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
I can't use sql command insert into but I can use select and it
condition.I use DSN that provide by ODBC and use MS-Access as source file.
I found Warning: SQL error: [Microsoft][ODBC Microsoft Access Driver]
Operation must use an updateable query., SQL state S1000 in
and my code is insert into order_trans_detail
(order_id,cus_id,order_place) values
('0212027','0071577722451188','548/12 payatai Road Patumwan') ; all
field is text field for database MS-Access .and I connect it through ODBC
by
ms access driver use (DSN)c
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Session confusion :-(
Hi All
I am send variable values via set links i.e.:
http://domian_name.com/page.php?category=1
http://domian_name.com/page.php?category=2
http://domian_name.com/page.php?category=3
Etc etc
The value is sent to a session. THIS WORKS BUT.
If I click on the first link session variable category is set to 1.
But if I then click on the send , third, forth or whatever link
Session variable is still set to 1.
I have tried
If(ISSET($category)){
Unset($category);
Session_register(category) // hoping it would accept the new sent value.
}
No Go
Can anyone throw some light on this please.
As Always I am grateful for any pointers or exampled help
:-)
Dave Carrera
Php / MySql Development
Web Design
Site Marketing
http://www.davecarrera.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Connecting to mySQL database
Hi ppl, I'm new to both PHP and mySQL but have no choice but to crash-course on both before end of mid of march to complete my University project. I would really appreciate alittle help from any kind soul out there. I have already created a mySQL database and create all the tables *well at least all those that i think i need*. Now i am trying to connect to the database and test if everything was created properly and make sure i can connect. Can anyone please advise on how i should go abt it?? Thanks a million!!! Alvin P.S: My school project is on developing a web-based inventory managament system. :P -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Connecting to mySQL database
Hi Alvin, I'm new to both PHP and mySQL but have no choice but to crash-course on both before end of mid of march to complete my University project. I would really appreciate alittle help from any kind soul out there. I have already created a mySQL database and create all the tables *well at least all those that i think i need*. Now i am trying to connect to the database and test if everything was created properly and make sure i can connect. Can anyone please advise on how i should go abt it?? Thanks a million!!! Welcome to our happy little (ever-expanding) world! I suggest a visit to the PHP and MySQL homepages. If you look for the links and tutorial pages, you will find plenty of the latter. There are a number of web articles on 'intro to MySQL and PHP' and 'making dynamic web sites'. These types of tutorials cover all the basics that you're going to need to get started. Later on, another visit may even yield something about inventory systems, perhaps the sites offering classes might have something handy you can pick up and apply. If you prefer books/the Uni library: PHP and MySQL Web Development by Welling and Thomson or MySQL by DuBois are good choices - both cover interfacing the two products, but there are numerous alternatives that others will recommend. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Page Expired - Post form
My situation is extremely common. I've searched the
newsgroups for several hours now looking for a
straight answer and have found no solution. I am
testing on my local but will not have access to apache
config file. I get the infamous page expired warning
and have tried all i know to fix. I can't use get
method because it is a login to account manager. I
have tried
session_cache_limiter('private'); and it does nothing?
I am out of ideas and patience, please help if you
can. thanks,
olinux
__
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] counting multiple columns based on different values
DL, I ran the query exactly as you had sent it and ran into the same error where it talked about mixing counts without a group clause. So, I added a quick group by clientname and ran it again One word PERFECTION. I can't thank you enough. This is now a blistering fast query that produces the exact report I was trying to get. Thanks again!! John Hawkins --- DL Neil [EMAIL PROTECTED] wrote: John, I'm gonna go out on a limb and guess that I'm missing something obvious (and easy) because this sure seems like it should be able to be done. Here's the issue: I need to pull the client name and ID out of one table and then, count the records in a different table (called ratings) that match two different criteria. If I was doing 2 different sql statments, they would look like this: select clientname, clients.ID, count(*) FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '2' select clientname, clients.ID, count(*) FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '3' In a perfect world, I'd be able to receive the following data from a single query: | ClientName | ClientID | Status-2 | Status-3| | Bob| 28 | 103 | 87 | | Steve | 29 | 11 | 106 | | Jerry | 30 | 50 | 82 | The first idea (multiple COUNT()s and GROUP BYs) didn't work, probably because of the way I was confusing the use of table aliases with VIEWs. No matter, let's go to 'plan B'. Following my usual 'formula' approach, breaking it down into smaller problems: 1 getting the data out of the clients tbl and grab all of the status rows 2 refine the first ratings criteria to a 'count' 3 do the same for the second ratings criteria The first is a trivial join: SELECT clientname, clients.ID, ratings.status-2, ratings.status-3 FROM clients, ratings WHERE clients.ID = ratings.clientID AND (ratings.status = '2' OR ratings.status = '3') Just to be sure of my assumptions (remember I don't have enough info to be able to debug/test!), please check that this is ALL of the data you want included, and that none other exists/is missing. This won't satisfy you - you don't want line after line of status data, you want that info aggregated. Taking status=2 first, we should go from: SELECT clientname, clients.ID, ratings.status-2 FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '2' to: SELECT clientname, clients.ID, count(ratings.status-2) FROM clients, ratings WHERE clients.ID = ratings.clientID AND ratings.status = '2' GROUP BY clients.ID - or should that be GROUP BY ratings.clientID? (they've 'fiddled' with some of the finer points of GROUP BY recently) Trouble is this falls apart when we add status-3 - as we discovered earlier. Let's review what COUNT() does. SQL groups the rows according to the WHERE clause and then notes the number of rows in the group. Ok, so we can do that too - let's imagine adding an extra column to the table, and put a TRUE into the column if it is relevant to the count, and FALSE if it is not. Yes, there is an IF() in SQL (some very 'meaty' RTFM starts at 6.3 Functions for Use in SELECT and WHERE Clauses). IF( ratings.status = '2', TRUE, FALSE ) So now we could simply count the TRUEs to arrive at the number of status=2 rows. Substitute 1 for TRUE and 0 for FALSE, and we can have SQL do that last calc for us, ie SUM( imaginary-column ). SUM( IF( ratings.status = '2', 1, 0 ) ) Because there is no GROUP structure, we can replicate the same for status='3'. SELECT clientname, clients.ID, SUM( IF( ratings.status = '2', 1, 0 ) ) AS Status-2, SUM( IF( ratings.status = '3', 1, 0 ) ) AS Status-3 FROM clients, ratings WHERE clients.ID = ratings.clientID BTW I am taking from your query the idea that status is a string not a numeric. Another BTW is that appropriate indexing will speed things up considerably if you are operating on larger tables! Beaten it this time? Please advise, =dn __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Is it possible ?
Is it possible to get the AUTO-INCREMENTed ID's value while doing an INSERT and have the value INSERTed to another table in the same FORM ? Raju ** Message from InterScan E-Mail VirusWall NT ** ** No virus found in attached file noname.htm This is a virus free message scanned by Zyberway * End of message *** -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Is it possible ?
On Saturday 16 February 2002 07:35 pm, you wrote: Is it possible to get the AUTO-INCREMENTed ID's value while doing an INSERT and have the value INSERTed to another table in the same FORM ? Raju mysql_query(insert whatever); $insertid=mysql_last_insert(); Then you use this value for your other insert. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] finding ID's
First off, sorry for the newbie question... :(. I want to be able to query the database and find the record with the highest ID value. Example... each row ideally has an incremented integer ID (1, 2, 3, 4...) but I am running into problems when I try and delete a row (let's say row 2). My PHP currently selects all of the rows and formulates the ID off of that... This I found out is bad because when I delete row 2 the query says there are 3 rows so my PHP will try to make the ID = 4. I just need the code to find the highest ID so I can ++ it. Sorry again for the easy question! -Jon Gales http://www.macmerc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
