mysql INSERT INTO...SELECT with multiple tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bwv2
Forum Commoner
Posts: 83
Joined: Fri Jun 10, 2005 11:50 am
Location: AZ

mysql INSERT INTO...SELECT with multiple tables

Post by bwv2 »

I'm trying to insert information into a single table from a bunch of separate tables. I have written the following code, but nothing is being written to the table. All of the source tables are full and should query, and no errors are being shown, but the table is coming up empty. Anyone see anything illegal in this query?

Code: Select all

$sql="INSERT INTO seasonalresource (user,title,jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec,winc,wink,sprc,sprk,sumc,sumk,falc,falk,win,spr,sum,fal)"
			."SELECT variables.username,"
					."variables.title,"
					.$username."_tmp_sun.jan,"
					.$username."_tmp_sun.feb,"
					.$username."_tmp_sun.mar,"
					.$username."_tmp_sun.apr,"
					.$username."_tmp_sun.may,"
					.$username."_tmp_sun.jun,"
					.$username."_tmp_sun.jul,"
					.$username."_tmp_sun.aug,"
					.$username."_tmp_sun.sep,"
					.$username."_tmp_sun.oct,"
					.$username."_tmp_sun.nov,"
					.$username."_tmp_sun.dec,"
					.$username."_tmp_wind.winWeibC,"
					.$username."_tmp_wind.winWeibK,"
					.$username."_tmp_wind.sprWeibC,"
					.$username."_tmp_wind.sprWeibK,"
					.$username."_tmp_wind.sumWeibC,"
					.$username."_tmp_wind.sumWeibK,"
					.$username."_tmp_wind.falWeibC,"
					.$username."_tmp_wind.falWeibK,"
					."NULL,NULL,NULL,NULL "
			."FROM variables, "
					.$username."_tmp_sun,"
					.$username."_tmp_wind "
			."WHERE variables.username='$username' "
				."AND variables.title='$title' "
				."AND ".$username."_tmp_sun.sunlatitude=$sunLat "
				."AND ".$username."_tmp_sun.sunlongitude=$sunLon "
				."AND ".$username."_tmp_wind.latitude=$windLat "
				."AND ".$username."_tmp_wind.longitude=$windLon";
		$result = mysql_query($sql) or die("couldn't write values to table: ".mysql_error());
Any help would be appreciated.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Does the SELECT query by itself return the expected records?

Mac
bwv2
Forum Commoner
Posts: 83
Joined: Fri Jun 10, 2005 11:50 am
Location: AZ

Post by bwv2 »

The SELECT query gives nothing. Empty. Any ideas? Is it something with the way I'm drawing entries out of different tables?
I printed my $sql string and it gives me this:
sql:

INSERT INTO
seasonalresource
(username,title,sunjan,sunfeb,sunmar,sunapr,sunmay,sunjun,sunjul,sunaug,sunsep,sunoct,sunnov,sundec,winc,wink,
sprc,sprk,sumc,sumk,falc,falk,winavg,spravg,sumavg,falavg)
SELECT
variables.username,variables.title,brad_tmp_sun.jan,brad_tmp_sun.feb,brad_tmp_sun.mar,brad_tmp_sun.apr,
brad_tmp_sun.may,brad_tmp_sun.jun,brad_tmp_sun.jul,brad_tmp_sun.aug,brad_tmp_sun.sep,brad_tmp_sun.oct,
brad_tmp_sun.nov,brad_tmp_sun.dec,brad_tmp_wind.winWeibC,brad_tmp_wind.winWeibK,brad_tmp_wind.sprWeibC,
brad_tmp_wind.sprWeibK,brad_tmp_wind.sumWeibC,brad_tmp_wind.sumWeibK,brad_tmp_wind.falWeibC,
brad_tmp_wind.falWeibK,NULL,NULL,NULL,NULL FROM variables, brad_tmp_sun,brad_tmp_wind
WHERE
variables.username='brad'
AND variables.title='Testing'
AND brad_tmp_sun.sunlatitude=33.264
AND brad_tmp_sun.sunlongitude=-111.037
AND brad_tmp_wind.latitude=33.339
AND brad_tmp_wind.longitude=-111.25
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

I notice there's no JOIN in your SELECT... is this intentional?
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

I think the problem is $username...

If you look at the select I doubt if the variables table has a field for every different $username that can be possible in that select.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Yeah, unless the database design is more than a little poor. You'd get errors in the output if what you're saying is right Panama Jack. I can't think why there would be so many tables neither, if the table name itself is variable :?
bwv2
Forum Commoner
Posts: 83
Joined: Fri Jun 10, 2005 11:50 am
Location: AZ

Post by bwv2 »

Thanks for the ideas. I'm not a database architect by any stretch of the imagination, but in my defense these tables are temporary tables filled with values used in calculations. The tables are only needed in one page, then are deleted. That's why I'm pulling from 3 different tables. I made the tables start with $username so that 2 simultaneous users wouldn't experience cross contamination.

I looked more into the JOIN syntax and opted to change the way the table "seasonalresource" is made. Now it is very close to working, but I need to educate myself a little more on index fields to make things work more smoothly.

I'm basically trying to teach myself how to program with HTML, PHP and mySQL with little to no programming background (I'm a mechanical engineer). I have been at it for almost a year and have made great progress. Database design is really deserving of more time than I have, so I'm learning it a little more slowly than everything else. All is going well though, and I'm at the final stages of starting my own web based company. It's help from more advanced programmers like yourselves that helps me learn more quickly, so I appreciate the help. Soon I will hand it over to a real web programmer to clean up my work and keep everything straight. Just wanted to get that straight so you don't lose sleep over why my code is so bumbly.

Cheers.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I am wondering whether using views would help improve the readability of the query.
But note that views are available only from mysql 5.1
bwv2
Forum Commoner
Posts: 83
Joined: Fri Jun 10, 2005 11:50 am
Location: AZ

Post by bwv2 »

I will look into views to see what that does, but for now I have made the query work. It seemed to be having trouble with the way I was joining the tables using a variable. i.e. it didn't like the table.column syntax I was using. Instead I queried each table and made arrays for each of the 3 tables, then pulled values out of the arrays to fill my table. for example:

Code: Select all

$sql="SELECT * FROM table WHERE username='$username'";
$result=mysql_query($sql);
$temp_array=mysql_fetch_array($result);

$sql="INSERT INTO seasonalresource " 
          ."SELECT ".$temp_array['value1'].",".....";
and so on. It works, so I'm happy for now. Thanks for the help.
Post Reply