Page 1 of 1
results from many tables
Posted: Wed Mar 15, 2006 1:28 pm
by ed209
I'm not great with sql, and I'm a bit stuck on a particular query.
Code: Select all
+--------------------------+
| Tables_in_thee_libraries |
+--------------------------+
| files |
| quotes |
| quotes_to_file |
+--------------------------+
The above tables + following query produce the results below.
Code: Select all
SELECT
q.`quote_id`, qf.`quote_id`, qf.`file_id`, f.`file_name`
FROM
quotes q, quotes_to_file qf, files f
WHERE
q.`quote_id` = qf.`quote_id`
AND
`quote_visible` = 2;
Code: Select all
+----------+----------+---------+----------------------------+
| QUOTE_ID | quote_id | file_id | file_name |
+----------+----------+---------+----------------------------+
| 2 | 2 | 19 | 708435091441845b8a8905.jpg |
+----------+----------+---------+----------------------------+
The problem is that if any of those tables are empty (or there are no rows returned from them) then the whole query returns a blank.
However, I would still like to be able to get the quote from the quotes table even if there are no files associated with it, is this possible?
Posted: Wed Mar 15, 2006 1:37 pm
by feyd
LEFT JOIN
Your query is using implicit inner joins. Inner joins require all associations exist. Left joins do not.
Code: Select all
SELECT
`q`.`quote_id`, `f`.`file_name`
FROM
`quotes` AS `q`
LEFT JOIN
`quotes_to_file` AS `qf`
ON
`q`.`quote_id` = `qf`.`quote_id`
LEFT JOIN
`files` AS `f`
ON
`qf`.`file_id` = `f`.`file_id`
WHERE
`q`.`quote_visible` = 2
or similar
Posted: Wed Mar 15, 2006 1:55 pm
by ed209
thanks!

Posted: Wed Mar 15, 2006 3:18 pm
by ed209
This may be more of a design issue with my code - but being new to JOIN, I'm not too sure about how to fix the following:
The above table quotes_to_file holds may files for a particular quote. So a query that returns 2 quotes may return 8 rows (assuming each quote has 4 associated files).
Code: Select all
[0] => stdClass Object
(
[quote_id] => 6
[file_id] => 22
[file_name] => 2407498184418809d779de.jpg
)
[1] => stdClass Object
(
[quote_id] => 6
[file_id] => 20
[file_name] => 67707718344188089aaeaa.jpg
)
etc...
With multiple rows from a DB I would usually loop through and extract the info:
Code: Select all
//$quotes = a function that returns an array of results
for($i=0; $i<count($quotes); $i++){
$output .= $quotes[$i]->quote_id;
$output .= $quotes[$i]->file_name;
}
However, I would be adding the same quote a multiple amount of times so I was thinking of storing the quote id in an array, if the quote id is in there, just grab the file else grab the lot:
Code: Select all
//$quotes = a function that returns an array of results
$got_these_quotes = array();
for($i=0; $i<count($quotes); $i++){
if(!in_array($quotes[$i]->quote_id, $got_these_quotes)){
$got_these_quotes[] = $quotes[$i]->quote_id;
$output .= $quotes[$i]->quote_id;
}
$output .= $quotes[$i]->file_name;
}
Is the above sensible, or should I split the query in two, one for quotes, one for associated files ?
Posted: Wed Mar 15, 2006 3:35 pm
by feyd
Code: Select all
// query stuff
// ...
$quote_files = array();
while($row = mysql_fetch_assoc($query))
{
if(!isset($quote_files[$row['query_id']]))
{
$quote_files[$row['query_id']] = array();
}
$quote_files[$row['query_id']][] = $row['file_name'];
}

Posted: Wed Mar 15, 2006 4:16 pm
by ed209
Code: Select all
// Grab all the images from the array returned buy database query
for($i=0; $i<count($quotes); $i++){
$quote_images[$quotes[$i]->quote_id][] = $news_items[$i]->file_name;
}
/* print_r($quote_images); where array element is quote_id
Array
(
[5] => Array
(
[0] => 3509498184418809d779de.jpg
[1] => 9127498184418809d779de.jpg
)
[6] => Array
(
[0] => 2407498184418809d779de.jpg
[1] => 1135757488441880938575e.jpg
[2] => 708435091441845b8a8905.jpg
)
)
*/
Code: Select all
// then output the quotes
for($i=0; $i<count($quotes); $i++){
if(!in_array($quotes[$i]->quote_id, $got_these_quotes)){
$output .= $quotes[$i]->quote_id;
// and for each quote - loop through it's images
for($c=0; $c<count( $quote_images[ $quotes[$i]->quote_id ] ); $c++){
$output .= '<img src="'.$quote_images[ $news_items[$i]->news_id ][$c].'" />';
}
}
}
I was heading in the right direction at least! Thanks for the short cut, there's hope for me yet.