Data between dates again

[harrence]

Hi, my first post.
I'm trying to gather attendance records between two dates the user selects.
I have two tables 'attendance' and 'artists_details' which I'm hoping I've
successfully joined. The field I'm working on is just above the commented one
'ethnicity=maori'. I get no errors, however whatever dates I choose the number
returned is always 16,500 which is patently ridiculous. Any thoughts?
Regards Harrence

<?php
if ($logged_in=='true') { ?>
			<table border="0">
				<form name="date_picker" id="date_picker" method="post">
					<tr><td><h4>Please choose a period of time between two dates</h4>
						</td>
					</tr>
					<tr><td with="200" align="center">From:</td><td><a href="javascript:cal1.popup();"><input type="text" name="date1" value="" /></a>
						</td>
					</tr>
					<tr><td with="200" align="center">To:</td><td><a href="javascript:cal2.popup();"><input type="text" name="date2" value="" /></a>
						</td>
	
					</tr>
						</form>
					</table>


<script type="text/javascript">var cal1 = new calendar1(document.forms['date_picker'].elements['date1']);
									var cal2 = new calendar1(document.forms['date_picker'].elements['date2']);</script>

		<form action="<?php echo $PHP_SELF; ?>" method="post" name="numbers" id="numbers">
<?php
$sql=mysql_query("SELECT artist_id FROM artists_details WHERE ethnicity='pacific islands'");
$num_rows_p = mysql_num_rows($sql);

$result=mysql_query("SELECT * FROM artists_details, attendance WHERE ethnicity='maori' AND date='record_id' BETWEEN 'date1' AND 'date2'");
$num_rows_m = mysql_num_rows($result);

//$result=mysql_query("SELECT artist_id FROM artists_details WHERE ethnicity='maori'");
//$num_rows_m = mysql_num_rows($result);

$result=mysql_query("SELECT artist_id FROM artists_details WHERE ethnicity='other'");
$num_rows_o = mysql_num_rows($result);

$result=mysql_query("SELECT artist_id FROM artists_details WHERE youth='youth'");
$num_rows_y = mysql_num_rows($result);

$result=mysql_query("SELECT artist_id FROM artists_details WHERE outreach='outreach'");
$num_rows_out = mysql_num_rows($result);

$result=mysql_query("SELECT artist_id FROM artists_details");
$num_rows_total = mysql_num_rows($result);

?>
			<table style="border: 1px solid #000000;" name="numbers" width="150">
				<tr><td>Pacific Islands</td><?php if (isset($_POST['submit'])){?><td><?php echo "$num_rows_p";}?></td></tr>
				<tr><td>Maori</td><?php if (isset($_POST['submit'])){?><td><?php echo "$num_rows_m";}?></td></tr>
				<tr><td>Other</td><?php if (isset($_POST['submit'])){?><td><?php echo "$num_rows_o";}?></td></tr>
				<tr><td>Youth</td><?php if (isset($_POST['submit'])){?><td><?php echo "$num_rows_y";}?></td></tr>
				<tr><td>Outreach</td><?php if (isset($_POST['submit'])){?><td><?php echo "$num_rows_out";}?></td></tr>
				<tr><td>Total Numbers</td><?php if (isset($_POST['submit'])){?><td><?php echo "$num_rows_total";?></td></tr>
				
				
				
				
			</table>

<?php
echo?><br /><a href="ksadmin.php">Back to admin</a>

<?php
		
	}

?><br />

<?php
 if (!isset($_POST['submit'])){ echo?>
			<input type="submit" name="submit" value="Get Numbers" />
		</form>
<?php
}
?>

<?php
}else{
echo "You are not authorised to view this page, please login or visit our home page";
?><br /><a href="login.php">Log in</a><br />
					<a href="../index.html">Home</a>
<?php
}
?>

[nav33n]

$result=mysql_query("SELECT * FROM artists_details, attendance WHERE ethnicity='maori' AND date='record_id' BETWEEN 'date1' AND 'date2'");

The query is wrong.
Try this.

$result=mysql_query("SELECT * FROM artists_details, attendance WHERE ethnicity='maori' AND date='record_id' and date BETWEEN 'date1' AND 'date2'");

You have to specify which column to check while using 'between' clause.

[harrence]

