CODEWITHSUNDEEP
phpmysql
David Mukoro
David Mukoro

Reputation: 467

Select Mutiple Data from a table in mysqli

I want to retrieve mutiple data from a single table in an database, having the different data for a particluar week.See below:

<th>FLT No</th>
   <th>Fuel Type</th>
   <th>Capacity</th>
    <th>Ton Fuel-Mix</th>
    <th>Week1 Avg Utilization </th>
    <th>Remarks</th>
    <th>Week2 Avg Utilization </th>
    <th>Remarks</th>
    <th>Week3 Avg Utilization </th>
    <th>Remarks</th>
    <th>Week4 Avg Utilization </th>
    <th>Remarks</th>
    <th>Week5 Avg Utilization </th>
    <th>Remarks</th>

See the Query below:

$dd = date('Y-m-1'); $dd2  = date('Y-m-31');

$fd = mysqli_query($connection,"SELECT fltno,ago,ton,mix,(SELECT AVG(utilization) AS Ut1 FROM tab_fueltrans WHERE cast(refillDate as date) BETWEEN '$dd' AND '$dd2' AND weekno='week1'),control,
(SELECT AVG(utilization) AS Ut2 FROM tab_fueltrans WHERE cast(refillDate as date) BETWEEN '$dd' AND '$dd2' AND weekno='week2'),control,
(SELECT AVG(utilization) AS Ut3 FROM tab_fueltrans WHERE cast(refillDate as date) BETWEEN '$dd' AND '$dd2' AND weekno='week3'),control,
(SELECT AVG(utilization) AS Ut4 FROM tab_fueltrans WHERE cast(refillDate as date) BETWEEN '$dd' AND '$dd2' AND weekno='week4'),control,
(SELECT AVG(utilization) AS Ut5 FROM tab_fueltrans WHERE cast(refillDate as date) BETWEEN '$dd' AND '$dd2' AND weekno='week5'),control,
 FROM tab_fueltrans WHERE cast(refillDate as date) BETWEEN '$dd' AND '$dd2' GROUP BY fltno");


while($rw = mysqli_fetch_array($fd)){
$c++;?>
<tr>
     <td nowrap="nowrap"><?php echo $c;?></td>
      <td nowrap="nowrap"><?php echo $rw['fltno'];?></td>
    <td nowrap="nowrap"><?php echo $rw['ago'];?></td>
    <td nowrap="nowrap"><?php echo $rw['ton'];?></td>
   <td nowrap="nowrap"><?php echo $rw['mix'];?></td>
   <td nowrap="nowrap"><?php echo $rw['Ut1'];?></td>
    <td nowrap="nowrap"><?php echo $rw['control'];?></td>
     <td nowrap="nowrap"><?php echo $rw['Ut2'];?></td>
      <td nowrap="nowrap"><?php echo $rw['control'];?></td>
      <td nowrap="nowrap"><?php echo $rw['Ut3'];?></td>
      <td nowrap="nowrap"><?php echo $rw['control'];?></td>
      <td nowrap="nowrap"><?php echo $rw['Ut4'];?></td>
      <td nowrap="nowrap"><?php echo $rw['control'];?></td>
      <td nowrap="nowrap"><?php echo $rw['Ut5'];?></td>
      </tr>

<?php };}?>
</tbody>

The Above fetches result but display that those average columns are unknown like : Undefined index: Ut1,Undefined index: Ut2,Undefined index: Ut3,Undefined index: Ut4,Undefined index: Ut5.Please, is there something I am doing wrong? Any guide /assistance will be appreciated.

Upvotes: 0

Views: 94

Answers (1)

R. Chappell
R. Chappell

Reputation: 1282

MySQL will use the given column definition or expression as the column alias if no alias is provided. In your case it will be using your sub select statements as the column alias. When it gets returned to your script, instead of the alias you used within the sub select, it will actually be the entire sub select string as the key.

An example of this will be:

SELECT col1, col2 FROM tbl1 WHERE col1 > 10;

The keys for this query will be col1 and col2. However when we change this query to include an expression:

SELECT AVG(col1), col2 FROM tbl1 WHERE col1 > 10;

We now get the keys AVG(col1) and col2. This is because MySQL is using the expression as the column key. If we change this query to use a column alias:

SELECT AVG(col1) AS col1avg, col2 FROM tbl1 WHERE col1 > 10;

We should get our keys as col1avg and col2. This is the same when we use a sub select to get a column value per row. The sub select becomes our key if we do not provide a column alias, this is the same even if you provide a column alias within your sub select. When using expressions (functions, cases, sub selects) it is always best practice to give that column its own alias.

Also you should define aliases when using multiple tables which contain the same column names. A common scenario is id which can be common place across many tables. This will prevent any ambiguity and help you with your query.

With reference to your example and using sub selects, you should be giving all of your sub select expressions unique aliases.

SELECT
    col1,
    (SELECT AVG(value) FROM tbl2) AS tbl2avg,
    (SELECT MAX(value) AS max FROM tbl2) AS tbl2max,
    col2,
    col3 AS newCol
FROM
    tbl1
WHERE
    col1 > 10;

With the above query we should be getting the keys col1, tbl2avg, tbl2max, col2 and newCol as they have all been defined as our column aliases.

Upvotes: 1

Related Questions