Page 1 of 1

mysql INSERT INTO...SELECT with multiple tables

Posted: Mon Jan 09, 2006 12:03 pm
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.

Posted: Mon Jan 09, 2006 12:58 pm
by twigletmac
Does the SELECT query by itself return the expected records?

Mac

Posted: Mon Jan 09, 2006 1:40 pm
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

Posted: Mon Jan 09, 2006 3:50 pm
by Chris Corbyn
I notice there's no JOIN in your SELECT... is this intentional?

Posted: Mon Jan 09, 2006 7:01 pm
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.

Posted: Tue Jan 10, 2006 3:12 am
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 :?

Posted: Tue Jan 10, 2006 9:50 am
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.

Posted: Tue Jan 10, 2006 10:03 am
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

Posted: Tue Jan 10, 2006 11:49 am
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.