Hi Guys,
I'm sure this is really simple, but I can't find the answer anywhere.
I have a MySQL query i am looking to run in php, but the query is quite large and I don't really want to type it all on one line as that would be a nightmare to look after. Is there a way I can run it over multiple lines?
multi line query
Moderator: General Moderators
Re: multi line query
I'm not sure what you mean running it over multiple lines.... if you're running mysql from console and you leave off the semicolon at the end you can hit the enter key as many times as you want... the query won't execute until you add the semicolon and hit enter.
Re: multi line query
is called heredoc
Code: Select all
$str = <<<EOT
This is a
demo message
with heredoc.
EOT;
echo $str;
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: multi line query
You can actually use any quoting that you want:
Code: Select all
$query = "
SELECT col1, col2, col3
FROM table_name
WHERE col1='something'
AND col2='something else'
AND col3=1
ORDER BY col1 DESC
";mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: multi line query
Maybe I'm confusing things, but what I have is this. My first page has a form into which I put the jobname, startdate and enddate, and this passes this data to the second page, and puts it into a MySQL query (see below), before displaying the result.
And what I was wondering is what I need to do so I can enter the PHP like this. I was assuming I would need to put something to tell it that the next line follows on as part of the previous line (like in VBS you use the _ character).
But this gives a parse error on line 11 ( $query="SELECT )
Code: Select all
<?php
$connection = mysql_connect("localhost:3310", "root", "");
//Set variables
$db = "db1";
$jobname = $_POST['jobname'];
$startdate = $_POST['startdate'];
$enddate = $_POST['enddate'];
$query="SELECT
CASE WHEN t.jobname >= $jobname AND LEFT(t.StartTime,10) > CURDATE() THEN 'today for future date'
WHEN t.jobname >= $jobname AND LEFT(t.StartTime,10) < CURDATE() THEN 'today for past date'
WHEN t.jobname >= $jobname AND LEFT(t.StartTime,10) = CURDATE() THEN 'TODAY FOR TODAY'
ELSE 'BEFORE TODAY'
END AS 'Created on db2'
,t.jobname AS 'JobName'
,t.task AS 'task'
,m.header AS 'Header'
,m.f10 AS 'F10'
,t.created AS 'Create Time'
,CONCAT(a.name,' ',a.surname) AS 'user'
,t.starttime AS 'Job StartTime'
,t.endtime AS 'Job EndTime'
FROM mf_task_jobs t
JOIN inbox m ON m.jobid=t.jobid
JOIN tab1 a ON a.id=t.receiver
WHERE t.customerid = 123
AND LEFT(t.starttime,10) BETWEEN $startdate AND $enddate
AND t.jobname REGEXP '^[[:digit:]]{6}\$'
AND t.jobid NOT IN ( SELECT t.jobid FROM jobs t
JOIN inbox m ON t.jobid=m.jobid
WHERE m.msgstatus IS not null
AND m.latest <> 1
AND t.customerid = 123
GROUP BY t.jobname
HAVING COUNT(t.jobname) >=2
)
AND t.deleted is null
GROUP BY t.jobname
HAVING COUNT(t.jobname) <2
ORDER BY t.jobname;";
?>
<head><head>
<body> etc. etc.
But this gives a parse error on line 11 ( $query="SELECT )
Re: multi line query
I'm not quite sure what I have done, but I'm not getting any error messages anymore. I think maybe I had some "" that I removed.
Re: multi line query
I think $jobname is a string, so it must be put in single or double quotes in your query.
There are 10 types of people in this world, those who understand binary and those who don't
Re: multi line query
It isn't $jobname that was the problem (as this is always a number -> 123456), but the $startdate and $enddate. to run in mysql they need to be enclosed by ' 'VladSun wrote:I think $jobname is a string, so it must be put in single or double quotes in your query.
e.g. '$startdate' -> '2010-07-14'