Member Avatar for 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
}
?>
  • 3 Contributors
  • 10 Replies
  • 95 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by harrence

Recommended Answers

All 10 Replies

Member Avatar for 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.

Member Avatar for 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.

Member Avatar for 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.

Member Avatar for 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'
Member Avatar for 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.

Member Avatar for 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. :-/

Member Avatar for 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.

Member Avatar for 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.

Member Avatar for 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 !

Member Avatar for harrence

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

Happy day and night. H

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.