Quick SQL Query Question

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
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Quick SQL Query Question

Post by TheBrandon »

Hello everyone,

I'm trying to execute a join query and its executing somewhat correctly. It's joining the data, but it's pulling every instance where the data matches, as opposed to the most recent case which is all I want. There won't be a usual limit either; it's basically like a blog and the user could upload 0 pictures one day and 10 the next. So I'm not sure how to use a LIMIT in this case or anything else to make it work.

Here is the code I am using:

Code: Select all

SELECT surf_reports.surf_report_type, surf_report_images.image_data FROM surf_reports, surf_report_images WHERE surf_reports.surf_report_id=surf_report_images.report_id
It works in pulling the data, but it pulls every instance where the numbers match; so instead of getting "todays" entry, I get every entry that has a picture attached to it. Any ideas?

Any help would be much appreciated!

-Brandon
Last edited by Weirdan on Mon Jun 09, 2008 5:03 pm, edited 1 time in total.
Reason: sql tags
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Quick SQL Query Question

Post by Christopher »

Code: Select all

SELECT surf_reports.surf_report_type, surf_report_images.image_data FROM surf_reports LEFT JOIN surf_report_images ON surf_reports.surf_report_id=surf_report_images.report_id
Last edited by Weirdan on Mon Jun 09, 2008 5:03 pm, edited 1 time in total.
Reason: sql tags
(#10850)
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Quick SQL Query Question

Post by TheBrandon »

arborint wrote:

Code: Select all

SELECT surf_reports.surf_report_type, surf_report_images.image_data FROM surf_reports LEFT JOIN surf_report_images ON surf_reports.surf_report_id=surf_report_images.report_id

Thanks for the quick reply!

This is what that does in phpMyAdmin for me:
Image

Is that what it's supposed to do?

The query I posted does pretty much the same thing, just without the NULL values:
Image

Did I post your code wrong?

Basically in these images, I just want 44, but it won't always be 44, and it won't always have 3 instances of 44.

To explain exactly how it works, there is a form that updates a report on the website. It is assigned an auto increment value. Then there is another form to "attach" images to that post, and it gets the auto increment value of the post and assigns it in the "surf_report_id" field so the images know which post they go with.

But right now I can't pull just the newest post. Well, I can, but when it comes to the image part it pulls every image that has a matching ID value. That's basically where I'm running into trouble. I can post the code I am using for the whole thing if it will help, but it's a lot of code.

Again though; there is one form that adds an increment to a field. So 44 becomes 45 and so on. Auto incremented. Then the image form grabs that number, whatever it may be, and let's the user attach images to it, however many they want (if any). 1 day there could 0, 1 day 6, etc. But the problem I am having is that I don't know to say "only grab the newest group of ID's" instead of "grab every instance where this id corresponds with this id"

I hope I'm making sense. I don't know a lot of the technical terms yet. :)

(Note: in these images, I changed the "surf_report_type" and "image_data" values to also be the ID fields so I could see exactly how MySQL is manipulating the data)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Quick SQL Query Question

Post by Christopher »

If you don't want the NULLs then that is the job for WHERE.

Code: Select all

