MySQL Help

carnageX

carnageX

Private Joker,
Staff member
Messages
25,056
Location
Oregon
So, trying to run a MySQL query that uses a parameter as column name. Found a way to do it, however, it's not quite working...

Here's what I have (with test values in single quotes:

Code: 
SET @datalogs_query = CONCAT
(
	"SELECT DATE_FORMAT(date_session, '%Y-%m-%d') AS date_formatted, ", 'kff5201',
	" FROM raw_logs 
	WHERE (DATE(date_session) >= ", '2014-04-14', 
		" AND DATE(date_session) <= ", '2014-04-14', 
		" AND eml=", '''test@gmail.com''', 
	") ORDER BY date_formatted ASC"
);

-- prepared statement
PREPARE datalogs FROM @datalogs_query;
EXECUTE datalogs;

It works if I remove the entire WHERE clause contents... but I kind of need those to correctly run the query for what I want.

Anybody have an idea? Thanks!
 
I'm not sure why you're using concat. it looks like its a static string.

you point us towards the where clause. I would think the logic is filtering more results than expected. I would try running the static select query without the parameter. tweak it until you get desired results and add it back to the parameter.

After that you could run a select on the concat to preview what query you are sending into the prepared statement. Confirm it is what you expected.

you may want to change how the dates are selected; see below.

quoted:
WHERE DATE(mycolumn) = '2009-10-20'

or try this also and see whether it works on not. Does this helps

WHERE mycolumn LIKE '2009-10-20%'
 
office politics said:
I'm not sure why you're using concat. it looks like its a static string.
Click to expand...

Because that's the only way I found where using a parameter as a column will work. Otherwise, if I just use a normal query, it will give an error that the column was not found. Using the concat will make it a normal query string, and then I can pass that into the PREPARE statement to execute it.

you point us towards the where clause. I would think the logic is filtering more results than expected. I would try running the static select query without the parameter. tweak it until you get desired results and add it back to the parameter.
Click to expand...

Tried doing that. Like I said, I can get it to return results if I'm not using anything in the WHERE clause... but as soon as I add either the date range part, or the check on the 'eml' column, then it chokes and doesn't return any values. Not sure why, though.

After that you could run a select on the concat to preview what query you are sending into the prepared statement. Confirm it is what you expected.
Click to expand...
I'll try running the select on the concat; didn't think of that. I was trying to figure out how to make it so I could see the query string at run time, but couldn't. Doing the select makes sense, though, thanks.

you may want to change how the dates are selected; see below.

quoted:
WHERE DATE(mycolumn) = '2009-10-20'

or try this also and see whether it works on not. Does this helps

WHERE mycolumn LIKE '2009-10-20%'
Click to expand...

I don't believe using LIKE in this case will work, because I'm trying to filter based on a date range, not just a single date. I also tried using:

WHERE date_session BETWEEN beginDate AND endDate
WHERE DATE(date_session) BETWEEN beginDate AND endDate
WHERE DATE(date_session) >= beginDate AND DATE(date_session) <= endDate

None of those worked, either.

Also: date_session is a DATETIME type, just an fyi, hence the DATE() function use to extract just the date.

Edit: Here's my stored procedure that I'm trying to use, which fails as well (same format as above, except the test data is replaced with the actual input parameters):
Code: 
CREATE DEFINER=`root`@`%` PROCEDURE `spGetChartData`(parPIDType varchar(12), parBegDate date, parEndDate date, parEmail varchar(50))
BEGIN
	SET @datalogs_query = CONCAT
	(
		"SELECT DATE_FORMAT(date_session, '%Y-%m-%d') AS date_formatted, ", parPIDType, 
		" FROM raw_logs 
		WHERE date_session >= ", parBegDate, 
			" AND date_session <= ", parEndDate, 
			" AND eml = ", parEmail,
		" ORDER BY date_formatted ASC;"
	);

	-- prepared statement
	PREPARE datalogs FROM @datalogs_query;
	EXECUTE datalogs;
END
 
Last edited:
Yeah, I thought it looked right too... but apparently MySQL doesn't like it for some reason lol. Maybe I'll try splitting it up into nested queries or something.

Anyway... here's a SQL dump of one of the rows with real data:

Code: 
-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 16, 2014 at 12:54 AM
-- Server version: 5.5.24-log
-- PHP Version: 5.4.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `torque`
--

-- --------------------------------------------------------

--
-- Table structure for table `raw_logs`
--

CREATE TABLE `raw_logs` (
  `primary_id` int(25) NOT NULL AUTO_INCREMENT,
  `v` varchar(1) NOT NULL,
  `session` varchar(15) NOT NULL,
  `id` varchar(32) NOT NULL,
  `time` varchar(15) NOT NULL,
  `deleted` int(1) NOT NULL DEFAULT '0',
  `eml` varchar(50) NOT NULL DEFAULT '0',
  `kff1005` float NOT NULL DEFAULT '0' COMMENT 'GPS Longitude',
  `kff1006` float NOT NULL DEFAULT '0' COMMENT 'GPS Latitude',
  `kff1001` float NOT NULL DEFAULT '0' COMMENT 'Speed (gps)',
  `kff1007` float NOT NULL DEFAULT '0' COMMENT 'GPS Bearing',
  `k4` float NOT NULL DEFAULT '0' COMMENT 'Engine Load',
  `k2f` float NOT NULL DEFAULT '0' COMMENT 'Fuel Level',
  `k10` float NOT NULL DEFAULT '0' COMMENT 'Mass Air Flow Rate',
  `k11` float NOT NULL DEFAULT '0' COMMENT 'Throttle Position',
  `k5` float NOT NULL DEFAULT '0' COMMENT 'Engine Coolant Temp',
  `kc` float NOT NULL DEFAULT '0' COMMENT 'Engine RPM',
  `kd` float NOT NULL DEFAULT '0' COMMENT 'Speed (odb)',
  `kf` float NOT NULL DEFAULT '0' COMMENT 'Intake Air Temp',
  `kff1226` float NOT NULL DEFAULT '0' COMMENT 'Horsepower',
  `kff1220` float NOT NULL DEFAULT '0' COMMENT 'Accel (X)',
  `kff1221` float NOT NULL DEFAULT '0' COMMENT 'Accel (Y)',
  `k46` float NOT NULL DEFAULT '0' COMMENT 'Ambiant Air Temp',
  `kff1222` float NOT NULL DEFAULT '0' COMMENT 'Accel (Z)',
  `kff1201` float NOT NULL DEFAULT '0' COMMENT 'MPG Instant',
  `kff1202` float NOT NULL DEFAULT '0' COMMENT 'Turbo & Vacuum',
  `kff5201` float NOT NULL DEFAULT '0' COMMENT 'MPG Average',
  `kff1010` float NOT NULL DEFAULT '0' COMMENT 'GPS Altitude',
  `kff1223` float NOT NULL DEFAULT '0' COMMENT 'Acceleration Total',
  `kff1239` float NOT NULL DEFAULT '0' COMMENT 'GPS Accuracy',
  `kff123b` float NOT NULL DEFAULT '0' COMMENT 'GPS Bearing',
  `kff123a` float NOT NULL DEFAULT '0' COMMENT 'GPS Satelites',
  `kff1249` float NOT NULL DEFAULT '0' COMMENT 'Air Fuel Ratio (Measured)',
  `kff1258` float NOT NULL DEFAULT '0' COMMENT 'CO2 in g/km (Average)',
  `kff125d` float NOT NULL DEFAULT '0' COMMENT 'Fuel flow rate/hour',
  `kfe1805` float NOT NULL DEFAULT '0' COMMENT 'Transmission Temperature (Method 1)',
  `k47` float NOT NULL DEFAULT '0' COMMENT 'Absolute Throttle Position B',
  `k49` float NOT NULL DEFAULT '0' COMMENT 'Accelerator Pedal Position D',
  `k4a` float NOT NULL DEFAULT '0' COMMENT 'Accelerator Pedal Position E',
  `kff124d` float NOT NULL DEFAULT '0' COMMENT 'Air Fuel Ratio (Commanded)',
  `k33` float NOT NULL DEFAULT '0' COMMENT 'Barometric pressure (from vehicle)',
  `k3c` float NOT NULL DEFAULT '0' COMMENT 'Catalyst Temperature (Bank 1, Sensor 1)',
  `kff1273` float NOT NULL DEFAULT '0' COMMENT 'Engine kW (At the wheels)',
  `k43` float NOT NULL DEFAULT '0' COMMENT 'Enginer Load (Absolute)',
  `k32` float NOT NULL DEFAULT '0' COMMENT 'Evap System Vapour Pressure',
  `k23` float NOT NULL DEFAULT '0' COMMENT 'Fuel Rail Pressure',
  `k7` float NOT NULL DEFAULT '0' COMMENT 'Fuel Trim Bank 1 Long Term',
  `k15` float NOT NULL DEFAULT '0' COMMENT 'Fuel Trim Bank 1 Sensor 2',
  `k6` float NOT NULL DEFAULT '0' COMMENT 'Fuel Trim Bank 1 Short Term',
  `kb` float NOT NULL DEFAULT '0' COMMENT 'Intake Manifold Pressure',
  `k34` float NOT NULL DEFAULT '0' COMMENT 'O2 Sensor1 Equivalency Ratio (alternate)',
  `kff1215` float NOT NULL DEFAULT '0' COMMENT 'O2 Volts Bank 1 sensor 2',
  `k45` float NOT NULL DEFAULT '0' COMMENT 'Relative Throttle Position',
  `ke` float NOT NULL DEFAULT '0' COMMENT 'Timing Advance',
  `kff1225` float NOT NULL DEFAULT '0' COMMENT 'Torque',
  `kff1205` float NOT NULL DEFAULT '0' COMMENT 'Trip average MPG',
  `k1f` float NOT NULL DEFAULT '0' COMMENT 'Run time since engine start',
  `k42` float NOT NULL DEFAULT '0' COMMENT 'Voltage (Control Module)',
  `kff1238` float NOT NULL DEFAULT '0' COMMENT 'Voltage (OBD Adapter)',
  `kff1269` float NOT NULL DEFAULT '0' COMMENT 'Volumetric Efficiency (Calculated)',
  `k44` float NOT NULL DEFAULT '0' COMMENT 'Commanded Equivalence Radio (lambda)',
  `kff1271` float NOT NULL DEFAULT '0' COMMENT 'Fuel used (trip)',
  `kff1204` float NOT NULL DEFAULT '0' COMMENT 'Trip Distance',
  `kff1257` float NOT NULL DEFAULT '0' COMMENT 'kff1257',
  `profileName` varchar(255) NOT NULL DEFAULT '0',
  `profileFuelType` varchar(255) NOT NULL DEFAULT '0',
  `profileWeight` varchar(255) NOT NULL DEFAULT '0',
  `profileVe` varchar(255) NOT NULL DEFAULT '0',
  `profileFuelCost` varchar(255) NOT NULL DEFAULT '0',
  `date_session` datetime NOT NULL,
  `kff125a` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Fuel flow rate/minute',
  `kff1263` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Average trip speed (whilst moving only)',
  `notice` varchar(255) NOT NULL DEFAULT '0',
  `noticeClass` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1005` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1006` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1001` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1007` varchar(255) NOT NULL DEFAULT '0',
  `userUnit04` varchar(255) NOT NULL DEFAULT '0',
  `userUnit2f` varchar(255) NOT NULL DEFAULT '0',
  `userUnit11` varchar(255) NOT NULL DEFAULT '0',
  `userUnit05` varchar(255) NOT NULL DEFAULT '0',
  `userUnit0d` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1201` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff5201` varchar(255) NOT NULL DEFAULT '0',
  `userUnit0f` varchar(255) NOT NULL DEFAULT '0',
  `userUnit46` varchar(255) NOT NULL DEFAULT '0',
  `userUnit0c` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1220` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1221` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1222` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1202` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1239` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1010` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1249` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1258` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff125d` varchar(255) NOT NULL DEFAULT '0',
  `userUnit10` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1226` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1271` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff125a` varchar(255) NOT NULL DEFAULT '0',
  `userUnit5e` varchar(255) NOT NULL DEFAULT '0',
  `userUnit33` varchar(255) NOT NULL DEFAULT '0',
  `userUnit32` varchar(255) NOT NULL DEFAULT '0',
  `userUnit47` varchar(255) NOT NULL DEFAULT '0',
  `userUnit49` varchar(255) NOT NULL DEFAULT '0',
  `userUnit4a` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1263` varchar(255) NOT NULL DEFAULT '0',
  `userUnit07` varchar(255) NOT NULL DEFAULT '0',
  `userUnit15` varchar(255) NOT NULL DEFAULT '0',
  `userUnit06` varchar(255) NOT NULL DEFAULT '0',
  `userUnit0b` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1215` varchar(255) NOT NULL DEFAULT '0',
  `userUnit45` varchar(255) NOT NULL DEFAULT '0',
  `userUnit0e` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1225` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1205` varchar(255) NOT NULL DEFAULT '0',
  `userUnit42` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1238` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1269` varchar(255) NOT NULL DEFAULT '0',
  `userUnit44` varchar(255) NOT NULL DEFAULT '0',
  `userUnitff1204` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1005` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1006` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1001` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1007` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnit04` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnit2f` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnit11` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnit05` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnit0d` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1201` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff5201` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnit0f` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnit46` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnit0c` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1220` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1221` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1222` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1202` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1239` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1010` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1249` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1258` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff125d` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnit10` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1226` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff1271` varchar(255) NOT NULL DEFAULT '0',
  `defaultUnitff125a` varchar(255) NOT NULL DEFAULT '0',
  PRIMARY KEY (`primary_id`),
  KEY `session_3` (`session`,`id`,`deleted`),
  KEY `session` (`session`,`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `raw_logs`
--

INSERT INTO `raw_logs` (`primary_id`, `v`, `session`, `id`, `time`, `deleted`, `eml`, `kff1005`, `kff1006`, `kff1001`, `kff1007`, `k4`, `k2f`, `k10`, `k11`, `k5`, `kc`, `kd`, `kf`, `kff1226`, `kff1220`, `kff1221`, `k46`, `kff1222`, `kff1201`, `kff1202`, `kff5201`, `kff1010`, `kff1223`, `kff1239`, `kff123b`, `kff123a`, `kff1249`, `kff1258`, `kff125d`, `kfe1805`, `k47`, `k49`, `k4a`, `kff124d`, `k33`, `k3c`, `kff1273`, `k43`, `k32`, `k23`, `k7`, `k15`, `k6`, `kb`, `k34`, `kff1215`, `k45`, `ke`, `kff1225`, `kff1205`, `k1f`, `k42`, `kff1238`, `kff1269`, `k44`, `kff1271`, `kff1204`, `kff1257`, `profileName`, `profileFuelType`, `profileWeight`, `profileVe`, `profileFuelCost`, `date_session`, `kff125a`, `kff1263`, `notice`, `noticeClass`, `userUnitff1005`, `userUnitff1006`, `userUnitff1001`, `userUnitff1007`, `userUnit04`, `userUnit2f`, `userUnit11`, `userUnit05`, `userUnit0d`, `userUnitff1201`, `userUnitff5201`, `userUnit0f`, `userUnit46`, `userUnit0c`, `userUnitff1220`, `userUnitff1221`, `userUnitff1222`, `userUnitff1202`, `userUnitff1239`, `userUnitff1010`, `userUnitff1249`, `userUnitff1258`, `userUnitff125d`, `userUnit10`, `userUnitff1226`, `userUnitff1271`, `userUnitff125a`, `userUnit5e`, `userUnit33`, `userUnit32`, `userUnit47`, `userUnit49`, `userUnit4a`, `userUnitff1263`, `userUnit07`, `userUnit15`, `userUnit06`, `userUnit0b`, `userUnitff1215`, `userUnit45`, `userUnit0e`, `userUnitff1225`, `userUnitff1205`, `userUnit42`, `userUnitff1238`, `userUnitff1269`, `userUnit44`, `userUnitff1204`, `defaultUnitff1005`, `defaultUnitff1006`, `defaultUnitff1001`, `defaultUnitff1007`, `defaultUnit04`, `defaultUnit2f`, `defaultUnit11`, `defaultUnit05`, `defaultUnit0d`, `defaultUnitff1201`, `defaultUnitff5201`, `defaultUnit0f`, `defaultUnit46`, `defaultUnit0c`, `defaultUnitff1220`, `defaultUnitff1221`, `defaultUnitff1222`, `defaultUnitff1202`, `defaultUnitff1239`, `defaultUnitff1010`, `defaultUnitff1249`, `defaultUnitff1258`, `defaultUnitff125d`, `defaultUnit10`, `defaultUnitff1226`, `defaultUnitff1271`, `defaultUnitff125a`) VALUES
(279, '5', '1397480634668', '8a51b21b26fd10650deabccc109e0df1', '1397480906632', 0, 'test@gmail.com', -100.354, 44.3749, 46.2779, 89.9, 4.70588, 0, 6.47, 0.392157, 55, 1159.25, 45, -3, 4.17481, -0.369873, -0.0665283, 0, 0.966894, 44.7638, -10.2038, 18.6183, 441, 0, 3, 0, 0, 0, 630.669, 2.36456, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -21.0938, 0, -6.25, 31, 0, 0.63, 0, 29, 18.9141, 15.7613, 0, 0, 14.9, 56, 0, 0.401148, 1.46673, 0, '0', '0', '0', '0', '0', '2014-04-14 08:08:16', '39.409286', '32.469944', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');
 
Alright, well I got it working after a lot of different stuff.

Apparently I was missing some single quotes which was throwing everything off, or something.

Ended up putting everything on one line in the SET block for the stored proc, and troubleshooting from there. Then after getting that to work, I broke it down into separate lines for readability again. And now it looks like it works.

Final code:

Code: 
CREATE DEFINER=`root`@`%` PROCEDURE `spGetChartData`(parPIDType varchar(12), parBegDate date, parEndDate date, parEmail varchar(50))
BEGIN
	-- build query 
	SET @datalogs_query = CONCAT
	(
		"SELECT DATE_FORMAT(date_session, '%Y-%m-%d') AS date_formatted, ", parPIDType, " 
		FROM raw_logs WHERE DATE(date_session) >= '", parBegDate, "' AND DATE(date_session) <= '", parEndDate, "' 
			AND eml='", parEmail, "' 
		ORDER BY date_formatted ASC"
	);

	-- prepared statement
	PREPARE datalogs FROM @datalogs_query;
	EXECUTE datalogs;
END
 
You must log in or register to reply here.

Similar threads

danhodge
PHP Slim Post Request
Replies
6
Views
3K
carnageX
carnageX
M
Help With PHP Calender Code
2
Replies
18
Views
6K
crghsam
C
M
PHP Code to Edit MySQL Database
Replies
3
Views
2K
root
root
K
  • Poll
Need Your Help! FPS Drops & Stuttering on High-End PC
Replies
1
Views
2K
PP Mguire
PP Mguire
bevolasko
Nasty virus removal
Replies
9
Views
3K
TinyTek
T
Back
Top Bottom