hyperdallas Posted March 21, 2013 Share Posted March 21, 2013 I'm new to PHP and learning more and more everyday the more I play with things.I'm trying to write a MYSQL statement to output all results via php to a page. I have the PHP code sweet, but having some dramas trying to compose the SQL query.Here is what I am trying to achieve..The lines are the relationships between fields. Could someone gimme a hand writing an SQL statement to satisfy this? Thanks heaps!! I have tried for a few days to do this and research, but its not getting me anywhere. Thanks. Quote Link to comment Share on other sites More sharing options...
Solution awjudd Posted March 21, 2013 Solution Share Posted March 21, 2013 SELECT d.label, d.serial, d.exe, d.eal, d.epl, dt.Model, m.Manufacturer FROM fac_Device AS d JOIN fac_DeviceTemplates AS dt ON d.TemplateID = dt.TemplateID JOIN fax_Manufacturer AS m ON dt.ManufacturerID = m.ManufacturerID WHERE d.label = 'server1' A few simple joins and you are good to go. ~awjudd Quote Link to comment Share on other sites More sharing options...
hyperdallas Posted March 23, 2013 Author Share Posted March 23, 2013 (edited) Thanks awjudd... but no good. any ideas??? the idea of the query is to select all and list the detail... Here is the error: Unknown column 'd.label' in 'field list' Here is the php script: <html> <body> <?php $username="root"; $password=""; $database="db"; mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="SELECT `d.label` , `d.exe` , `d.eal` , `d.epl` , `dt.Model` , `m.Manufacturer` FROM `fac_Device` AS d JOIN `fac_DeviceTemplate` AS dt ON `d.TemplateID` = dt.TemplateID JOIN `fac_Manufacturer` AS m ON `dt.ManufacturerID` = `m.ManufacturerID`"; $result=mysql_query($query)or die(mysql_error()); $num=mysql_numrows($result)or die(mysql_error()); mysql_close(); ?> <IMG SRC="http://localhost/dcim/images/logo.png"></IMG> <table border="0" cellspacing="2" cellpadding="2"> <tr> <h2><B>Report<BR></h2></B> <b></B><BR><BR> <td><B><font face="Arial, Helvetica, sans-serif">Device Name</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">Model</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">Manufacturer</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">OS Version</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">EPL</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">EAL</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">EXEMPT</font></B></td> </tr> <?php $i=0; while ($i < $num) { $f1=mysql_result($result,$i,"d.label"); $f2=mysql_result($result,$i,"dt.Model"); $f3=mysql_result($result,$i,"m.manufacturer"); $f4=mysql_result($result,$i,"d.softver"); $f5=mysql_result($result,$i,"d.epl"); $f6=mysql_result($result,$i,"d.eal"); $f7=mysql_result($result,$i,"d.exe"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f6; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td> </tr> <?php $i++; } ?> </body> </html> Edited March 23, 2013 by hyperdallas Quote Link to comment Share on other sites More sharing options...
awjudd Posted March 23, 2013 Share Posted March 23, 2013 You copied my query wrong. You included the aliased field in your backticks. Because of that it is looking for `d.label` verbatim. Also your script is wrong because they don't come out with the alias up front, they come out with the names as they appears. <html> <body> <?php $username="root"; $password=""; $database="db"; mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="SELECT d.label, d.serial, d.exe, d.eal, d.epl, dt.Model, m.Manufacturer FROM fac_Device AS d JOIN fac_DeviceTemplates AS dt ON d.TemplateID = dt.TemplateID JOIN fax_Manufacturer AS m ON dt.ManufacturerID = m.ManufacturerID"; $result=mysql_query($query)or die(mysql_error()); $num=mysql_numrows($result)or die(mysql_error()); mysql_close(); ?> <IMG SRC="http://localhost/dcim/images/logo.png"></IMG> <h2><B>Report<BR></h2></B> <b></B><BR><BR> <table border="0" cellspacing="2" cellpadding="2"> <tr> <td><B><font face="Arial, Helvetica, sans-serif">Device Name</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">Model</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">Manufacturer</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">OS Version</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">EPL</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">EAL</font></B></td> <td><B><font face="Arial, Helvetica, sans-serif">EXEMPT</font></B></td> </tr> <?php $i=0; while (($row=mysql_fetch_assoc($result))!==FALSE { echo '<tr> <td><font face="Arial, Helvetica, sans-serif">', $row['label'], '</font></td> <td><font face="Arial, Helvetica, sans-serif">', $row['Model'], '</font></td> <td><font face="Arial, Helvetica, sans-serif">', $row['Manufacturer'], '</font></td> <td><font face="Arial, Helvetica, sans-serif">', $row['serial'], '</font></td> <td><font face="Arial, Helvetica, sans-serif">', $row['exe'], '</font></td> <td><font face="Arial, Helvetica, sans-serif">', $row['eal'], '</font></td> <td><font face="Arial, Helvetica, sans-serif">', $row['epl'], '</font></td> </tr>'; } ?> </table> </body> </html> Quote Link to comment Share on other sites More sharing options...
hyperdallas Posted March 23, 2013 Author Share Posted March 23, 2013 (edited) your code causes:Parse error: syntax error, unexpected '{' in C:\xampp\htdocs\report_certification.php on line 33 while (($row=mysql_fetch_assoc($result))!==FALSE { Edited March 23, 2013 by hyperdallas Quote Link to comment Share on other sites More sharing options...
awjudd Posted March 24, 2013 Share Posted March 24, 2013 Because I'm missing a closing bracket on that line. while (($row=mysql_fetch_assoc($result))!==FALSE) { Quote Link to comment Share on other sites More sharing options...
hyperdallas Posted March 24, 2013 Author Share Posted March 24, 2013 Thanks mate.. appreciate it. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.