curl mysql php - entry into mysqk fails

What does the MySQL log say?
Might help to echo out the built sql statement before trying to execute it.

I did echo and the variables are there.
I tried putting them in " " and not.
Mysql is not showing any errors.
Apache is not showing any either.
I'm not sure how to view php errors in the case of something like this.

So you are not getting any MySQL error reported?
Is the db code set up correctly?
It's hard to debug something we cannot see.
Is error reporting turned on in the page - put at the top of the php page
error_reporting(E_ALL);

Happy to show what ever I can. What do you need?
It's not a php page that I look at in a browser, it's only at the command line.

Db looks like this.

Field name          Type          Allow nulls?          Key          Default value          Extras  
var1       varchar(15)       No       None       NULL       
var2       varchar(15)       No       None       NULL       
var3       varchar(15)       No       None       NULL       
var4       varchar(15)       No       None       NULL

I have added the error reporting.
Where will the error show up then? In the apache logs?

When you make the curl request from the command line it should show up there. If there is an error

Remove the -o flag

Try this for your query.  If your query text is double-quoted, then you can include variables $ in it.  This is the way I format queries all the time... except that I usually assign $_POST variables to regular variables first and filter them when I do.

mysql_query("INSERT INTO hwinfo ( var1, var2, var3, var4, id ) VALUES ('$_POST['var1']', '$_POST['var2']', '$_POST['var3'] ', '$_POST['var4']', '$id')" )

Select allOpen in new window

% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
 27   757  100   210    0     0   3359      0 --:--:-- --:--:-- --:--:--  3559

Seems to show that it makes it. I had already checked using -v as well.

#  curl -o /dev/null  -u "xxx:xxxx" --connect-timeout 5 -X POST -F function=hw_info -F var1="
123" -F var2="456" -F var3="789" -F var4="000" xxx.com/myapp.php
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   603  100    56  100   547    719   7032 --:--:-- --:--:-- --:--:--  7391

Nothing in database, no errors in apache logs, no errors sending as you can see.

In my example, I'm just entering those variables but in the script, I am picking them up from the hardware itself so they are being passed as $VAR, $VAR2, etc.

You missed my other comment
Remove the -o flag - that is showing the transfer/download - you don't want that, you want the response from the server.

Have to go for a while now, but for debugging purposes create a testing page that posts those values to your receiving php page and make sure the page is working correctly to start with and adding to the database, if that is working then you need to check the variables you are passing are correct - maybe easier to just change your $_POST to $_GET so you can enter a url directly in the browser with your var1... variables in the url
When debugging something like this it is always easier to start at the lowest common denominator - your receiver page - with known good values and make sure that is working.
Is it possible any of the vars contain a "

I would make up a plain form page that posts to your PHP.  I frequently do that for prototyping and troubleshooting because it is so easy.

On the cURL side:
Since you are using the command line, enclose the variable name AND value in double quote characters ".

curl 
-o /dev/null  \ 
-u "xxx:xxxx" \ 
--connect-timeout 5 \ 
-F "function=hw_info" \ 
-F "var1=123" \ 
-F "var2=456" \ 
-F "var3=789" \ 
-F "var4=000" \ 
xxx.com/myapp.php

Select allOpen in new window

on the PHP/mySQL side:

Note: in your if statement you are using the === operator (IDENTICAL value and data type match) instead of the == operator (EQUAL value match). I wanted point this out in case you meant to use == instead.

Other than that your PHP/mySQL code snippet looks perfect.

else if ($_POST['function'] == 'hw_info') 
{
   mysql_query
   (' 
      INSERT INTO hwinfo ( var1, var2, var3, var4, id ) 
      VALUES ("' . $_POST['var1'] . '","' . $_POST['var2'] . '","' . $_POST['var3'] . '","' . $_POST['var4'] . '","' . $id . '");
   ' )
   or die('Query failed: ' . mysql_error());
}

Select allOpen in new window

Thus, the issue was in the cURL syntax.

not sure how to view php errors

This should help a bit.  It will capture any messages and the contents of the POST array and will mail them to you.  If you make this change and do not get an email message your script may have failed for a PHP parse error.

<?php
ini_set('display_errors', TRUE);
ini_set('log_errors', TRUE);
error_reporting(E_ALL);
ob_start();
var_dump($_POST);
/* REST OF SCRIPT HERE */
$msg = ob_get_clean();
mail('You@your.org', 'PHP SCRIPT MESSAGES', $msg);

Select allOpen in new window

When you run a query, you can organize it like this.  If you do this, you will be able to see the MySQL side messages.

// ESCAPE THE EXTERNAL VARIABLES FOR SAFE USE IN A QUERY
$var1 = mysql_real_escape_string($_POST['var1']);
$var2 = mysql_real_escape_string($_POST['var2']);
$var3 = mysql_real_escape_string($_POST['var3']);
$var4 = mysql_real_escape_string($_POST['var4']);

// CONSTRUCT THE QUERY STRING IN ITS OWN VARIABLE
$sql = "INSERT INTO hwinfo ( var1, var2, var3, var4, id ) VALUES ('var1', '$var2', '$var3', '$var4', '$id')";

// RUN THE QUERY 
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    trigger_error($err, E_USER_WARNING);
}

Select allOpen in new window

If you want to be able to test from a browser address bar, you can (usually) use $_REQUEST instead of $_POST.  But before you deploy the application, change back to $_POST.

Also, I can't really tell from what we have here, but PHP variable names are case-sensitive.  $VAR != $var.  Same is true for PHP array indexes.  $_POST['VAR1'] != $_POST['var1'].  If you set error_reporting(E_ALL) PHP will tell you if the script accidentally relied on an undefined variable.

If you're new to PHP and want to get some foundation in how this all works, this article will guide you to some good learning resources and most importantly will guide you away from the many terrible and obsolete code examples that litter the internet.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

PHP is doing away with support for the MySQL extension, so you are facing a database conversion.  This article will help you make that as smoothly as possible.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

I didn't expect so many replies and will have to get on this later today.
However, I did remove the -o as a quick test and received the following error;

curl: (3) <url> malformed
Query failed: Unknown column 'client_id' in 'field list'

This insert line is exactly the same as others are expect for the table and the fields of course. I must be overlooking something that is starring me in the face.

Here is the full code instead of the edited stuff I originally posted. I like to keep things obfuscated when posting in public but this won't hurt anything :)

I changed the full curl command to be a variable to make the lines shorter
CURL="curl -o /dev/null -u "$USERNAME:$PASSWD" --connect-timeout 5 -X POST"

This is the function I am having a problem with.
sending side;

$CURL -F function=client_info -F mac_conf=$MAC -F dns1=$DNS1 -F dns2=$DNS2 -F versionsw=$VERSION $SERVER_URL/myapp.php

pgp side;

mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw, client_id ) VALUES ("' . $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' .
 $_POST['versionsw'] . '","' . $clientid . '")' )

This function for example works just fine.
sending side;

$CURL -F function=pingtests -F type=ping -F time="$TIME" -F loss=$LOSS -F min=$MIN -F avg=$AVG -F max=$MAX -F unit=$UNIT $SERVER_URL/myapp.php

php side;

mysql_query('INSERT INTO pingtests ( type, time, loss, min, avg, max, unit, client_id ) VALUES ("' . $_POST['type'] . '","' . $_POST['time'] . '","' . $_POST['loss'] . '","' .
 $_POST['min'] . '","' . $_POST['avg'] . '","' . $_POST['max'] . '","' . $_POST['unit'] . '","' . $clientid . '")' )

The sql that works (table pingtests) is not the same as the one that doesn't (table clients)

The error is quite obvious the clients table has not column called client_id

Ah, you're right. Everything I send to the db always has the client_id but in this case, the table doesn't have that.

I removed the client_id and received this error;

curl: (3) <url> malformed
Query failed: Column count doesn't match value count at row 1

I gotta run so I'll get on this later today.

Did you remove the extra var from the values (...)

Just a thought... You might want to read the information in the large red warning box.
http://php.net/manual/en/function.mysql-query.php

And this one, where it says, "This function must always (with few exceptions) be used to make data safe before sending a query to MySQL."
http://php.net/manual/en/function.mysql-real-escape-string.php

Extra var? I posted the exact test above. No extra var?

Ray, if it's as simple as just changing the command, then I can do that. If it requires reprogramming, I'll need to wait. You've pointed that out before but there's not much I can do about it at this moment. Too many other things to  try and fix :)

That's the function which works. It is the first in that post which isn't working.

