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".
"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'";
$D = mysql_fetch_array($Result);
$RCount = ($D[count(DocName)]) + 1;
$NewTitle = $DocName.' '.$RCount;
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...
algorithm looks complicated...
what about this
Title (YYYY-MM-DD HH:MM:SS)
instead, assuming you insert not more than once per second...
if it is not what you want... then
you need to check
max (rightpart(title)) + 1 where title like $title.' %' and isnumeric(rightpart(title)
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
you need to check
max (rightpart(title)) + 1 where title like $title.' %' and isnumeric(rightpart(title)
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
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
:)
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
:)
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]+$'";
$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]+$'";
I never wrote php code but
it should be something like this
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]+$'";
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
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]+$'";
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,'$Name
WHERE DocName LIKE '$DocName%' AND SUBSTRING(DocName,0,'$Name
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')'
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,'$Name
WHERE DocName LIKE '$DocName%' AND SUBSTRING(DocName,0,'$Name
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')'
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,'$Name
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.
So the query looks like:
$Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName,0,'$Name
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.
dont wrap $NameLength with '
SUBSTRING(DocName,0,'$Name
>>>
SUBSTRING(DocName,0,$NameL
or just
SUBSTRING(DocName,$NameLen
SUBSTRING(DocName,0,'$Name
>>>
SUBSTRING(DocName,0,$NameL
or just
SUBSTRING(DocName,$NameLen
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
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
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
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
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];
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];
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...
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...
$DocName = $DocName.' '.$D[NewTitle];
looks good to me...
dont use print_r use print to see the value of $DocName
looks good to me...
dont use print_r use print to see the value of $DocName
oops, it should be
$DocName = $DocName.' '.$D["NewTitle"];
pay attention to "
also, you will set $DocName only if it returns one row!
$DocName = $DocName.' '.$D["NewTitle"];
pay attention to "
also, you will set $DocName only if it returns one row!
This is the entire code set
$NameLength = strlen($DocName)+1;
$Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName,0,$NameL
$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.
$NameLength = strlen($DocName)+1;
$Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName,0,$NameL
$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.
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.
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.
This still does not work.
$NameLength = strlen($DocName)+1;
$Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName,0,$NameL
$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.
$NameLength = strlen($DocName)+1;
$Q = "SELECT 1 + MAX(1 * SUBSTRING(DocName,0,$NameL
$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.
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,$Namele
SELECT 1 + MAX(1 * SUBSTRING(DocName, $NameLength)) as NewTitle FROM Pickup WHERE DocName LIKE '$DocName%' AND SUBSTRING(DocName,$NameLen
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,$Namele
SELECT 1 + MAX(1 * SUBSTRING(DocName, $NameLength)) as NewTitle FROM Pickup WHERE DocName LIKE '$DocName%' AND SUBSTRING(DocName,$NameLen
ok, regexp should be
([1-9])|(^[1-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...
so final solution should be
([1-9])|(^[1-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
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]$)'";
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.
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.
it is final
check this
http://rextester.com/VOAFD78254
$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]$)'";
check this
http://rextester.com/VOAFD78254
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy 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
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
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.