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'];
}
:idea:

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.