Jump to content

php mysql question from a form


montytx

Recommended Posts

I have a form that people use to calculate their blood alcohol level. It currently does this with weight, sex and number of drinks. I want to add a multiplier that uses the type of drink to impact the BAC number since some drinks have more alcohol than others. The alcohol is selected in the form from a drop down iist.

So far I have created a separate table with a column called Drink and a column called Alcohol_Content. I am getting stuck tying the two queries together . Any help would be appreciated. Thanks!

 

Here is the code I have:

<?php require_once ('db-connect.php'); ?>
<?php
	$result = '';
	if(isset($_POST['Sex'])){
		$sex = mysql_real_escape_string ($_POST['Sex']);
		$weight = mysql_real_escape_string ($_POST['Weight']);
		$number = mysql_real_escape_string ($_POST['Number']);
		$hours = mysql_real_escape_string ($_POST['Hours']) * 0.015;
                $Drinktype = mysql_real_escape_string( $_POST['Drink'] );
                $Alcohol = mysql_real_escape_string( $_POST['Alcohol_Content'] );
       
		
		$query = "SELECT `".$number."` FROM ".$sex." WHERE body_weight <= '".$weight." lb.' ORDER BY body_weight DESC";
		$result = mysql_query($query);
		$row = mysql_fetch_array($result);

  $query = "SELECT `".$Alcohol."` FROM ".$Drinktype.";
    	$result = mysql_query($query);
		$row2 = mysql_fetch_array($result);

       
       		$result = $row[$number] - $hours * $row2;
                $result = 'Your Blood Alcohol Level is: '.$result.'%';
		
	}
?>

Link to comment
Share on other sites

I have 3 tables. Two are female/male with with weight and # of drinks with BAC Levels.

The third table is just drinks with a column called Alcohol_Content. That column is primarily  filled with 1 as the answer. A few are 1.2 and some are .75 if they have less alcohol in the drink than normal. I have been working on the statement and have gotten it to here so far...But I am having trouble with it. 

  $query = "SELECT Alcohol_Content FROM DrinkType WHERE Drinktype = '".$_GET['DrinkType']."'");
    	$result = mysql_query($query);
		$row2 = mysql_fetch_array($result);

The DrinkType is the form name for the drop down select drink options.

Link to comment
Share on other sites

I actually had someone set up the basic calculator, but realized they had set it up to allow injections. So I am trying to clean it up. They set it up with two tables.Here you go: 

 

-- phpMyAdmin SQL Dump
-- version 3.4.11.1
--
-- Host: localhost
-- Generation Time: Jan 03, 2014 at 02:43 PM
-- Server version: 5.5.33
-- PHP Version: 5.3.17
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
 
--
-- Database: `netmonge_blood_alcohol`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `DrinkType`
--
 
