Page 1 of 1
MySQL question.
Posted: Thu May 15, 2008 8:28 pm
by jelkimantis
ok, so I've read three different explanations of join, but none of them seem to actually do what I know join does... Ok, so let me restate that, one of them shows that join does what I want, but doesn't explain how to get there.
I have a database for recipies. In this recipe DB, each recipe has an average of 6 cards (I know, non-standard, it's for children with aspergers). I might be wrong, but here's the setup I have.
tables---
recipe:
recipe_id
recipe_name
recipe_card:
recipe_id
card_id
card:
card_id
card_number
line
format
image
Ok, now I need find the recipe, and have all the cards (of a variable amount) show up in the correct order. ok so the order is going to be determined by my php, that's no problem, but how do I join? I thought of a way I could do this in php, but it seems that I would be taking forever doing it and it would be redundent.
jsl
Re: MySQL question.
Posted: Thu May 15, 2008 9:18 pm
by dann
SELECT recipe.*, card.* FROM recipe
JOIN recipe_card ON recipe.recipe_id = recipe_card.recipe_id
JOIN card ON recipe_card.card_id = card.card_id
WHERE recipe.recipe_name like '%some_name%'
A join takes into consideration the match across tables and the where clause. So you won't get data that does not match the syntax. So if you have a recipe card that does not link to anything in the card_recipe table or card table it won't show. A left join on the other hand preserves the table to the left in that it will return all values that match in the recipe table regardless if there is a value in the recipe_card or card tables.
For instance let's say you had this:
recipe_id recipe_name
0001 tacos
0002 tacos
0003 sweet bread
0004 tacos
recipe_id card_id
0001 0001
0002
0003 0002
0004 0003
card_id card_number
0001 33
0002 44
0003 22
the regular join would return these results:
SELECT recipe.*, card.* FROM recipe
JOIN recipe_card ON recipe.recipe_id = recipe_card.recipe_id
JOIN card ON recipe_card.card_id = card.card_id
WHERE recipe.recipe_name like '%taco%'
0001 tacos 0001 33
0004 tacos 0003 22
but the left join would return:
SELECT recipe.*, card.* FROM recipe
LEFT JOIN recipe_card ON recipe.recipe_id = recipe_card.recipe_id
LEFT JOIN card ON recipe_card.card_id = card.card_id
WHERE recipe.recipe_name like '%taco%'
0001 tacos 0001 33
0002 tacos
0004 tacos 0003 22
Left joins are much more resource intensive. Does that help at all?
Re: MySQL question.
Posted: Fri May 16, 2008 7:58 am
by jelkimantis
That makes sense. It actually makes perfect sense, because really the way I have the DB setup, I just need the card ID for most of it (the lines are stored individually as well). (don't you hate it when people talk to themselves in a public message board? I'm sure no one cares about this who paragraph... Or the next one... probably, the only paragraph you need to read is the last one where I don adulations to our hero.)
and then I can just write php to ignore the rest of the data, use the card ID's to make the cards and dump them into the pdf file...
dann, you are again my hero.
Re: MySQL question.
Posted: Thu May 29, 2008 10:19 am
by jelkimantis
oh massive guru of php & mysql.
Here is the relevant code, assume that it's being called correctly (because I'm not going to dump the complete classes in here.
Code: Select all
private function get_cards($rid){
$db = new database();
$db->connect();
$query = sprintf(
"SELECT card.cid FROM recipe JOIN recipe_card on recipe.rid = recipe_card.rid JOIN card on recipe_card.cid = card.cid WHERE recipe.rid = %d",
mysql_real_escape_string($rid));
$result = $db->select($query);
$db->close();
//print_r($result);
unset($query, $rid, $db);
return $result;
which calls:
Code: Select all
public function select($query){
$result = mysql_query($query);
//print_r($result);
if(!$result) {
die('Invalide entry, please contact site administrator with: <br>'.mysql_error());
}
$data=mysql_fetch_assoc($result);
print_r($data);
unset ($result);
return $data;
}
Now, for rid=6, there are three cards. the mysql query listed above, verbatim, when put into mysql directly, shows 3 cards associated with rid=6. When I use this php to retrieve this information, it gives me 1 card. (Which happens to be a blank card).
I'm not sure why this is, because I have a similar few lines of code which retrieve use permissions, and all the user permissions show up using the $db->select($query);
Any guidance you could give me would be greatly appreciated.
jsl