Avatar of RationalRabbit
RationalRabbit

asked on 

MySQL - Adding an incremented value to a duplicate string

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

DatabasesPHP

Avatar of undefined
Last Comment
HainKurt
Avatar of HainKurt
HainKurt
Flag of Canada image

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...
Avatar of HainKurt
HainKurt
Flag of Canada image

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
Avatar of HainKurt
HainKurt
Flag of Canada image

isnumeric in mysql can be

 str REGEXP '^[1-9][0-9]+$'
Avatar of HainKurt
HainKurt
Flag of Canada image

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

:)
Avatar of RationalRabbit
RationalRabbit

ASKER

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]+$'";
Avatar of HainKurt
HainKurt
Flag of Canada image

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

Avatar of HainKurt
HainKurt
Flag of Canada image

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

Avatar of RationalRabbit

ASKER

Working on it ...
Avatar of RationalRabbit

ASKER

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')'
Avatar of HainKurt
HainKurt
Flag of Canada image

maybe

SUBSTRING(DocName,0,'$NameLength')

>>>

SUBSTRING(DocName,0,$NameLength)
Avatar of RationalRabbit

ASKER

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.
Avatar of HainKurt
HainKurt
Flag of Canada image

dont wrap $NameLength with '

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

or just

SUBSTRING(DocName,$NameLength)
Avatar of HainKurt
HainKurt
Flag of Canada image

can you get the string value of $Q before running...
Avatar of RationalRabbit

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of RationalRabbit

ASKER

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
Avatar of RationalRabbit

ASKER

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];
Avatar of HainKurt
HainKurt
Flag of Canada image

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...
Avatar of RationalRabbit

ASKER

Here's what I get (using print_r)
Array ( [0] => [NewTitle] => ) Doc_Name: Doc Title
Avatar of HainKurt
HainKurt
Flag of Canada image

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

looks good to me...

dont use print_r use print to see the value of $DocName
Avatar of HainKurt
HainKurt
Flag of Canada image

oops, it should be

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

pay attention to "

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

ASKER

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.
Avatar of HainKurt
HainKurt
Flag of Canada image

as I said before

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

>>>

$DocName = $DocName.' '.$D["NewTitle"];
Avatar of RationalRabbit

ASKER

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.
Avatar of RationalRabbit

ASKER

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.
Avatar of RationalRabbit

ASKER

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]+\$'
Avatar of HainKurt
HainKurt
Flag of Canada image

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

Avatar of RationalRabbit

ASKER

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.
Avatar of HainKurt
HainKurt
Flag of Canada image

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
Avatar of HainKurt
HainKurt
Flag of Canada image

see the sample solution here

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

PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.

125K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo