6 Replies - 1465 Views - Last Post: 24 April 2013 - 10:59 AM Rate Topic: -----

#1 JD.CoolPenguin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 05-May 12

PHP to insert or update mySQL database if row already exists

Posted 24 April 2013 - 09:49 AM

Okay, I'm mostly a front end developer, and I rarely dip into PHP, and I have never before dipped into mySQL.

However, I am in need of creating a VERY BASIC download counter using PHP and mySQL.

Here's the logic I'm following:

I have a table called 'download_log' with fields 'id' as auto_increment, 'url' as varchar(255), 'count' as integer(10).
1) Get url of page and pass to $url
2) Check database if $url already exists in url column
3) If it does, increase downloads integer by 1, if it doesn't insert new row.

Now, I've been googling and reading for several hours, and this is how I think it will be done (using Object Orientated mySQLi):

You are testing, testing, testing.
$mysqli = new mysqli("localhost", "username", "password", "test");

if ($mysqli->connect_errno) {
    printf("Connection failed: %s\n", $mysqli->connect_error);
    exit();
}

$url = $_SERVER["SERVER_NAME"] . $_SERVER["REQUEST_URI"];

echo $url;

$existchk = $mysqli->query("SELECT `url` FROM `download_log` WHERE $url");

$exist = ($existchk->num_rows > 0 ? true : false);

if ($exist == true) {
$curdown = $existchk['downloads'];
$newdown = $curdown + 1;

$mysqli->query("UPDATE `download_log`
SET `downloads` = ".$newdown."
WHERE $url");
}
else
{
$mysqli->query("INSERT INTO `download_log`
SET `url` = ".$url.",
`downloads` = ".$newdown);
}
?>



This doesn't work, and I have no idea why, I imagine there are several syntax errors in there, but I have no idea...

Connecting seems to be working (no failure, error or warning message is appearing), and echoing the url works, but nothing is happening in the database.

(This is only on a test page and a test database since I didn't want to accidentally screw any existing stuff up).

Thanks for your help,
JD

This post has been edited by JD.CoolPenguin: 24 April 2013 - 09:50 AM


Is This A Good Question/Topic? 0
  • +

Replies To: PHP to insert or update mySQL database if row already exists

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2881
  • View blog
  • Posts: 9,555
  • Joined: 12-December 12

Re: PHP to insert or update mySQL database if row already exists

Posted 24 April 2013 - 10:14 AM

Add this to the top of your script so you can see all errors:

ini_set('display_errors',1); 
error_reporting(E_ALL);


WHERE $url won't work on its own.

$existchk = $mysqli->query("SELECT `url` FROM `download_log` WHERE `url` = '$url'");

(placing apostrophes around the string $url.)

BTW The back-ticks aren't necessary, unless the field or table-names contain reserved words or non alphanumeric characters.

I suggest that you investigate INSERT.. ON DUPLICATE KEY UPDATE as well (here). If you make the url field a unique index or primary key then this statement will either inesrt a new row, or update an existing row.
Was This Post Helpful? 1
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2881
  • View blog
  • Posts: 9,555
  • Joined: 12-December 12

Re: PHP to insert or update mySQL database if row already exists

Posted 24 April 2013 - 10:21 AM

Here's an example from the linked page:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

Was This Post Helpful? 0
  • +
  • -

#4 JD.CoolPenguin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 05-May 12

Re: PHP to insert or update mySQL database if row already exists

Posted 24 April 2013 - 10:24 AM

View Postandrewsw, on 24 April 2013 - 10:14 AM, said:

Add this to the top of your script so you can see all errors:

ini_set('display_errors',1); 
error_reporting(E_ALL);


WHERE $url won't work on its own.

$existchk = $mysqli->query("SELECT `url` FROM `download_log` WHERE `url` = '$url'");

(placing apostrophes around the string $url.)

BTW The back-ticks aren't necessary, unless the field or table-names contain reserved words or non alphanumeric characters.

I suggest that you investigate INSERT.. ON DUPLICATE KEY UPDATE as well (here). If you make the url field a unique index or primary key then this statement will either inesrt a new row, or update an existing row.


Hello Andrewsw, I will do that and let you know what it comes back as shortly (just popped out).

I saw that about the ON DUPLICATE KEY, but couldn't find any information about using it with mySQLi, I wasn't sure if it would work or not.

So, I'll try using that instead when I get back.

Thanks for the advice.

JD.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2881
  • View blog
  • Posts: 9,555
  • Joined: 12-December 12

Re: PHP to insert or update mySQL database if row already exists

Posted 24 April 2013 - 10:28 AM

Don't forget to modify the table firstly to add a unique index on the url field.

ALTER TABLE download_log 
ADD UNIQUE INDEX url_ind(url);

Was This Post Helpful? 0
  • +
  • -

#6 JD.CoolPenguin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 05-May 12

Re: PHP to insert or update mySQL database if row already exists

Posted 24 April 2013 - 10:48 AM

Thank you very much Andrewsw!!

This:

<html>
<body>
<p>You are testing, testing, testing.</p>
<?php
ini_set('display_errors',1); 
error_reporting(E_ALL);

$mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli->connect_errno) {
    printf("Connection failed: %s\n", $mysqli->connect_error);
    exit();
}

$url = $_SERVER["SERVER_NAME"] . $_SERVER["REQUEST_URI"];

echo $url;

$mysqli->query("INSERT INTO `download_log`(`url`, `downloads`) VALUES ('".$url."', '1')
ON DUPLICATE KEY UPDATE downloads = downloads + 1");

?>
</body>
</html>


Works! :D/>

Of course, I'm now going to go and try and get it to work in a wordpress install, and I'm sure there is something extra I'm going to have to do to connect to the database.

Thanks a million.

JD

This post has been edited by JD.CoolPenguin: 24 April 2013 - 10:50 AM

Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2881
  • View blog
  • Posts: 9,555
  • Joined: 12-December 12

Re: PHP to insert or update mySQL database if row already exists

Posted 24 April 2013 - 10:59 AM

View PostJD.CoolPenguin, on 24 April 2013 - 05:48 PM, said:

Works! :D

Of course, I'm now going to go and try and get it to work in a wordpress install, and I'm sure there is something extra I'm going to have to do to connect to the database.

Thanks a million.

JD

You are welcome. Andy.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1