Solved

php mysql insert

Posted on 2014-09-17
21
298 Views
Last Modified: 2014-09-20
I am having a difficult time getting this to work and am looking for a code solution please.

First of all, I am not able to change the db field to a 0 and have tried a number of things but not the correct one :).
Second, it seems to be that I should not need the additional 'reboot_reset' function to do this and should be able
to do all of this in one function.

The requirement is that when I change the field 'reboot_now' from a 0 to a 1, I want the script to reboot the workstation. However, I don't want the workstation stuck in a reboot loop so I want it to confirm and reset the 1 to a 0 before rebooting. This will also tell me that it rebooted by looking in the DB.

The bash side;

function reboot_now()
{
        bootcheck=$(curl -u "1234:1234" --connect-timeout 5 -X POST http://server.com/app.php -F function=remote_reboot)
        if [ "x$bootcheck" = "x" ]
        then
        echo "Could not retrieve boot check configuration"
        return
        fi
        do_test=$(echo $bootcheck | cut -f 1 -d ,)

        if [ "$do_test" != "1" ]
        then

    echo "No remote boot set, continuing"

        return

        fi

    echo "Reset to 0 before rebooting"

        $CURL -F function=reboot_reset -F reboot_now="0"

    echo "Time to reboot the client"

#        reboot
}

Open in new window


The script side;

                else if ($_POST['function'] === 'remote_reboot') {
                    try{
                    $res = mysql_query("SELECT reboot_now FROM clients WHERE id='" . $clientid . "'" );
                        if (mysql_num_rows($res) < 1 ) {
                        exit;
                        }
                            $row = mysql_fetch_assoc($res);
                                echo $row['reboot_now'];
                        }
                            catch( Exception $e )
                        {
                            error_log($e);
                        }
                 }
                else if ($_POST['function'] === 'reboot_reset') {
                    mysql_query('INSERT INTO clients ( reboot_now, client_id ) VALUES ("' . $_POST['reboot_now'] . '","' . $clientid . '")' )
                    or die('Query failed: ' . mysql_error());
                        }
                }

Open in new window

0
Comment
Question by:projects
  • 11
  • 10
21 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40329409
For starters you will need to get off MySQL.  PHP is doing away with the MySQL extension.  This article tells why and what you must do to keep your scripts running.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

To the question of the queries... Do you mean to INSERT new rows or UPDATE existing rows when you toggle the zero and one?
0
 

Author Comment

by:projects
ID: 40329414
I can't, I don't have control over that so need to use what is there now.
And yes, you are right, it is a UPDATE I want to do, to toggle from 1 to 0.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40329431
If you don't have control over the MySQL extension, how can you change the query?  If you can change the query, surely you can upgrade the MySQL extension to something that is still supported!

In order to do your UPDATE, you will need to use a WHERE clause that uniquely identifies the row(s) you want to change.  Do you have a unique identifier, like an AUTO_INCREMENT key?
0
 

Author Comment

by:projects
ID: 40330723
But Ray, as I've said countless times, I'm not becoming a programmer, I am just in a bad position of having to take care of a small software development project with little funds and having to count on programmers to know how to do these things. However, the programmers are piece work, not always the lowest bidder but certainly not the highest one.

I don't even have a clue on how I would go about changing the software unless it's just a few commands that need to be changed and that someone who isn't a programmer could do it.

Otherwise, I have to trudge on with what I have and hope that once it's all completed, that I can then hire one or two people to come in and clean things up. It would be better to post a question related to this than to see my question turn into a confusing document about this topic. Each time the topic changes, my question is lost and I don't find the answer I need so have to re-post it again.

I've posted my question, I am reading the article you wrote and I am already under pressure to get this software completed and working. Once that is done, perhaps then I will have the budget to hire someone who can fix the problems.
0
 

Author Comment

by:projects
ID: 40330743
I wanted to mention above... besides, it will take a while before this is changed over to the point where it can no longer be used so for now, I need to use the current method. Once everything is said and then, then I will convert everything.

Ok, moving forward please.

>In order to do your UPDATE, you will need to use a WHERE clause that uniquely
>identifies the row(s) you want to change.  Do you have a unique identifier, like
>an AUTO_INCREMENT key?

There is an ID field which is auto_increment. The ID field is a unique identifier for the script/workstation.

