I give up

ok, I have added your code to my page but I don't know what you mean by:

 

I got this error:

 

Check this out: http://www.php.net/manual/en/mysqli.real-escape-string.php
The best way is to use prepared statements for more security: http://php.net/manual/en/mysqli.prepare.php

 

I'm sorry, but I don't know what the create query is, or what escaping video_id or video_category is.

I honestly have no clue. I don't know MySQL

 

I am about to go back to my flatfiling lol

 

I don't know what prepared statements are, creating a query means, escaping, or any of these advanced terms. I literally created my first mysql database 1 hour ago. So I need some more simple explanations, not various links to complicated programming. I really just need to figure out how to prevent doubling up info in the table, and I am pretty sure my previous code was 98% correct, re (this page:

http://stackoverflow.com/questions/2219786/best-way-to-avoid-duplicate-entry-into-mysql-database

I have already written everything I need and it is all working, but I just need to prevent it from doubling up in a column and ..

screw it. bye. going back to flatfile

*gone*

I want to try and get this test script running today. Thanks for your help everyone.

 

I have actually been sitting here working on this since I cracked the sads a few hours ago, "giving it one more chance" -- I have been in "one more chance mode" for hours. I have gone through about 2 dozen articles and 23,450,234 errors after re-uploading the file and I am really starting to get the sh*ts. I swear.

By using the code above, I am getting 2 errors:

1). Error: Duplicate entry 'a1Y73sPHKxw' for key 'PRIMARY'

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// Create table
$sql="CREATE TABLE IF NOT EXISTS Videos(Video CHAR(30),Category CHAR(30),Views INT, PRIMARY KEY (Video))";

// Execute query
if (mysqli_query($con,$sql))
  {
  echo "<p>Table videos created successfully</p>";
  }
else
  {
  echo "<p>Error creating table: " . mysqli_error($con) ."</p>";
  }

Code (markup):

2). 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 ''Views' = 'Views' + 1' at line 1

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
   


$sql="INSERT INTO Videos (Video, Category, Views) VALUES('$video_id','$video_category',1) ON DUPLICATE KEY UPDATE 'Views' = 'Views' + 1";

if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }
echo "1 record added";

mysqli_close($con);

Code (markup):

Re problem 1:

This article seems to address it, but I don't understand it:

http://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm

It would seem a pity for me to revert back to flatfile considering I have gone through this all day.

I have basically created exactly what I need, except that I am having great difficulty writing code to prevent the double entries (or the double entry error).

Surely there's someone here that actually knows what they're doing and can simply tweak the code I've spent all day compiling from reading dozens of articles, instead of sending me to another article full of loosely related jargon?

Please? I am starting to get dizzy from frustration.

 

What I am going to do for now (considering that I at least have some values in a database to work with) is complete the rest of the script, pretending that my ability to add info to the database is working. I hope that I can come back to this thread and there will be some possibility of me being able to prevent the duplicate entry problem.

Here is what my database looks like:

video | category | views
GDRESDRTFD | cars | 1
GYTGFTRFDE | animals | 1
IHYTGFRDEW | Howto | 1

There's 2 ways that the database will be altered:

One way is by (initially) submitting the video ID to the database:

If the video ID does not already exist, the video ID and category will be added to the database; and the views set as 1.
If the video ID exists, it will simply update the category if it has changed (the video id and views will be left alone).

The other is by someone viewing that video on a web page:

It just has to update the view count +1 and that's it.

 

Some advice:

First up, STOP stuffing everything into single lines, it's harder to work with. Much like HTML, SQL is whitespace neutral so you can divide your commands into multiple easier to read lines.

Second, STOP creating 'variables for nothing' like $sql. It's right there, plug the value in.

Third, while I'm not sure who to point you at for a tutorial, I would advise against the steaming pile of webrot known as W3Schools. I learned from php.net and mysql.com -- basically cut out the middle-man and go right to the horse's mouth.

Fourth, do NOT use string addition or double quotes to plug values into queries -- that's broken insecure methodology circa 2005 and half the reason we're supposed to be using mysqli or PDO instead of mysql_. (the other half being restriction of scope) There's this thing called 'prepared queries' that are pretty much injection proof, and that's what you should be using when plugging in values.

Fifth, I suggest using just lowercase for field and table names in SQL. Some backup utilities and some SQL implementations will mangle them to lowercase or add case and it ends up being a royal pain in the ass long term.