SELECT surf_reports.surf_report_type, surf_report_images.image_data FROM surf_reports LEFT JOIN surf_report_images ON surf_reports.surf_report_id=surf_report_images.report_id WHERE surf_report_images.image_data!=NULL
Last edited by Weirdan on Mon Jun 09, 2008 5:04 pm, edited 1 time in total.
Reason: sql tags
(#10850)
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Quick SQL Query Question

Post by TheBrandon »

arborint wrote:If you don't want the NULLs then that is the job for WHERE.

Code: Select all

SELECT surf_reports.surf_report_type, surf_report_images.image_data FROM surf_reports LEFT JOIN surf_report_images ON surf_reports.surf_report_id=surf_report_images.report_id WHERE surf_report_images.image_data!=NULL
Hmmm, this query gives this even if I make it the ID's:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0001 sec)

Here is the SQL to create the tables I am using:

Code: Select all

CREATE TABLE `surf_reports` (
  `surf_report_date` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `surf_report_type` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `surf_report_surface` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `surf_report_form` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `surf_report_wave_size` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `surf_report_comments` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `surf_report_id` smallint(6) NOT NULL AUTO_INCREMENT,
  `imgdata` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `imgone` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `imgtwo` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `imgthree` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `imgfour` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `title` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  `user` text character SET utf8 collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`surf_report_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=45 ;
 
-- 
-- Dumping data for table `surf_reports`
-- 
 
INSERT INTO `surf_reports` (`surf_report_date`, `surf_report_type`, `surf_report_surface`, `surf_report_form`, `surf_report_wave_size`, `surf_report_comments`, `surf_report_id`, `imgdata`, `imgone`, `imgtwo`, `imgthree`, `imgfour`, `title`, `user`) VALUES 
('2008-05-21', 'First', 'Semi Choppy', 'Poor to Fair', '1.1 Surf is waist high on sets. Looks knee high from the beach; brownish water and so so WSW winds.', 'New skim DVD''S, special order you KG Fish Today, from at once inventory sheet.', 18, '310w232.5hCK2Soka001.JPG', '', '', '', '', '', '70.183.227.110'),
('2008-06-05', 'First', 'semi choppy', 'POOR TO FAIR', '0.7 Surf is a hair bigger today, around knee high, with onshore winds, the models show a small increase starting on Friday thru the weekend', 'STEWART EPOXY AND POLY FUNBOARDS and LONGBOARDS--SUPER HOT *schools out for summer!!!!!!!!!!!!!!!!!!!', 31, '', '', '', '', '', '', '68.225.122.213'),
('2008-06-06', 'FIRST', 'semi choppy', 'fair', '0.8 Surf looks to be thigh to waist high with ESE winds on already this morning, looks OK but a few reports of jellies and some seaweed showing up', 'SCHOOLS OUT FOR SUMMER!!!!!!!!!!!!!!!!!', 32, '', '', '', '', '', '', '68.225.122.213'),
('', 'Test', '', '', '', '', 33, '', '', '', '', '', '', ''),
('', 'Test', '', '', '', '', 34, '', '', '', '', '', '', ''),
('', 'Test', '', '', '', '', 35, '', '', '', '', '', '', ''),
('', 'Test', '', '', '', '', 36, '', '', '', '', '', '', ''),
('', 'Test', '', '', '', '', 37, '', '', '', '', '', '', ''),
('', 'Test', '', '', '', '', 38, '', '', '', '', '', '', ''),
('', 'Test', '', '', '', '', 39, '', '', '', '', '', '', ''),
('', 'Test', '', '', '', '', 40, '', '', '', '', '', '', ''),
('', 'Test', '', '', '', '', 41, '', '', '', '', '', '', ''),
('', 'Test', '', '', '', '', 42, '', '', '', '', '', '', ''),
('', 'Test', '', '', '', '', 43, '', '', '', '', '', '', ''),
('', 'Test', '', '', '', '', 44, '', '', '', '', '', '', '');
 

Code: Select all

CREATE TABLE `surf_report_images` (
  `id` smallint(20) NOT NULL AUTO_INCREMENT,
  `report_id` smallint(20) NOT NULL,
  `image_data` text collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;
 
-- 
-- Dumping data for table `surf_report_images`
-- 
 
INSERT INTO `surf_report_images` (`id`, `report_id`, `image_data`) VALUES 
(1, 39, '21.jpg'),
(2, 42, '7.jpg'),
(3, 43, '4.jpg'),
(4, 43, '8.jpg'),
(5, 44, '21.jpg'),
(6, 44, '2.jpg'),
(7, 44, '9.jpg');
Any idea why that would show up zero?

Thank you very much for helping me. I really do appreciate it.
Last edited by Weirdan on Mon Jun 09, 2008 5:05 pm, edited 2 times in total.
Reason: sql tags
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Quick SQL Query Question

Post by Christopher »

Try: surf_report_images.image_data!=''
(#10850)
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Quick SQL Query Question

Post by TheBrandon »

arborint wrote:Try: surf_report_images.image_data!=''
That functions, but again it's result is basically the same as the query in my first post:

Code: Select all

SELECT surf_reports.surf_report_id, surf_report_images.report_id FROM surf_reports LEFT JOIN surf_report_images ON surf_reports.surf_report_id=surf_report_images.report_id WHERE surf_report_images.image_data!=''
Image

Code: Select all

SELECT surf_reports.surf_report_type, surf_report_images.image_data FROM surf_reports, surf_report_images WHERE surf_reports.surf_report_id=surf_report_images.report_id
Image

Any other ideas?

Thanks again man. I really do appreciate any help I can get with this stuff.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Quick SQL Query Question

Post by Christopher »

I reread you first post and found the hidden question. ;)

Code: Select all

SELECT DISTINCT surf_reports.surf_report_type, surf_report_images.image_data FROM surf_reports LEFT JOIN surf_report_images ON surf_reports.surf_report_id=surf_report_images.report_id WHERE surf_report_images.image_data!=NULL
(#10850)
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Quick SQL Query Question

Post by TheBrandon »

Hmm, that returns the "MySQL returned an empty result set (i.e. zero rows). (Query took 0.0002 sec)" thing as well.

If I switch it to the !='' thing like before, it gives this:
Image

So it seems to just group them, not select the newest one or anything. Least the !='' thing does.

Did I enter it wrong? I just copied this in there:

Code: Select all

SELECT DISTINCT surf_reports.surf_report_type, surf_report_images.image_data FROM surf_reports LEFT JOIN surf_report_images ON surf_reports.surf_report_id=surf_report_images.report_id WHERE surf_report_images.image_data!=NULL
I also tried this:

Code: Select all

SELECT DISTINCT surf_reports.surf_report_id, surf_report_images.report_id FROM surf_reports LEFT JOIN surf_report_images ON surf_reports.surf_report_id=surf_report_images.report_id WHERE surf_report_images.image_data!=NULL
And I also tried both of them with the !='' thing, as mentioned above.

I'm doing this inside the PHP My Admin SQL query thing, if that makes any difference. I'm not actually using any of this inside the script yet; just inside MySQL's built in SQL query thing to test stuff.
Post Reply