multi line query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

multi line query

Post by IGGt »

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?
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: multi line query

Post by Jade »

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.
User avatar
yacahuma
Forum Regular
Posts: 870
Joined: Sun Jul 01, 2007 7:11 am

Re: multi line query

Post by yacahuma »

is called heredoc

Code: Select all


$str = <<<EOT
This is a
demo message
with heredoc.
EOT;

echo $str;

User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: multi line query

Post by AbraCadaver »

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.
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: multi line query

Post by IGGt »

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.

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.
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 )
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: multi line query

Post by IGGt »

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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: multi line query

Post by VladSun »

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
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: multi line query

Post by IGGt »

VladSun wrote:I think $jobname is a string, so it must be put in single or double quotes in your 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 ' '
e.g. '$startdate' -> '2010-07-14'
Post Reply