Simple (hopefully) php IF statement not working (using Dreamweaver)

Hi there, new to the site, and must say, looks like a good resource! :slight_smile:

Anyway, i am trying to do a simple IF statement using Dreamweaver, and am a bit stuck - i know it’s a simple solution but seem to be having a blank, haha.

I have 3 MySQL tables - tbl_fighters, tbl_events and a tbl_link table (containing both fighter_id and event_id, using the 1:M scenario - 1 fighter has fights in many events etc).

On the page i have displayed fighter_id and event_id from tbl_link, and i want to display the event name (from tbl_event) where the event_id (again from tbl_event) is equal to event_id (from tbl_link). Hope that makes sense?

Below is the (relevant) code:


$colname_rs_link = "-1";
if (isset($_GET['fighter_id'])) {
  $colname_rs_link = $_GET['fighter_id'];
}
mysql_select_db($database_db_connect, $db_connect);
$query_rs_link = sprintf("SELECT * FROM tbl_link WHERE fighter_id = %s", GetSQLValueString($colname_rs_link, "int"));
$rs_link = mysql_query($query_rs_link, $db_connect) or die(mysql_error());
$row_rs_link = mysql_fetch_assoc($rs_link);
$totalRows_rs_link = mysql_num_rows($rs_link);

mysql_select_db($database_db_connect, $db_connect);
$query_rs_event = "SELECT * FROM tbl_event";
$rs_event = mysql_query($query_rs_event, $db_connect) or die(mysql_error());
$row_rs_event = mysql_fetch_assoc($rs_event);
$totalRows_rs_event = mysql_num_rows($rs_event);

?>

</head>

<body>
<table width="80%" border="1">
  <?php do { ?>
    <tr>
      <td><?php echo $row_rs_link['fighter_id']; ?></td>
      <td>
	  
      <?php echo $row_rs_link['event_id']; ?>
      
	  <?php if (($row_rs_event['event_id']) == ($row_rs_link['event_id'])) { /* this is the bit i presuming im doing wrong */ ?>
      <?php echo $row_rs_event['event_name']; ?>
      <?php } ?>
	  
      </td>
    </tr>
    <?php } while ($row_rs_link = mysql_fetch_assoc($rs_link)); ?>
</table>
</body>
</html>


Any help would be greatly appreciated.

Cheers

Hungarian notation on SQL tables? Really??

And no, you do NOT use an interceding table for a 1:M scenario. You use a foreign key. An interceding or “cross-reference” table is reserved for N:M relations

Is this N:M or 1:M? Well, it’s N:M because multiple fighters can be at multiple events.

Proper names for the tables are ‘fighters’, ‘events’, ‘fighters_events’. Link is too ambiguous a table name. Hungarian notation of tbl_ is a waste of typing - of course it’s a table, what else could it be?

Do NOT use the mysql_ library of functions – use [fphp]pdo[/fphp]

Going with the intent of your code - that you want to see all events for a fighter, it will look something like this.


$pdo = new PDO( '{CONNECTION STRING GOES HERE}');

