Jump to content


Photo

Multiple tables help with "WHERE"


  • Please log in to reply
1 reply to this topic

#1 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 20 September 2006 - 09:56 AM

What I have is a scores table. It has a list of every sports match, some are played and some are not.

What I want to do is make a playerlist, for who is playing in the next UNPLAYED sports match who are in teams 1, 2, 3 and 4. (There are 19 teams but I only want to do this for first 4).

Here is my code:

<?
require_once("connection.php");

	$sql  = "SELECT home, away, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `11`, `12`, `13`, `14`, `15`, `16`, `17`, `18`, `19` ";
	$sql .= "FROM scores ";
	$sql .= "WHERE team_id="1", "2", "3", "4" ";
	$sql .= "ORDER BY date";
	$sql .= "LIMIT 4";

	$result = mysql_query($sql);
	
	if(mysql_num_rows($result)!=0) {

	while(list($home, $away, $_1, $_2, $_3, $_4, $_5, $_6, $_7, $_8, $_9, $_10, $_11, $_12, $_13, $_14, $_15, $_16, $_17, $_18, $_19 ) = mysql_fetch_row($result)) {
	?>
Playerlist for match: <?=$home?> vs <?=$away?>
<br /><br />
 <table width="300" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="30"><strong>#</strong></td>
    <td width="270"><strong>Player</strong></td>
  </tr>
  <tr>
    <td>1</td>
    <td><?=$_1?></td>
  </tr>
  <tr>
    <td>2</td>
    <td><?=$_2?></td>
  </tr>
  <tr>
    <td>3</td>
    <td><?=$_3?></td>
  </tr>
  <tr>
    <td>4</td>
    <td><?=$_4?></td>
  </tr>
  <tr>
    <td>5</td>
    <td><?=$_5?></td>
  </tr>
  <tr>
    <td>6</td>
    <td><?=$_6?></td>
  </tr>
  <tr>
    <td>7</td>
    <td><?=$_7?></td>
  </tr>
  <tr>
    <td>8</td>
    <td><?=$_8?></td>
  </tr>
  <tr>
    <td>9</td>
    <td><?=$_9?></td>
  </tr>
  <tr>
    <td>10</td>
    <td><?=$_10?></td>
  </tr>
  <tr>
    <td>11</td>
    <td><?=$_11?></td>
  </tr>
  <tr>
    <td>12</td>
    <td><?=$_12?></td>
  </tr>
  <tr>
    <td>13</td>
    <td><?=$_13?></td>
  </tr>
  <tr>
    <td>14</td>
    <td><?=$_14?></td>
  </tr>
  <tr>
    <td>15</td>
    <td><?=$_15?></td>
  </tr>
  <tr>
    <td>16</td>
    <td><?=$_16?></td>
  </tr>
  <tr>
    <td>17</td>
    <td><?=$_17?></td>
  </tr>
  <tr>
    <td>18</td>
    <td><?=$_18?></td>
  </tr>
  <tr>
    <td>19</td>
    <td><?=$_19?></td>
  </tr>
</table>
<br/><br/>
	<?
			}
		}	
?>


As you can see the WHERE is probably wrong? I want it to do teams 1, 2, 3 and 4. The other thing is, how do I make it only display the matches with no score? So maybe:

if($score == "") or something? I really need help on what to do here.

The numbers you see 1 to 19 are the players.
~ Mutley.

#2 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 20 September 2006 - 11:03 AM

You need to use IN for your WHERE clause and you can use IS NULL for your scores, assuming the field is null by default.

<?php
$sql  = "SELECT home, away, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `11`, `12`, `13`, `14`, `15`, `16`, `17`, `18`, `19` ";
$sql .= "FROM scores ";
$sql .= "WHERE team_id IN (1, 2, 3, 4) ";
$sql .= "AND score IS NULL ";
$sql .= "ORDER BY date ";
$sql .= "LIMIT 4";
?>

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users