Having trouble querying a database

Question

jetlife76 0 Junior Poster in Training

13 Years Ago

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!!!

php

4 Contributors
17 Replies
136 Views
18 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by jetlife76

All 17 Replies

diafol

13 Years Ago

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).

diafol

13 Years Ago

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

diafol

13 Years Ago

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.

vaultdweller123 32 Posting Pro

13 Years Ago

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

Edited 13 Years Ago by vaultdweller123 because: n/a

diafol

13 Years Ago

Totals should be acalculated field?

Reply to this topic

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.

jetlife76 0 Junior Poster in Training · Answer 1 · 2012-02-21T02:12:09+00:00

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);
	
?>

jetlife76 0 Junior Poster in Training · Answer 2 · 2012-02-21T03:29:58+00:00

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

jetlife76 0 Junior Poster in Training · Answer 3 · 2012-02-21T04:43:13+00:00

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

jetlife76 0 Junior Poster in Training · Answer 4 · 2012-02-21T08:54:24+00:00

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

jetlife76 0 Junior Poster in Training · Answer 5 · 2012-02-21T09:15:31+00:00

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);
	?>

jawanda0 0 Newbie Poster · Answer 6 · 2012-02-21T11:14:56+00:00

$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.

jetlife76 0 Junior Poster in Training · Answer 7 · 2012-02-21T12:38:31+00:00

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

vaultdweller123 32 Posting Pro · Answer 8 · 2012-02-21T12:47:57+00:00

vaultdweller123 32 Posting Pro

13 Years Ago

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

jetlife76 0 Junior Poster in Training · Answer 9 · 2012-02-21T13:34:48+00:00

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 | |
+-----------+-------------+------+-----+---------+-------+

jetlife76 0 Junior Poster in Training · Answer 10 · 2012-02-21T13:37:01+00:00

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);
	?>

vaultdweller123 32 Posting Pro · Answer 11 · 2012-02-21T14:04:50+00:00

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

jetlife76 0 Junior Poster in Training · Answer 12 · 2012-02-21T14:18:25+00:00

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?

Having trouble querying a database

Recommended Answers Collapse Answers

All 17 Replies

Recommended Answers