Page 1 of 1

Selecting multiple tables on database for later use in Flash

Posted: Tue Sep 01, 2009 5:41 am
by agebpt
Hello all,

I've researched but am yet to find a solution...
I currently have a site I'm constructing in Flash, and I'm using PHP and a mySQL db to ensure the content on the flash page is database driven.
I currently have one php file, which connects to the DB in the usual way and outputs all fine, but I need to print out some information from multiple tables on this one page. So for instance I have one table which holds all the content for, say the Home Page, consisting of simple welcome text and title. I then have another table storing the latest news, which i intended to have a snippet of on that same Home Page as the welcome text, so the user could read a bit then click through to the news page to read more.

Whilst researching, the only logical way I could see that other people suggest to connect to multiple tables within one SELECT query is using UNION?? I couldn't find anything on php.net about this, but assumed as it comes up quite a lot it must be used. I get this error:
Notice: Undefined index: news_title in /Applications/MAMP/htdocs/flashSite/testing.php on line 22
This applies to each field in that second news table I'm trying to connect to. I've tested in another php file, just connecting to this db and printing out details from it and it works fine, but won't work combined with the other database in one page. Can this be done (i.e. connect to two database tables in one PHP file SELECT statement and print out the relevant fields for use later on, or am i going about this in completely the wrong way!?!? Any advice would be hugely appreciated. Below is my code:

Code: Select all

 
<?php 
//variables for connecting to database
include("db_connect.php");
loginInfo();
 
mysql_pconnect ($host, $user, $pass) or die ("Login info is incorrect") ; 
mysql_select_db ($database);
 
$qResult = mysql_query ("SELECT id,home_title,home,about FROM $table UNION SELECT id,news_title,news_post,date FROM $tableTwo");
 
$nRows = mysql_num_rows($qResult);
 
//string var for first db table
$rString =""; 
//string var for 2nd db table
$newsString = ""; 
 
$row = mysql_fetch_array($qResult);
 
//the & are for use in Flash later on
$rString .="&ID"."=".$row['id']."&home_title"."=".$row['home_title']."&home"."=".$row['home']."&about"."=".$row['about'];
$newsString .="&ID"."=".$row['id']."&newsTitle"."=".$row['news_title']."<br>"."&date"."=".$row['date']."<br><br>"."&newsPost"."=".$row['news_post'];;
 
// printing it to the document
echo nl2br($rString."&"); 
echo $newsString . "<br>";
 
?>
 
Cheers for reading

Re: Selecting multiple tables on database for later use in Flash

Posted: Tue Sep 01, 2009 6:10 am
by Eran
If there is a relationship between the tables, then use a JOIN. If there is none, simply execute separate tables. UNION is for collecting similarly structured data together. Notice that in UNION, the fields of the first table in the union would be used as associative keys unless specified otherwise using aliases. Dump the rows you get from the database and you should see what I mean.

Re: Selecting multiple tables on database for later use in Flash

Posted: Tue Sep 01, 2009 6:44 am
by agebpt
Hi pytrin,

Thanks for your fast response!

Think as you say, its best to not use the UNION in this instance.
If there is a relationship between the tables, then use a JOIN. If there is none, simply execute separate tables.
When you say relationship, I'm assuming you mean between the information in the databases, so similar content and field names. I guess they are reasonably similar, but I might be better off executing the separate tables as you say - this is what I was struggling with unfortunately, I wasn't sure how to execute two tables inside the same php file, because when i try to execute multiple select queries i end up with numerous errors, especially with running out of disk space, which is why i tried the UNION statement.

Could you possibly explain a bit more with how to go about executing the separate tables within the same php file if that's what you meant? Or do I need to execute them in different php files - I could do with printing out the info all in one page at some point I think.

Thanks again for the feedback.

Re: Selecting multiple tables on database for later use in Flash

Posted: Tue Sep 01, 2009 7:41 am
by Eran
Sorry, I meant executing separate queries... slip of tongue
Retrieve the unrelated information with separate queries and use it in your scripts.

Code: Select all

$result = mysql_query ("SELECT id,home_title,home,about FROM $table");
$secondResult = mysql_query("SELECT id,news_title,news_post,date FROM $tableTwo");

Re: Selecting multiple tables on database for later use in Flash

Posted: Tue Sep 01, 2009 8:06 am
by agebpt
Thanks pytrin.

Have been going over that in my head for days, just needed someone else to point me in the right direction!! Works perfectly now, awesome!