If Inside of SELECT?

[Xtremer360]

As of right now I have values of 0 for the fields in champ_id as well as the con1_id, con2_id, and con3_id which is all fine. The end query returns 0 results which I understand however I'm trying to figure out that if the values in these fields is 0 then have it echo "Vacant" but i'm not sure if you can do a if statement inside a select statement.

 

$query = "SELECT 
titles.titlename,
    titles.id, 
( SELECT c.charactername FROM characters AS c WHERE c.id = champions.champ_id ) AS champion,
( SELECT c.charactername FROM characters AS c WHERE c.id = champions.con1_id ) AS con1, 
( SELECT c.charactername FROM characters AS c WHERE c.id = champions.con2_id ) AS con2, 
( SELECT c.charactername FROM characters AS c WHERE c.id = champions.con3_id ) AS con3
FROM 
     champions 
     INNER JOIN titles 
          ON champions.title_id = titles.id";

[JonnoTheDev]

If you want to return the values that have no associating records in other tables you must use a LEFT JOIN as opposed to an INNER JOIN. Then your if statement can be contained in the php loop when displaying the results so that if the value in a field is 0 it prints, 'vacant'

[Xtremer360]

Now I"m wondering if I can do this:

 

<?php 
            while ( $row = mysqli_fetch_array ( $result, MYSQL_ASSOC ) ) {
              echo '	
              <tr>
                <td><a href="#" title="' . $row['titlename'] . ' History">' . $row['titlename'] . '</a></td>
                <td>if (' . $row['champion'] . ' == 0 ) { echo "Vacant" } else { echo  . $row['champion'] .  }</td>
                <td>' . $row['con1'] . '</td>
                <td>' . $row['con2'] . '</td>
    			<td>' . $row['con3'] . '</td>
                <td style="text-align:center;"><img src="img/notepad.png" class="edit" rel="' . $row['id'] . '"/></td>
    		  </tr>';
            }
            ?>   

 

or if I have to do some if statement after the query.

[Maq]

Sure, something like this:

 

 ' . echo ($row['champion']==0) ? "Vacant" : "$row['champion']" . ' 

[Xtremer360]

while ( $row = mysqli_fetch_array ( $result, MYSQL_ASSOC ) ) {

              echo '

              <tr>

                <td><a href="#" title="' . $row['titlename'] . ' History">' . $row['titlename'] . '</a></td>

                <td> ' . echo ($row['champion']==0) ? "Vacant" : "$row['champion']" . ' </td>

                <td>' . $row['con1'] . '</td>

                <td>' . $row['con2'] . '</td>

    <td>' . $row['con3'] . '</td>

                <td style="text-align:center;"><img src="img/notepad.png" class="edit" rel="' . $row['id'] . '"/></td>

      </tr>';

            }

            ?> 

 

produces an unexpected T_Echo error

[Maq]

Sorry, you didn't need that echo inside the echo

 

Try this:

 

 ' . ($row['champion']==0) ? "Vacant" : "$row['champion']" . ' 

[Xtremer360]

Now its

 

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/y/a/n/yankeefaninkc/html/efedmanager/mods/champions.php on line 117

[Maq]

Now its

 

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/y/a/n/yankeefaninkc/html/efedmanager/mods/champions.php on line 117

 

Can I see your current code?

[Xtremer360]

http://pastebin.com/PvTJuZez