Page 1 of 1

Query SQL database in an FTP server (some code included)

Posted: Mon Sep 29, 2008 10:26 pm
by MALLON
I'm trying to query a database that resides on an FTP server, but I must not be doing it right because it's not working. :( I can successfully log into the FTP server, that works fine, but I'm just not sure how to query it. When I make a query, it tries to make the query based on the server which the php file lies in rather than the server where the database lies. I looked around on the forums, but I didn't see much. I guess most everyone's database is usually within the same server as the php file.

Thanks anyone that can help, what am I doing wrong?

Code: Select all

 
<?php
                     
$ftp_server = "x.x.x.x";
$ftp_user = "hello";
$ftp_pass = "world";
 
// set up a connection or die
$conn_id = ftp_connect($ftp_server) or die("Couldn't connect to $ftp_server");
 
// try to login
if (@ftp_login($conn_id, $ftp_user, $ftp_pass)) {
 
    //successful connection
    echo "Connected as $ftp_user@$ftp_server\n";
    
    //failed connection here
    $connection = new pdo("sqlite:ftp://x.x.x.x/path/path/path/database.sqlite3");
    $query="SELECT * FROM table";
    $result = $connection->query($query);
    foreach($result as $entry){
        echo $entry['name'];
    }
} else {
    echo "Couldn't connect as $ftp_user\n";
}
// close the connection
ftp_close($conn_id);  
?>
--MALON

Re: Query SQL database in an FTP server (some code included)

Posted: Tue Sep 30, 2008 12:49 am
by califdon
An FTP server is one piece of software, a Web server is another piece of software, a MySQL database server is still a different piece of software. The only way you can connect to a server is to use its domain name or IP address (using an authorized username and password). If that's really an FTP server, it cannot respond to a database connection request.

Re: Query SQL database in an FTP server (some code included)

Posted: Tue Sep 30, 2008 1:21 am
by alex.barylski
As it's already been said...

MySQL is a RDBMS and servers usually accept remote connections. MySQL is configured by default to NOT allow remote connections but it is quite possible.

Re: Query SQL database in an FTP server (some code included)

Posted: Tue Sep 30, 2008 1:52 am
by The_Anomaly
Holy heck. Do my eyes deceive me? Or did a one-post member actually use the CODE tags correctly??

No, that's impossible. He must be some forum regular that got a new account for some reason :D

(Sorry for the useless post, but about 99.9% of all questions that are asked in this forum are asked without the CODE tags--and even if they have the tags, they don't use them right :) )

Re: Query SQL database in an FTP server (some code included)

Posted: Tue Sep 30, 2008 2:31 am
by panic!
califdon wrote:An FTP server is one piece of software, a Web server is another piece of software, a MySQL database server is still a different piece of software. The only way you can connect to a server is to use its domain name or IP address (using an authorized username and password). If that's really an FTP server, it cannot respond to a database connection request.
I see what you're saying but SQLite databases can be used straight off the file system and PDO acts as the database server by opening the .db file and interpreting it.

The problem is that PDO constructor isn't using the same connection as your FTP connection pointer, I'm not entirely sure if you can make it use the pointer either, If you're just doing select queries just download the db file over FTP and query it from the local file system.
Hockey wrote:As it's already been said...

MySQL is a RDBMS and servers usually accept remote connections. MySQL is configured by default to NOT allow remote connections but it is quite possible.
He's using SQLite, you can see in the PDO connection string.


Why does everyone assume MySQL? :)

Re: Query SQL database in an FTP server (some code included)

Posted: Tue Sep 30, 2008 2:53 am
by MALLON
Thanks, I appreciate the thumbs up for using the code tags correctly. I always try to post my questions in the most concise manner possible, it usually gets me better responses and helps the reader in understanding my question.

I've also solved my issue on this topic. It's a terrible, awful solution, but it works (btw, yes it really is an FTP server).

I ftp_get() the database file, then I run the queries on it locally to my PHP file. It's not efficient because every time somone visits that page it has to re-download the entire database, but it seems to be working. It's not a huge database either. I can't see it ever growing larger than 1 MB in size. Biggest I've had it so far is about 350k. I couldn't seem to mess up the FTP download by refreshing the page a ton either, so maybe the file downloading is relatively safe from corruption from too many people making it 'download' all at once.

Anyway, that's what I've come up with. If someone has a better idea, I'd love to hear it.

Re: Query SQL database in an FTP server (some code included)

Posted: Tue Sep 30, 2008 3:09 am
by The_Anomaly
The fact you have to download the thing for every single visit is pretty atrocious. If you really have to use that approach, is there any way you could set some type of flag when someone updates it? This way, if the DB has not changed from your local copy, there's no reason to redownload it.

Not sure about the details of your situation, but just throwing it out there. Maybe it's a file on the server that has some boolean set whenever the FTP server's copy is changed that can be checked every time a visitor comes or something.

Re: Query SQL database in an FTP server (some code included)

Posted: Tue Sep 30, 2008 3:27 am
by MALLON
Yeah, that's a pretty good idea about setting a flag. I'll probably just have a txt file with either a 1 or 0 in it, and I'll have the php page first open that txt file and see the flag, if 0, then redownload the database.

I run a racing-type mod of counter-strike, and people get points on positions they finish in.

http://xyzcrew.info/phpQuery.php is the results of my effort. It's a plain looking page, but it's only for an ingame scoreboard thing.

So, yeah...

Thanks guys.

--MALON

P.S. Yes, I'm MALON from that list of people. I spelled my fricking name wrong when creating this account lol.