Hi guys, i am currently working on a project that queries an inventory database through the use of radio buttons, for example; If the first button FOR THE Item(HAMMERS)is clicked, the output should be the name of the item, the number sold and the total profit(calculations) into a table. the way i have it now, when io click on the radio button my output comes back as the results for all the items in the table, i want it to display only the information about hammers, then only about each individual item when the corresponding radio button is clicked. any help would be greatly appreciated!!!

Here's what i have so far, There are 4 radio buttons for each item in the table that if selected the output should be for example: (Hammer - number sold - total profit) and so on and so forth if the other 3 are selected. Then there's a 5th radio button that gives the total value of all items in the inventory.
I have a great idea of how this should work but my syntax skills suck, Thanks in advance for any help given.

<?PHP

	$server = 'localhost';
 	$user = 'root';
	$password = '';
	
	$mydb = 'Tools';
	$table_name = 'Inventory';
	$SQLcmd = "select * from $table_name";
	$connect = mysql_connect($server, $user, $password);

	if (!$connect) {
		die ("Cannot connect to the $server using $user");  }
	else {
		mysql_select_db($mydb, $connect);
		
		$result = mysql_query($SQLcmd, $connect) or die(mysql_error());				
		print '<table border = 1>';
		print '<tr><td>Item Name</td><td>Number Sold</td><td>Profit</td></tr>';
		
		while($row = mysql_fetch_array($result)){
		

			print '<tr>';
			print "<td> {$row['Name']} </td>";
			
			print "<td> {$row['Sold']} </td>";
			
			
			$Profits = ($row['Price'] - $row['Cost']) * $row['Sold'] ;
			
			print "<td> $Profits </td>";
			
			print '</tr>';
			} 
		print '</table>';
		}
		
	mysql_close($connect);
	
?>
Member Avatar for diafol

So in other words you want us to provide you with a SQL string?

if(isset($_POST['name']) && in_array($_POST['name'],array('hammer','chisel','saw','spanner','all'))){
  if($_POST['name'] == 'all')$where="";
}else{
  $post = mysql_real_escape_string($_POST['name']);
  $where = " WHERE `Name` = '$post'";
}
$SQLcmd = "select * from $table_name$where";

??

