CODEWITHSUNDEEP
phpmysqlmysqli
ZZZZZZZZZZ
ZZZZZZZZZZ

Reputation: 57

Updating to MySQLi and having some trouble

So I've spent a bit of time looking at MySQLi and I'm having trouble updating a script with the new functions. This script is used for a dynamic dropdown form, using data sent to it using JS. You can find a live version of the script here to check out what I'm takling about. I've looked up and down my code and have compared it to other MySQLi examples and I'm just not sure where I'm going wrong.

Now, the first dropdown doesn't even initiate a query, all the PHP does is return predefined results as it's just simpler for the first option. What's weird, to me, is that even the first drop down is now not working when it does not rely at all on the MySQLi connection. It all worked before updating, just for reference.

Here's my script:

$db = new mysqli($dbHost, $dbUser, $dbPass, $dbDatabase);

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

//prevents injections
//any order
isset($_GET['type'])?$type = urldecode($_GET['type']):"";
isset($_GET['source'])?$source = $db->real_escape_string(urldecode($_GET['source'])):"";
isset($_GET['range'])?$power = $db->real_escape_string(urldecode($_GET['range'])):"";
isset($_GET['setpoint'])?$setpoint = $db->real_escape_string(urldecode($_GET['setpoint'])):"";

//forms the query depending on what data is recieved through GET
//first option on the bottom; last option on the top to avoid conflicts 
if (isset($_GET['setpoint'])) {
    $query = "SELECT DISTINCT stp FROM meters WHERE sio='$range' AND pso='$power' AND stp='$setpoint' ORDER BY model";
} elseif (isset($_GET['power'])) {
    $query = "SELECT DISTINCT stp FROM meters WHERE sio='$range' AND pso='$power' ORDER BY model";
} elseif (isset($_GET['range'])) {
    $query = "SELECT DISTINCT pso FROM meters WHERE sio='$range' ORDER BY model";
} elseif (isset($_GET['source'])) {
    $query = "SELECT DISTINCT sir FROM meters WHERE sio LIKE '%$source%' ORDER BY sir";
}

//creates a result array from query results
isset($query)?$result = $db->query($query):"";

//outputs dropdown options dependent on what GET variables are set
//first option on the bottom; last option on the top to avoid conflicts
if (isset($_GET['setpoint'])) {
    while ($row = $result->fetch_assoc()) {
        echo "<option value='" . $row['stp'] . "'>" . $row['stp'] . "</option>";
    $result->free();
    }
} elseif (isset($_GET['power'])) {
    echo "<option>Please Choose Setpoint Options</option>";
    while ($row = $result->fetch_assoc()) {
        $row{'stp'} = ucfirst($row{'stp'}); //capitalizes the first letter; necessary?
        echo "<option value='" . $row['stp'] . "'>" . $row['stp'] . "</option>";
    $result->free();
    }
} elseif (isset($_GET['source'])) {
    echo "<option>Please Choose Input Range</option>";
    while ($row = $result->fetch_assoc()) {
        echo "<option value='" . $row['sir'] . "'>" . $row['sir'] . "</option>";
    $result->free();
    }
} elseif (isset($_GET['type']) && $_GET['type'] == "Digital") {
    echo "<option>Please Choose Input Source</option>";
    echo "<option value='RS232C'>RS232C</option><option value='RS422'>RS422</option><option value='RS485'>RS485</option><option value='current loop'>current loop</option>";
    $result->free();
} elseif (isset($_GET['type']) && $_GET['type'] == "Analog") {
    echo "<option>Please Choose Input Source</option>";
    echo "<option value='DC current'>DC Current</option><option value='DC voltage'>DC Voltage</option><option value='AC current'>AC Current</option><option value='AC voltage'>AC Voltage</option><option value='process'>Process</option><option value='thermocouple'>Thermocouple</option><option value='RDT'>rdt</option>";
    $result->free();
}

