PHP MySQL Query Syntax

I have the following query but I'm not sure about the syntax  at he WHERE.  I need to identify a url_title and the first six letters to the left.

<?php
public function policyNumber()	{	
		$query_id = $this->EE->db->select('title')
				->where(LEFT.( url_title 6)', 'order_')
				->order_by('title', 'desc')
				->limit('1')
				->get('exp_channel_titles');				
	
		$order_id = $query_id->result_array();
		
		$query_id->free_result();
		
			foreach ($order_id as $row)
			{
				$pre_policy_number = $row['title'];
				$policy_number = $pre_policy_number + 1;
			}
		return $policy_number;
	}
?>

Open in new window

LVL 8
rgranlundAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dan CraciunIT ConsultantCommented:
Looks like you're selecting every title where `url_title` starts with 'order_'.

This is not what you want?
rgranlundAuthor Commented:
Here is a cleaner version with the error:

<?php
$query_id = $this->EE->db->select('title')
				->where('LEFT(url_title, 6)', 'order_')
				->order_by('title', 'desc')
				->limit('1')
				->get('exp_channel_titles');		

Open in new window

ERROR Message:
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''order_' ORDER BY `title` desc LIMIT 1' at line 3

SELECT `title` FROM (`exp_channel_titles`) WHERE LEFT(url_title, 6) 'order_' ORDER BY `title` desc LIMIT 1
Dan CraciunIT ConsultantCommented:
Looks like something is off with CodeIgniter, so you could force the = sign (untested):

->where('LEFT(url_title, 6) =', 'order_')
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

rgranlundAuthor Commented:
That was it.  However I realize I need to match those two and I'm not sure how.
$query_id = $this->EE->db->select('title')
				->where('LEFT(url_title, 6)', 'order_') AS matching
->where('title=', 'matching')				
->order_by('title', 'desc')
				->limit('1')
				->get('exp_channel_titles');	

Open in new window

Dan CraciunIT ConsultantCommented:
Your query translates to:
SELECT `title` FROM (`exp_channel_titles`) 
  WHERE LEFT(url_title, 6) = 'order_' AS matching
    AND `title` = 'matching'
  ORDER BY `title` desc 
  LIMIT 1

Open in new window

which is invalid. AS should be used in the FROM clause, not in the WHERE.

Maybe you could write the query in plain English and we could go from there?
rgranlundAuthor Commented:
OK, Thanks Dan.  In my DB table I have two columns.  title & url_title
My CMS (expression Engine) stores the data like this;
title = 300
url_title = order_300

However in the table there are different types of values stored in the title and url_title columns.  They don't always stay in the same naming convention.  I want to select the highest number in title column when in the corresponding url_title is formatted like this; order_NUMBER.
So if I have:
title             url_title
100             order_100
Fast             fast_car
New Page   404
200             order_200
300             order_300

My Query will return "300"
This is close:
<?php
		$query_id = $this->EE->db->select('title', 'url_title as match')
				->where('LEFT(url_title, 6)=', 'order_')
				->where('title =', 'match')
				->where('channel_id', '29')
				->order_by('title', 'desc')
				->limit('1')
				->get('exp_channel_titles');	

Open in new window

Dan CraciunIT ConsultantCommented:
$query_id = $this->EE->db->select('MAX(title)', 'url_title as match')
                        ->where('LEFT(url_title, 6)=', 'order_')
                        ->where('channel_id', '29')
                        ->order_by('url_title', 'desc')
                        ->limit('1')
                        ->get('exp_channel_titles');      

will return the highest value in the 'title' column from the rows where url_title starts with order_
rgranlundAuthor Commented:
I get an error that says title is not defined.  Is the query complete? What about "match"?
<?php
		$query_id = $this->EE->db->select('MAX(title)', 'url_title')
                        ->where('LEFT(url_title, 6)=', 'order_')
                        ->where('channel_id', '29')
                        ->order_by('url_title', 'desc')
                        ->limit('1')
                        ->get('exp_channel_titles');     				
	
		$order_id = $query_id->result_array();
		
		$query_id->free_result();
		
			foreach ($order_id as $row)
			{
				$policy_number = $row['title'];
			}
		return $policy_number;
	}

Open in new window

Dan CraciunIT ConsultantCommented:
If you're going to use title further down the road, then it need to be defined:
$query_id = $this->EE->db->select('MAX(title) AS title', 'url_title AS match')
                        ->where('LEFT(url_title, 6)=', 'order_')
                        ->where('channel_id', '29')
                        ->order_by('url_title', 'desc')
                        ->limit('1')
                        ->get('exp_channel_titles'); 

Open in new window

rgranlundAuthor Commented:
I have defined it.  Please see the code example above your last comment.
Dan CraciunIT ConsultantCommented:
Nope, you haven't. Your code will return 2 columns, one named `MAX(title)` and one named `url_title`.

When, further down, you're requesting
$policy_number = $row['title'];
there is no such column.
rgranlundAuthor Commented:
Oh man, geez. Sorry about that.  The query grabs the entry 99.  However, there are higher numbers in that column.  Also, the Column  is by design a VARCHAR column.  Does that matter?  Should I put in something that says only look for a number?  Or should I change my query to grab the title from where order_Number is the highest?
Dan CraciunIT ConsultantCommented:
If the title column is VARCHAR, then I suspect the result is correct. The string '99' is bigger, alphabetically, than the string '300'.

Try this:
$query_id = $this->EE->db->select('MAX(CAST(title AS UNSIGNED)) AS title', 'url_title AS match')
                        ->where('LEFT(url_title, 6)=', 'order_')
                        ->where('channel_id', '29')
                        ->order_by('url_title', 'desc')
                        ->limit('1')
                        ->get('exp_channel_titles'); 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rgranlundAuthor Commented:
MAX CAST UNSIGNED!  I will need to read up on this.  Thanks for all of your help.  This is a big relief for me.
Dan CraciunIT ConsultantCommented:
You're welcome.

CAST will transform from one variable type to another. In this case, from varchar to integer.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.