Trying to do multiple queries in PHP Mysql and add to formula for Calculator

Question

I have a drink calculator that pulls from mysql the weight and sex of the person to figure out what their blood alcohol level is based on how many drinks they have had over time. I need to factor in the strength of drinks. Some drinks are stronger than others. Most of the drinks in the database table have a value of one but a few are more and some are less. The table is called DrinkType. It has a list of the drinks and their corresponding value.

I am trying to figure out how to do a second query to pull the drink type number and then multiply the final result by it. Any help is greatly appreciated.

Update:
Thanks to everyone for their help. I pasted the code supplied by Dainis and I am getting an error about the array. The error is : Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/netmonge/public_html/note/wp-content/plugins/exec-php/includes/runtime.php(42) : eval()’d code on line 22. I think it may be because I don't have the table for DrinkType done correctly. That table has a Drink column with the names of the drink that can be selected from the form, and an Alcohol_Content column which consist of primarily ones. That column needs to be selected based on the drink chosen in the form and multiply the result ( $row2[$Alcohol])...

<?php
require_once( 'db-connect.php' );

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) ;
    $row1 = mysql_fetch_array( $result );

    $query = "SELECT $Alcohol
            FROM $Drinktype";
    $result = mysql_query( $query ) or die($query."<br/><br/>".mysql_error());;
    $row2 = mysql_fetch_array( $result );

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

You're really using a string datatype for the weight column? With a number followed by ` lb.`? All hope is lost! — Mark Baker, Jan 02 '14 at 13:22
Get rid of spurious quotes at end of `$query = "SELECT ".$Alcohol_Content." FROM ".$Drinktype.";` — Mark Baker, Jan 02 '14 at 13:24
And stop overwriting the value of $row for each query result — Mark Baker, Jan 02 '14 at 13:25
I think I'm just going to give up looking for errors in this.... I just don't have the time to list them all — Mark Baker, Jan 02 '14 at 13:26
I didn't even get to most of the errors before i got a headache from the unfiltered $_POST values used in the query. I thought by now even the newest of new programmers would have heard of mysql injection. — , Jan 02 '14 at 13:49
possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) — John Conde, Jan 03 '14 at 01:59

score 0 · Answer 1 · answered Jan 02 '14 at 13:30

Syntax highlighter shows some error which should be

<?php include('db-connect.php'); ?><?php
$result = '';
if(isset($_POST['Sex'])){
    $sex = $_POST['Sex'];
    $weight = $_POST['Weight'];
    $number = $_POST['Number'];
    $hours = $_POST['Hours'] * 0.015;
    $DrinkType = $_POST['DrinkType'];

    $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_Content."` FROM ".$Drinktype;
    $result = mysql_query($query);
    $row = mysql_fetch_array($result);      

    $result = $row[$number] - $hours -$DrinkType;
            $result = 'Your Blood Alcohol Level is: '.$result.'%';

}

?>

Peon · Answer 2 · 2014-01-02T14:02:15.970

This is for starters:

<?php

    require_once( 'db-connect.php' );

    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['DrinkType'] );

        $query = "SELECT $number
                FROM $sex
                WHERE body_weight <= '$weight lb'
                ORDER BY body_weight DESC";
        $result = mysql_query( $query) ;
        $row1 = mysql_fetch_array( $result );

        $query = "SELECT $Alcohol_Content
                FROM $Drinktype";
        $result = mysql_query( $query );
        $row2 = mysql_fetch_array( $result );

        $result = $row1[$number] - $hours - $row2[$Alcohol_Content];
        $result = "Your Blood Alcohol Level is: $result%";
    }

?>

But you should rewrite the code ( including DB structure and PDO connections ). For instance:

Create one table for both, man and women ( not two sepperare );
Create a sub table where you store the ID of the person, drink id and time, when the drink was used;
Create another table where you store drinks names and the alchocol % of that drink;
Create 1 query with joins that will take in consideration time now, time of drinks and the amount of drinks summ all of that up after your formula and shows you the end result.

Adding your code I get an error that says : Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/netmonge/public_html/note/wp-content/plugins/exec-php/includes/runtime.php(42) : eval()’d code on line 22 — montytx, Jan 02 '14 at 15:32

Trying to do multiple queries in PHP Mysql and add to formula for Calculator

2 Answers2