In the first part of the php page, there is code which looks up the unique client ID based on the username and password of the remotely connecting script. This is the 'client_id' that you see in the code above.
0
 

Author Comment

by:projects
ID: 40330944
As an update, I've looked into this and most everything we have built is using  mysqli and pdo_mysql. The only part which has not been updated yet is the code I've been posting but it will be updated once everything is working.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40330982
Try changing the INSERT query to something like this.  I cannot test it because I do not have the CREATE TABLE statements or the test data, but I think it's close to right.  You probably want to capture the return value and check for affected_rows or use something like phpMyAdmin to examine the clients table and verify that the query worked.
mysql_query("UPDATE clients SET reboot_now = '" . $_POST['reboot_now'] . "' WHERE id= $clientid LIMIT 1");

Open in new window

0
 

Author Comment

by:projects
ID: 40331092
For testing, I have a window open to the DB so I can manually change it and also view the results. I also have a window open to the script and watchi it while also working on the php page.

That said, the code above didn't work. I think it's because the php code already has identified the client script so I'm not sure what the LIMIT 1 means.

The line was causing a number of php errors but I was able to get the script to talk with the php side by changing as follows;

mysql_query("UPDATE clients SET reboot_now = '" . $_POST['reboot_now'] . "' WHERE id='" . $clientid . '")' )

However, while the script seems to be sending the '0', the php side is not making any updates to mysql. It remains at 1.

The script output is;

Reset to 0 before rebooting
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   421  100   166  100   255   2057   3160 --:--:-- --:--:-- --:--:--  3311
Time to reboot the client

No change to the DB and no php errors.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40331133
For better or worse, computer programming is a complex activity requiring meticulous attention to detail.  When I see unbalanced parentheses and missing terminators (the semi-colon) I don't really know what to say.  If you had a programmer doing this project, (s)he would probably want to capture the return value and check for affected_rows.

Please post the CREATE TABLE statement and your test data set.  I'll try to set up a complete example.

By way of explanation, the LIMIT 1 clause tells MySQL to update only one row of the table.  This usually makes sense when you're using the AUTO_INCREMENT key in the WHERE clause, since there can be only one row that matches the key.  Without LIMIT 1, MySQL may have to look at all rows of the table.  That may be release-dependent; not sure.  You use the LIMIT clause to avoid the unwanted overhead of a table scan.  In database programming a table scan is like dog poop.  It's out of place wherever you find it.
0
 

Author Comment

by:projects
ID: 40331307
It's a mess, there is no doubt about that. Many lines have code which ends differently than others and is probably why this thing keeps being so difficult to work with in terms of adding a few little functions here and there.

No choice however, this is what I am stuck with at the moment.

You want me to run CREATE TABLE where? On the table itself or the database?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40331695
In MySQL the CREATE TABLE statement can be gotten with this query.  Put in your table name.  The results set will contain the statement.

$res = mysql_query("SHOW CREATE TABLE `$table`");
0
 

Author Comment

by:projects
ID: 40331773
I sure hope this is sufficient.


