results from many tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

results from many tables

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Post by ed209 »

thanks! :)
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Post 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 ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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:
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Post 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.
Post Reply