You could do that or for expansion's sake you'd have a different check for data validity (maybe use the tool's 'id' instead of the name).

ok i added that bit of code but i keep getting an undefined index for "name" and also when i use "tools"

Member Avatar for diafol

Perhaps if you actually posted your form code we could see what your form field names were...

<html>
<body>
<form name="input" action="SQLQuery.php" method="post">

 <input type="radio" name="hammers" value="hammers" /> Total profit and number sold for Hammers<br />
 <input type="radio" name="wrenches" value="wrenches" /> Total profit and number sold for Wrenches<br />
 <input type="radio" name="pliers" value="pliers" /> Total profit and number sold for Pliers<br />
 <input type="radio" name="handsaw" value="handsaw" /> Total profit and number sold for Handsaw<br />
 <input type="radio" name="total" value="total" /> Total value of items in Inventory<br /><br />
 
 <input type="submit" value="Submit">
</form>
<p>
Click the "Submit" button to query the record.
</p>
</body>
</html>
Member Avatar for diafol

I think you've misunderstood the use of radio - they should all have the same name attribute, i.e. name="name". You seem to be using these are glorified checkboxes. This is not their purpose. Optionboxes are there to chose either/or. Also you should choose a default value to be checked (checked="checked").

then this should work:

if(isset($_POST['name']) && in_array($_POST['name'],array('hammer','wrenches','pliers','handsaw','total'))){
  if($_POST['name'] == 'all')$where="";
}else{
  $post = mysql_real_escape_string($_POST['name']);
  $where = " WHERE `Name` = '$post'";
}
$SQLcmd = "SELECT * FROM $table_name$where";

if your tablename and fieldname (`Name`) are correct.

ok i had them named 'item' the first time when you advised the code and it was still giving me that error, i also tried 'tools' as well and got the same result

Here's what i have now, and the result i get is just the table headers no names, sold or totals

<?PHP

	$server = 'localhost';
 	$user = 'root';
	$password = '';
	
	$mydb = 'Tools';
	$table_name = 'Inventory';
	//$SQLcmd = "select * from $table_name";
	 $post = mysql_real_escape_string($_POST['items']);
	  $where = " WHERE `Name` = '$post'";
	$SQLcmd = "SELECT * FROM $table_name$where";
	$connect = mysql_connect($server, $user, $password);

	if (!$connect) {
		die ("Cannot connect to the $server using $user");  }
	else {
		mysql_select_db($mydb, $connect);
		
		$result = mysql_query($SQLcmd, $connect) or die(mysql_error());				
		print '<table border = 1>';
		print '<tr><td>Item Name</td><td>Number Sold</td><td>Profit</td><td>Inventory Total</td></tr>';
		
	if(isset($_POST['items']) && in_array($_POST['items'],array('hammer','wrenches','pliers','handsaw','total'))){
	if($_POST['items'] == 'total')$where="";
	}
	else{
 



		
		while($row = mysql_fetch_array($result)){
		

			print '<tr>';
			print "<td> {$row['Name']} </td>";
			
			print "<td> {$row['Sold']} </td>";
			
			
			$Profits = ($row['Price'] - $row['Cost']) * $row['Sold'] ;
			
			print "<td> $Profits </td>";
			
			print '</tr>';
			} 
		print '</table>';
		}}
		
	mysql_close($connect);
	?>
$where = " WHERE `Name` = '$post'";

The name of the field, in this case "Name", does not need to be in quotes, single or otherwise. Not sure that this is your problem, but it may be part of it.

add a variable to catch the value of your your chosen item $tools = $_POST, then display the selected item via id

<?php

	$tools = $_POST['tools'];

	$server = 'localhost';
 	$user = 'root';
	$password = '';
	
	$mydb = 'Tools';
	$table_name = 'Inventory';
	$SQLcmd = "select * from $table_name WHERE id=".$tools;
	$connect = mysql_connect($server, $user, $password);

	if (!$connect) {
		die ("Cannot connect to the $server using $user");  }
	else {
		mysql_select_db($mydb, $connect);
		
		$result = mysql_query($SQLcmd, $connect) or die(mysql_error());				
		print '<table border = 1>';
		print '<tr><td>Item Name</td><td>Number Sold</td><td>Profit</td></tr>';
		
		while($row = mysql_fetch_array($result)){
		

			print '<tr>';
			print "<td> {$row['Name']} </td>";
			
			print "<td> {$row['Sold']} </td>";
			
			
			$Profits = ($row['Price'] - $row['Cost']) * $row['Sold'] ;
			
			print "<td> $Profits </td>";
			
			print '</tr>';
			} 
		print '</table>';
		}
		
	mysql_close($connect);
	
?>

then put the item id's on it's respective radio field

<html>
<body>
<form name="input" action="SQLQuery.php" method="post">

 <input type="radio" name="tools" value="1" /> Total profit and number sold for Hammers<br />
 <input type="radio" name="tools" value="2" /> Total profit and number sold for Wrenches<br />
 <input type="radio" name="tools" value="3" /> Total profit and number sold for Pliers<br />
 <input type="radio" name="tools" value="4" /> Total profit and number sold for Handsaw<br />
 <input type="radio" name="tools" value="total" /> Total value of items in Inventory<br /><br />
 
 <input type="submit" value="Submit">
</form>
<p>
Click the "Submit" button to query the record.
</p>
</body>
</html>

values 1,2,3 and 4 are just a sample id, changed it to the id on your table

ok did that but now i'm getting;
Undefined index: "tools" Line 5 which is where the tools variable is set = to POST

did you change the name of radios to 'tools'?

ok i figured out what the issue was, i had my id's misspelled everything works fine except when i click the radio to show the total inventory value. Here's what i have, but it think the problem has something to do with the row and column of my table for
the totals i'm thinking i should have just created a column for the total value instead of a row and column.
my table:

mysql> select * from inventory;
+-------------+------+-------+------+-----------+---------+--------+
| Name | Cost | Price | Sold | Inventory | Profits | Totals |
+-------------+------+-------+------+-----------+---------+--------+
| Hammer | 5 | 12 | 122 | 26 | NULL | NULL |
| Wrench | 2 | 4 | 9 | 155 | NULL | NULL |
| Screwdriver | 3 | 5 | 55 | 115 | NULL | NULL |
| Pliers | 2 | 3 | 26 | 75 | NULL | NULL |
| Handsaw | 6 | 9 | 33 | 93 | NULL | NULL |
| InvValue | NULL | NULL | NULL | NULL | NULL | 1076 |
+-------------+------+-------+------+-----------+---------+--------+
6 rows in set (0.00 sec)

mysql> describe inventory;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Name | varchar(30) | YES | | NULL | |
| Cost | int(11) | YES | | NULL | |
| Price | int(11) | YES | | NULL | |
| Sold | int(11) | YES | | NULL | |
| Inventory | int(11) | YES | | NULL | |
| Profits | int(11) | YES | | NULL | |
| Totals | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+

here is my code with the buttons working except the total value button

<?PHP

	$server = 'localhost';
 	$user = 'root';
	$password = '';
	
	$mydb = 'Tools';
	$table_name = 'Inventory';
	//$SQLcmd = "select * from $table_name";
	 $post = mysql_real_escape_string($_POST['items']);
	  $where = " WHERE Name = '$post'";
	$SQLcmd = "SELECT * FROM $table_name$where";
	$connect = mysql_connect($server, $user, $password);

	if (!$connect) {
		die ("Cannot connect to the $server using $user");  }
	else {
		mysql_select_db($mydb, $connect);
		
		$result = mysql_query($SQLcmd, $connect) or die(mysql_error());				
		print '<table border = 1>';
		print '<tr><td>Item Name</td><td>Number Sold</td><td>Profit</td><td>Inventory Value</td></tr>';
		
	if(isset($_POST['items']) && in_array($_POST['items'],array('hammer','wrench','screwdriver','pliers','handsaw','invvalue'))){
	if($_POST['items'] == 'total')$where="";
	}else{
 		
		while($row = mysql_fetch_array($result)){
		

			print '<tr>';
			print "<td> {$row['Name']} </td>";
			
			print "<td> {$row['Sold']} </td>";
			
			
			$Profits = ($row['Price'] - $row['Cost']) * $row['Sold'] ;
			
			print "<td> $Profits </td>";
			print "<td> {$row['Totals']} </td>";
			print '</tr>';
			} 
		print '</table>';
		}}
		
	mysql_close($connect);
	?>
Member Avatar for diafol

Totals should be acalculated field?

of course total not gonna return anything because it's empty, it has no value on the 'total' field on the table. Unless you want to compute the total based on some formulas

ok so my question is how would i calculate them, i did the calculations for $profits
but how would i do it for total value, how can i add a single column of numbers to get the result. would i do it like the $profits variable?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.