CREATE TABLE IF NOT EXISTS `DrinkType` (
  `Drink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Alcohol_Content` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
--
-- Dumping data for table `DrinkType`
--
 
INSERT INTO `DrinkType` (`Drink`, `Alcohol_Content`) VALUES
('US_beer', '1'),
('Light_beer', '.8'),
('Reduced_alcohol_beer', '.70'),
('Pint_of_beer', '1.25'),
('Malt_liquor', '1.3'),
('Glass_of_table_wine', '1'),
('Glasses_of_Champagne', '1'),
('Bloody_Mary', '1'),
('Gin_and_Tonic', '1'),
('Highball', '1'),
('Irish_Coffee', '1'),
('On_the_Rocks', '1'),
('Pina_Colada', '1'),
('Screwdriver', '1'),
('Tom_Collins', '1'),
('Whiskey_Sour', '1'),
('Margarita', '1'),
('Airline_miniature', '1'),
('Gimlet', '1'),
('Old-fashioned', '1'),
('Mint_Julep', '1'),
('Black_Russian', '1'),
('Dry_Martini', '1'),
('Manhattan', '1'),
('Rob_Roy', '1'),
('Doubles', '2'),
('Frozen_Daquiri', '1');
 
-- --------------------------------------------------------
 
--
-- Table structure for table `female`
--
 
CREATE TABLE IF NOT EXISTS `female` (
  `body_weight` varchar(255) DEFAULT NULL,
  `1` varchar(255) DEFAULT NULL,
  `2` varchar(255) DEFAULT NULL,
  `3` varchar(255) DEFAULT NULL,
  `4` varchar(255) DEFAULT NULL,
  `5` varchar(255) DEFAULT NULL,
  `6` varchar(255) DEFAULT NULL,
  `7` varchar(255) DEFAULT NULL,
  `8` varchar(255) DEFAULT NULL,
  `9` varchar(255) DEFAULT NULL,
  `10` varchar(255) DEFAULT NULL,
  `11` varchar(255) DEFAULT NULL,
  `12` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `female`
--
 
INSERT INTO `female` (`body_weight`, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `11`, `12`) VALUES
('100 lb.', '0.047', '0.094', '0.141', '0.188', '0.235', '0.282', '0.329', '0.376', '0.423', '0.47', '0.517', '0.564'),
('110 lb.', '0.042', '0.084', '0.126', '0.168', '0.21', '0.252', '0.294', '0.336', '0.378', '0.42', '0.462', '0.504'),
('120 lb.', '0.039', '0.078', '0.117', '0.156', '0.195', '0.234', '0.273', '0.312', '0.351', '0.39', '0.429', '0.468'),
('130 lb.', '0.036', '0.072', '0.108', '0.144', '0.18', '0.216', '0.252', '0.288', '0.324', '0.36', '0.396', '0.432'),
('140 lb.', '0.033', '0.066', '0.099', '0.132', '0.165', '0.198', '0.231', '0.264', '0.297', '0.33', '0.363', '0.394'),
('150 lb.', '0.031', '0.062', '0.093', '0.124', '0.155', '0.186', '0.217', '0.248', '0.279', '0.31', '0.341', '0.372'),
('160 lb.', '0.029', '0.058', '0.087', '0.116', '0.145', '0.174', '0.203', '0.232', '0.261', '0.29', '0.319', '0.348'),
('170 lb.', '0.027', '0.054', '0.081', '0.108', '0.135', '0.162', '0.189', '0.216', '0.243', '0.27', '0.297', '0.324'),
('180 lb.', '0.026', '0.052', '0.078', '0.104', '0.13', '0.156', '0.182', '0.208', '0.234', '0.26', '0.286', '0.312'),
('190 lb.', '0.024', '0.048', '0.072', '0.096', '0.12', '0.144', '0.168', '0.192', '0.216', '0.24', '0.264', '0.288'),
('200 lb.', '0.023', '0.046', '0.069', '0.092', '0.115', '0.138', '0.161', '0.184', '0.207', '0.23', '0.253', '0.276'),
('210 lb.', '0.022', '0.044', '0.066', '0.088', '0.11', '0.132', '0.154', '0.176', '0.198', '0.22', '0.242', '0.264'),
('220 lb.', '0.021', '0.042', '0.063', '0.084', '0.105', '0.126', '0.147', '0.168', '0.189', '0.21', '0.231', '0.252'),
('230 lb.', '0.02', '0.04', '0.06', '0.08', '0.1', '0.12', '0.14', '0.16', '0.18', '0.2', '0.22', '0.24'),
('240 lb.', '0.019', '0.038', '0.057', '0.076', '0.095', '0.114', '0.133', '0.152', '0.171', '0.19', '0.209', '0.228'),
('250 lb.', '0.018', '0.036', '0.054', '0.072', '0.09', '0.108', '0.126', '0.144', '0.162', '0.18', '0.198', '0.216'),
('100 lb.', '0.047', '0.094', '0.141', '0.188', '0.235', '0.282', '0.329', '0.376', '0.423', '0.47', '0.517', '0.564'),
('110 lb.', '0.042', '0.084', '0.126', '0.168', '0.21', '0.252', '0.294', '0.336', '0.378', '0.42', '0.462', '0.504'),
('120 lb.', '0.039', '0.078', '0.117', '0.156', '0.195', '0.234', '0.273', '0.312', '0.351', '0.39', '0.429', '0.468'),
('130 lb.', '0.036', '0.072', '0.108', '0.144', '0.18', '0.216', '0.252', '0.288', '0.324', '0.36', '0.396', '0.432'),
('140 lb.', '0.033', '0.066', '0.099', '0.132', '0.165', '0.198', '0.231', '0.264', '0.297', '0.33', '0.363', '0.394'),
('150 lb.', '0.031', '0.062', '0.093', '0.124', '0.155', '0.186', '0.217', '0.248', '0.279', '0.31', '0.341', '0.372'),
('160 lb.', '0.029', '0.058', '0.087', '0.116', '0.145', '0.174', '0.203', '0.232', '0.261', '0.29', '0.319', '0.348'),
('170 lb.', '0.027', '0.054', '0.081', '0.108', '0.135', '0.162', '0.189', '0.216', '0.243', '0.27', '0.297', '0.324'),
('180 lb.', '0.026', '0.052', '0.078', '0.104', '0.13', '0.156', '0.182', '0.208', '0.234', '0.26', '0.286', '0.312'),
('190 lb.', '0.024', '0.048', '0.072', '0.096', '0.12', '0.144', '0.168', '0.192', '0.216', '0.24', '0.264', '0.288'),
('200 lb.', '0.023', '0.046', '0.069', '0.092', '0.115', '0.138', '0.161', '0.184', '0.207', '0.23', '0.253', '0.276'),
('210 lb.', '0.022', '0.044', '0.066', '0.088', '0.11', '0.132', '0.154', '0.176', '0.198', '0.22', '0.242', '0.264'),
('220 lb.', '0.021', '0.042', '0.063', '0.084', '0.105', '0.126', '0.147', '0.168', '0.189', '0.21', '0.231', '0.252'),
('230 lb.', '0.02', '0.04', '0.06', '0.08', '0.1', '0.12', '0.14', '0.16', '0.18', '0.2', '0.22', '0.24'),
('240 lb.', '0.019', '0.038', '0.057', '0.076', '0.095', '0.114', '0.133', '0.152', '0.171', '0.19', '0.209', '0.228'),
('250 lb.', '0.018', '0.036', '0.054', '0.072', '0.09', '0.108', '0.126', '0.144', '0.162', '0.18', '0.198', '0.216'),
('100 lb.', '0.047', '0.094', '0.141', '0.188', '0.235', '0.282', '0.329', '0.376', '0.423', '0.47', '0.517', '0.564'),
('110 lb.', '0.042', '0.084', '0.126', '0.168', '0.21', '0.252', '0.294', '0.336', '0.378', '0.42', '0.462', '0.504'),
('120 lb.', '0.039', '0.078', '0.117', '0.156', '0.195', '0.234', '0.273', '0.312', '0.351', '0.39', '0.429', '0.468'),
('130 lb.', '0.036', '0.072', '0.108', '0.144', '0.18', '0.216', '0.252', '0.288', '0.324', '0.36', '0.396', '0.432'),
('140 lb.', '0.033', '0.066', '0.099', '0.132', '0.165', '0.198', '0.231', '0.264', '0.297', '0.33', '0.363', '0.394'),
('150 lb.', '0.031', '0.062', '0.093', '0.124', '0.155', '0.186', '0.217', '0.248', '0.279', '0.31', '0.341', '0.372'),
('160 lb.', '0.029', '0.058', '0.087', '0.116', '0.145', '0.174', '0.203', '0.232', '0.261', '0.29', '0.319', '0.348'),
('170 lb.', '0.027', '0.054', '0.081', '0.108', '0.135', '0.162', '0.189', '0.216', '0.243', '0.27', '0.297', '0.324'),
('180 lb.', '0.026', '0.052', '0.078', '0.104', '0.13', '0.156', '0.182', '0.208', '0.234', '0.26', '0.286', '0.312'),
('190 lb.', '0.024', '0.048', '0.072', '0.096', '0.12', '0.144', '0.168', '0.192', '0.216', '0.24', '0.264', '0.288'),
('200 lb.', '0.023', '0.046', '0.069', '0.092', '0.115', '0.138', '0.161', '0.184', '0.207', '0.23', '0.253', '0.276'),
('210 lb.', '0.022', '0.044', '0.066', '0.088', '0.11', '0.132', '0.154', '0.176', '0.198', '0.22', '0.242', '0.264'),
('220 lb.', '0.021', '0.042', '0.063', '0.084', '0.105', '0.126', '0.147', '0.168', '0.189', '0.21', '0.231', '0.252'),
('230 lb.', '0.02', '0.04', '0.06', '0.08', '0.1', '0.12', '0.14', '0.16', '0.18', '0.2', '0.22', '0.24'),
('240 lb.', '0.019', '0.038', '0.057', '0.076', '0.095', '0.114', '0.133', '0.152', '0.171', '0.19', '0.209', '0.228'),
('250 lb.', '0.018', '0.036', '0.054', '0.072', '0.09', '0.108', '0.126', '0.144', '0.162', '0.18', '0.198', '0.216');
 
-- --------------------------------------------------------
 
--
-- Table structure for table `male`
--
 
CREATE TABLE IF NOT EXISTS `male` (
  `body_weight` varchar(255) DEFAULT NULL,
  `1` varchar(255) DEFAULT NULL,
  `2` varchar(255) DEFAULT NULL,
  `3` varchar(255) DEFAULT NULL,
  `4` varchar(255) DEFAULT NULL,
  `5` varchar(255) DEFAULT NULL,
  `6` varchar(255) DEFAULT NULL,
  `7` varchar(255) DEFAULT NULL,
  `8` varchar(255) DEFAULT NULL,
  `9` varchar(255) DEFAULT NULL,
  `10` varchar(255) DEFAULT NULL,
  `11` varchar(255) DEFAULT NULL,
  `12` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `male`
--
 
INSERT INTO `male` (`body_weight`, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `11`, `12`) VALUES
('100 lb.', '0.038', '0.076', '0.114', '0.152', '0.19', '0.228', '0.266', '0.304', '0.342', '0.38', '0.418', '0.456'),
('110 lb.', '0.034', '0.068', '0.102', '0.136', '0.17', '0.204', '0.238', '0.272', '0.306', '0.34', '0.374', '0.408'),
('120 lb.', '0.031', '0.063', '0.094', '0.125', '0.155', '0.186', '0.217', '0.248', '0.279', '0.31', '0.341', '0.372'),
('130 lb.', '0.029', '0.058', '0.087', '0.116', '0.145', '0.174', '0.203', '0.232', '0.261', '0.29', '0.32', '0.348'),
('140 lb.', '0.027', '0.054', '0.081', '0.108', '0.135', '0.162', '0.189', '0.216', '0.243', '0.27', '0.297', '0.324'),
('150 lb.', '0.025', '0.05', '0.075', '0.1', '0.125', '0.15', '0.175', '0.2', '0.225', '0.25', '0.275', '0.3'),
('160 lb.', '0.023', '0.046', '0.069', '0.092', '0.115', '0.138', '0.161', '0.184', '0.207', '0.23', '0.253', '0.276'),
('170 lb.', '0.022', '0.044', '0.066', '0.088', '0.11', '0.132', '0.154', '0.176', '0.198', '0.22', '0.242', '0.264'),
('180 lb.', '0.021', '0.042', '0.063', '0.084', '0.105', '0.126', '0.147', '0.168', '0.189', '0.21', '0.231', '0.252'),
('190 lb.', '0.02', '0.04', '0.06', '0.08', '0.1', '0.12', '0.14', '0.16', '0.18', '0.2', '0.22', '0.24'),
('200 lb.', '0.019', '0.038', '0.057', '0.076', '0.095', '0.114', '0.133', '0.152', '0.171', '0.19', '0.209', '0.228'),
('210 lb.', '0.018', '0.036', '0.054', '0.072', '0.09', '0.108', '0.126', '0.144', '0.162', '0.18', '0.198', '0.216'),
('220 lb.', '0.017', '0.034', '0.051', '0.068', '0.085', '0.102', '0.119', '0.136', '0.153', '0.17', '0.187', '0.204'),
('230 lb.', '0.016', '0.032', '0.048', '0.064', '0.08', '0.096', '0.112', '0.128', '0.144', '0.16', '0.176', '0.192'),
('240 lb.', '15', '0.03', '0.045', '0.06', '0.075', '0.09', '0.105', '0.12', '0.135', '0.15', '0.172', '0.188'),
('250 lb.', '15', '0.03', '0.045', '0.06', '0.075', '0.09', '0.105', '0.12', '0.135', '0.15', '0.172', '0.188'),
('100 lb.', '0.038', '0.076', '0.114', '0.152', '0.19', '0.228', '0.266', '0.304', '0.342', '0.38', '0.418', '0.456'),
('110 lb.', '0.034', '0.068', '0.102', '0.136', '0.17', '0.204', '0.238', '0.272', '0.306', '0.34', '0.374', '0.408'),
('120 lb.', '0.031', '0.063', '0.094', '0.125', '0.155', '0.186', '0.217', '0.248', '0.279', '0.31', '0.341', '0.372'),
('130 lb.', '0.029', '0.058', '0.087', '0.116', '0.145', '0.174', '0.203', '0.232', '0.261', '0.29', '0.32', '0.348'),
('140 lb.', '0.027', '0.054', '0.081', '0.108', '0.135', '0.162', '0.189', '0.216', '0.243', '0.27', '0.297', '0.324'),
('150 lb.', '0.025', '0.05', '0.075', '0.1', '0.125', '0.15', '0.175', '0.2', '0.225', '0.25', '0.275', '0.3'),
('160 lb.', '0.023', '0.046', '0.069', '0.092', '0.115', '0.138', '0.161', '0.184', '0.207', '0.23', '0.253', '0.276'),
('170 lb.', '0.022', '0.044', '0.066', '0.088', '0.11', '0.132', '0.154', '0.176', '0.198', '0.22', '0.242', '0.264'),
('180 lb.', '0.021', '0.042', '0.063', '0.084', '0.105', '0.126', '0.147', '0.168', '0.189', '0.21', '0.231', '0.252'),
('190 lb.', '0.02', '0.04', '0.06', '0.08', '0.1', '0.12', '0.14', '0.16', '0.18', '0.2', '0.22', '0.24'),
('200 lb.', '0.019', '0.038', '0.057', '0.076', '0.095', '0.114', '0.133', '0.152', '0.171', '0.19', '0.209', '0.228'),
('210 lb.', '0.018', '0.036', '0.054', '0.072', '0.09', '0.108', '0.126', '0.144', '0.162', '0.18', '0.198', '0.216'),
('220 lb.', '0.017', '0.034', '0.051', '0.068', '0.085', '0.102', '0.119', '0.136', '0.153', '0.17', '0.187', '0.204'),
('230 lb.', '0.016', '0.032', '0.048', '0.064', '0.08', '0.096', '0.112', '0.128', '0.144', '0.16', '0.176', '0.192'),
('240 lb.', '15', '0.03', '0.045', '0.06', '0.075', '0.09', '0.105', '0.12', '0.135', '0.15', '0.172', '0.188'),
('250 lb.', '15', '0.03', '0.045', '0.06', '0.075', '0.09', '0.105', '0.12', '0.135', '0.15', '0.172', '0.188'),
('100 lb.', '0.038', '0.076', '0.114', '0.152', '0.19', '0.228', '0.266', '0.304', '0.342', '0.38', '0.418', '0.456'),
('110 lb.', '0.034', '0.068', '0.102', '0.136', '0.17', '0.204', '0.238', '0.272', '0.306', '0.34', '0.374', '0.408'),
('120 lb.', '0.031', '0.063', '0.094', '0.125', '0.155', '0.186', '0.217', '0.248', '0.279', '0.31', '0.341', '0.372'),
('130 lb.', '0.029', '0.058', '0.087', '0.116', '0.145', '0.174', '0.203', '0.232', '0.261', '0.29', '0.32', '0.348'),
('140 lb.', '0.027', '0.054', '0.081', '0.108', '0.135', '0.162', '0.189', '0.216', '0.243', '0.27', '0.297', '0.324'),
('150 lb.', '0.025', '0.05', '0.075', '0.1', '0.125', '0.15', '0.175', '0.2', '0.225', '0.25', '0.275', '0.3'),
('160 lb.', '0.023', '0.046', '0.069', '0.092', '0.115', '0.138', '0.161', '0.184', '0.207', '0.23', '0.253', '0.276'),
('170 lb.', '0.022', '0.044', '0.066', '0.088', '0.11', '0.132', '0.154', '0.176', '0.198', '0.22', '0.242', '0.264'),
('180 lb.', '0.021', '0.042', '0.063', '0.084', '0.105', '0.126', '0.147', '0.168', '0.189', '0.21', '0.231', '0.252'),
('190 lb.', '0.02', '0.04', '0.06', '0.08', '0.1', '0.12', '0.14', '0.16', '0.18', '0.2', '0.22', '0.24'),
('200 lb.', '0.019', '0.038', '0.057', '0.076', '0.095', '0.114', '0.133', '0.152', '0.171', '0.19', '0.209', '0.228'),
('210 lb.', '0.018', '0.036', '0.054', '0.072', '0.09', '0.108', '0.126', '0.144', '0.162', '0.18', '0.198', '0.216'),
('220 lb.', '0.017', '0.034', '0.051', '0.068', '0.085', '0.102', '0.119', '0.136', '0.153', '0.17', '0.187', '0.204'),
('230 lb.', '0.016', '0.032', '0.048', '0.064', '0.08', '0.096', '0.112', '0.128', '0.144', '0.16', '0.176', '0.192'),
('240 lb.', '15', '0.03', '0.045', '0.06', '0.075', '0.09', '0.105', '0.12', '0.135', '0.15', '0.172', '0.188'),
('250 lb.', '15', '0.03', '0.045', '0.06', '0.075', '0.09', '0.105', '0.12', '0.135', '0.15', '0.172', '0.188');
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Link to comment
Share on other sites

I couldn't resist tidying up those tables.

 

The male and female tables both have many redundant columns. Cols 2 - 12 are just multiples of Col 1 and therefore totally unnecessary (just multiply Col 1 by the number). No need for two separate tables either so I combined into one. (BTW, both had all the data duplicated in the dump)

CREATE TABLE `weight_factor` (
  `sex` char(1) NOT NULL,
  `weight` int(11) NOT NULL,
  `factor` decimal(5,3) DEFAULT NULL,
  PRIMARY KEY (`sex`,`weight`)
)

The weight column has the "lb" removed from the end of every value. It should be a numeric value.

 

For the drinktype table I added a numeric id (primary key) and removed those untidy underscores from the descriptions.

 

(I'm attaching a dump of a database called "monty" containing the two revised tables. I'm also attaching a php script calculator with the now simplified queries.)

AlcoholCalc.php

Dump20140104.txt

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.