Help With Selecting Data [mysql]

[Savvi]

card table code (the names of the fields is in the tables)

	<?php	$d='</td><td>';	for ($k=0;$k<$n;$k++) {				$sql = mysql_fetch_assoc($result);					echo ('<tr><td id="emptyTableBar"><a href="individualCard.php?id='.$sql["id"].'"><img src="images/selectCardButton.jpg" border="0" margin="1"></a>'.$d.$sql["id"].$d.$sql["valid_from"].$d.$sql["expiry_date"].$d.$sql["person_id"].'</td></tr>'); }	?>

competitor table code

<?php	$d='</td><td>';	for ($k=0;$k<$n;$k++) {											$sql = mysql_fetch_assoc($result);					echo ('<tr><td id="emptyTableBar"><a href="individual.php?id='.$sql["id"].'"><img src="images/selectButton.jpg" border="0" margin="1"></a>'.$d.$sql["id"].$d.$sql["first"].$d.$sql["last"].$d.$sql["dob"].$d.$sql["nation"].$d.$sql["sport"].$d.$sql["type"].'</td></tr>'); }	?>

This is what I got so far

$currentDate = getdate();$query = 'SELECT * FROM `person` WHERE 1 LIMIT 0, 30'; $result=mysql_query($query, MyActiveRecord::Connection() );$n=mysql_num_rows($result);$query2 = 'SELECT * FROM `card` WHERE 'expiry_date'';if ($query2 < $currentDate) {	//don't know what to put in here yet =_=;}

May someone in this forum give me a example of how to do this?(I'm very new to this ; )Lots of thanksSavvi

[Synook]

Is it a one-to-one relationship between competitors and cards?You can do this in a single query (that is, if the "certain data" you want to select is competitors with expired cards):

SELECT Competitors.*, `Expiry date` FROM Competitors, Cards WHERE `Expiry date` < DATE(NOW()) AND `Person ID` = Competitors.ID

[Savvi]

Is it a one-to-one relationship between competitors and cards?You can do this in a single query (that is, if the "certain data" you want to select is competitors with expired cards):

SELECT Competitors.*, `Expiry date` FROM Competitors, Cards WHERE `Expiry date` < DATE(NOW()) AND `Person ID` = Competitors.ID

Good question that you've brang up.Its a one-to-many.The project gives us this:[person]-<[cards]-<[card_venue]>-[venue]I'm going to try out the code you given ::update::changed the code to fit my usage. Working, but returns no results in the table.

$query = "SELECT person.*, 'expiry_date' FROM person, card WHERE 'expiry_date' < DATE(NOW()) AND 'person_id'=person.id";

[Synook]

Column names, if you so wish, are enclosed in backticks (`), not single quotation marks ('). However, if the column name has no spaces, and is not a reserved keyword, you don't need to delimit it.You can use mysql_error() to figure out whether a certain statement has an error.Note that because of the one-to-many relationship you may want to use GROUP BY to limit what information you get (e.g. if a person has multiple expired cards, how many times do you want them to appear?).

[Savvi]

Column names, if you so wish, are enclosed in backticks (`), not single quotation marks ('). However, if the column name has no spaces, and is not a reserved keyword, you don't need to delimit it.You can use mysql_error() to figure out whether a certain statement has an error.Note that because of the one-to-many relationship you may want to use GROUP BY to limit what information you get (e.g. if a person has multiple expired cards, how many times do you want them to appear?).

My bad for not using backtick (`), I'm sort of new to this ^^The GROUP BY is something we needed, thank you Synook.Anyways, we sorted out both looking for valid and expired codes.Instead of

SELECT person.*, `expiry_date` FROM . . .

I just made it as

SELECT * FROM card, person . . .

Thanks both for the help!I think we got two hard problems to solve coming soon. Will post new problems when I can't figure out anything ^^;