Personally, I'd be using PDO for this just because IMHO it's more versatile and easier to pass values to; well, and it's engine neutral so you could use engines other than mySQL from the same codebase.

Now, let's get to what you are trying to to. First, let's make a PDO version of connecting to the database.

Doing so uses an object constructor. Because it's an object you can restrict it's scope inside a function or another object making it more secure, but we'll leave it global for now to keep it simple for you.

try {
	$db = new PDO(
		'mysql:host=localhost;dbname=xxx',
		'xxx', // username
		'xxx' // password
	);
} catch (PDOException $e) {
	echo 'Connection failed: ' . $e->getMessage();
}

Code (markup):

Then creating the table:

$db->exec('
	CREATE TABLE videos (
		session_id VARCHAR(43),
		video      VARCHAR(31),
		category   VARCHAR(31),
		views      INT
	)
');

Code (markup):

I added the session_id since you said you'd have per user/session tracking.

Now there's updating the view count... This is where you need to step back and think; what you were doing is just inserting them. Insert makes a new one so you get multiple rows. What you want is to INSERT if it doesn't exist, UPDATE if it does.

We can use SELECT COUNT(*) to figure out how many rows match. If it's zero, INSERT, if it's more than zero, we UPDATE. Setting up an array of values ahead of time lets us plug them into PDOStatement's ->execute method cleanly.

$data = [
	':sessionId' => session_id(),
	':video' => $_SESSION['video'],
	':category' => $_SESSION['category']
];

Code (markup):

The colons show that they are parameter values to be plugged into our prepared queries.

Then we check for how many exist.

$statement = $db->prepeare('
	SELECT COUNT(*) FROM videos
	WHERE session_id = :sessionId
	AND video = :video
	AND category = :category
');
$statement->execute($data);

Code (markup):

That's called a prepared query. Our array $data is passed as the parameters, plugging in it's values to those that exist in our query. They are auto-sanitized removing the risk of code injection completely. One cool thing about prepared queries is that if you have a whole bunch of data using the same query, you can re-use the statement with a new row worth of data. (Not what you are doing here, just something to be aware of).

We can then pull the first column (since COUNT(*) returns one field in one row) to see if we should INSERT or UPDATE.

this assumes you've run the COUNT(*) query right before this IF statement

if ($statement->fetchColumn > 0) {
	$statement = $db->prepare('
		INSERT INTO videos (
			session_id, video, category, views
		) VALUES (
			:sessionId, :video, :category, 1
		)
	');
} else {
	$statement = $db->prepare('
		UPDATE videos
		SET views = views + 1
		WHERE session_id = :sessionId
		AND video = :video
		AND category = :category
	');
}
$statement-execute($data);

Code (markup):

That's another nice thing, if you're working from the same data, you can use that same array for either query. The magic being not only that we UPDATE instead of INSERT when it exists, but also that we have the UPDATE increment views instead of setting it.

That's all a bit rough and untested, but should give you an idea what I mean.

Some advice:

First up, STOP stuffing everything into single lines, it's harder to work with. Much like HTML, SQL is whitespace neutral so you can divide your commands into multiple easier to read lines.

Second, STOP creating 'variables for nothing' like $sql. It's right there, plug the value in.

Third, while I'm not sure who to point you at for a tutorial, I would advise against the steaming pile of webrot known as W3Schools. I learned from php.net and mysql.com -- basically cut out the middle-man and go right to the horse's mouth.

Fourth, do NOT use string addition or double quotes to plug values into queries -- that's broken insecure methodology circa 2005 and half the reason we're supposed to be using mysqli or PDO instead of mysql_. (the other half being restriction of scope) There's this thing called 'prepared queries' that are pretty much injection proof, and that's what you should be using when plugging in values.

Fifth, I suggest using just lowercase for field and table names in SQL. Some backup utilities and some SQL implementations will mangle them to lowercase or add case and it ends up being a royal pain in the ass long term.

Personally, I'd be using PDO for this just because IMHO it's more versatile and easier to pass values to; well, and it's engine neutral so you could use engines other than mySQL from the same codebase.

Now, let's get to what you are trying to to. First, let's make a PDO version of connecting to the database.

Doing so uses an object constructor. Because it's an object you can restrict it's scope inside a function or another object making it more secure, but we'll leave it global for now to keep it simple for you.

try {
	$db = new PDO(
		'mysql:host=localhost;dbname=xxx',
		'xxx', // username
		'xxx' // password
	);
} catch (PDOException $e) {
	echo 'Connection failed: ' . $e->getMessage();
}

Code (markup):

Then creating the table:

$db->exec('
	CREATE TABLE videos (
		session_id VARCHAR(43),
		video      VARCHAR(31),
		category   VARCHAR(31),
		views      INT
	)
');

Code (markup):

I added the session_id since you said you'd have per user/session tracking.

Now there's updating the view count... This is where you need to step back and think; what you were doing is just inserting them. Insert makes a new one so you get multiple rows. What you want is to INSERT if it doesn't exist, UPDATE if it does.

We can use SELECT COUNT(*) to figure out how many rows match. If it's zero, INSERT, if it's more than zero, we UPDATE. Setting up an array of values ahead of time lets us plug them into PDOStatement's ->execute method cleanly.

$data = [
	':sessionId' => session_id(),
	':video' => $_SESSION['video'],
	':category' => $_SESSION['category']
];

Code (markup):

The colons show that they are parameter values to be plugged into our prepared queries.

Then we check for how many exist.

$statement = $db->prepeare('
	SELECT COUNT(*) FROM videos
	WHERE session_id = :sessionId
	AND video = :video
	AND category = :category
');
$statement->execute($data);

Code (markup):

That's called a prepared query. Our array $data is passed as the parameters, plugging in it's values to those that exist in our query. They are auto-sanitized removing the risk of code injection completely. One cool thing about prepared queries is that if you have a whole bunch of data using the same query, you can re-use the statement with a new row worth of data. (Not what you are doing here, just something to be aware of).

We can then pull the first column (since COUNT(*) returns one field in one row) to see if we should INSERT or UPDATE.

this assumes you've run the COUNT(*) query right before this IF statement

if ($statement->fetchColumn > 0) {
	$statement = $db->prepare('
		INSERT INTO videos (
			session_id, video, category, views
		) VALUES (
			:sessionId, :video, :category, 1
		)
	');
} else {
	$statement = $db->prepare('
		UPDATE videos
		SET views = views + 1
		WHERE session_id = :sessionId
		AND video = :video
		AND category = :category
	');
}
$statement-execute($data);

Code (markup):

That's another nice thing, if you're working from the same data, you can use that same array for either query. The magic being not only that we UPDATE instead of INSERT when it exists, but also that we have the UPDATE increment views instead of setting it.

That's all a bit rough and untested, but should give you an idea what I mean.

You might also want to consider regenerating your session_id every access -- but that means replacing them in the database and that can get a bit... tricky; and heavy depending on how many vids you're working with.

 

@ThePHPMaster -- that's gibberish, you can't have TWO primary keys... Did you mean to INDEX those? and again, his problem is that he doesn't just want insert -- he wants INSERT if it doesn't exist, UPDATE if it does. That's why COUNT needs to be in the mix.

 

A table can have at most one primary key. A primary key consists of one or more columns (from that table). If a primary key consists of two or more columns it is called a composite (compound) primary key

Almost all SQL systems (including MySQL/Except SQLite) support Composite (Compound) Primary Keys. Using that, on duplicate key update should work (no idea why it isn't working for him).

 

You know, I've been programming databases for three or so decades, and I've never even heard of that -- and it doesn't even make any sense, and even if it DID (which it doesn't -- nor should it!) shouldn't it then either be a single field for better index efficiency and/or not be called "primary"?!? (since by definition PRIMARY implies ONE field?!?)

... and when you list them that way, isn't that just an index for FOREIGN keys with rules, NOT actually declaring them as primary? (Not that I understand why anyone would use either). I thought that's what said construct was for... without FOREIGN after what possible purpose could it serve that simply declaring INDEX wouldn't?

Looks like a really stupid way to build a table to me...

 

It all depends on your business needs. One time for a users table I had the option of doing surrogate key as primary or email/account type as primary (business rules stated that an email can be associated with multiple account types but can only exist once in an account type). I went the route of surrogate key, simply because I love them, however the correct route would've been to use the email/account for future normalization purposes.

I find this a good read for anyone interested on the advantages and disadvantages of using surrogate keys instead of data application primary keys:

http://en.wikipedia.org/wiki/Surrogate_key