Wow!
Thanks so much Nav33.
Didn't solve but that's obviously something else
I'm struggling with.

'ethnicity = maori' now returns a result of '0';

Brilliant, thanks again.

Will do some more effort and post again.

harrence.

[harrence]

Hi
The code has changed considerably over the last couple of days.

I now have:

$from_date = $_REQUEST['date1'];
$to_date = $_REQUEST['date2'];

just below the session_start() etc.
and....

//use this method for variables always cause it works, '".$from_date."'
$sql = "SELECT * FROM artists_details JOIN attendance WHERE  artists_details.ethnicity='maori' AND attendance.date >= '".$from_date."' AND attendance.date <= '".$to_date."'";
echo $sql;
$result=mysql_query($sql);
$num_rows_m = mysql_num_rows($result);

The echoed sql gives something of a successful result...

SELECT * FROM artists_details JOIN attendance WHERE artists_details.ethnicity='maori' AND attendance.date >= '2008-06-01' AND attendance.date <= '2008-06-20'

BETWEEN was, or as far as I can tell, just gives a 0 or 1 value.

I'm still getting a statistics result of 19,890 which is way to high.
It's as though I'm selecting every record in every table.
Any ideas out there?

harrence.

[R0bb0b]

SELECT * FROM artists_details JOIN attendance WHERE artists_details.ethnicity='maori' AND attendance.date >= '2008-06-01' AND attendance.date <= '2008-06-20'

try

SELECT DISTINCT * FROM artists_details JOIN attendance WHERE artists_details.ethnicity='maori' AND attendance.date >= '2008-06-01' AND attendance.date <= '2008-06-20'

[harrence]

Sadly no,

SELECT DISTINCT * FROM artists_details JOIN attendance WHERE artists_details.ethnicity='maori' AND attendance.visit_date >= '2008-06-01' AND attendance.visit_date <= '2008-06-19'

Thanks all the same and I'll keep trying.
At least it's getting the date picker values.
Thanks Rob from harrence.

[nav33n]

Umm.. Just curious, how are you joining those 2 tables ? On what condition ?
If there is no condition for join, wouldn't it give the cartesian product of 2 tables ?
That is, first, it will select all the records from artists_details where ethnicity is 'maori'. Then, it will select the records from attendance table where visit_date >= June 1st and visit_date <= June 19th. :-/

[R0bb0b]

Umm.. Just curious, how are you joining those 2 tables ? On what condition ?
If there is no condition for join, wouldn't it give the cartesian product of 2 tables ?
That is, first, it will select all the records from artists_details where ethnicity is 'maori'. Then, it will select the records from attendance table where visit_date >= June 1st and visit_date <= June 19th. :-/

Good eye! I believe you are right.

[harrence]

Eureka!!

We knocked the '' off!

Finally got it in to my dense head I needed to use a common field between the two tables
namely 'artist_id'. I'd worked out by counting that the number of visits by Maori had been a number under 40. I got 35 and hit the roof, better than you know what.

$sql = "SELECT * FROM artists_details LEFT JOIN attendance ON attendance.record_id AND attendance.visit_date >= '".$from_date."' AND attendance.visit_date <= '".$to_date."' WHERE artists_details.ethnicity='maori' AND attendance.artist_id=artists_details.artist_id";
echo $sql;
$result=mysql_query($sql);
$num_rows_m = mysql_num_rows($result);

Thanks guys for all your help and interest,
Happy day Harrence.

[nav33n]

Eureka!!

We knocked the '' off!

Finally got it in to my dense head I needed to use a common field between the two tables
namely 'artist_id'. I'd worked out by counting that the number of visits by Maori had been a number under 40. I got 35 and hit the roof, better than you know what.

$sql = "SELECT * FROM artists_details LEFT JOIN attendance ON attendance.record_id AND attendance.visit_date >= '".$from_date."' AND attendance.visit_date <= '".$to_date."' WHERE artists_details.ethnicity='maori' AND attendance.artist_id=artists_details.artist_id";
echo $sql;
$result=mysql_query($sql);
$num_rows_m = mysql_num_rows($result);

Thanks guys for all your help and interest,
Happy day Harrence.

Bingo! Thats what I was talking about ! :) Congrats !

[harrence]

Thanks for that, but don't relax too soon.
Might query some refinements to the code.

Happy day and night. H