The programmer also has the sending side set up like this. Are you allowed to have more than one curl event in one function?

function some_test()
{
        COUNT=0

        $CURL -F function=blah_1

        $CURL -F function=blah_2

        $CURL -F function=blah_3

       done
}

Post the real sql line, because what you have in the first post does not tally with the errors you were getting.

using the following command;

$CURL -F function=client_info -F mac_conf="123" -F dns1="456" -F dns2="789" -F versionsw="000" $SERVER_URL/myapp.php

php side;

mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' . $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' .
 $_POST['versionsw'] . '")' )


Result now;

curl: (3) <url> malformed

What is the value of $SERVER_URL

SERVER_URL=http://some_server_IP/myapp_dir

Remember, the other functions are all working. It's only this one that isn't.
They are all the same except for the table/fields and data of course.

Not really sure as I cannot replicate the error. Try like

$CURL -F "function=client_info" -F "mac_conf=123" -F "dns1=456" -F "dns2=789" -F "versionsw=000" $SERVER_URL/myapp.php

Select allOpen in new window

There's no redirect happening is there?

Same result;

Result now;

curl: (3) <url> malformed

Try using the code I provided above regarding your cURL syntax.

Is this from the command line or in a php page?

It's from the command line.
Hang on, I'm going to try a few things based on suggestions and update shortly.

Removing the /dev/null at the start of my curl command seems to remove the error.
curl: (3) <url> malformed

NerdsOfTech; Wrapping each in quotes didn't seem to make any difference.
Dave Baldwin (40206795) Quotes didn't make any difference. No entry at all.

I am now back to my original line;
$CURL -F function=client_info -F mac_conf=$MAC -F dns1=$DNS1 -F dns2=$DNS2 -F versionsw=$VERSION $SERVER_URL/myapp/myapp.php

My php side looks like;
mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' . $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' . $_POST['versionsw'] . '")' )

Am I missing something here? This looks exactly as it was when I started other than removing the client_id which allows the query to run. However, it is inserting a new record instead of updating the proper existing record.

Each sender knows it's id which is needed when they send an update. Updates show the senders id so I can associate it's entries.
 
Looks like I need to determine the id of the sender in order to insert into the proper existing record. And also, some error control which says do NOT insert if ID doesn't exist.

What is the unique identifier for the row?
There is an ON DUPLICATE KEY UPDATE function in mysql that when an insert would create a duplicate key then you can update the values instead e.g.

mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' . $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' . $_POST['versionsw'] . '")
ON DUPLICATE KEY UPDATE dns1="'.$_POST['dns1'].'"' )

Gary, I think the final problem is that I am not getting the scripts id so that it can post to the proper existing record.

I can post a little more code from the php side which shows the id function so the script can post with it's id. My insert command is creating a new record because it is not looking up it's id then posting to the proper record with the matching id.


// searching user in database
$query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// get user id in case we need it later
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$clientid = $row['id'];

// if query does not have results abort script
if (mysql_num_rows($result) < 1 ) exit;

Removing the client_id is what allowed the command to at least create a new record but now it needs to go into the proper existing one to complete the solution.

What is ID in the table? An autonumber field?
What is/are the unique column/s in the table?

NerdsOfTech (40207150)

>on the PHP/mySQL side:

>Note: in your if statement you are using the === operator (IDENTICAL value and data type match) >instead of the == operator (EQUAL value match). I wanted point this out in case you meant to use >== instead.

>Other than that your PHP/mySQL code snippet looks perfect.

I don't really know since this was done by the previous programmer. My task is just to get things running so that things are working as they should be. Then, I will have a programmer go over the entire app fixing errors, problems, etc.

The point is that it takes such a long time to get someone up to speed when you're trying to explain how things work so wanting to show them so that it's easier for them to get on it.

Gary, the table id column is auto increment.

          Field name          Type          Allow nulls?          Key          Default value          Extras  
      id       int(8)       No       Primary       NULL       auto_increment

The id is the unique id of the script connecting to php so that we know which records belong to which script. It is used the first time a device (running the script) is created so that it has a unique identifier.

>What is/are the unique column/s in the table?

Unique? They are all unique, no doubles or duplicates if that is what you mean?

Make sure the mac column is set to unique in the db
Change your sql to

mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' . $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' . $_POST['versionsw'] . '")
ON DUPLICATE KEY id=id' )

Then to get the new ID or the updated ID you use

mysql_query( "SELECT LAST_INSERT_ID()" )

Well, that didn't create a new ID for once but it didn't enter the info into the existing one. Obviously, I'm not understanding how you want me to change this.

Here is what it looks like on the php side now;

        else if ($_POST['function'] === 'client_info') {

#           mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' . $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' . $_POST['versio
nsw'] . '")' )

            mysql_query( "SELECT LAST_INSERT_ID()" )

            mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' . $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' . $_POST['versio
nsw'] . '") ON DUPLICATE KEY id=id' )



                    or die('Query failed: ' . mysql_error());
        }


I also set the mac field to unique.

I misunderstood, I thought you didn't want it updating.

mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' . $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' . $_POST['versionsw'] . '")
 ON DUPLICATE KEY UPDATE
 dns1 = "'.$_POST['dns1'].'",
 dns2 = "'.$_POST['dns2'].'",
 versionsw = "'.$_POST['versionsw'].'"'
) 

Select allOpen in new window

(there was an error in my previous code.

Maybe it's me not explaining correctly or not entering as you are telling me to;

This is how the php function is looking currently. No new entry is being create but no updates in proper client id either. I need to insert into the proper client record which is based on the id.

        else if ($_POST['function'] === 'client_info') {
#           mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' . $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' . $_POST['versionsw'] . '")' )
            mysql_query( "SELECT LAST_INSERT_ID()" )
            mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' .

$_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' . $_POST['versionsw'] . '")
            ON DUPLICATE KEY UPDATE
            dns1 = "'.$_POST['dns1'].'",
            dns2 = "'.$_POST['dns2'].'",
            versionsw = "'.$_POST['versionsw'].'"'
        )


                    or die('Query failed: ' . mysql_error());
        }

Try this

else if ($_POST['function'] == 'client_info') {
	mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' . 
	$_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' . $_POST['versionsw'] . '")
	ON DUPLICATE KEY UPDATE
	dns1 = "'.$_POST['dns1'].'",
	dns2 = "'.$_POST['dns2'].'",
	versionsw = "'.$_POST['versionsw'].'"')

	or die('Query failed: ' . mysql_error());

	$client_id=mysql_query("SELECT LAST_INSERT_ID()")
}

Select allOpen in new window

When using this last entry, it breaks the entire php side, nothing gets written to the db.

This site should have a function which allows people to agree to a non disclosure in order to privately see more information. The reason this is so difficult is mainly because I simply cannot post all of my code. I do try to post everything I possibly can however.

Missing a colon at the end of...

$client_id=mysql_query("SELECT LAST_INSERT_ID()");

OK, so now it's sending the rest into the db again but the proper id record is not being updated, instead, it is still creating a new record.

The following is the function which checks the remote's id;

// searching user in database
$query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// get user id in case we need it later
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$clientid = $row['id'];

// if query does not have results abort script
if (mysql_num_rows($result) < 1 ) exit;

The sender side is as follows; ($CULR simply shortened as a variable)

$CURL -F function=client_info -F mac_conf=$MAC -F dns1=$DNS1 -F dns2=$DNS2 -F versionsw=$VERSION


The php side is as follows;

else if ($_POST['function'] == 'client_info') {
       mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' .
       $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' . $_POST['versionsw'] . '")
       ON DUPLICATE KEY UPDATE
       dns1 = "'.$_POST['dns1'].'",
       dns2 = "'.$_POST['dns2'].'",
       versionsw = "'.$_POST['versionsw'].'"')

       or die('Query failed: ' . mysql_error());

       $client_id=mysql_query("SELECT LAST_INSERT_ID()");
}

So the only problem I am still having is that the client_info function is not finding it's unique id first so that it can enter it's info in the correct record.

Gary: You are definitely the winner of this question with your answer of removing the client_id.
I can award any time but wanted to let you know that I certainly appreciate your help at this point because I'd love to see this working finally :)

This code is defunct

// searching user in database
 $query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
 $result = mysql_query($query) or die('Query failed: ' . mysql_error());

 // get user id in case we need it later
 $row = mysql_fetch_array($result, MYSQL_ASSOC);
 $clientid = $row['id'];

 // if query does not have results abort script
 if (mysql_num_rows($result) < 1 ) exit;

