randommman randommman - 6 months ago 10
MySQL Question

Using multiple queries in one foreach when tables don't have matching column count

I am trying to create a timeline, that shows statuses and jobs from a company, I have 2 seperate tables:

Jobs
and
Statuses


Both tables don't match column numbers, so I cannot use a union all (to my knowledge). How can I go about doing what I want?

Jobs:

CREATE TABLE `jobs` (
`job_id` int(11) NOT NULL,
`job_company` int(11) NOT NULL,
`job_user` int(100) NOT NULL,
`job_name` varchar(100) NOT NULL,
`job_description` text NOT NULL,
`job_duration` int(11) NOT NULL,
`job_category` varchar(250) NOT NULL,
`job_employer` varchar(100) NOT NULL,
`job_enabled` int(2) NOT NULL DEFAULT '1',
`job_startdate` date NOT NULL,
`job_enddate` date NOT NULL,
`job_wage_type` varchar(15) NOT NULL,
`job_wage` decimal(13,2) NOT NULL,
`job_requirements` varchar(255) NOT NULL,
`job_application_start` date NOT NULL,
`job_application_end` date NOT NULL,
`job_experience` varchar(255) NOT NULL,
`job_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`job_live` varchar(10) NOT NULL,
`job_address_name` varchar(100) NOT NULL,
`job_address_street` varchar(100) NOT NULL,
`job_address_city` varchar(100) NOT NULL,
`job_address_zip` varchar(15) NOT NULL,
`job_country` varchar(100) NOT NULL,
`job_address_lat` varchar(50) NOT NULL,
`job_address_long` varchar(50) NOT NULL,
`job_address_differs` int(11) NOT NULL,
`job_type` varchar(15) NOT NULL,
`job_featured` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `jobs`
--

INSERT INTO `jobs` (`job_id`, `job_company`, `job_user`, `job_name`, `job_description`, `job_duration`, `job_category`, `job_employer`, `job_enabled`, `job_startdate`, `job_enddate`, `job_wage_type`, `job_wage`, `job_requirements`, `job_application_start`, `job_application_end`, `job_experience`, `job_date`, `job_live`, `job_address_name`, `job_address_street`, `job_address_city`, `job_address_zip`, `job_country`, `job_address_lat`, `job_address_long`, `job_address_differs`, `job_type`, `job_featured`) VALUES
(26, 1, 29, 'Senior Web developer', 'Test', 1, 'CEO & General Management;Information & Communication Technology', '1', 0, '2016-04-07', '2017-06-11', 'weekly', '12.50', '1', '2016-03-31', '2016-05-19', '2', '2016-03-31 06:07:19', 'live', 'london', 'london', 'london', 'london', 'United Kingdom', '51.4654277', '-0.2547461', 0, 'fulltime', NULL),


status table

CREATE TABLE `timeline_status` (
`timeline_status_id` int(11) NOT NULL,
`timeline_status_user` int(11) NOT NULL,
`timeline_status_privacy` varchar(25) NOT NULL DEFAULT 'public',
`timeline_status_type` varchar(25) NOT NULL DEFAULT 'user',
`timeline_status_profile_user` int(11) DEFAULT NULL,
`timeline_status_post` text NOT NULL,
`timeline_status_date` datetime NOT NULL,
`timeline_status_enabled` varchar(25) NOT NULL DEFAULT 'enabled',
`timeline_status_postedby` int(11) NOT NULL,
`timeline_status_post_type` varchar(50) NOT NULL,
`timeline_status_image_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `timeline_status`
--

INSERT INTO `timeline_status` (`timeline_status_id`, `timeline_status_user`, `timeline_status_privacy`, `timeline_status_type`, `timeline_status_profile_user`, `timeline_status_post`, `timeline_status_date`, `timeline_status_enabled`, `timeline_status_postedby`, `timeline_status_post_type`, `timeline_status_image_id`) VALUES
(47, 29, 'public', 'user', NULL, 'test', '2016-04-16 00:47:50', 'enabled', 0, '', 0);


What I am trying to accomplish is merging it all in one timeline, in a random organisation, I could do 2 foreaches, but that means one would be on top, and the other at the bottom.

So is this possible in any way?

Answer

This single query would produce one line containing both job and status row data. One row for each timeline_status

This should make your foreach loop easier to process than having 2 seperate results to keep in line with each other, you just need to look for a change in the job_id, to know when the row relates to the next job.

SELECT j.*, s.*
FROM jobs j
    JOIN timeline_status as s ON s.timeline_status_user = j.job_user
ORDER BY j.job_id

Of course the query would be more efficient if you limited the select to just those columns you actually want to use in the following processing.