$s = $pdo->prepare("
  SELECT 
   f.name AS fighter
   e.name AS event
   l.name AS location 
  FROM fighters f
  INNER JOIN fighters_events fe ON f.id = fe.fighters_id
  INNER JOIN events e ON e.id = fe.events_id
  INNER JOIN locations l ON l.id = e.locations_id
  WHERE f.id = ?
");

// With the statement created above, we now execute the query.
$s->execute(array($_GET['fighter_id']));

if ($s->errorInfo()) {
  // error code here.
}


Let’s look at this first - the query above differs from yours in a couple ways. First, I’m joining tables - this is the power of SQL - to break information up into areas of concern. You got that much I take it from your post but joining is where the magic really happens. Note I also show a possible 1:M relationship for your database - events can only have one location each, but many events may occur at a location (though not at the same time).

Second note the lack of a wildcard (*). It is not usually a good idea to use wildcards in queries.

Third, note the use of aliases on the table joins and on the query. Both forms of aliasing MySQL supports are used here. “name alias” is used on the joins, and for brevity the tables are aliased to single letters (which is fine if there aren’t a ton of them). The fields meanwhile are aliased using the “name AS alias” format. Note that since we joined we must specify the origin table for each field. If two tables have fields with identical names (all tables should have their primary key named ‘id’) then aliasing is forced.

PDO does our filtering for us. That is, you prepare the statement with PDO which returns a PDO_Statement object. That object is then passed an array with our parameters. The ? in the query marks where the parameter is to be passed and PDO figures out how to filter the input to prevent an SQL injection attack on its own.

If the query runs $s->errorInfo() will return an empty array, which evals as false. So if it’s true we get the error info.

Continuing to the HTML. We’ll switch to braceless syntax here which is the best method of writing PHP interspersed with HTML.


?>
<table width="80%" border="1">
  <? while($row = $s->fetch(PDO::FETCH_ASSOC)): ?>
    <tr>
      <td><?= $row['fighter'] ?></td>
      <td><?= $row['event'] ?></td>
      <td><?= $row['location'] ?></td>
    </tr>
   <? endwhile ?>
</table>

While, not do while. The fighter may not exist and the query returns an empty result in that event. $row is given an array from your query on each run, the PDO::FETCH_ASSOC constant means you get an array with the names you created using aliases during the query.

Minor note – you don’t need to have a ; before a ?> The ?> will terminate the line so omitting the ; is a little cleaner.

Short tags are used above. If they aren’t enable for your server you can go back to <?php for <? and <?php echo for <?=

I used them in this example because it’s quicker to type.

Thanks Michael, i will have a closer look at your code over the weekend, but hopefully it will be what i’m after. I have been out of the PHP loop for about 4 years now and am just getting back into it, hence me not knowing about PDO.

The reason i used a link table was, as you say, it is a n:m relationship overall, with many fighters being able to be at many events.

And as for my naming conventions, that was how i was trained initially to do it, so i’ve just stuck with it. :slight_smile:

If i have any more questions, i will let you know.

Cheers

Hungarian Notation has fallen out of favor because it is hard to maintain and misleads more often than it helps. More importantly, in a loosely typed language like PHP it is a very bad idea since the interpreter is not obligated to lock the data contained into any particular type unless you are extremely careful with it.

If the worry is confusing table names with SQL reserved words and functions, then backtick them likeThis. But putting tbl_ on all the tables is just… Stop doing it please - for the sake of the guy that comes after you to maintain it.

Well, i’ve been looking into PDO and it seems great, but now i have another problem - i can see the issue but the brain is foggy on a solution.

PHP:


$url_param = $_GET['event_id'];

$sql = 	"SELECT	fighter_id, fighter_last_name, pk_event_id, fighter_a, fighter_b, event_id 
		FROM fighters
		INNER JOIN fightcard
		ON (fighter_id = fighter_a) OR (fighter_id = fighter_b)
		INNER JOIN event 
		on pk_event_id = event_id
		WHERE event_id = '$url_param'
		";

$statement = $db->prepare($sql);

$statement->execute(array($_GET['run_request']));

HTML/PHP:


<table width="80%" border="1">
<?php while ($result = $statement->fetch()): ?>
<tr>
    <td>
	
    <?php if ($result['fighter_a'] == $result['fighter_id'] ) { ?>
    <?php echo $result['fighter_last_name']; ?> 
    <?php } ?>
     vs. 
	 
     <?php if ($result['fighter_b'] == $result['fighter_id'] ) { ?>
    <?php echo $result['fighter_last_name']; ?> 
    <?php } ?>
    </td>
	
 </tr>   
<?php endwhile ?>
</table>

I have a table called fightcard where i have 2 columns, fighter_a and fighter_b. I want to display the name of the respective fighter next to fighter_a and fighter_b by matching these fields to fighter_id from a table called fighters.

The result of this code is that it loops through and displays each fighter on a separate row, when it should be 2 fighters per row.

I guess i dont really need to use the ON statement, as i can match the fighter_id and fighter_a/fighter_b using the PHP code within the table, but am unsure if this is correct or not?

One last query- when is it appropriate to use a LEFT JOIN instead of INNER JOIN.

Thanks

LEFT JOIN is appropriate when you want everything from one table, regardless of if it matches anything from the other table.

To use your example; if you wanted to show all events, even if they have no fighters assigned to them yet, you would do a events LEFT JOIN fighters (probably with a GROUP BY thrown in there too).

To set a parameter into a query you must place a mark or a parameter name starting with a colon into the query body. The execute statement is given a parameter correctly for the question mark style, but because you didn’t put in the query where it goes it can’t do anything with it. Also, the hashing in of the $url_param should go in the parameters. So the where line becomes

WHERE event_id = :url

And the execute statement…

$statement->execute( array( ':url' => $url_request) ); 

Look over that much and if problems persist we’ll go over the rest of the code.

Correct - sorta. LEFT JOIN is implicitly a LEFT OUTER JOIN, but certain join conditions can cause MySQL to treat it as an INNER JOIN. If you simply state “LEFT JOIN” you leave it up to the db engine on how it’s going to bind the two tables.

The users most familiar with the nuances of join types (like r937) tend to hang out on the Database forum. A more detailed discussion of the differences of these join types should go there.

Or the MySQL subforum if the question is specifically MySQL related.

Correct - sorta. LEFT JOIN is implicitly a LEFT OUTER JOIN, but certain join conditions can cause MySQL to treat it as an INNER JOIN. If you simply state “LEFT JOIN” you leave it up to the db engine on how it’s going to bind the two tables

Unless you specify righttable IS NOT NULL, it will be a LEFT OUTER JOIN (No such real thing as a LEFT INNER JOIN, which is why it’s just “LEFT JOIN” or “INNER JOIN”). If you left join and then discard the null results, you’re being silly :wink:

Thanks for the reply Michael, but unfortunately my displayed results are still the same:

7 Rua vs. 8
7 vs. 8 Jones
9 Faber vs. 10
9 vs. 10 Wineland

when it should be displayed:

7 Rua vs. 8 Jones
9 Faber vs. 10 Wineland

Im presuming its the WHILE statement that i should be adjusting somewhere?


<table width="80%" border="1">
<?php while ($result = $statement->fetch()): ?>
<tr>
    <td>
	<?php echo $result['fighter_a']; ?> 
    <?php if ($result['fighter_a'] == $result['fighter_id'] ) { ?>
    <?php echo $result['fighter_last_name']; ?> 
    <?php } ?>
     vs. 
	 <?php echo $result['fighter_b']; ?>
     <?php if ($result['fighter_b'] == $result['fighter_id'] ) { ?>
    <?php echo $result['fighter_last_name']; ?> 
    <?php } ?>
    </td>
	
 </tr>   
<?php endwhile ?>
</table>

Thanks again for your help, and thanks to you others who replied RE: JOIN.

Hi guys, just thought i would let you know i figured it out - was quite simple really - the issue was where the table tags were positioned in the loop.


<table>
<?php while ($result = $statement->fetch()): ?>

	<?php  if ($result['fighter_a'] == $result['fighter_id'] ) { ?>
    <?php echo "<tr><td>", $result['fighter_last_name'], " vs."; } ?>
    
	<?php  if ($result['fighter_b'] == $result['fighter_id'] ) { ?>
    <?php echo $result['fighter_last_name'], "</td></tr>"; } ?>  
	
<?php endwhile ?>
</table>

By placing them within the PHP tags they did not keep looping for each result.