Select allOpen in new window

The new code I posted above takes care of the case where you are inserting a new record or in the case where the mac_conf values exists then updating the record.
As long as mac_conf  is set to be unique in the table the record will always be updated and not inserted - so I don't see how you can be getting multiple entries for the same vaue.
And this
$client_id=mysql_query("SELECT LAST_INSERT_ID()");
will (should) always return the autoincrement ID

Actually you need to re-implement reference to your client_id column so that you can edit an existing record if a match is found; otherwise you'll need to NULL the id to create a new record automatically.

// searching user in database
$query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());


// if query does not have results, put $client_id = NULL (for SQL autoincrement) otherwise put id as $client_id (for SQL UPDATE)

if (mysql_num_rows($result) < 1 ){
       $client_id='NULL'; //null
}else{
// get user id
       $row = mysql_fetch_array($result, MYSQL_ASSOC);
       $client_id = $row['id'];
}

Select allOpen in new window

// ... code continues ... ///
else if ($_POST['function'] == 'client_info') {
       mysql_query('
INSERT INTO clients ( mac_conf, dns1, dns2, versionsw, id) VALUES ("' .
       $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2'] . '","' . $_POST['versionsw'] . '",' . $client_id . ')
       ON DUPLICATE KEY UPDATE
       dns1 = "'.$_POST['dns1'].'",
       dns2 = "'.$_POST['dns2'].'",
       versionsw = "'.$_POST['versionsw'].'"')

       or die('Query failed: ' . mysql_error());

       $client_id=mysql_query("SELECT LAST_INSERT_ID()");
}

Select allOpen in new window

The first code block is defunct - there is no point doing a select to see if a record exists to only insert/update a record regardless.

NerdsOfTech; there is no client_id field in the clients table.

mac_conf cannot be used as a unique field. Every value but id could change which is why the id is always the unique qualifier in this case.

Only the id field is the unique field.

-- Table structure for table `clients`
--

DROP TABLE IF EXISTS `clients`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `clients` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `mac` varchar(45) NOT NULL,
  `versionsw` varchar(15) NOT NULL DEFAULT 'NULL',
  `mac_conf` varchar(15) NOT NULL DEFAULT 'NULL',
  `dns1` varchar(15) NOT NULL DEFAULT 'NULL',
  `dns2` varchar(15) NOT NULL DEFAULT 'NULL',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mac` (`mac`),
  KEY `user_fk_idx` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Then what is the correllation between tables?
How do you know if the data should be inserted or which row should be updated?

You were passing client_id originally which doesn't exist in the table

Should you not be passing some id to the sql?

I'm getting a bit confused by your setup

PS: Should I change that mac key back from being 'unique'?

I'm sorry about that, it's mainly because I can't post everything otherwise it would be pretty clear I think. Mind you, the last programmer really messed things up.

Not sure how I can explain the correlation but I'll try.

The clients have a unique id.
My mentioning client_id was a mistake as there is no client_id in the clients table.

For the clients table, I don't need to pass anything because other than the info you see, everything else is static. The only info I need to update are the ones I am showing in this question but when I update the client record, I need to know it's id so that I can update the correct record.

When a client script posts it's updates, it includes the unique client id so that the logs are associated with that client script (device) so each table has an 'id' field which the sender inserts it's id into.

So, when I want to update the client info in the clients table. I am not trying to update the id, I am trying to determine my own (the sender) id so that I can update the correct (it's own) record and not another record.
Maybe I'm using the word 'record' wrong but when looking at the clients table, these are records to me

id   name       password      mac
9       name       password    xx:xx:xx:xx:xx:xx       
41       name       password   xx:xx:xx:xx:xx:xx

And of course, I've since added the additional fields which I am wanting to fill, versionsw,dns1,dns2, etc

The senders are finding their id already since they need it to update the other tables. I think that the function they are using to determine their id is the following code I've posted.

// searching user in database
$query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// get user id in case we need it later
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$clientid = $row['id'];

// if query does not have results abort script
if (mysql_num_rows($result) < 1 ) exit;

the is also the following which is just above the code I posted a moment ago. Maybe this is how the sender is getting it's id?

for ($i = 0; $i < strlen($user); $i++) {
    $mac .= $user[$i];
    if ($i % 2 && $i < strlen($user) - 1)
        $mac .= ':';
}

This is confusing - you want to update the clients table but I don't know what it is in the clients table that identifies which row to update

When a client script posts it's updates, it includes the unique client id

Is this client ID equal to the ID in the clients table?

Yes, each client has a unique 'id'.
That client users this 'id' as it's unique identifier in other tables when it updates records.
When a client updates records, it includes it's 'id' so that we can see which client sent that update.

In the case of the clients table, the client trying to send the dns1, dns2 etc information already has an id and a record in the clients table, it just needs to update the fields in it's record. It identifies it's record by using it's 'id'.

This is why I've posted the other code because something in the php page is what the client is using to figure out it's 'id' to begin with so that it can update the 'id' field in the other tables.

What ever that function is, is what I need in order to tell the client which 'id' record to update in the clients table.

OK making a bit more sense - lets try this.

else if ($_POST['function'] == 'client_info') {
	// This is building the mac value from the username
	// which being a username I assume is unique
	for ($i = 0; $i < strlen($user); $i++) {
		$mac .= $user[$i];
		if ($i % 2 && $i < strlen($user) - 1)
		$mac .= ':';
	}

	// So we just need to update the record based on the mac value
	mysql_query('UPDATE clients SET mac_conf="'.$_POST['mac_conf'].'", dns1="'.$_POST['dns1'].'", dns2="'.$_POST['dns2'].'", versionsw="'.$_POST['versionsw'].'"'
		WHERE mac="'.$mac.'"')

	or die('Query failed: ' . mysql_error());

	$client_id=mysql_query("SELECT LAST_INSERT_ID()")
}

Select allOpen in new window

Here is another example which I hope explains/makes sense.

id 	name  password 	mac 	        versionsw 	mac_conf 	dns1 	dns2
41 	name  password 	xx:xx:xx:xx:xx  
109 	0          0 	                xx:xx:xx:xx:xx 	someversion                           192.168.1.30 	192.168.1.29

Select allOpen in new window

The client sending the information that you see in id 109 should actually be updating the fields in it's own record. There is nothing to be put into mac_conf at the moment so it's empty as should be.

After the update, it should look like;

id 	name  password 	mac 	        versionsw 	mac_conf 	dns1 	dns2
41 	name  password 	xx:xx:xx:xx:xx   someversion                           192.168.1.30 	192.168.1.29

Select allOpen in new window

Instead, what is happening is that when client 41 is trying to update it's record, it is creating a new one (id 109) because it doesn't know it's own 'id'. If it knew it's 'id' then it would be updating those fields in record 'id' #41.

So, since there is no error catching, when client #41 tried to update it's own record, it created id #109 instead of updating it's own record.

See the previous comment - it makes more sense now with the extra code you posted
I assume $user is being set earlier on in the page.

My example gets all messed up when squashed. Just paste it into a wider window. I think it'll make sense now. I'll wait to hear back before editing with your last post :).

Use the CODE tag in the comment window - select the text and click Code

I think what is creating (or finding the 'id' is the code I posted in ID: 40210236

It's not code, it's just examples I am writing up but I'll try it next time.

Yep, got that - try my previous comment code.
This is just building the mac value and then just update the db.

The code your posted has a duplicated entry of what is already in the php page.

for ($i = 0; $i < strlen($user); $i++) {
    $mac .= $user[$i];
    if ($i % 2 && $i < strlen($user) - 1)
        $mac .= ':';
}

Also, it's not the mac value we need to find, it's the 'id'.

Then remove it if it's already in place earlier than the if/else - the mac value is the formatted username - this must be unique in the clients table - unless you have multiple users with the same username?
I'm making assumptions here but the ID in the clients table is what is used elsewhere in other tables...?

This is quite cumbersome not seeing the whole picture.

Let me post a bigger sample of code which surely should help. Give me a minute please :).

Now I understand which section you needed to see which I failed to post because of my lack of knowledge of php. Here is more information in the starting section of the php page which I probably needed to post early on.

$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];
$mac = "";

for ($i = 0; $i < strlen($user); $i++) {
    $mac .= $user[$i];
    if ($i % 2 && $i < strlen($user) - 1)
        $mac .= ':';
}

// searching user in database
$query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// get user id in case we need it later
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$clientid = $row['id'];

// if query does not have results abort script
if (mysql_num_rows($result) < 1 ) exit;


The function which is not working;

        else if ($_POST['function'] === 'client_info') {
        mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw ) VALUES ("' . $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2']
. '","' . $_POST['versionsw'] . '")' )
                or die('Query failed: ' . mysql_error());


This is a function which is working. The client is able to update the 'pingtests' table and it includes it's 'id' which it picked up earlier in the page;

        else if ($_POST['function'] === 'pingtests') {
            mysql_query('INSERT INTO pingtests ( type, time, loss, min, avg, max, unit, client_id ) VALUES ("' . $_POST['type'] . '","' . $_POST['time'] . '","
' . $_POST['loss'] . '","' . $_POST['min'] . '","' . $_POST['avg'] . '","' . $_POST['max'] . '","' . $_POST['unit'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());

Select allOpen in new window

ALL variables in the 'clients' table could change so the only reliable field is the 'id' field which will never change.
When the client connects, it needs to find out it's 'id' from the clients table so that it can include it when it updates records in other tables. I think this will help solve the mystery.

If I understand right, you needed lines 12 and 13 to fully see the picture.

I'm assuming you've removed some code since you have an else with no starting if

This line
mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw

Do you not want to be updating the row - not inserting a new row?

See below code.

In your SQL INSERT code you'll want to make ID = NULL on non-match; and on MATCH the ID you retrieved from your SELECT query should be used (so that you trigger the ON DUPLICATE clause).

Next, a minor recommendation: the === (IDENTICAL) operator should be replaced with the == (EQUAL) operator in your if statements since you aren't comparing data types (just values).

$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];
$mac = "";

for ($i = 0; $i < strlen($user); $i++) {
    $mac .= $user[$i];
    if ($i % 2 && $i < strlen($user) - 1)
        $mac .= ':';
}

// searching user in database
$query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// check match
if (mysql_num_rows($result) < 1 ){
	//NO MATCH FOUND
	// make $client_id = 'NULL' for SQL autoincrement
	$clientid = 'NULL';
	
}else{
	// MATCH FOUND
	// get user id in case we need it later
	$row = mysql_fetch_array($result, MYSQL_ASSOC);
	$clientid = $row['id'];
}


if ($_POST['function'] == 'client_info') {
        mysql_query('INSERT INTO clients ( mac_conf, dns1, dns2, versionsw, id ) VALUES ("' . $_POST['mac_conf'] . '","' . $_POST['dns1'] . '","' . $_POST['dns2']
. '","' . $_POST['versionsw'] . '"' . $clientid . ')' )
                or die('Query failed: ' . mysql_error());
else if ($_POST['function'] == 'pingtests') {
            mysql_query('INSERT INTO pingtests ( type, time, loss, min, avg, max, unit, client_id ) VALUES ("' . $_POST['type'] . '","' . $_POST['time'] . '","
' . $_POST['loss'] . '","' . $_POST['min'] . '","' . $_POST['avg'] . '","' . $_POST['max'] . '","' . $_POST['unit'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
}                                        

Select allOpen in new window

I just cut and pasted the sections which I figured are most relative.
Can't really explain it much better than ID: 40210280.

>Do you not want to be updating the row - not inserting a new row?

I'm not fully sure what this means. Is a row a record? To me, a record is an entry which has data in the fields. Or, if you look at the example above, a row would be the entry which begins with id #41.

The client needs to know it's 'id' so that it can update it's own record or fields in the 'clients' table.

it would probably help if I had the ON DUPLICATE KEY in the code :)

$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];
$mac = "";

for ($i = 0; $i < strlen($user); $i++) {
    $mac .= $user[$i];
    if ($i % 2 && $i < strlen($user) - 1)
        $mac .= ':';
}

// searching user in database
$query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// check match
if (mysql_num_rows($result) < 1 ){
	//NO MATCH FOUND
	// make $client_id = 'NULL' for SQL autoincrement
	$clientid = 'NULL';
	
}else{
	// MATCH FOUND
	// get user id in case we need it later
	$row = mysql_fetch_array($result, MYSQL_ASSOC);
	$clientid = $row['id'];
}


if ($_POST['function'] == 'client_info') {
	mysql_query
	('
	INSERT INTO clients 
		( mac_conf, dns1, dns2, versionsw, id ) 
	VALUES 	(
	"' . 
	$_POST['mac_conf'] . '","' . 
	$_POST['dns1'] . '","' . 
	$_POST['dns2'] . '","' . 
	$_POST['versionsw'] . '"' . 
	$clientid . ')' . '
	ON DUPLICATE KEY UPDATE
       	dns1 = "'.$_POST['dns1'].'",
       	dns2 = "'.$_POST['dns2'].'",
       	versionsw = "'.$_POST['versionsw'].'"'
	) or die('Query failed: ' . mysql_error());

else if ($_POST['function'] == 'pingtests') {
            mysql_query('INSERT INTO pingtests ( type, time, loss, min, avg, max, unit, client_id ) VALUES ("' . $_POST['type'] . '","' . $_POST['time'] . '","
' . $_POST['loss'] . '","' . $_POST['min'] . '","' . $_POST['avg'] . '","' . $_POST['max'] . '","' . $_POST['unit'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
}                                        

Select allOpen in new window

@projects, try testing the latest code I submitted.

As it stands in your previous code:
Your insert statement was adding new records (AKA rows) ONLY.

Gary first provided the ON DUPLICATE KEY UPDATE clause needed to "UPDATE" a record with the same "ID"; however, there were some underlying issues that need to be ironed out.

The solution I provided above NULLs the ID in case there is NO "ID" MATCH (this triggers SQL to create, or technically speaking, INSERT, a new record with an AUTOINCREMENTED ID). Conversely, ON "ID" MATCH, your SQL INSERT query transforms into an UPDATE query due to the ON DUPLICATE KEY UPDATE clause.

I hope this sheds light on this issue

Yes a row is a record

The client needs to know it's 'id' so that it can update it's own record or fields in the 'clients' table.

So your insert statement is wrong - you don't want to be adding records for every request in the clients table do you?

You are making a curl request for client_info and posting the dns1... data which ends up in this function where you are doing an insert
else if ($_POST['function'] === 'client_info') {

You say it shouldn't be inserting but updating.

This makes no sense
$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];
You are posting the username/password in the curl request

Can you understand why this hard when we only see bits of the picture
Maybe if you can do a flowchart or similar of the process of what you are posting and what should be happening.

I'm not sure I am understanding but I cannot have a new 'id' created in the clients table.
The updates need to go into the fields of the client which is sending the updates.

@NerdsOfTech
I don't think he wants to be inserting new users willy nilly from a curl post - else what is the point of posting a username/password in the curl post. Or maybe I can't see the bear in the room

Something here is just not tallying in the logic

Correct Gary but it's not a user, it's a client connecting. A client is simply a workstation running the script.

Of course I understand that this is difficult when I am not able to post everything. What makes you think I don't, I've even said that myself several times :).

I don't know where to go with this because no matter how I explain it, it doesn't seem to make sense. Yet it is in fact very simple.

Please, look closely at message ID: 40210280

That post should so clearly explain what I am trying to say.

I am not trying to create new ids if one doesn't exist.
If one doesn't exist then the query should fail because it's not from the correct client.
The client needs to find out it's own 'id' when it connects so that it can update it's own record.
It's record is id #41 in the example I posted.
It is instead creating a new record because there is no error catching and because the client doesn't know it's own 'id' so doesn't know which record to update.

I just don't know how else to explain this in text.

Look at this - which changed the function to update the record but the mac value makes no sense since it is based on a server value and I have no idea where that is coming from or what the purpose of posting the username/password in the curl post

I'm not sure why the previous programmer has the following in the php page
$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];

The sending script (client) already contains the user name and the password. However... I THINK the reason he's using this is to FIND the 'id' of the client  in the clients table so that the client can also insert it's 'id' into the row it is sending for updates in other tables.

@Gary
It looks like @project's code is using explicit server variables from the cURL request (to check to see if the person running the page is logged in) and POSTing the fields in simultaneously.

So, according to the code I submitted the code would INSERT a new record when there is a NEW USER (NULL id) and ON DUPLICATE KEY, UPDATE a record on an id match (the id match found in the SELECT query prior).


@projects have you tried my code above?

The mac and a password are being used as a unique name/passwd for the device.
That's the only reason those are there. Those have no relation to the client id. The client id is simply another unique means of identifying a client so that we can look up it's records, logs, etc.

The only thing that is missing to allow the client to update it's own record is that it doesn't know what it's own id is. I posted showing that it is in fact #41 but IT doesn't know that.

There is something missing in the client_info function which needs to tell the client what it's 'id' is so that it can update it's own fields in the clients table and no one elses nor create any new records or rows.

@nerds
Yeah just realized that now looking back - d'oh

So the previous code in 40210266 should be correct - update the clients table - no insert just an update.

NerdsOfTech2014-07-21 at 17:00:41ID: 40210430

Nope. I've said countless times that I DONT want any new records or rows created and that if an id doesn't exist in the clients table, that the function should terminate, NOT create a new record :).

For example, client id 41 should be able to update it's own fields in it's own record and non else.

Those have no relation to the client id

They do - the username is used to create the mac value in the clients table.
Look at 40210266 which uses this value to update the clients table with the new values.

Oh, yes, in that way. I'm getting confused trying to explain I guess. Like I said, I wish there was a way of sharing code without it being public. Then it would be simpler I think.

Hang on now, no, the username is not being used that way at all. The mac value comes from the workstation and so does the password.

@projects

I understand that you want to have the client run a script that obtains info from the client which generates a cURL command that HTTP POSTs the info to the PHP processing page.

The PHP processing page determines what 'function' the cURL POSTed to insert/update the appropriate table.

Since the id is automatically generated for NEW users, the id can only be MATCHED running the SELECT query (which is ran at the beginning of the PHP script); if no match is found a NEW id is created; on a non-match an EXISTING id is UPDATED.

From this assumption, my post on ID: 40210384 should be the solution

Is this correct?

Maybe the programmer left some old junk in the php file.

// these are obtained from the cURL command to make sure the user is running the script (and NOT some unauthorized or spoofed user)
$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];

LOL
Then what is the point of
$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];

Is that not logging onto the server - or rather the sending of the username/password in the curl post to log in to the server?

Sorry I'm getting more confused

ditto @nerds

I have to go very soon

>The PHP processing page determines what 'function' the cURL POSTed to
>insert/update the appropriate table.

The client script connects to the php page.
Say that it is sending ping stats so using the 'pingtests' table, then it is updating that table by sending it's unique 'id' and entering that into the firm column of each record so that we know which client it is coming from.

HOW the client determines what it's 'id' is, IS what we are trying to find out.

>Since the id is automatically generated for NEW users, the id can only be
>MATCHED running the SELECT query (which is ran at the beginning of the
>PHP script); if no match is found a NEW id is created; on a non-match an
>EXISTING id is UPDATED.

No, clients are created manually by someone and when they are, they are auto assigned an 'id' because the 'id' field is auto increment. Once a client is created, it's 'id' never changes but some of the fields in it's record could change. Name, password, dns1, dns2, etc, all of those could change but those would still be for which ever client that is.

If a query is made and the client doesn't know it's 'id', then it should NOT be able to write anything to the database nor can it create any new records. The previous programmer may have left confusing code which shows otherwise in the php page but I am telling you here and now that nothing should be allowed to create a new record.

So, the quest here is to find a way for the client to find out what it's OWN 'id' is when it connects to php so that it can update it's OWN record fields if need be.

or in technical terms:

$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];

is for HTTP authentication purposes since cURL is logging in with the clients credentials.

http://php.net/manual/en/features.http-auth.php

Yes, the client must be authenticated to send anything to php/mysql.

The user_id and a client_id are other things being used in some tables which basically just keep track of which human being to contact (department) when there are problems.

ASSUMPTIONS:
in the author's communication the cURL request is logging in as the client on the box they are on:
CURL="curl -o /dev/null -u "$USERNAME:$PASSWD" --connect-timeout 5 -X POST"...

$USERNAME:$PASSWORD are both likely derived from the user logged in on from the cURL request generation script

for HTTP authentication purposes the cURL is logging in with the clients credentials; thus, the PHP processing page is AUTHENTICATING the user and UPDATING the CURRENT user's box info.

Again this is a theory---

I don't know where to go with this LOL. I guess the difficulty is that the id being found it related to some other table or something.

The client ID is got from the little routine you have their parsing the username and selecting the row from the clients table matching this $mac value
(at least from what I gather)

@Nerd; Correct BUT how is it getting it's 'id' so that it can enter it into other tables?

That's what I figure also Gary, which is why I posted it.
Is there some way of testing from the command line or something to find some info on how the client is getting that id? Once I know which item IS the id, then it's a matter of putting that into the function so that the correct client id record is updated.

Unlike a bash script, where I could echo and print the values somewhere, since the php page is not being used with a browser, is there some way of having it output all of it's values once it's run from a client connection?

The update code I posted a few hours ago does this - it uses the $mac value to update the correct record
Did you try it?

Reposted since it was so long ago...

else if ($_POST['function'] == 'client_info') {
	// So we just need to update the record based on the mac value
	mysql_query('UPDATE clients SET mac_conf="'.$_POST['mac_conf'].'", dns1="'.$_POST['dns1'].'", dns2="'.$_POST['dns2'].'", versionsw="'.$_POST['versionsw'].'"'
		WHERE mac="'.$mac.'"')

	or die('Query failed: ' . mysql_error());

// You may not need this
	$client_id=mysql_query("SELECT LAST_INSERT_ID()")
}

Select allOpen in new window

I've lost track now. Can you point to the msg id? I tried pretty much everything I was shown or asked but I could have missed something.

Thank you for the clarification @projects... all you need is an UPDATE SQL statement... not an INSERT ON DUPLICATE KEY UPDATE statement.

:)

$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];
$mac = "";

for ($i = 0; $i < strlen($user); $i++) {
    $mac .= $user[$i];
    if ($i % 2 && $i < strlen($user) - 1)
        $mac .= ':';
}

// searching user in database
$query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// get user id in case we need it later
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$clientid = $row['id'];

// if query does not have results abort script
if (mysql_num_rows($result) < 1 ) exit; // UNAUTHORIZED USER EXIT SCRIPT


else if ($_POST['function'] === 'client_info') {
        mysql_query('
UPDATE clients  
SET
mac_conf = "' . $_POST['mac_conf'] . '",
dns1 = "' . $_POST['dns1'] . '",
dns2 = "' . $_POST['dns2'] . '", 
versionsw = "' . $_POST['versionsw'] . '",
WHERE id = ' . $clientid . ';') or die('Query failed: ' . mysql_error());
                


else if ($_POST['function'] === 'pingtests') {
            mysql_query('INSERT INTO pingtests ( type, time, loss, min, avg, max, unit, client_id ) VALUES ("' . $_POST['type'] . '","' . $_POST['time'] . '","
' . $_POST['loss'] . '","' . $_POST['min'] . '","' . $_POST['avg'] . '","' . $_POST['max'] . '","' . $_POST['unit'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
                                        

Select allOpen in new window

Code was reposted just above.

Yes, right after I posted :).
Syntax errors?

syntax error, unexpected 'WHERE' (T_STRING)

line:         WHERE mac="'.$mac.'"')

@Gary your UPDATE post from before is correct except the id column is coming from the matched id found in the SELECT query and put into the $clientid variable.

In @projects scenario, we can assume that the users can be on a variety of boxes; ergo, the user, not the mac address, would be unique.

Actually nerds has got the better method of matching the ID against the initial select query (forgotten about that) so use his update

the user is the mac address

Yeah @nerds - getting tired

WHERE id = ' . $clientid . ';') or die('Query failed: ' . mysql_error());
               
should be
WHERE id = "' . $clientid . '";') or die('Query failed: ' . mysql_error());
(this is nerds code - can you not change your name - calling you nerds is just wrong :o)

You mean us msg ID: 40210505?

I'm not really sure how, I tried it earlier and it didn't work.

Thanks @Gary I forgot the double quotes for id = " "

And you can call me Nerds or Nerd :)

mysql_query('
UPDATE clients  
SET
mac_conf = "' . $_POST['mac_conf'] . '",
dns1 = "' . $_POST['dns1'] . '",
dns2 = "' . $_POST['dns2'] . '",
versionsw = "' . $_POST['versionsw'] . '",
WHERE id = "' . $clientid . '";') or die('Query failed: ' . mysql_error());

else if ($_POST['function'] === 'client_info') {
        mysql_query('
UPDATE clients  
SET
mac_conf = "' . $_POST['mac_conf'] . '",
dns1 = "' . $_POST['dns1'] . '",
dns2 = "' . $_POST['dns2'] . '", 
versionsw = "' . $_POST['versionsw'] . '",
WHERE id = "' . $clientid . '";') or die('Query failed: ' . mysql_error());

Select allOpen in new window

Guys, slow down a moment please!!! :).

Yes, weird to call someone nerd.

Not sure what I am putting into php anymore. Your last code Gary or your last code with @nerds updated line?

nerds just before your comment

Ok, I posted @nerds update and there are no errors but no updates and not new record either which is good :)

@projects,
So you are saying that there is one user per box and that users are forbidden to migrate to a new box?

If so, both @Gary's (MAC) and my (id found from the previous SELECT) SQL solution would work; I just based mine off of the other SQL statement found in the else if, since it referenced $clientid for its id.

Add before the mysql_query line - you can still write out debugging lines in a curl post

echo '
UPDATE clients  
SET
mac_conf = "' . $_POST['mac_conf'] . '",
dns1 = "' . $_POST['dns1'] . '",
dns2 = "' . $_POST['dns2'] . '", 
versionsw = "' . $_POST['versionsw'] . '",
WHERE id =' . $clientid;

Select allOpen in new window

Verify the update is correct, post the resulting echo here.

Double quotes is wrong (doesn't matter) its an auto increment field - getting confused from earlier when projects said all fields are varchar

There is one user per workstation (aka script, aka client).
A client row for that client is created manually by an admin.
No other client should be able to update any other client row but it's own.
No other client should be able to create any new rows.

I updated the php with your code.
I tested and the client didn't update it's record with the dns1, dns2 etc data. This is because it doesn't know what it's own 'id' is so it cannot update it's own row.

However, no new row was created which is a good thing.

Add the echo in so we can verify what the actual SQL is

To be sure - are you sending a valid username/password?

On second thought since id is a number field remove the double quotes AND I forgot the ending bracket to terminate the first elseif; speaking of which, if this code is continuous as stated (and nothing is between the authentiation code and the if statements it should be "elseif" opposed to "else if" as the syntactic meaning is slightly different (if you're familiar with C, C's behavior in terms of if statements is the same as PHP's behavior). Therefore only the FINAL if should be else if (separated) in this case.

$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];
$mac = "";

for ($i = 0; $i < strlen($user); $i++) {
    $mac .= $user[$i];
    if ($i % 2 && $i < strlen($user) - 1)
        $mac .= ':';
}

// searching user in database
$query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// get user id in case we need it later
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$clientid = $row['id'];

// if query does not have results abort script
if (mysql_num_rows($result) < 1 ) {
   exit; // UNAUTHORIZED USER EXIT SCRIPT
}


elseif ($_POST['function'] === 'client_info') {
        mysql_query('
UPDATE clients  
SET
mac_conf = "' . $_POST['mac_conf'] . '",
dns1 = "' . $_POST['dns1'] . '",
dns2 = "' . $_POST['dns2'] . '", 
versionsw = "' . $_POST['versionsw'] . '",
WHERE 
id = ' . $clientid . ';
') or die('Query failed: ' . mysql_error());
}         

       
else if ($_POST['function'] === 'pingtests') {
            mysql_query('
INSERT INTO pingtests 
( type, time, loss, min, avg, max, unit, client_id ) 
VALUES ("' . 
$_POST['type'] . '","' . 
$_POST['time'] . '","' . 
$_POST['loss'] . '","' . 
$_POST['min'] . '","' . 
$_POST['avg'] . '","' . 
$_POST['max'] . '","' . 
$_POST['unit'] . '","' . 
$clientid . '")
') or die('Query failed: ' . mysql_error());
}                                     

Select allOpen in new window

But that's not the whole script so it'll take me a while to update that unless you want me to create another test page instead?

No just replace this section

elseif ($_POST['function'] === 'client_info') {

I replaced the whole sections before seeing this and tested. No errors in apache log, no other errors that I can see. No updates in client fields for client #41 in this case.

The client I am using to test is id 41 so would update it's own row in the clients table.

Yes, sending correct name/passwd because I also see other tables getting updated.

keep the last else if (separated by a space) in tact, all predecessor else if's in the same if tree should be elseif (no space)

please insert debug code:

echo 'clientid: ' . $clientid; 

Select allOpen in new window

on a line after

// if query does not have results abort script
if (mysql_num_rows($result) < 1 ) {
   exit; // UNAUTHORIZED USER EXIT SCRIPT
}

Select allOpen in new window

Is the mac on the box you are running the script on in the user table?

There are other functions and I don't want to post the whole thing in public. I didn't know that spacing between else and else if sections make a difference so now wondering if I have other problems. However all other functions do work as they should.

I should've put a "minor" remark in that optional change.

As long as you are using curly brackets throughout the if tree, then else if and elseif are the same.

Same "minor" remark should go with my previous === and == operator optional change.

These are just minor nitpicks because I'm OCD... so you can ignore those unimportant mentions.

Back on track:
The UPDATE sql should be working... so is there an EXISTING record in table `clients` to match against???

>Is the mac on the box you are running the script on in the user table?

There is a 'users' table but there is no 'mac' field in that table.

We could try a test if you like. The client I am using is id 41 in the clients table. Would it have any value to manually update it just to make sure that's working?

>The UPDATE sql should be working... so is there an EXISTING record in table `clients`
>to match against???

Yes, this is what I've said many times :).
The client HAS a record in the clients table. In this case, I am using client 'id' 41.

Why does the client know that it is id 41 when it posts in the pingtests table?
This is what is needed in order to update it's own record.

Also, I am not using a browser to the php page so where do I look for any echo output or values, etc?

this is another reason why I explicitly added curly brackets to the first if statement in case it was part of the same if tree as your else if's.

:)

http://php.net/manual/en/control-structures.elseif.php

=== vs. ==

http://php.net/manual/en/language.operators.comparison.php

Awaiting the echo results...

Your PHP code finds the user's id so we are using that to tell your SQL to UPDATE the clients table.

And yes, in this case there is no MAC field in clients so we are using the MAC correlated id found in the first SELECT statement `id`

elseif ($_POST['function'] === 'client_info') {
        mysql_query('
UPDATE clients  
SET
mac_conf = "' . $_POST['mac_conf'] . '",
dns1 = "' . $_POST['dns1'] . '",
dns2 = "' . $_POST['dns2'] . '", 
versionsw = "' . $_POST['versionsw'] . '",
WHERE 
id = ' . $clientid . ';
') or die('Query failed: ' . mysql_error());
} 

Select allOpen in new window

How can I give you the echo results? Can I output the values to a file instead so that when the client connects, running the php code, we can see some results

in your cURL the result is in the file specified in -o flag

if you have access to the cURL code you can change /dev/null to something like "file_#1.txt" etc

the results would post that file... and if the client was viewing that processing page via a browser, they would see the echo as well (you would have to remove the HTTP authentication code at the beginning of the PHP script to do that though [debug]).

so the output into the file was; clientid: 41

the echo in php didn't show anything on the page but the whole page is now displayed where it wasn't before which is how it was supposed to act :)

Now I am confused because I thought the id was being found by the php code.

So this tells us that client knows what it's 'id' is even AS it's connecting?

This tells the client its id only AFTER the php spits out the "response" (echos, content, etc.) to cURL's specified OUTPUT file :)

Ok so we have the client id, and we know we have a match... more debug (and I fixed the spacing on the SQL code I sent you earlier:

else if ($_POST['function'] === 'client_info') {

echo "\n mac_conf: $_POST['mac_conf']";
echo "\n dns1: $_POST['dns1']";
echo "\n dns2: $_POST['dns2']";
echo "\n versionsw: $_POST['versionsw']";
exit; // EXIT for DEBUG

        mysql_query('
UPDATE clients  
SET 
mac_conf = "' . $_POST['mac_conf'] . '", 
dns1 = "' . $_POST['dns1'] . '", 
dns2 = "' . $_POST['dns2'] . '", 
versionsw = "' . $_POST['versionsw'] . '", 
WHERE 
id = ' . $clientid . ';
') or die('Query failed: ' . mysql_error());
} 

Select allOpen in new window

I just ran a manual test;

curl -o "file.txt" -u "xxx:xxx" --connect-timeout 5 -X POST myserver/myapp/myapp.php -
F function=client_info -F mac_conf="123" -F dns1="456" -F dns2="789" -F versionsw="000"

The file contains the following;
Query failed: 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 'WHERE id = 41' at line 7

else if ($_POST['function'] === 'client_info') {

echo "\n mac_conf: $_POST['mac_conf']";
echo "\n dns1: $_POST['dns1']";
echo "\n dns2: $_POST['dns2']";
echo "\n versionsw: $_POST['versionsw']";
$sql = '
UPDATE clients  
SET 
mac_conf = "' . $_POST['mac_conf'] . '", 
dns1 = "' . $_POST['dns1'] . '", 
dns2 = "' . $_POST['dns2'] . '", 
versionsw = "' . $_POST['versionsw'] . '", 
WHERE 
id = ' . $clientid . ';';
echo "\n SQL: $sql;";
        mysql_query($sql) or die('Query failed: ' . mysql_error());
} 

Select allOpen in new window

post the output txt file

The new code didn't do anything, not even input in the txt file.

I updated and ran it, don't see any output.txt file?

hmmm. ok that's odd. does your php output anything else before this if tree? If not, lets debug exit before the sql is execution and see if this gives you results: check your db for changes too.

elseif ($_POST['function'] === 'client_info') {

echo "\n mac_conf: $_POST['mac_conf']";
echo "\n dns1: $_POST['dns1']";
echo "\n dns2: $_POST['dns2']";
echo "\n versionsw: $_POST['versionsw']";
$sql = '
UPDATE clients  
SET 
mac_conf = "' . $_POST['mac_conf'] . '", 
dns1 = "' . $_POST['dns1'] . '", 
dns2 = "' . $_POST['dns2'] . '", 
versionsw = "' . $_POST['versionsw'] . '", 
WHERE 
id = ' . $clientid . ';';
echo "\n SQL: $sql;";
exit; // EXIT DEBUG
        mysql_query($sql) or die('Query failed: ' . mysql_error());
} 
                                        

Select allOpen in new window

Yes, I keep checking the db for changes, I check the file.txt on the client and I look for any output from php but it needs to go into a file so I can cut/paste it otherwise I won't see it since it's all command line.

Just ran your update. Nothing in file.txt or anywhere else, nothing in db.

Sorry, forgot to reply to your question. There are two functions just before this client_info function if that is what you are asking.

PHP Parse error:  syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING)

Wow a lot going on since I went to the bar

You have an extraneous comma

versionsw = "' . $_POST['versionsw'] . '", //remove this comma
WHERE ...

Select allOpen in new window

I'm back to my original at the moment otherwise, I will lose track.
That version creates a new record instead of updating.

I wish I was at the bar.

@Gary: thanks for spotting that extra comma (my copy/paste error).

@projects: I was asking if there is any other echos that occur anywhere else in the php script because we want the debug to output.
1. keep your -o on your cURL output to a file

2. keep this debug line

echo 'clientid: ' . $clientid; 

Select allOpen in new window

after

// if query does not have results abort script
if (mysql_num_rows($result) < 1 ) {
   exit; // UNAUTHORIZED USER EXIT SCRIPT
}

Select allOpen in new window

3. replace the 'client_info' else if line code with this familiar [fixed] code:

else if ($_POST['function'] === 'client_info') {

echo "\n mac_conf: $_POST['mac_conf']";
echo "\n dns1: $_POST['dns1']";
echo "\n dns2: $_POST['dns2']";
echo "\n versionsw: $_POST['versionsw']";
$sql = '
UPDATE clients  
SET 
mac_conf = "' . $_POST['mac_conf'] . '", 
dns1 = "' . $_POST['dns1'] . '", 
dns2 = "' . $_POST['dns2'] . '", 
versionsw = "' . $_POST['versionsw'] . '" 
WHERE 
id = ' . $clientid . ';';
echo "\n SQL: $sql;";
        mysql_query($sql) or die('Query failed: ' . mysql_error());
} 

Select allOpen in new window

No file.txt output.
Nothing added in clients table.

 PHP Parse error:  syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in line 62

else if ($_POST['function'] === 'client_info') {

echo "\n mac_conf: $_POST['mac_conf']";   <<Line 62
echo "\n dns1: $_POST['dns1']";
echo "\n dns2: $_POST['dns2']";
echo "\n versionsw: $_POST['versionsw']";
$sql = '
UPDATE clients
SET
mac_conf = "' . $_POST['mac_conf'] . '",
dns1 = "' . $_POST['dns1'] . '",
dns2 = "' . $_POST['dns2'] . '",
versionsw = "' . $_POST['versionsw'] . '"
WHERE
id = ' . $clientid . ';';
echo "\n SQL: $sql;";
        mysql_query($sql) or die('Query failed: ' . mysql_error());
        }
        else if ($_POST['function'] === 'outage') {
            try{
                $res = mysql_query("SELECT ip FROM hops where level='" . $_POST['level'] . "' and client_id='" . $clientid . "'" );
                $row = mysql_fetch_assoc($res);
                mysql_query('INSERT INTO error_logs ( hop, start_time, end_time, client_id ) VALUES ("' . $row['ip'] . '","' . $_POST['start_time'] . '","' . $
_POST['end_time'] . '","' . $clientid . '")' )
                    or die('Query failed: ' . mysql_error());
            }
            catch( Exception $e )
            {
                error_log($e);
            }
        }

I bet this would take all of 10 minutes if I could simply hire someone to look at it.

I think we might be on the verge of the solution. Try this last one here:

else if ($_POST['function'] === 'client_info'){
	echo "\n mac_conf:" 	. $_POST['mac_conf'];
	echo "\n dns1: " 	. $_POST['dns1'];
	echo "\n dns2: " 	. $_POST['dns2'];
	echo "\n versionsw: " 	. $_POST['versionsw'];
	$sql = '
		UPDATE clients
		SET
		mac_conf = "' . $_POST['mac_conf'] . '", 
		dns1 = "' . $_POST['dns1'] . '", 
		dns2 = "' . $_POST['dns2'] . '", 
		versionsw = "' . $_POST['versionsw'] . '" 
		WHERE 
		id = ' . $clientid . ';';
	echo "\n SQL: $sql";
	mysql_query($sql) or die('Query failed: ' . mysql_error());
}

Select allOpen in new window

PHP Parse error:  syntax error, unexpected end of file in 88

} This is 88, the last item

Here is the full php page. I am told that as it is in this stage, I can post it publicly.

 

<?php

error_reporting(E_ALL);

// connecting, selecting database
$link = mysql_connect('dbserver', 'xxx', 'xxx')
    or die('Could not connect: ' . mysql_error());
mysql_select_db('myapp') or die('Could not select database');

if (!isset($_SERVER['PHP_AUTH_USER']) && !isset($_SERVER['PHP_AUTH_PW'])){
    header( 'WWW-Authenticate: Basic realm="Input user and password"' );
    header( 'HTTP/1.0 401 Unauthorized' );
    echo "Unauthorized\n";
    exit;
}

// making the code more readable
$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];
$mac = "";

for ($i = 0; $i < strlen($user); $i++) {
    $mac .= $user[$i];
    if ($i % 2 && $i < strlen($user) - 1)
        $mac .= ':';
}

// searching user in database
$query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// get user id in case we need it later
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$clientid = $row['id'];

// if query does not have results abort script
if (mysql_num_rows($result) < 1 ) {
   exit; // UNAUTHORIZED USER EXIT SCRIPT
echo 'clientid: ' . $clientid;

// if you get here you are authorized to download the update
if($_SERVER['REQUEST_METHOD'] === 'GET'){
    // open the update file and retrieve
    $file = fopen("files/updates.txt","r");
    if (!$file) trigger_error('UNABLE TO OPEN files/updates.txt FOR READ', E_USER_ERROR);
    while (!feof($file)){
        echo fread($file,1024);
    }
    fclose($file);
}
        else if ($_SERVER['REQUEST_METHOD'] === 'POST'){
        if (isset($_POST['function'])){
        if ( $_POST['function'] === 'add_hop' ){
            mysql_query('INSERT INTO hops ( ip, level, client_id ) VALUES ("' . $_POST['ip'] . '","' . $_POST['level'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
        }
        else if ($_POST['function'] === 'clean_hops') {
            mysql_query('DELETE FROM hops WHERE client_id = ' . $clientid)
                or die('Query failed: ' . mysql_error());
        }
        else if ($_POST['function'] === 'client_info'){
            echo "\n mac_conf:"         . $_POST['mac_conf'];
            echo "\n dns1: "    . $_POST['dns1'];
            echo "\n dns2: "    . $_POST['dns2'];
            echo "\n versionsw: "       . $_POST['versionsw'];
            $sql = '
            UPDATE clients
            SET
            mac_conf = "' . $_POST['mac_conf'] . '", dns1 = "' . $_POST['dns1'] . '", dns2 = "' . $_POST['dns2'] . '", versionsw = "' . $_POST['versionsw'] . '
"
            WHERE id = ' . $clientid . ';' ;
            echo "\n SQL: $sql";
            mysql_query($sql) or die('Query failed: ' . mysql_error());
        }
        else if ($_POST['function'] === 'pingtests') {
            mysql_query('INSERT INTO pingtests ( type, time, loss, min, avg, max, unit, client_id ) VALUES ("' . $_POST['type'] . '","' . $_POST['time'] . '","
' . $_POST['loss'] . '","' . $_POST['min'] . '","' . $_POST['avg'] . '","' . $_POST['max'] . '","' . $_POST['unit'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
        }
        else if ($_POST['function'] === 'sec_test') {
            mysql_query('INSERT INTO test_logs ( type, value, time, measure_unit, client_id ) VALUES ("' . $_POST['type'] . '","' . $_POST['value'] . '","' . $
_POST['time'] . '","' . $_POST['measure_unit'] . '","'. $clientid . '")' )
                or die('Query failed: ' . mysql_error());
        }
    }
}

?>
              

Select allOpen in new window

Few corrections

<?php
	error_reporting(E_ALL);
	// connecting, selecting database
	$link = mysql_connect('dbserver', 'xxx', 'xxx')    or die('Could not connect: ' . mysql_error());
	mysql_select_db('myapp') or die('Could not select database');
	
	if (!isset($_SERVER['PHP_AUTH_USER']) && !isset($_SERVER['PHP_AUTH_PW'])){
		header( 'WWW-Authenticate: Basic realm="Input user and password"' );
		header( 'HTTP/1.0 401 Unauthorized' );
		echo "Unauthorized\n";
		exit;
	}

	// making the code more readable
	$user = $_SERVER['PHP_AUTH_USER'];
	$pass = $_SERVER['PHP_AUTH_PW'];
	$mac = "";
	for ($i = 0; $i < strlen($user); $i++) {
		$mac .= $user[$i];
		
		if ($i % 2 && $i < strlen($user) - 1)        $mac .= ':';
	}

	// searching user in database
	$query = 'SELECT id FROM clients WHERE mac = "' . $mac . '" and password = "'. $pass .'"';
	$result = mysql_query($query) or die('Query failed: ' . mysql_error());
	// get user id in case we need it later
	$row = mysql_fetch_array($result, MYSQL_ASSOC);
	$clientid = $row['id'];
	// if query does not have results abort script
	
	if (mysql_num_rows($result) < 1 ) {
		exit;
	}

	// UNAUTHORIZED USER EXIT SCRIPT
	echo 'clientid: ' . $clientid;
	// if you get here you are authorized to download the update
	
	if($_SERVER['REQUEST_METHOD'] === 'GET'){
		// open the update file and retrieve
		$file = fopen("files/updates.txt","r");
		
		if (!$file) trigger_error('UNABLE TO OPEN files/updates.txt FOR READ', E_USER_ERROR);
		while (!feof($file)){
			echo fread($file,1024);
		}

		fclose($file);
	}
	elseif ($_SERVER['REQUEST_METHOD'] === 'POST'){
		
		if (isset($_POST['function'])){
			
			if ( $_POST['function'] === 'add_hop' ){
				mysql_query('INSERT INTO hops ( ip, level, client_id ) VALUES ("' . $_POST['ip'] . '","' . $_POST['level'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
			}
			elseif ($_POST['function'] === 'clean_hops') {
				mysql_query('DELETE FROM hops WHERE client_id = ' . $clientid)
				or die('Query failed: ' . mysql_error());
			}
			elseif ($_POST['function'] === 'client_info'){
				
				$sql = 'UPDATE clients SET
				mac_conf = "' . $_POST['mac_conf'] . '", 
				dns1 = "' . $_POST['dns1'] . '", 
				dns2 = "' . $_POST['dns2'] . '", 
				versionsw = "' . $_POST['versionsw'] . '"
				WHERE id = ' . $clientid ;
				echo "\n SQL: $sql";
				mysql_query($sql) or die('Query failed: ' . mysql_error());
			}
			elseif ($_POST['function'] === 'pingtests') {
				mysql_query('INSERT INTO pingtests ( type, time, loss, min, avg, max, unit, client_id ) VALUES ("' . $_POST['type'] . '","' . $_POST['time'] . '","
' . $_POST['loss'] . '","' . $_POST['min'] . '","' . $_POST['avg'] . '","' . $_POST['max'] . '","' . $_POST['unit'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
			}
			elseif ($_POST['function'] === 'sec_test') {
				mysql_query('INSERT INTO test_logs ( type, value, time, measure_unit, client_id ) VALUES ("' . $_POST['type'] . '","' . $_POST['value'] . '","' . $_POST['time'] . '","' . $_POST['measure_unit'] . '","'. $clientid . '")' )
                or die('Query failed: ' . mysql_error());
			}
		}
	}
?>

Select allOpen in new window

Seems to work as it should!!!

Only error I see is when starting the script side;

md5sum: can't open 'updates.txt': No such file or directory
/tmp/testscript.sh: line 1: clientid:: command not found

No errors on the php side. It updated the proper client and all of the other tables it should.
Testing a little more so I can give input :)

Strange, I just don't see where that error is coming from or why?

It's coming from this bit

    // open the update file and retrieve
    $file = fopen("files/updates.txt","r");
    if (!$file) trigger_error('UNABLE TO OPEN files/updates.txt FOR READ', E_USER_ERROR);
    while (!feof($file)){
        echo fread($file,1024);
    }
    fclose($file);

Select allOpen in new window

Not sure what the purpose of it is but the error is saying the file doesn't exist.

Yes the file does exist which is why I am curious as to why it is showing the error.

Another reason I know this is because it is being retrieved and I do see it on the client.

And updates.txt is in a folder called files relative to the folder the script is in?

correct, it is in files/updates.txt and is in fact getting retrieved as it should.

Probably best to close this question and start afresh as this is a new issue - this has gone on quite some time.

Agreed. I just don't want to post all that code for no good reason again since it's already here.

Can you guys decide/agree on who should get what? I can't believe that you stuck it out as you did and am simply amazed at your tenacity.

I cannot thank you enough.
I went to bed thinking about this and how frustrating it would be to try and solve it once everyone here gave up LOL.

However you see fit.

You don't need to post all the code again. It's probably just a path or permission problem.
You can reference this question if need be.

I really don't know how to award this. There was so much help.
Gary, you certainly didn't quit and had a lot to offer.

You both spent time on sections then @Gary came up with all of the code including re-doing the entire php side but you both agreed that it was at times with each others help.

I wish I could give you both the full points but I don't think that's possible.

Then just do a split.

I hope I got this split right. I wish I could give you both the max points for the way you stuck to this well above and beyond the actual question.

Thank you

I thought I posted a 500 point question but the site seemed to only allow splitting of 167 each?

Something in the php is putting 'clientid: 41' at the very start of the sending script.

clientid: 41#!/bin/bash

That is preventing it from running later.

Whats the sending script?

Sorry, I mean the script on the workstation side.
Once it runs and connects to the php, it then ends up with the clientid in it as noted above.

clientid: 41#!/bin/bash

Maybe the echo statement in //UNAUTHORIZED section is doing that?
Commenting that out fixed the problem but I'm not sure if that's the fix :)

The client id is echoed here which you can remove - it was only there for testing.
echo 'clientid: ' . $clientid;

Are you saying 41 is wrong?

change your cURL back your -o to /dev/null and on the PHP side, comment out your debug (add double slashes // in front, or remove)

Give the most to @gary since he did the most work :)

No no, 41 is correct for sure in this case :).

The client I've been testing is in fact 41 so all good.
The echo statement was sticking it's name just before the bin/bash line, preventing the scripts from running.

It was editing the script basically and doing this to it; clientid: 41#!/bin/bash


        // UNAUTHORIZED USER EXIT SCRIPT
        //        echo 'clientid: ' . $clientid;
        // if you get here you are authorized to download the update

I edited to the above and that problem is now gone.
Just wasn't sure if it was safe to do since I am unclear of certain ramifications when changing things.

One question which was left unanswered... someone asked me to change the 'mac' field in the clients table to 'unique'. Should I change that back now?

No you can leave as it should be unique since it is based on the username which by all accounts should be unique.

Wonderful. Thanks again very much. This has to be the longest question I've seen on this site yet. It was hard to pick the right answers but I decided to pick the ones which actually were based on the original question.