Searching Google is like finding a needle in a haystack...

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
elsiebuck
Forum Newbie
Posts: 6
Joined: Tue Jan 06, 2015 5:46 pm

Searching Google is like finding a needle in a haystack...

Post by elsiebuck »

Is there a simple way to read one mysql database written by one program and create a new one (also mysql) that a different program can read? I'd want to use php so I can understand how it is done. I have some other projects I want to convert to php and mysql. It would be a good learning experience for me.

I started this looking for (Google search) php movie management database. The requirements were, it had to read the movies I had on my fileserver, cross reference with IMDb. All was good, but I couldn't access the program from the other computers on the network. When I did find a program that would, it wouldn't read the movies from the fileserver. I couldn't have both.

My solution, convert the database created by #1 to be readable by #2.

I'd write the whole thing myself, but it wouldn't be a pretty as either of those two. I'd name the two programs, but I think that might be in bad taste. Of course if any one knows of one that will do what I want to do, why re-create the wheel?

Did I mention, Google is of no help...
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Searching Google is like finding a needle in a haystack.

Post by requinix »

The only way you can convert the database from the first program to be readable by the second is if the second explicitly provided some way to do that. Or you wanted to go through the work of learning everything about the two database formats and manually converting them.

The first was fine except you couldn't access it from another computer on the same network, right? That's a firewall matter and it's pretty easy to solve given a couple answers:
1. What URL are you using to access the application on the (first) computer?
2. Can the second computer even reach the first at all? Browse files, even see it on the network?
3. What operating system on the first computer?
elsiebuck
Forum Newbie
Posts: 6
Joined: Tue Jan 06, 2015 5:46 pm

Re: Searching Google is like finding a needle in a haystack.

Post by elsiebuck »

The first program was java - runs under any OS. The problem was the first computer used 127.0.0.1 The second computer had to use 192.168.15.146 to access the first. If I changed the bind address to 192.168.15.146 then other programs ceased to work. As I stated, the first program didn't install with intranet access in mind.

Your question - what url to access the program on the first - well, ya just click the "program".
Your second question - oh yeah, I have complete connectivity between 13 computers/laptops/cell phones/notebooks/MyClouds.
Your 3rd- linux debian wheezy

Your first statement - the obvious - I looked at mysql. I can see what table is what. I can also see what the second program created in it's database. I just needed to know how to write the conversion. I would do all the rest...
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Searching Google is like finding a needle in a haystack.

Post by requinix »

