We help IT Professionals succeed at work.

MySQL - Adding an incremented value to a duplicate string

RationalRabbit
on
138 Views
Last Modified: 2017-04-28
Inserting a title into a table, if it is a duplicate, I need to number it, such as Title Title 2 Title 3, etc. I thought I could just compare the new title with those in the table by getting the length, then doing something like

"SELECT field FROM tablename WHERE LEFT(field,".$Length.") = '$NewTitle'"

then just count the rows and increment to create the new title and store it. Guess that's not legal. Can't seem to find a simple solution. The entries come through an API, so I can't easily ask users to rename a title if it conflicts.

I came up with this, which doesn't work, but I really don't understand why.

Even if this DID work, it would still pose a problem, as, if you have the title "This Title", and you send another title called "This", since you are using the Title string length, you would find everything that starts with the name "This", so it might be your first entry, but gets saved as "This 250".

$NameLength = strlen($DocName);
    SELECT DocName, count(DocName) FROM Pickup GROUP BY DocName HAVING LEFT(DocName,'$NameLength') = '$DocName'";

Open in new window

So, if I have three titles, "Title"; "Title 2"; "Title 3", I thought I could then do

$D = mysql_fetch_array($Result);
$RCount = ($D[count(DocName)]) + 1;
$NewTitle = $DocName.' '.$RCount;

Open in new window

Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
nothing to do with length...

algorithm looks complicated...

what about this

Title (YYYY-MM-DD HH:MM:SS)

instead, assuming you insert not more than once per second...
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
if it is not what you want... then

you need to check

max (rightpart(title)) + 1 where title like $title.' %'  and isnumeric(rightpart(title))=1

then your new title should be

title = title.' '.(max (rightpart(title)) + 1)

rightpart is a function that returns substr(title, length($title)+1)
isnumeric is a function that returns 1 if str is all numeric, o/w returns 0
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
isnumeric in mysql can be

 str REGEXP '^[1-9][0-9]+$'
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
so, if you combine all pieces

select 1 + max(substr(title, length($title)+1)) as NewTitle
where title like $title+' % ' and substr(title, length($title)+1) REGEXP '^[1-9][0-9]+$'

then use title + ' ' + NewTitle

:)

Author

Commented:
Certainly sound like what I am looking for, but I've got something wrong:
$Q = "SELECT 1 + MAX(SUBSTRING(DocName, length('$DocName')+1)) as NewTitle
WHERE DocName LIKE '$DocName'+' % ' AND SUBSTRING(DocName, length('$DocName')+1) REGEXP '^[1-9][0-9]+$'";
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
I never wrote php code but

it should be something like this

$Q = "SELECT 1 + MAX(SUBSTRING(DocName, length('".$DocName."')+1)) as NewTitle
WHERE DocName LIKE '".$DocName."%' AND SUBSTRING(DocName, length('".$DocName."')+1) REGEXP '^[1-9][0-9]+$'";

Open in new window

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
actually above query is not 100% correct!
you should convert the right part to number before getting the max

o/w

max('10','2') gives you 2 :) but you want 10
so you need to cast it to number or just multiply it with 1

ie
$Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName, length('".$DocName."')+1)) as NewTitle
WHERE DocName LIKE '".$DocName."%' AND SUBSTRING(DocName, length('".$DocName."')+1) REGEXP '^[1-9][0-9]+$'";

Open in new window

Author

Commented:
Working on it ...

Author

Commented:
In PHP and a mysql query, you don't need to '".$Variable."'. You can just use '$Variable'.
Substring can be SUBSTRING(str,pos); SUBSTRING(str FROM pos); SUBSTRING(str,pos,len), or SUBSTRING(str FROM pos FOR len)
https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substring

So, my query ended up looking like this:
$NameLength = strlen($DocName)+1;
$Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName,0,'$NameLength') as NewTitle
      WHERE DocName LIKE '$DocName%' AND SUBSTRING(DocName,0,'$NameLength') REGEXP '^[1-9][0-9]+$'";
