Question 20133 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL query from PHP knows there is a value but it's blank

I have a query that looks for the highest value of a given name in a table and returns that number. It works great in the SQL console. But, I am trying to get that value and then create radio buttons in an HTML table and although there is data, it’s an empty set. It will always be just one value, so an array is not needed. There can be multiple entries in the table with the same name, but not also with the same net.

$sqltankonly = "SELECT * FROM HoldingUnit WHERE tankfacility = 'test' AND net = '0' ORDER BY name"; // Select Holding Units that are not nets
$resulttankonly = $conn->query($sqltankonly); // Select Holding Units that are not nets unless they have something in them


if ($resulttankonly->num_rows > 0) {
  // Setup Table
  echo '<table><tr><th>Destination Tank</th><th>Net</th></tr>';
  while($row = $resulttankonly->fetch_assoc()) {
    echo '<tr><td><input type="radio" name="dtank" " value="' . $row["name"] . '" required>' . $row["name"] . '</td>';
    echo '<td>';

// Below is my problem *******************

    $sqlnetcapacity = 'SELECT MAX(net) FROM HoldingUnit WHERE name="' . $row["name"] . '"';
    $resultnetcapacity = $conn->query($sqlnetcapacity);
    $netcapacity = $conn->query('SELECT MAX(net) FROM HoldingUnit WHERE name="Test-01"')->fetch_object()->net;
    while($netcapacity > 0) {
      echo '<input type="radio" name="net" value="' . $netcapacity . '" />' . $netcapacity . ';
      $netcapacity = $netcapacity - 1;
    }
  }
  echo '</td></tr></table>';

I tried treating $resultnetcapacity like an array and used a while loop instead of fetch_object(). I have tried just printing the values and nothing shows even if an if statement proves there are more than 0 values.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you for the help to point me in the right direction. The below query gave me the result I needed...

    SELECT m.name, m.net
FROM HoldingUnit m
    LEFT JOIN HoldingUnit b
        ON m.name = b.name
        AND m.net < b.net
WHERE b.net IS NULL;

    And here is my result...

    +---------+------+
| name    | net  |
+---------+------+
| Test-02 |    0 |
| Test-03 |    0 |
| Test-04 |    0 |
| Dead    |    0 |
| Test-01 |    1 |
+---------+------+

    Thank you. As I add more tanks and more nets to those tanks, this seems to work for my requirements.


  2. 0

    There’s no net column in the result; the column you selected is named MAX(net). You should assign an alias to the value of MAX(net).

    $netcapacity = $conn->query('SELECT MAX(net) AS net FROM ...')->fetch_object()->net;

    There’s also no need to do these queries inside the loop. You can join the original query with the MAX() queries.

    SELECT t1.*, t2.net_capacity
FROM HoldingUnit AS t1
JOIN (
    SELECT name, MAX(net) AS net_capacity
    FROM HoldingUnit
    GROUP BY name
) AS t2 ON t1.name = t2.name

    Then use $netcapacity = $row['net_capacity']

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search