select from 2 tables

[Hooch]

Hey all. I have a need to select data from 2 tables. Here's my present code

<?phpinclude '../includes/db.php';echo"<br>";$result = mysql_query("SELECT id, area, inout, COUNT(*) FROM railcar WHERE area = 'AA' AND inout = 1 GROUP BY area;")or die(mysql_error());while($r=mysql_fetch_array($result)) { $field4 = 5;$field1 = $r["COUNT(*)"]; echo "Adipic has $field1 railcar(s) onsite";echo "  "; }if ( $field1 > $field4 ) {$field2 = $field4 - $field1;$field3 = $field2 - $field2 - $field2;echo " <br><b>WARNING!!</b><br>";echo "$field3"; echo " car(s) over the allotted limit!";echo "<br>$field4 is the recommended limit.";	}?>

Where you see "$field4 = 5;", I need the 5 to be from another table. This number can change anytime from an update page made for that reason.The table name is limits and the row I need the info from is AAThanks in advance for your help.Hooch

[justsomeguy]

SELECT r.id, r.area, r.inout, COUNT(r.*) as nr, l.AA as field4 FROM railcar r, limits l WHERE r.area = 'AA' AND r.inout = 1 GROUP BY r.area;You can use that query, and find it in $r['field4'] (I also renamed the count aggregator to nr, so you can use $r['nr'] instead of $r['COUNT(*)']).Or, you can also do a separate query before the while loop and get the value from the limits table first, save it, and then do your next query.

[Hooch]

I think the query is off a bit. field4 is taking the info from the limits table. The row it is taking it from is AA. (which for now is 5) Not to confuse the AA from the railcar table. (which is the entry for area)Here's the 2 tables...

CREATE TABLE `limits` (  `AA` tinyint(4) NOT NULL default '1',  `HMD` tinyint(4) NOT NULL default '1',  `KA` tinyint(4) NOT NULL default '1',  `Peroxide` tinyint(4) NOT NULL default '1',  `Suva` tinyint(4) NOT NULL default '1') TYPE=MyISAM;

CREATE TABLE `railcar` (  `id` int(11) NOT NULL auto_increment,  `date` varchar(35) NOT NULL default '',  `carid` varchar(35) NOT NULL default '',  `tare` int(20) NOT NULL default '0',  `gross` int(20) default NULL,  `net` int(20) default NULL,  `area` text NOT NULL,  `commodity` varchar(35) NOT NULL default '',  `inout` int(5) NOT NULL default '0',  `fullempty` int(5) NOT NULL default '0',  `comments` varchar(255) default NULL,  PRIMARY KEY  (`id`)) TYPE=MyISAM AUTO_INCREMENT=38;

[justsomeguy]

I think that query should work, did you try it? It does return the AA field from the limits table, I was assuming there is only 1 row in the table. But you can always just get the value before the loop.

include '../includes/db.php';echo"<br>";$limits = mysql_fetch_assoc(mysql_query("SELECT * FROM limits"));//now you can access $limits['aa'] when you need to$result = mysql_query("SELECT id, area, inout, COUNT(*) FROM railcar WHERE area = 'AA' AND inout = 1 GROUP BY area;")or die(mysql_error());while($r=mysql_fetch_array($result)) { $field4 = $limits['aa'];...

Also, if you are printing a variable:echo "$field3";you don't need the quotes:echo $field3;Why do people do that?

[Hooch]

Oh man, that's a better solution for me. I can understand it. I did try the 1st fix you so kindly made, but it had an error inthe table selection. I tried a cpl things, but I never got it. This last fix you made opens up countless possiblities for me too. As far as the quotes around the variables, I only go by the tutorialsI follow, or whatever I learn from kind people as yourself.I started learning PHP and mysql a cpl months ago. I have nocomputer schooling at all, just a hobby I like to play with after work. Again, thank you very much for your time and effort. Your a very generous person to devote so much time to peoplelike me. Hooch

[justsomeguy]

No problem, good luck.