edit: This is my old code using the deprecated method.

$con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error());

mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());


//prevents injections
//any order
isset($_GET['type'])?$type = urldecode($_GET['type']):"";
//$type = mysql_real_escape_string(urldecode($_GET['type']));
isset($_GET['source'])?$source = mysql_real_escape_string(urldecode($_GET['source'])):"";
isset($_GET['range'])?$power = mysql_real_escape_string(urldecode($_GET['range'])):"";
isset($_GET['setpoint'])?$setpoint = mysql_real_escape_string(urldecode($_GET['setpoint'])):"";

//forms the query depending on what data is recieved through GET
//first option on the bottom; last option on the top to avoid conflicts 
if (isset($_GET['setpoint'])) {
    $query = "SELECT DISTINCT stp FROM meters WHERE sio='$range' AND pso='$power' AND stp='$setpoint' ORDER BY model";
} elseif (isset($_GET['power'])) {
    $query = "SELECT DISTINCT stp FROM meters WHERE sio='$range' AND pso='$power' ORDER BY model";
} elseif (isset($_GET['range'])) {
    $query = "SELECT DISTINCT pso FROM meters WHERE sio='$range' ORDER BY model";
} elseif (isset($_GET['source'])) {
    $query = "SELECT DISTINCT sir FROM meters WHERE sio LIKE '%$source%' ORDER BY sir";
}

//creates a result array from query results
isset($query)?$result = mysql_query($query):"";

//outputs dropdown options dependent on what GET variables are set
//first option on the bottom; last option on the top to avoid conflicts
if (isset($_GET['setpoint'])) {
    while ($row = mysql_fetch_array($result)) {
        echo "<option value='" . $row{'stp'} . "'>" . $row{'stp'} . "</option>";
    }
} elseif (isset($_GET['power'])) {
    echo "<option>Please Choose Setpoint Options</option>";
    while ($row = mysql_fetch_array($result)) {
        $row{'stp'} = ucfirst($row{'stp'}); //capitalizes the first letter; necessary?
        echo "<option value='" . $row{'stp'} . "'>" . $row{'stp'} . "</option>";
    }
} elseif (isset($_GET['source'])) {
    echo "<option>Please Choose Input Range</option>";
    while ($row = mysql_fetch_array($result)) {
        echo "<option value='" . $row{'sir'} . "'>" . $row{'sir'} . "</option>";
    }
} elseif (isset($_GET['type']) && $_GET['type'] == "Digital") {
    echo "<option>Please Choose Input Source</option>";
    echo "<option value='RS232C'>RS232C</option><option value='RS422'>RS422</option><option value='RS485'>RS485</option><option value='current loop'>current loop</option>";
} elseif (isset($_GET['type']) && $_GET['type'] == "Analog") {
    echo "<option>Please Choose Input Source</option>";
    echo "<option value='DC current'>DC Current</option><option value='DC voltage'>DC Voltage</option><option value='AC current'>AC Current</option><option value='AC voltage'>AC Voltage</option><option value='process'>Process</option><option value='thermocouple'>Thermocouple</option><option value='RDT'>rdt</option>";
}

Upvotes: 2

Views: 245

Answers (1)

immulatin
immulatin

Reputation: 2118

You are freeing your $result inside the while loop. This will cause the loop to fail on the second iteration.

Since you are freeing the result in all of the ifs, why don't you just do it once at the end?

...
} elseif (isset($_GET['type']) && $_GET['type'] == "Analog") {
    echo "<option>Please Choose Input Source</option>";
    echo "<option value='DC current'>DC Current</option><option value='DC voltage'>DC Voltage</option><option value='AC current'>AC Current</option><option value='AC voltage'>AC Voltage</option><option value='process'>Process</option><option value='thermocouple'>Thermocouple</option><option value='RDT'>rdt</option>";
}

$result->free();

However this doesn't explain why analog and digital won't work still..

Upvotes: 1

Related Questions