clients       CREATE TABLE `clients` (
 `id` int(8) NOT NULL AUTO_INCREMENT,
 `user_id` int(8) NOT NULL,
 `xxx` varchar(15) DEFAULT NULL,
 `xxx` datetime DEFAULT NULL,
 `xxx` varchar(45) NOT NULL,
 `xxx` varchar(45) NOT NULL,
 `xxx` varchar(45) NOT NULL,
 `xxx` varchar(12) DEFAULT NULL,
 `xxx` varchar(50) DEFAULT NULL,
 `xxx` tinyint(4) DEFAULT '0',
 `xxx` varchar(15) DEFAULT NULL,
 `xxx` varchar(15) DEFAULT NULL,
 'xxx` varchar(45) DEFAULT NULL,
 `xxx` varchar(45) DEFAULT NULL,
 `xxx` varchar(45) DEFAULT NULL,
 `xxx` varchar(45) DEFAULT 'Null',
 `xxx` varchar(100) DEFAULT NULL,
 `xxx` varchar(45) DEFAULT NULL,
 `xxx` varchar(45) DEFAULT NULL,
 `xxx` varchar(45) DEFAULT NULL,
 `xxx` varchar(12) DEFAULT NULL,
 `xxx` varchar(12) DEFAULT NULL,
  'xxx` varchar(100) DEFAULT '0',
 `xxx` varchar(100) DEFAULT NULL,
 `xxx` tinyint(4) DEFAULT '0',
 `xxx` tinyint(4) DEFAULT '10',
 `xxx` int(11) DEFAULT NULL,
 `xxx` int(11) DEFAULT NULL,
 `xxx` varchar(50) DEFAULT NULL,
 `reboot_now` tinyint(4) DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `mac` (`mac`),
 KEY `user_fk_idx` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=latin1
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40332557
This cannot possibly be correct - you can't have two columns with the same name in a table.  It's much easier to help you get the right answers if we receive true, correct and complete information.
0
 

Author Comment

by:projects
ID: 40333341
Where do you see two columns with the same name???
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40333396
 `xxx` varchar(15) DEFAULT NULL,
 `xxx` datetime DEFAULT NULL,

Open in new window

0
 

Author Comment

by:projects
ID: 40333411
Of course not. As I mentioned, I hoped this was sufficient because I cannot post everything on a public site. I post everything I can however. I thought you just wanted to see the field types.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40333484
You really want to get help from a programmer for this one.  It's going to be quick and easy for someone who has the knowledge, skills and abilities and can get "hands on" with the machinery and software.

The query you probably want says something like this.

UPDATE clients SET reboot_now = '0' WHERE id = '$clientid' LIMIT 1

Open in new window

Best regards, ~Ray
0
 

Author Comment

by:projects
ID: 40333978
That did the trick. Thank you very much Ray.

I'll try to explain partly what is going on since you respond to many of my questions.
I don't have the skills of a programmer but with some help here and there, especially this site, I have been able to work on completing a client/server application.

There have been many other parts to this project and once I've had an example working, I then hired a professional to go over the code.

I quickly learned earlier this year that when you let programmers build your ideas from scratch the costs are many many times more than what the project could otherwise cost if you have an example.

The code you've noticed which doesn't have the proper use is pretty much the last program that I need to complete so that I can then hire someone to go over it, tidy things up, make sure it's up to date, secure etc but I won't have to rely on that person to try and figure out what we need since they will have a fully working example.

So basically, you've got a non programming doing some programming so that I can build an example project. All this also partly because many programmers wasted our budget early on this year so I had to find a creative way of achieving the project.

Thanks again.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40334009
Good points, all.  Maybe your "hired gun" programmers are not the best of breed.  You're always welcome back here at E-E, and we have many professionals who can step in to help.  No magic bullets, but we can often help. ~Ray
0
 

Author Comment

by:projects
ID: 40334442
>non programming doing some programming
I meant non programmer :)

And yes, maybe we didn't hire the best but there is no real way of knowing until someone starts working for you. I have paid from $12 to $130 per hour to get the exact same results.

I have one part of this project which was created by one person, who did a wonderful job but then had life problems so abandoned. This was taken over by someone else who said the first guy didn't know what he was doing so 'fixed' it up then abandoned us. I hired another person to try and continue, who told me the last guy wrecked everything and after a week, abandoned us. Now I have someone else who says the first guy did an amazing job but the others completely ruined his work so now this new person spent a week just putting together documentation on where things stand, what needs to be done and why we need to nuke everything everyone did since the first guy so we can get back to the original work then start over going forward from there. LOL... it's been insane. That part alone has cost many times what it could have had I found the right person from the start, which I had.

People can talk up a storm about themselves, have the most amazing resumes and once they start working, it all comes to light but unfortunately, it's after you've paid them a fair amount of money. I tried escrow services, milestones, money down, balance later, you name it, money still ends up wasted. Most have been screwed over at one time or another so refuse to work unless something is paid up front. You have to trust sometimes but unfortunately, it doesn't always pay off.

This has been one of the most stressful things I have ever taken on. In my fifties and I never wanted to feel this kind of stress again but I believe in what I am working on so have tried hard to stick to it no matter what.

This site has been pure gold for a guy like me who is out of my element but needing to get something done. After I figure something out and get it working to some extent, then get stuck, that's when I post once I've exhausted Google and other searches for solutions.

Anyhow... back at it... Saturday no less.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40334471
When you get ready to remodel a house, join Angie's List and use only the highly-recommended contractors.

My rate is $90.  You won't get $12 results from me.  When you hire the next programmer, ask for references, ask to see code samples and ask them to show you their contributions to sites like E-E and StackOverflow.  If they can't produce, you'll know before you spend any money, not after.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now