Trouble with database query in PHP

[jetlife76]

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

[jetlife76]

 

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

?>

[therealwesfoster]

First, you would need to send data to your website with either AJAX or a simple form. For this example, I'm using a form:

 

<form action="" method="post">
  Select your item:
  <input type="radio" name="item" value="1" /> Hammer <br />
  <input type="radio" name="item" value="2" /> Arm <br/>

  <input type="submit" value="Submit" />
</form>

 

All I did was create the radio buttons for each of the items in your database. I used the item's ID as the radio button's value, but you can use whatever you want.

 

Next, I've altered your PHP code to this:

<?PHP

$server = 'localhost';
	$user = 'root';
$password = '';

$mydb = 'Tools';
$table_name = 'Inventory';

if ( $_POST['Submit'] ) {  /// Check if the form was submitted
	$item_id = mysql_real_escape_string($_POST['item']);    /// Get the item's ID from the POST (form)

	$SQLcmd = "select * from $table_name WHERE item_id='{$item_id}' ";   /// Add the "WHERE" clause

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

} /// END POST "IF"

?>

 

I added comments to the lines that I altered. Simply put, I:

 

1. Check if a POST was sent (from the form)

2. Grabbed the $_POST['item'] value (which is the item's id) and filtered it using mysql_real_escape_string()

3. Altered your MySQL query to only grab records where the item_id is equal to what was sent in the form.

 

You might need to change the name of your column name in the WHERE clause, but overall, that's how you get what you want.

 

I hope I understood you correctly

 

Wes