Jump to content

Help With Selecting Data [mysql]


Savvi
 Share

Recommended Posts

validhelp.pngcard 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

Edited by Savvi
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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";

Edited by Savvi
Link to comment
Share on other sites

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?).

Link to comment
Share on other sites

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 ^^;

Edited by Savvi
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...