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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
MALLON
Forum Newbie
Posts: 10
Joined: Mon Sep 29, 2008 9:48 pm

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

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

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

Post 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.
User avatar
The_Anomaly
Forum Contributor
Posts: 196
Joined: Fri Aug 08, 2008 4:56 pm
Location: Tirana, Albania

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

Post 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 :) )
User avatar
panic!
Forum Regular
Posts: 516
Joined: Mon Jul 31, 2006 7:59 am
Location: Brighton, UK

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

Post 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? :)
MALLON
Forum Newbie
Posts: 10
Joined: Mon Sep 29, 2008 9:48 pm

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

Post 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.
User avatar
The_Anomaly
Forum Contributor
Posts: 196
Joined: Fri Aug 08, 2008 4:56 pm
Location: Tirana, Albania

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

Post 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.
MALLON
Forum Newbie
Posts: 10
Joined: Mon Sep 29, 2008 9:48 pm

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

Post 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.
Post Reply