Jump to content

MySQL Statement - Help

hyperdallas

By hyperdallas
in MySQL Help

Recommended Posts

hyperdallas Newbie

hyperdallas

Posted
Posted

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..
joine.jpg


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.

Link to comment
https://forums.phpfreaks.com/topic/275987-mysql-statement-help/
Share on other sites
awjudd Member

awjudd

Posted
Posted

 

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

hyperdallas Newbie

hyperdallas

Posted
  • Author
Posted

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>
awjudd Member

awjudd

Posted
Posted

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>

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.