PHP MySQL Query Syntax

Looks like you're selecting every title where `url_title` starts with 'order_'.

This is not what you want?

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');		

Select allOpen 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

Looks like something is off with CodeIgniter, so you could force the = sign (untested):

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

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');	

Select allOpen in new window

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

Select allOpen 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?

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');	

Select allOpen in new window

$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_

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;
	}

Select allOpen in new window

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'); 

Select allOpen in new window

I have defined it.  Please see the code example above your last comment.

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.

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?

MAX CAST UNSIGNED!  I will need to read up on this.  Thanks for all of your help.  This is a big relief for me.

You're welcome.

CAST will transform from one variable type to another. In this case, from varchar to integer.