...Java. So it wasn't a PHP application you were using, which you implied when you said "php movie management database".
Can the program bind to 0.0.0.0? How is it that changing the address Java was binding to (I don't even know what it needs a port for...) affected other programs on your computer?

As for program #2, any luck trying to figure out why it "wouldn't read the movies"? Sounds like a huge problem if the program can't even do that so maybe there's a bug report or blog post somewhere with an explanation and workaround.

As for "how to write the conversion", you're on your own. You've already tried Googling and I'm sure that included obvious phrases such as "import program1 database to program2" so there isn't really anybody out there who can help because nobody has tried it before. Figure out how data maps between the two and write a lot of INSERTs and such to get it across.
elsiebuck
Forum Newbie
Posts: 6
Joined: Tue Jan 06, 2015 5:46 pm

Re: Searching Google is like finding a needle in a haystack.

Post by elsiebuck »

Java is the first program. The second is web based php - I can access it from my other computers via local lan.
Unless I'm not understanding, it seems I can't connect/select to both databases at the same time. So, I'm looking at other options - perhaps finding an alternative #2 "program". As I previously stated, Google only came up with one program that would read my "files". There were plenty of others that were more desirable, but none (except the one) would read MyCloud/server full of movies. If you know of another, please, I don't really want to re-invent the wheel...
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Searching Google is like finding a needle in a haystack.

Post by requinix »

You can use two databases at once, but they have to be on the same server (same MySQL server) and, naturally, the MySQL user you have needs access to both.

I don't know anything about the Java program. Would it be possible to run the Java itself on the two/various machines separately, but have them both connect to the same database and file server? The answer is "yes" if you can control where the database and files are, via configuration files or whatever.
Firewall stuff does matter for that, too, so it might not work if you tried it right now.
elsiebuck
Forum Newbie
Posts: 6
Joined: Tue Jan 06, 2015 5:46 pm

Re: Searching Google is like finding a needle in a haystack.

Post by elsiebuck »

requinix wrote:You can use two databases at once
mysql_pconnect("localhost","user","secret") or die("error: could not connect to database!")
mysql_select_db("#1")

See why I'm stuck? How does the next line go?

mysql_select_db("#2") ???

The #1 has General_Info within that is Title (text) and IMDb (also text) - the only two fields I want/need.
#2 has videodata within title (text) and imdbID (text) - again the only two fields I want to populate from #1... if it doesn't exist obviously...
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Searching Google is like finding a needle in a haystack.

Post by requinix »

You don't have to do a mysql_select_db(). That changes the default database but you can specify one in your queries manually. You know the table.field syntax? You can tack on the database too like database.table.field.

Code: Select all

first_database.General_Info.Title

Code: Select all

second_database.videodata.title
elsiebuck
Forum Newbie
Posts: 6
Joined: Tue Jan 06, 2015 5:46 pm

Re: Searching Google is like finding a needle in a haystack.

Post by elsiebuck »

Just a quick check - will this work, or am I using an older version of something?

Code: Select all

<?
mysql_pconnect('localhost',"med","secret") or die("Error: Could not connect to database!");
/*mysql_select_db("med")*/
$query="select * FROM med.General_Info.Title";
$result=mysql_query($query);
$num=mysql_numrows($result);

echo "You have "$num" movies."
$updated=0;
$i=0;
while ($i<$num) {
	$title=mysql_result($result,$i,"med.General_Info.Title";
	$result = mysql_query("SELECT videodb.videodb_videodata.id FROM videodb.videodb_videodata WHERE videodb.videodb_videodata.title = \"$title\"");
	$row = mysql_fetch_array($result);
	if($row['videodb.videodb_videodata.id']) {
		/*  Already exists */ }
	else {
		/* If not, create one: */
     	$imdb=mysql_result($result,$i,"med.General_Info.Imdb";
	$date=mysql_result($result,$i,"med.General_Info.Date";
	$filesize=mysql_result($result,$i,"med.Additional_Info.File_Size";
	$audio_codec=mysql_result($result,$i,"med.Additional_Info.Audio_Codec";
	$video_codec=mysql_result($result,$i,"med.Additional_Info.Video_Codec";

		mysql_query("INSERT INTO videodb.videodb_videodata (title) VALUES (\"$title\")");
		mysql_query("INSERT INTO videodb.videodb_videodata (imdb) VALUES (\"$imdb\")");
		mysql_query("INSERT INTO videodb.videodb_videodata (filedate) VALUES (\"$date\")");
		mysql_query("INSERT INTO videodb.videodb_videodata (filesize) VALUES (\"$filesize\")");
		mysql_query("INSERT INTO videodb.videodb_videodata (audio_codec) VALUES (\"$audio_codec\")");
		mysql_query("INSERT INTO videodb.videodb_videodata (video_codec) VALUES (\"$video_codec\")");
		$updated++ }
$i++

echo "You have added "$updated" movies."
?>
Your opinion would be greatly appreciated.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Searching Google is like finding a needle in a haystack.

Post by Weirdan »

You're overwriting the results of outer query inside the loop:

Code: Select all

$result = mysql_query("SELECT videodb.videodb_videodata.id FROM videodb.videodb_videodata WHERE videodb.videodb_videodata.title = \"$title\"");
Also, your inserts are obviously wrong. You want to insert a single entry, but you're doing multiple inserts instead. Probably you wanted something like this:

Code: Select all

mysql_query("INSERT INTO videodb.videodb_videodata (title, imdb, filedate, filesize, audio_codec, video_codec) 
VALUES (\"$title\", \"$imdb\", \"$date\", \"$filesize\", \"$audio_codec\", \"$video_codec\")");
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Searching Google is like finding a needle in a haystack.

Post by requinix »

1. "database.table.field" syntax might not work with mysql_result(). If not, use just the field name.
2. $row will only have the field name "id". It will also be empty if there aren't any rows so trying to get the id will raise a notice.
3. Don't use lots of mysql_result()s. Fetch a row and get the values you need from the array it returned - like you did with $row.

And the many syntax errors, too.
elsiebuck
Forum Newbie
Posts: 6
Joined: Tue Jan 06, 2015 5:46 pm

Re: Searching Google is like finding a needle in a haystack.

Post by elsiebuck »

I only needed $num (total movies) from the outer result. The inner loop was based on $num.
Will mysql know the field name med's title from videodb's title? Is mysql case sensitive? Being med used Title and videodb used title...
videodb isn't empty - it has one movie. That's one way I figured which fields I needed.
Like med, videodb can update from imdb. So any empty fields will get filled in.

Many thanks. Should I get this to work, I'll post. If I can't, I'll post too...
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Searching Google is like finding a needle in a haystack.

Post by Weirdan »

Is mysql case sensitive?
Mysql is case-sensitive about table and database names on a case-sensitive filesystems (most unix filesystems are case-sensitive). Field names are case-insensitive.
Post Reply