which failed so miserably that I had to kill the process to get the error message.
This is the not-too-helpful error message:
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 'as NewTitle WHERE DocName LIKE 'Doc Title%' AND SUBSTRING(DocName,0,'10')'
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
maybe

SUBSTRING(DocName,0,'$NameLength')

>>>

SUBSTRING(DocName,0,$NameLength)

Author

Commented:
Ha! Didn't have the table reference in there. It is now there, but still coming up with an error.
So the query looks like:
$Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName,0,'$NameLength') as NewTitle FROM Pickup WHERE DocName LIKE '$DocName%' AND SUBSTRING(DocName,0,'$NameLength') REGEXP '^[1-9][0-9]+$'";

And the error looks like:
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 'as NewTitle FROM Pickup WHERE DocName LIKE 'Doc Title%' AND SUBSTRING(DocName,0,' at line 1

Ans, so far, I can't seem to find what it specifically doesn't like.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
dont wrap $NameLength with '

SUBSTRING(DocName,0,'$NameLength')
>>>
SUBSTRING(DocName,0,$NameLength)

or just

SUBSTRING(DocName,$NameLength)
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
can you get the string value of $Q before running...

Author

Commented:
Wrapped:
SELECT 1 + MAX(1 * SUBSTRING(DocName,0,'10') as NewTitle FROM Pickup WHERE DocName LIKE 'Doc Title%' AND SUBSTRING(DocName,0,'10') REGEXP '^[1-9][0-9]+$

Unwrapped:
SELECT 1 + MAX(1 * SUBSTRING(DocName,0,10) as NewTitle FROM Pickup WHERE DocName LIKE 'Doc Title%' AND SUBSTRING(DocName,0,10) REGEXP '^[1-9][0-9]+$

Results are the same, either way
Sr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Removed everything to the right of LIKE 'Doc Title%' and still received an error:
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 'as NewTitle FROM Pickup WHERE DocName LIKE 'Doc Title%'' at line 1

Author

Commented:
The last comment was actually written before your last comment.
I simply missed the final single quote when I copied. The right bracket, however, was not there, my apologies.

So, now i have
SELECT 1 + MAX(1 * SUBSTRING(DocName,0,10)) as NewTitle FROM Pickup WHERE DocName LIKE 'Doc Title%' AND SUBSTRING(DocName,0,10) REGEXP '^[1-9][0-9]+$'

It is processing, returning one row.
I should be able to access it using the alias, right?
$D = mysql_fetch_array($Result);
         $DocName = $DocName.' '.$D[NewTitle];
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
yes :)

I never worked with PHP and MySQL, but if it runs successfully and returns one row, which we are trying to get,
it should be new Title that you are looking for...

also, you should

set $DocName only if it returns a row...

Author

Commented:
Here's what I get (using print_r)
Array ( [0] => [NewTitle] => ) Doc_Name: Doc Title
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
$DocName = $DocName.' '.$D[NewTitle];

looks good to me...

dont use print_r use print to see the value of $DocName
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
oops, it should be

$DocName = $DocName.' '.$D["NewTitle"];

pay attention to "

also, you will set $DocName only if it returns one row!

Author

Commented:
This is the entire code set
   $NameLength = strlen($DocName)+1;
   $Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName,0,$NameLength)) as NewTitle FROM Pickup WHERE DocName LIKE '$DocName%' AND SUBSTRING(DocName,0,$NameLength) REGEXP '^[1-9][0-9]+$'";
   $Result = mysql_query($Q,$Conn);
   if(!$Result){$Error[] = mysql_error();}
   else
   {
      $Rows = mysql_num_rows($Result);
      if($Rows > 0)
      {
         $D = mysql_fetch_array($Result);
         $DocName = $DocName.' '.$D[NewTitle];
      }
   }
But I'm not getting a number.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
as I said before

