What is the correct validation to check a input is exists or not in database
Example my MySQL table
content_id content_u开发者_高级运维ser content_title content_slug
---------------------------------------------------------------------
1 1 Hello World hello-world
2 1 Hello Stackoverflow hello-stackoverflow
3 2 Fix me fix-me
4 3 Testing testing
Update
content_slug
is a unique key.
$input = 'Hello World';
$slug = function_slug($input); // this will be hello-world
/* begin the validation */
$query = $db->query("SELECT *
FROM tbl_content
WHERE content_slug='{$slug}'
");
$data = $db->fetch($query);
$check = $db->num_rows($query);
if($check == 1) {
$seo = $slug;
} else {
$seo = $slug.'-'.time();
}
/* end the validation */
$db->query("UPDATE tbl_content
SET content_slug= '{$db->escape($seo)}'
WHERE content_id ='{$db->escape($id)}'
AND content_user ='{$db->escape($_SESSION['user_id'])}'
");
A bit long :) Here I want to know, what is the correct validation should I use if I want
- If
hello-world
=
currentcontent_user
use the firstif
- If
hello-world
<>
currentcontent_user
andhello-world
already exists in database use the} else {
Let me know..
I really don't think you want what you're asking for, but this does what you are asking for in one update query.
UPDATE tbl_content
SET content_slug= IF(content_user = '{$db->escape($_SESSION['user_id'])}',
content_slug,
CONCAT(content_slug, '-', DATE_FORMAT(now(), '%Y%m%d%H%i%s%f')))
WHERE content_id ='{$db->escape($id)}'
ADDITION
I imagine you want to insert a new row in your table for the different user, in which case, you would need an insert statement. If you want to insert a new row no matter what, then this should work for you:
$slug = "'$db->escape($slug)'";
$db->query("INSERT INTO tbl_content (content_user, content_title, content_slug)
SELECT '{$db->escape($_SESSION['user_id'])}', '{$db->escape($title)}',
IF(EXISTS(SELECT content_id FROM tbl_content WHERE content_slug = $slug),
CONCAT($slug, DATE_FORMAT(now(), '-%Y%m%d%H%i%s%f')), $slug)");
If, however, for whatever reason you only want to insert a new row if you don't have the same content_user
as before, then you can go with the ugliness here:
$slug = "'{$db->escape($slug)}'";
$user = "'{$db->escape($_SESSION['user_id'])}'";
$db->query("INSERT INTO tbl_content (content_user, content_title, content_slug)
SELECT $user, '{$db->escape($title)}',
IF(EXISTS(SELECT content_id FROM tbl_content WHERE content_slug = $slug),
CONCAT($slug, DATE_FORMAT(now(), '-%Y%m%d%H%i%s%f')), $slug)
FROM tbl_content
WHERE NOT EXISTS(SELECT content_id FROM tbl_content
WHERE content_slug = $slug AND content_user = $user)
LIMIT 1"));
I think it would be something like this:
$input = 'Hello World';
$slug1 = function_slug($input); // this will be hello-world
$query = $db->query("SELECT *
FROM tbl_content
WHERE content_slug='{$slug1}'
");
$data = $db->fetch($query);
$check = $db->num_rows($query);
if($check == 1)
$slug2 = $slug1.'-'.time();
$db->query("UPDATE tbl_content
SET content_slug= '{$db->escape($slug2)}'
WHERE content_id ='{$db->escape($id)}'
AND content_user ='{$db->escape($_SESSION['user_id'])}'
AND content_slug <> '{$db->escape($slug1)}'
");
<> means not equal, != should do as well The extra where will only update if content_slug is not equal to hello-world
UPDATE <table> SET <field> = <new value> **WHERE** <current User> != <hello world>
I think your db-logic is flawed. but to answer your precise question: just append a UUID and it, whatever it is, will be unique. but what is content_slug and why should it be unique? that is your question for yourself.
精彩评论