php mysql insert

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.
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

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

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.

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?

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.

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.

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.

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");

Select allOpen in new window

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.

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.

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?

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`");

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

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.

Where do you see two columns with the same name???

 `xxx` varchar(15) DEFAULT NULL,
 `xxx` datetime DEFAULT NULL,

Select allOpen in new window

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.

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.

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

>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.

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.