MySQL and PHP Woes

[adamwhiles]

I'm having an issue selecting rows across multiple tables. Basically I have tables monday, tuesday, wednesday, thursday and friday and they all have a common field named lode. The field lode is not the same for each row across the tables like a primary key, lode is different in most instances. What I need to do is search all tables and find the rows where lode is equal to 7.

 

I tried this but figured it couldn't be that easy and it didn't work. Any ideas?

 

$result = mysql_query("SELECT * FROM monday, tuesday, wednesday, thursday, friday WHERE lode='7'") or die(mysql_error());

[ignace]

Your lode is ambiguous it exists 5 times.

 

SELECT * FROM monday m, tuesday t, wednesday w, thursday th, friday f WHERE m.lode='7' OR t.lode='7' OR ..

[adamwhiles]

Thanks! It works somewhat now. Now it creates a lot of duplicate entries. Here is the whole code segment.

 

$result = mysql_query("SELECT * FROM monday m, tuesday t, wednesday w, thursday th, friday f WHERE m.lode='7' OR t.lode='7' OR w.lode='7' OR th.lode='7' OR f.lode='7'") or die(mysql_error());
?>
<form action="test.php" name="unavailable_users" method="post">
<select name="listbox" size="3">
<?
while ($row = mysql_fetch_assoc($result)) {
?>
<option value="Option 1"><?echo "$row[name]"; ?>
<?
}
?>
?>
</select>
<input type="submit" name="edit_user_go" value="Make User Available" />
</form>


<?

[ignace]

Could you please post your db scheme instead you clearly have a cartesian problem

[adamwhiles]

basically I have 5 tables that look like this for monday through friday

CREATE TABLE IF NOT EXISTS `monday` (
  `name` varchar(60) NOT NULL,
  `lode` int(1) NOT NULL default '0',
  `slot1` int(1) NOT NULL default '0',
  `slot2` int(1) NOT NULL default '0',
  `slot3` int(1) NOT NULL default '0',
  `slot4` int(1) NOT NULL default '0',
  `slot5` int(1) NOT NULL default '0',
  `slot6` int(1) NOT NULL default '0',
  `slot7` int(1) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

name represent the intructors name, load represents the number of slots the instructor is booked for on that day and each slot represents a time slot for that day

[ignace]

Are you using this to create an agenda?

[DavidAM]

Since the 7 tables are not JOINed in anyway, you are definitely getting a cartesian product.

I think you are going to have to use a UNION:

 

SELECT 'Mon' as Day, m.* FROM monday m WHERE lode='7' 
UNION
SELECT 'Tue' as Day,t.* FROM tuesday t WHERE lode = '7'
UNION  
...

 

OR - I would consider combining the tables into one and adding a column for the Day of Week:

 

CREATE TABLE IF NOT EXISTS `weekdays` (
  `dayofweek` varchar(9) NOT NULL,
  `name` varchar(60) NOT NULL,
  `lode` int(1) NOT NULL default '0',
  `slot1` int(1) NOT NULL default '0',
  `slot2` int(1) NOT NULL default '0',
  `slot3` int(1) NOT NULL default '0',
  `slot4` int(1) NOT NULL default '0',
  `slot5` int(1) NOT NULL default '0',
  `slot6` int(1) NOT NULL default '0',
  `slot7` int(1) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;