add new field to complex INSERT query - experts only!

[doa24uk]

Hi guys,

 

I have downloaded a click counter script that uses JS and PHP to insert clicks into a mysql database.

 

It's quite clever because you can tell it to only count clicks from certain file extensions.

 

Anyway, here is the code that does the inserting into the database. I have created an additional field (profile-id) in the database and simply want to insert the users profile ID along with this data.

 

Here's the existing insert code

 

  function Set($key, $val)
  {
    $hash = '0x' . substr(md5($key), 0, 4);
    $key  = mysql_real_escape_string($key);
    $val  = mysql_real_escape_string($val);

    mysql_query(
    "insert into {$this->tbl} ({$this->prefix}hash, {$this->prefix}key)
     values ($hash, '$key')");

    mysql_query_debug(
    "update {$this->tbl}
     set    {$this->prefix}val  = '$val',
            {$this->prefix}cnt0 = {$this->prefix}cnt0+1
     where  {$this->prefix}hash = $hash && {$this->prefix}key = '$key'");

 

I thought it was just a case of this --

 

FIND

 

     values ($hash, '$key')");

 

INLINE FIND

 

'$key'

 

AFTER, ADD

 

, $profile_id

 

So the finished insert query would be

 

    mysql_query(
    "insert into {$this->tbl} ({$this->prefix}hash, {$this->prefix}key)
     values ($hash, '$key',$profile_id)");

 

This doesn't seem to work though.

 

Any ideas on how I can add $profile_id into a new field at the end of the DB ?

 

Regards,

Adam

[paul2463]

well an insert statement should look like

 

INSERT INTO tbl (field1, field2, field3) VALUES (value1, value2, value3)

 

so to make you insert statement correct, I think the insert statement should look like this (please update your columns names

 

mysql_query(
    "insert into {$this->tbl} ({$this->prefix}hash, {$this->prefix}key, {$this->prefix}profile_id)
     values ('$hash', '$key', '$profile_id')");

 

you will also have to pass the function the profile_id too so the function needs to be changed to

 

function Set($key, $val, $profile_id)

 

and the call to it should have the profile_id added as well

[doa24uk]

paul, thanks for the help - I think it will work, but for some reason the mysql and variables seem extremely complicated for what I am trying to do.

 

Here is the api.php file that (I believe) does the processing.

 

I have applied the mods you gave in your previous post, but I just can't find where the 'call to it' is!

 

Any help would be absolutely great!

 

Thanks

 

Adam

 

<?php

// ----- Queries ---------------------------------------------------------------

function __checked($key, $val)
{
  return (isset($_POST[$key]) && $_POST[$key] == $val) ? 'checked="checked"' : "";
}

function __selected($key, $val)
{
  return (isset($_POST[$key]) && $_POST[$key] == $val) ? 'selected="selected"' : "";
}


// ----- URLs ------------------------------------------------------------------

function url_extension($url)
{
  $url = parse_url($url);
  $url = isset($url['path']) ? end(explode('/', $url['path'])) : '';
  $url = (strpos($url, '.') !== false) ? end(explode('.', $url)) : '';
  return $url;
}


// ----- Enclose ---------------------------------------------------------------

function enclose($start, $end1, $end2)
{
  return "$start((?:[^$end1]|$end1(?!$end2))*)$end1$end2";
}


// ----- Debug Query -----------------------------------------------------------

function mysql_query_debug($str)
{
  $result = mysql_query($str);

  if (mysql_error())
    echo "<hr /><b>Message:</b> " . mysql_error() . "<br /><b>Query:</b> $str<hr />";

  return $result;
}


// ----- TMySQL_KeyVal ---------------------------------------------------------

class TMySQL_KeyVal
{
  var $tbl    = null;
  var $prefix = null;
  var $ncnt   = null;

  // ----- Init -----

  function Init($tbl, $prefix, $ncnt)
  {
    $this->tbl    = $tbl;
    $this->prefix = $prefix;
    $this->ncnt   = $ncnt;
  }

  // ----- CreateTable -----

  function CreateTable($drop = false)
  {
    if ($drop) mysql_query_debug("drop table {$this->tbl}");

    $cnt = Array();
    for ($i = 0; $i < $this->ncnt; $i++)
      $cnt[] = "{$this->prefix}cnt$i smallint unsigned not null";
    $cnt = implode(", ", $cnt);

    mysql_query_debug(
    "create table {$this->tbl}
     ({$this->prefix}hash smallint unsigned not null,
      {$this->prefix}key  varchar(255)      not null unique,
      {$this->prefix}val  text              not null,
      $cnt,
      index {$this->prefix}hash ({$this->prefix}hash))");
  }

  // ----- Set -----

function Set($key, $val, $profile_id)
  {
    $hash = '0x' . substr(md5($key), 0, 4);
    $key  = mysql_real_escape_string($key);
    $val  = mysql_real_escape_string($val);

mysql_query(
    "insert into {$this->tbl} ({$this->prefix}hash, {$this->prefix}key, {$this->prefix}profile_id)
     values ('$hash', '$key', '$profile_id')");

    mysql_query_debug(
    "update {$this->tbl}
     set    {$this->prefix}val  = '$val',
            {$this->prefix}cnt0 = {$this->prefix}cnt0+1
     where  {$this->prefix}hash = $hash && {$this->prefix}key = '$key'");
  }

  // ----- Get -----

  function Get($key, $start = 0, $end = 0)
  {
    $hash  = '0x' . substr(md5($key), 0, 4);
    $key   = mysql_real_escape_string($key);

    $start = ($start <  0 || $start >= $this->ncnt) ? 0 : $start;
    $end   = ($end   <= 0 || $end   >  $this->ncnt) ? $this->ncnt : $end;

    $cnt = Array();
    for ($i = $start; $i < $end; $i++) $cnt[] = "{$this->prefix}cnt$i";
    $cnt = implode("+", $cnt);

    $result = mysql_query_debug(
              "select {$this->prefix}key 'key', {$this->prefix}val 'val', $cnt cnt
               from {$this->tbl}
               where {$this->prefix}hash = $hash && {$this->prefix}key = '$key'");
    $result = mysql_fetch_assoc($result);

    return $result ? $result['val'] : false;
  }

  // ----- GetAll -----

  function GetAll($start = 0, $end = 0)
  {
    $start = ($start <  0 || $start >= $this->ncnt) ? 0 : $start;
    $end   = ($end   <= 0 || $end   >  $this->ncnt) ? $this->ncnt : $end;

    $cnt = Array();
    for ($i = $start; $i < $end; $i++) $cnt[] = "{$this->prefix}cnt$i";
    $cnt = implode("+", $cnt);

    $result = mysql_query_debug(
              "select {$this->prefix}key 'key', {$this->prefix}val 'val', $cnt 'cnt'
               from {$this->tbl} where {$this->prefix}key != '<< Time >>' && $cnt <> 0
               order by 'cnt' desc, 'val', 'key'");

    $data = Array();
    while($row = mysql_fetch_assoc($result)) $data[] = $row;
    return $data;
  }

  // ----- Clear -----

  function Clear($key)
  {
    $hash = '0x' . substr(md5($key), 0, 4);
    $key  = mysql_real_escape_string($key);

    mysql_query_debug(
    "delete from {$this->tbl}
     where {$this->prefix}hash = $hash && {$this->prefix}key = '$key'");
  }

  // ----- Clear All -----

  function ClearAll($key)
  {
    mysql_query_debug("delete from {$this->tbl}");
  }

  // ----- Shift -----

  function Shift()
  {
    $time  = (integer)(time()/24/3600);
    $shift = $time-(integer)$this->Get("<< Time >>");
    $shift = ($shift > 0)           ? $shift : 0;
    $shift = ($shift < $this->ncnt) ? $shift : $this->ncnt;

    if ($shift)
    {
      $this->Set("<< Time >>", $time);

      $cnt = Array();
      for ($i = $this->ncnt-1; $i >= $shift; $i--)
        $cnt[] = "{$this->prefix}cnt$i = {$this->prefix}cnt" . ($i-$shift);
      for ($i = $shift-1; $i >= 0; $i--)
        $cnt[] = "{$this->prefix}cnt$i = 0";
      $cnt = implode(", ", $cnt);

      mysql_query_debug("update {$this->tbl} set $cnt");
    }
  }
}

?>

[r4cc00n]

I have applied the mods you gave in your previous post, but I just can't find where the 'call to it' is!

 

Line 178?

 

<?php

    if ($shift)
    {
      $this->Set("<< Time >>", $time);

?>

[doa24uk]

I have applied the mods you gave in your previous post, but I just can't find where the 'call to it' is!

 

Line 178?

 

<?php

    if ($shift)
    {
      $this->Set("<< Time >>", $time);

?>

 

I'm not sure I follow you .....

 

I'm not really sure why that line is there, I downloaded this and am a complete noob when it comes to mysql. All that line / function (?) seems to input is <<time>> into the database - it is completely pointless.

[r4cc00n]

I'm not sure I follow you .....

 

I'm not really sure why that line is there, I downloaded this and am a complete noob when it comes to mysql. All that line / function (?) seems to input is <<time>> into the database - it is completely pointless.

 

In your function definition, you have:

 

function Set($key, $val, $profile_id)

 

But when you call the function here on 178:

 

$this->Set("<< Time >>", $time);

 

There is no $profile_id passed to the function, and you don't have a default value for it.

 

[paul2463]

doa24uk

 

yes that is the function call you need to change so

 

<?php

    if ($shift)
    {
      $this->Set("<< Time >>", $time, $profile_id); //where profile_id is set somewhere else in your script

?>

 

I'm not sure I follow you .....

 

I'm not really sure why that line is there, I downloaded this and am a complete noob when it comes to mysql. All that line / function (?) seems to input is <<time>> into the database - it is completely pointless.

 

In your function definition, you have:

 

function Set($key, $val, $profile_id)

 

But when you call the function here on 178:

 

$this->Set("<< Time >>", $time);

 

There is no $profile_id passed to the function, and you don't have a default value for it.

 

 

r4cc00n -> this si what we are sorting out