$DocName = $DocName.' '.$D[NewTitle];

>>>

$DocName = $DocName.' '.$D["NewTitle"];

Author

Commented:
Shouldn't make a difference in PHP. At any rate, I have lost connection to my server, so I can't test further.
I'm going to mark this as solved, though.
Will, of course, test again when I can.
Huseyin, Thanks so much for your time and quick responses.

Author

Commented:
This still does not work.
   $NameLength = strlen($DocName)+1;
   $Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName,0,$NameLength)) as NewTitle FROM Pickup WHERE DocName LIKE '$DocName%' AND SUBSTRING(DocName,0,$NameLength) REGEXP '^[1-9][0-9]+$'";
   $Result = mysql_query($Q,$Conn);
   if(!$Result){$Error[] = mysql_error();}
   else
   {
      $Rows = mysql_num_rows($Result);
      if($Rows > 0)
      {
         $D = mysql_fetch_array($Result);
      }
      $DocName = $DocName.' '.$D["NewTitle"];
   }

The results produces one row with the key "NewTitle", with a blank value.
$DocName = "Doc Title"
I tried changing the SUBSTRING parameters to (DocName,1,$NameLength), as I found that MySQL starts their positioning at 1, not 0. But it made no difference in the outcome.

Author

Commented:
It appears that I may have messed up the substrings by putting in the starting position. At the time, I thought you were having the query produce the full name, so I injected a starting point, whereas I believe you were using the namelength+1 as the starting point.

Since I had given an explanation at the time, and you did not object, I assumed that was correct.

Nevertheless, I changed these to "Substring(DocName,$Namelength)", but the results are still the same. I added numbers to some of the entries already in the database and still made no difference. Also added the escape on the final $.

SELECT 1 + MAX(1 * SUBSTRING(DocName, $NameLength)) as NewTitle FROM Pickup WHERE DocName LIKE '$DocName%' AND SUBSTRING(DocName,$NameLength) REGEXP '^[1-9][0-9]+\$'
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
ok, regexp should be

([1-9])|(^[1-9][0-9]$)|(^[1-9][0-9]*[0-9]$)

or better

([1-9])|(^[1-9][0-9]*[0-9]$)

maybe there is a better version for this, but this should work...

SELECT 1 + MAX(1 * SUBSTRING(DocName,9)) as NewTitle
FROM (    select 1 ID, 'My Document' DocName
union select 2, 'Animals'
union select 3, 'Animals 13'
union select 4, 'Animals 2'
union select 5, 'History of the World') Pickup
WHERE DocName LIKE 'Animals%' AND SUBSTRING(DocName,9) REGEXP '([1-9])|(^[1-9][0-9]*[0-9]$)'

14

Open in new window


so final solution should be

$Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName, $NameLength)) as NewTitle FROM Pickup WHERE DocName LIKE '$DocName%' AND SUBSTRING(DocName,$NameLength) REGEXP '([1-9])|(^[1-9][0-9]*[0-9]$)'";

Open in new window

Author

Commented:
Darn! Used your "final solution" example, and the result is still the same. Nevertheless, you've provided what, I'm sure, is the basic logic, and I can play with it until I can figure it out, which will also enhance my understanding of more complex queries than I normally use. Certainly there's an answer in this query somewhere.

I've had to move on to other things, so we are not providing the API users the option of naming their documents, instead just creating a name out of a timestamp and other info.

Sorry for subverting you along the way :)

Thanks again. If you decide to spend more time on this. let me know if you come up with an answer.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
it is final
$Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName, $NameLength)) as NewTitle FROM Pickup WHERE DocName LIKE '$DocName%' AND SUBSTRING(DocName,$NameLength) REGEXP '([1-9])|(^[1-9][0-9]*[0-9]$)'";

Open in new window


check this

http://rextester.com/VOAFD78254
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
see the sample solution here

http://sqlfiddle.com/#!9/902f56/2

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.